If I ignore a dbset used for a procedure I cannot use EF Core to get that procedure

Question

I have an EF Core 2.2 application that is working fine. Then I introduce procedures and know I can do this:

...In myContext

public virtual DbSet<MyProcsDbSet> MyProcsDbSet{ get; set; }

Example call to get data:

using(var context = myContext())
{
   var data = context.MyProcsDbSet.ExecuteSQL("Myproc @p0", 1);
}

This should be noted this works fine. However, when I go to create a new migration it generates a table. Fine no biggie, I will just state in the On Model Creating not to build that table.

bldr.Ignore<MyProcsDbSet>();

Nope, now when I call my procedure that was just working I get this:

Cannot create a DbSet for 'MyProcsDbSet' because this type is not included in the model for the context.

Is there a way to get a dbset for procedure returns and not have to forever suppress the want of ef core to create tables? The ability of .NET Core EF code first always seems like it's biggest drawback was with the custom objects and their creation and retrieval.

Answer 1

Ignore (or [NotMapped] attribute) is definitely not for suppressing table generation. It basically is telling EF Core to not consider the class and it's properties as part of the model, hence it can't be used in LINQ to Entities queries and other EF Core provided services.

The only option to achieve your goal in EF Core 2.x is to map the SP result class as query type:

In addition to entity types, an EF Core model can contain query types, which can be used to carry out database queries against data that isn't mapped to entity types.

Query types have some limitations, but at the same time by default are not mapped to a database object, hence are perfect for using with FromSql.

All you need is to change the definitinion from DbSet to DbQuery:

public virtual DbQuery<MyProcsDbSet> MyProcsDbSet{ get; set; } 

In case you have fluent configuration, make sure to use modelBuilder.Query<MyProcsDbSet> instead of modelBuilder.Entity<MyProcsDbSet> .

---

In EF Core 3.0 the above will change, because Query types will be consolidated with entity types. So basically you'll have to use DbSet (and modelBuilder.Entity) combined with HasNoKey fluent API. It's too early to say what will be the exact behavior, but I would expect the keyless entity types (as they call them in 3.0) by default won't map to table, or there would be a special fluent API to tell that the entity has no associated table. Which would have been useful in 2.x - as I mentioned in the beginning, Ignore has a different meaning, so having something like HasNoTable(), or [Table(null)], or .ToTable(null) to explicitly suppress table generation would have been nice.

But it is what it is, so either use query type, or manually remove the "table" and related commands from the generated migrations.

Answer 2

In previous versions there was DbQuery which is deprecated with EF Core >= 3.0

builder.Entity<MyProcsDbSet>.HasNoKey() doesn't exclude the DbSet from creation.

The only solution is either modify the generated migration

or add builder.Ignore<MyProcsDbSet>(); and comment it just after the generation of the migration.