Is there something that can return IQueryable
for a dynamic sql query in Entity Framework 6?
This is what I am using now but it is pulling all the records (as expected).
DbContext.Database.SqlQuery<T>("SELECT * FROM dbo.SomeDynamicView")
Problem is that SqlQuery
returns DbRawSqlQuery
which is IEnumerable
.
dbo.SomeDynamicView
is a database view created at runtime.
GetList(context) might return an object backed by a custom LINQ provider (like an Entity Framework collection), then you probably want to leave the data cast as an IQueryable: even though your benchmark shows it being 20 times faster to use a list, that difference is so small that no user is ever going to be able to ...
Once the breakpoint is hit hover over query to expand then click on > Debug View > The dropdown beside the magnifying glass> Text Visualizer. There you have it, the SQL that will be sent to the database. Text Visualizer option showing the query generated by EF Core 5.
IEnumerable: IEnumerable is best suitable for working with in-memory collection (or local queries). IEnumerable doesn't move between items, it is forward only collection. IQueryable: IQueryable best suits for remote data source, like a database or web service (or remote queries).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With