Efcore: Raw store access: Support execution of SQL that returns results but is not composable on the store, e.g. stored procedures

[divega]

We should provide a simple mechanism to execute queries/commands such as regular stored procedures that contain a SELECT statement. What FromSql() does won't work for those because you cannot used them as sub queries.

A few things we could use:

1. Provide an API akin FromSql() but switch composition to in-memory if subsequent LINQ operators are applied. Same warning and kill switch for query evaluation we have talked about having for other queries would apply to these.
2. Provide a completely separate API pattern that is meant for non-composable results and even not return IEnumerable<T> but a new type that implements GetEnumerator() and AsEnumerable()
3. Stick the stored procedure results into a table-valued variable and then keep composing as normal. This would move processing to the server and make composition more seamless but would most likely not make it any more efficient than option (1).
4. Recognize the token “EXEC” in FromSql() (which is pretty standard although no longer required) for SQL Server as an indication that what follows is a non-composable stored procedure and that needs to go through a different execution mode (e.g. the one described in (1) or the one described in (3)).

[anpete]

See here for some ways to make it work: http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure

[anpete]

We should consider providing an optional argument allowing specification of a SQL statement that would be run just before the actual SQL query. This would allow for things like declaring TVVs and temp tables etc. that could then be referenced from the query.

[divega]

We had another conversation around this:

If we add the ability in the value reader to infer a map between field names and field positions on first execution, we can execute stored procedures as well as raw SQL queries without any modification or wrapping as long as they have not been composed over using LINQ.

We see this as a good thing: it will very likely be more performant/scalable for stored procedures because it avoids buffering the results on the server, and it avoids fiddling with the user query for raw SQL, making FromSql() without composition equivalent to the old SqlQuery().

If LINQ operators are applied then we can:

• For raw SQL queries keep doing what we are currently doing: wrap a SELECT around them so that we can alias them and reorder fields.
• For stored procedures we can push the results into a table-valued variable to make it composable.

[anpete]

@divega We do this already at query compile time for TPH shapers: https://github.com/aspnet/EntityFramework/blob/dev/src/EntityFramework.Core/Metadata/Internal/EntityMaterializerSource.cs#L75

We don't need to change value reader itself as this is easily achieved by wrapping the underlying value reader in an index re-mapping decorator.

However, a likely better approach is to just delay shaper construction until query run time. An easy way to do this would be to generate a shaper stub into the query execution function.

[divega]

Sounds great. Let's chat about it in more detail with @mikary when you are around.

[mikary]

Added logic for forcing client evaluation on stored procedures in #2053
Still need to update materialization to address out of order and extra columns.

[mikary]

Materialization issues for stored procedures was fixed by 4f05fbfed3bfddf5228b82e21c8e535b9f1d8775

[Vasim-DigitalNexus]

How to pass "named" parameters in FromSql()?

Example:

object[] sqlParams = {
new SqlParameter("@Id", 45),
new SqlParameter("@Name", "Ada")
};

var UserType = dbcontext.Set().FromSql("dbo.SomeSproc", sqlParams)

If SqlParameter is not supported then something like this would suffice:

var parameterDictionary = new Dictionary
{
{"@Id", 45},
{"@Name", "Ada"}
};

[divega]

@mikary can you reply?

[mikary]

We don't currently have support for named parameters in FromSql and ExecuteSqlCommand. There are plans to support other patterns in the future (i.e. SqlParameter, DbParameter, anonymous types), but for now you should be able to use the somewhat clunky pattern that we use in FromSqlSprocQuerySqlServerTest:

var userType = dbContext.Set().FromSql("dbo.SomeSproc @Id = {0}, @Name = {1}", 45, "Ada");

[minteractive]

Hi,
Is there a support to execute a stored procedure that returns multiple result sets?

[rowanmiller]

Not at this stage, you would need to drop down to ADO.NET for that

[tiwariarvin]

Hi Rowanmiller - Is there a plan to resolve support to execute a stored procedure that returns multiple result sets? (or) Is that already resolved?

[tiwariarvin]

Hi Rowanmiller - Did you had a chance to check this? I am curious because I see some serous performance gains.

[rowanmiller]

@tiwariarvin - We will support multiple result sets in the future, but at this stage there is no support for them. Stored procedure support in general is tracked by https://github.com/aspnet/EntityFramework/issues/245.

[tiwariarvin]

Thanks, will keep an eye.

[verdie-g]

I have made this to call stored procedure and map the DataReader to a specified model. I hope it helps.