0

I have a stored procedure that returns data from a multi-table query. To do this do I need to create a DbSet for each of the tables that are involved in the query? All the examples I find that use FromSql have a DbSet (e.g., Books in the below example) specified before the FromSql clause.

using (var context = new SampleContext())
{
    var books = context.Books
        .FromSql("EXEC GetAllBooks")
        .ToList();
}

My understanding is a DbSet represents an table. Note that I am working against an existing DB so am not using EF to generate the tables.

Thanks,

MyDisplayName
  • 223
  • 5
  • 12
  • We're executing our SP's with ADO.NET and copy the result data by ourself into our domain objects. Works fast enough for us. – user743414 Nov 22 '19 at 14:48
  • You can do the above but you wont be able to return an object graph in a single stored proc call. Also you do not need a DbSet, you could also use `context.Database.SqlQuery` but you *should* still provide explicit mapping in your DbContext for the object to table mapping. – Igor Nov 22 '19 at 14:54
  • No, you don't need to use `DbSet` to call stored procedures. There are a few ways you can accomplish this. See answers here: https://stackoverflow.com/questions/20901419/how-to-call-stored-procedure-in-entity-framework-6-code-first – Canica Nov 22 '19 at 14:56
  • A DbSet is all the tables of a Database. So if you are using one database you should only have one dataset. – jdweng Nov 22 '19 at 16:01
  • @jdweng What you are talking about is `DbContext`. EF Core `DbSet` is different thing - represents table, view or just queryable source inside the database. – Ivan Stoev Nov 22 '19 at 16:08
  • The OP said " I need to create a DbSet for each of the tables" – jdweng Nov 22 '19 at 16:20

1 Answers1

0

I'm assuming EFCore given the tag and usage of FromSql.

For EFCore 2.1+ I would use a DbQuery (https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.dbquery-1?view=efcore-2.2).

For EFCore 3.0, which has deprecated DbQuery, the replacement is a keyless DbSet (https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types).

I've done the former with stored procs and it works as you'd expect.

rgvlee
  • 2,773
  • 1
  • 13
  • 20
  • Thanks! I would still have to create an entity model to map the results of the stored procedure, correct? Do I also need to create a migration for this too? – MyDisplayName Dec 04 '19 at 14:02
  • I got it to work. Created a DbSet with an entity model that matched the results of the stored procedure. Also changed FromSql to FromSqlRaw. – MyDisplayName Dec 04 '19 at 14:25
  • Yes, you will have to create a model. – rgvlee Dec 04 '19 at 14:26
  • In terms of a migration, that depends. Efcore can't automatically generate the code in this case so you'd have to create an empty migration and add the code to up/down the stored procedure yourself. I didn't do it this way as we migrated using another tool. – rgvlee Dec 04 '19 at 14:26
  • I working against a database that already contains the stored procedures I need to work with. Trying to understand what is required. Thx. – MyDisplayName Dec 04 '19 at 15:59
  • If the stored proc already exists outside of your code first migrations, then there is no need to create a migration for it. – rgvlee Dec 04 '19 at 23:52