.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.

Gelid

Is your query dynamic or static? – Peppy

It is dynamic query – Gelid

Unfortunately, there are some limitations of Ef Core 3.x. We have two options two map our queries: 1 - map query result to an Entity (per table). 2 - create a View in the database and then map a custom object to that view. Perhaps the second option is better to do the trick in order to map your custom type without creating the table. – Peppy

Thanks much, may be now i will discuss internally with our team and decide whether to stay with obsolete code(not acceptable) or move to view concept. – Gelid

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);

Cymogene

I did use ToView before but i didn't know about this sort of hack you can pass null to it. Interesting – Omar

For me using the KeylessAttribute fixed the issue. – Delphinedelphinia

@Delphinedelphinia yes, the KeylessAttribute was introduced in efcore 5.0 for anything prior to that, we have to use .ToView(null) as given in the answer. Please check the first note in learn.microsoft.com/en-us/ef/core/modeling/… – Reflexion

Answer 2

To exclude the model/class/entity from the code first approach while doing the migration in .net core, please check this. For example, the Complex Type class is used to map the stored procedure results. while doing the migration, we don't want to create a complex Type table in the DB, then we should exclude it from the migration.

 protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Ignore<PMEDetail>();
            modelBuilder.Entity<PMEDetail>().ToTable(nameof(PMEDetail), t => t.ExcludeFromMigrations());
            modelBuilder.Entity<PMEDetail>(entity => entity.HasNoKey());  
        }

Here PMEDetail class is a complex type of class, excluding the migration in OnModelCreating

Snore