EFCore Creates Unnecessary Table from Data Model used to call SPROC,

Question

I'm calling sprocs from asp.net core using Entity framework core in an MVC/Razor/WebAPI app. Newer dev so apologies if this is nube. I have read everything I can find on sprocs from EF...

Issue: When I setup my app to call a sproc, EF Core updates my DB (via the next Migration I run) to create an empty table based on the model I created to retrieve output from the sproc.

Example: I have a a sproc that returns two columns

SELECT customerID, customerName from MyTable

I add a simple POCO MyModel using [Keyless] directive

namespace MyApp.Models 
{
    [Keyless]
    public class MyModel 
    {
        public int customerID { get; set; }
        public string customerName { get; set; }
    }
}

I then register the class in my ApplicationDBContext

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

I can now call the sproc from my pages using:

public List<MyModel> CustomerList { get; set; }

and

CustomerList = _context.MySproc
                     .FromSqlRaw($"MySproc")
                     .ToList();

When I next run a migration, EF adds migration code to create a table based on my model, which I don't believe I need.

migrationBuilder.CreateTable(
     name: "VerifyEmailAndStripeCustomerID",
     columns: table => new
     {
     customerID = table.Column<int>(type: "int", nullable: false),
     customerName = table.Column<string>(type: "nvarchar(128)", maxLength: 128, nullable: true)
     },

How can I call my sprocs without this table creation behavior?

score 0 · Accepted Answer · answered Feb 02 '21 at 08:11

Inspired by: execute stored procedure in entity Framework Core without expecting map to dbset

[Keyless] merely specifies a table that doesn't have a primary key - so you need to go one step further on model creating to tell it not to create the backing table:

Remove the DbSet<MyModel> property from your contex (you may be able to leave it, but it would confusing since it's not backed by a table...)
In the OnModelCreating of the context, register and/or manipulate MyModel

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

Populate the model with the following:

CustomerList = Context.Set<MyModel>().FromSql($"MySproc").ToList();

Did this answer you question? – Lacutah Feb 03 '21 at 16:11 — Lacutah, Feb 03 '21 at 16:11

EFCore Creates Unnecessary Table from Data Model used to call SPROC,

1 Answers1