Best way to call stored procedures that return results in EF Core?

Groaner

I am working on the persistence layer for a new project (I guess the cool kids are calling them "repositories" these days? Certainly a great choice in terminology and no one will ever confuse them with the things that store source code.) and naturally want to be using cutting-edge stuff. The last time I dug deep into Entity Framework was back in the .edmx days, when you could point a designer at a database and BOOM all your stored procedures are imported as callable methods which return strongly-typed classes mapped to any result sets.

Apparently that's not a thing anymore in EF Core.

As far as I can tell, FromSqlRaw/FromSqlInterpolated are the way to go:

var results = dbcontext.Widgets.FromSqlRaw("dbo.BestestStoredProcEver @first_parameter, @second_parameter", firstParameter, secondParameter).ToList();

Except this method sucks not only because it feels like writing raw SQL to call a stored procedure, but because carves the parameter order in stone. There is a clunky workaround, I suppose:

var results = dbcontext.Widgets.FromSqlRaw("dbo.BestestStoredProcEver @first_parameter = @p0, @second_parameter = @p1", firstParameter, secondParameter).ToList();

This is almost workable except it's going to get messy once you have about a dozen parameters. The old ADO way feels a lot cleaner because you're only specifying a procedure name and parameters:

using(SqlConnection connection = new SqlConnection(connectionString))
using(SqlCommand cmd = new SqlCommand("dbo.BestestStoredProcEver", connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@first_parameter", firstParameter);
cmd.Parameters.AddWithValue("@second_parameter", secondParameter);

 ...
}

I believe the latter also has the advantage of being able to support things like table types (SqlDbType.Structured). But, the ADO way means that you're dealing in DataSet/DataTable and have to come up with your own mappers for the procedure results. Naturally, my preference is not to have to do this because I have maintained those classes in previous lives and it sucked:

var results = new List<Widget>();

foreach(var row in dt.Rows)
{
Widget w = new Widget();

w.Foobar = Convert.ToString(row["foobar"]);
w.BazID = Convert.ToInt32(row["baz_id"]);
w.QuuxDate = Convert.ToDateTime(row["quux_date"]);
...
 snip 30 more columns
...

results.Add(w);
}

There's also the problem of mapping a class to procedure parameters, for which neither method offers an easy solution. Ideally, I would like to be able to do something like this:

public void SaveWidget(Widget w)
{
dbcontext.CallSaveProcedure("dbo.save_widget", w);
}

And CallSaveProcedure() would map Widget fields to the correct parameters. One thing I've thought about is tagging fields/properties to accomplish this goal:

[InputProcedureParameter("@foobar")]
public string Foobar { get; set; }

...but then you're dealing with reflection and reflection is apparently evil. Is it a greater or lesser evil than modules full of hand-written mapping assignments, though?

What I'm thinking I might do is support both the EF way, which returns a strongly-typed collection of objects, and the ADO way, which may be a better fit for the ExecuteNonQuery() type operations. It's a shame that the old database-first designers don't seem to be available, as those offer a better solution all around.

Or is there a better way?

And before anyone asks, "Why are you using stored procedures in 2022? Just do direct inserts into the tables and call dbcontext.SaveChanges(), man! That's what EF is for," yes, I do know all about that. Unfortunately, that approach also has drawbacks, and we will need to be able to call stored procedures irregardless.

Tsaukpaetra

@Groaner said in Best way to call stored procedures that return results in EF Core?:

FromSqlRaw/FromSqlInterpolated

I'm kinda glad I'm still in Entity Framework 5....

izzion

@Groaner
In terms of using a stored procedure for data retreival when the stored procedure is the full result set you care about, I have prevously used DbSet<TEntity>.FromSql() to map the result set to our POCOs so we could use them in subsequent CLR code. https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationalqueryableextensions.fromsql?view=efcore-3.1

Though it looks like those are obsolete in favor of FromSqlRaw/Interpolated, so you're probably on more of the right track.

It does look like FromSqlRaw supports an overload that takes an array of SqlParameter objects, so you could probably get close to the ADO way:

var results = dbContext.FromSqlRaw<MyPoco>("dbo.StoredProcedure @CriticalParam, @UserSearchParam, @DoLog", 
                                           new SqlParameter[] { 
                                               new SqlParameter("@CriticalParam", 1),
                                               new SqlParameter("@UserSearchParam", "Foo"),
                                               new SqlParameter("@DoLog", false),
                                           });

ETA: Also, I bet $5 that my syntax for the inline array is wrong, I always get it wrong in both C# and JavaScript from moving back and forth between the two, and I didn't run this one through an IDE to tell me I'm stupid.

Groaner

@izzion said in Best way to call stored procedures that return results in EF Core?:

Though it looks like those are obsolete in favor of FromSqlRaw/Interpolated, so you're probably on more of the right track.

Yeah, FromSql doesn't even show up in Intellisense (.NET 6, VS 2022). This also seems to be unusually rapid deprecation by MS standards.

It does look like FromSqlRaw supports an overload that takes an array of SqlParameter objects, so you could probably get close to the ADO way:

var results = dbContext.FromSqlRaw<MyPoco>("dbo.StoredProcedure @CriticalParam, @UserSearchParam, @DoLog", 
                                           new SqlParameter[] { 
                                               new SqlParameter("@CriticalParam", 1),
                                               new SqlParameter("@UserSearchParam", "Foo"),
                                               new SqlParameter("@DoLog", false),
                                           });

I've seen a few variations of that in my research. I think I'm going to go with an approach with named parameters (e.g. exec dbo.myProc @CriticalParam=@p0, @UserSearchParam=@p1, @DoLog=@p2) so that reordering parameters in the SP definition does not break the call.

ETA: Also, I bet $5 that my syntax for the inline array is wrong, I always get it wrong in both C# and JavaScript from moving back and forth between the two, and I didn't run this one through an IDE to tell me I'm stupid.

That actually looks correct. Try moving between PHP, JS, and C# and it really messes with your head.

Groaner

@Tsaukpaetra said in Best way to call stored procedures that return results in EF Core?:

@Groaner said in Best way to call stored procedures that return results in EF Core?:

FromSqlRaw/FromSqlInterpolated

I'm kinda glad I'm still in Entity Framework 5....

There are some things I miss from .NET 4.x but being able to natively run a .NET app on a Linux VM/container is pretty neat.