.Net core 3.x Keyless Entity Types avoid table creation

Question

I need to execute a complex sql query in entity framework core 3.1.1, on researching i found out that keyless entity types is the way to go in code first approach. I see lot of documents for dbquery but this is marked as obsolete in .net core 3.x

keyless entity types

As per Microsoft documentation it says dbquery is obsolete so use dbset approach instead, but with dbset it is trying to create a new table in database. how to disable table generation in keyless entity types while applying migrations?

Sample code

public class ApplicationContext : DbContext
{
 public DbSet<CustomQuery> CustomQuery { get; set; }
 protected override void OnModelCreating(ModelBuilder modelBuilder)
 {
    modelBuilder.Ignore<CustomQuery>();
    modelBuilder.Entity<CustomQuery>().HasNoKey();
 }
}

with .net core 2.2

var entity = _context.Query<CustomQuery>().FromSqlRaw(Regex.Unescape(selectQuery)).AsNoTracking().FirstOrDefault();

with .net core 3.1

var newEntity = _context.CustomQuery.FromSqlRaw(Regex.Unescape(selectQuery)).AsNoTracking().FirstOrDefault();

if i try to apply migrations then a new table in the name of custom query is being created, but I don't need this to happen. because this is just an model used to hold the values from the join query and i will not insert, update or delete the values in this table. how to achieve this?

or is there any better approach for this situation.

Answer 1

This is a known defect in EF Core 3, reported here 3.0 Upgrade - Entity with HasNoKey() (formerly a query type) tries to create tables when adding migration #18116.

Closed as "duplicate" of To vs From methods: Proposal to rationalize ToTable, ToQuery, ToView, FromSql, and other related methods #17270 and Ability to exclude/skip/ignore parts of the model from migrations so that a table is not created (for overlapping bounded contexts) #2725, both scheduled for 5.0 release, which means it would eventually be addressed in that release.

The current workaround is mentioned in the comments by one of the EF Core team members:

For now, you can just use something like .ToView("You forgot to use FromSql with ModQueueEntry")

or more generally, using .ToView(null), e.g.

modelBuilder.Entity<CustomQuery>().HasNoKey().ToView(null);