Using SqlQuery to get IQueryable
I know this question is about EF6 but in case somebody else stumbles upon this question but interested in EFCore, this is actually possible there.
- Source from Docs
Most simple example:
var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
.FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
.ToList();
Parameters:
var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
.FromSqlRaw("EXECUTE dbo.GetMostPopularBlogs @filterByUser=@user", user)
.ToList();
More complex example (this is more specifically what this question is asking for):
var searchTerm = ".NET";
var blogs = context.Blogs
.FromSqlInterpolated($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
.Where(b => b.Rating > 3)
.OrderByDescending(b => b.Rating)
.ToList();
Resulting query for more complex example:
SELECT [b].[Id], [b].[Name], [b].[Rating]
FROM (
SELECT * FROM dbo.SearchBlogs(@p0)
) AS b
WHERE b."Rating" > 3
ORDER BY b."Rating" DESC
A few notes about this method:
- You effectively have to do a
SELECT *
to make sure all columns are returned from the table. - Joins to pull back additional data don't work, but
Include
does in normal SQL queries (not necessarily stored proc/function calls). - Often times your raw Sql will be put into a subquery. So make sure it's valid for such things (i.e. semicolons, order bys, hints, etc. can cause problems).
No, you can't get a IQueryable
from SqlQuery
*, this is because what IQueryable
is doing is building a SQL string dynamically based on what select and where filters you put in. Because in SqlQuery
you are providing the string Entity Framework can not generate that dynamic string.
Your options are either dynamically build the string your self to pass in to SqlQuery
and use it as a IEnumerable
instead of a IQueryable
or use a DbSet
in your DbContext
and do the more "normal" way of letting entity framework build the query for you.
* You technically can by calling AsQueryable() on the result, but that is just a IEnumerable pretending to be a IQueryable, it does not give you any of the benefits of using a "Real" IQueryable like only retrieving the needed rows from the server.