-
Notifications
You must be signed in to change notification settings - Fork 3.2k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Raw store access: Support execution of SQL that returns results but is not composable on the store, e.g. stored procedures #1864
Comments
See here for some ways to make it work: http://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure |
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. |
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 If LINQ operators are applied then we can:
|
@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. |
Sounds great. Let's chat about it in more detail with @mikary when you are around. |
Added logic for forcing client evaluation on stored procedures in #2053 |
Materialization issues for stored procedures was fixed by 4f05fbf |
How to pass "named" parameters in FromSql()? Example: object[] sqlParams = { var UserType = dbcontext.Set().FromSql("dbo.SomeSproc", sqlParams) If SqlParameter is not supported then something like this would suffice: var parameterDictionary = new Dictionary<string, object> |
@mikary can you reply? |
We don't currently have support for named parameters in var userType = dbContext.Set().FromSql("dbo.SomeSproc @Id = {0}, @Name = {1}", 45, "Ada"); |
Hi, |
Not at this stage, you would need to drop down to ADO.NET for that |
Hi Rowanmiller - Is there a plan to resolve support to execute a stored procedure that returns multiple result sets? (or) Is that already resolved? |
Hi Rowanmiller - Did you had a chance to check this? I am curious because I see some serous performance gains. |
@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 #245. |
Thanks, will keep an eye. |
I have made this to call stored procedure and map the DataReader to a specified model. I hope it helps. |
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:
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.IEnumerable<T>
but a new type that implementsGetEnumerator()
andAsEnumerable()
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)).The text was updated successfully, but these errors were encountered: