How Do I query for data using raw sql in a "non entity type format"

[ajcvickers]

Moved from discussion #22214

Question

From

I want to pull some data from the db, the SQL to produce it is complicated and the resulting format of that data is not matching a table in the DB.

So how do I tell EF "build this entity set but don't make a table for it".

Example method defined on my DbContext class ...

public IEnumerable<TopCompany> TopCompanies(string system, Perspective perspective, string calendarMonth)
{
    var period = DateTimeOffset.Parse($"{calendarMonth}-01");
    return Set<TopCompany>().FromSqlRaw(" <blob of SQL> ", system, period, period.AddMonths(1));
}

Configured using ...

void ConfigureModel(ModelBuilder builder)
{
    builder.Entity<TopCompany>().HasNoKey();
    ...

I thought this would be enough ... If I make this a DbSet will it not attempt to construct a table in the DB for it?

Replies

From @TehWardy

Never mind ... I just need to read more before asking questions ...
https://stackoverflow.com/questions/35631903/raw-sql-query-without-dbset-entity-framework-core

From @ErikEJ

If you want to avoid "pulluting" your DbContext, you can use this approach:
https://github.com/ErikEJ/EFCorePowerTools/blob/master/src/GUI/RevEng.Core/DbContextExtensions#L10

From @TehWardy

Interesting idea ... but the query in question is specific to a particular context / db.
I just couldn't understand why I had to declare a DbSet<> which I natuallr y think of as a "thing that exists in the db" when this was clearly just a computed set that didn't.

I thought Query,FromSql() like we had in 2.2 was a good idea and was disappointed to see it go.