Mixing raw SQL with IQueryable for dynamic filter

Question

In entity framework 6 is it possible to mix raw SQL generated dynamically with IQueryable like this:

IQueryable<Tree> tree_query = context.Trees.Where(t=>t.Height> 2);
IEnumerable<int> tree_additional_filter = context.Database.SqlQuery<int>("SELECT Id FROM TREE_VIEW WHERE Width < 1");

IQueryable<Tree> final_query = from tree in tree_query 
                               join filtering_tree in tree_additional_filter on filtering_tree.id equals tree.id
                               select tree;

This produces a result as is, but the "tree_additional_filter" is executed in the database in order to construct the final_query. How can I make entity framework construct only one query from this?

I need this to create dynamic filter fields which work together with static ones.

I also tried creating TREE_VIEW entity with only Id column, which I know to always be there. Used System.Linq.Dynamic to construct "where" clause dynamically on a TREE_VIEW entity which has only Id property, but apparently expressions can't be built if properties do not exist in the Type.

This code *doesn't* mix queries. Those are two separate queries. The results of the first query are stored in `tree_additional_filter` — Panagiotis Kanavos, Oct 07 '19 at 15:26
How can I make tree_additional_filter an Iqueryable to add to the other query? — Krk Črn, Oct 07 '19 at 15:28

score 0 · Answer 1 · answered Oct 07 '19 at 15:33

0

In entity framework 6 is it possible to mix raw SQL generated dynamically with IQueryable like this:

No. Notice that Database.SqlQuery returns an IEnumerable<T>, not an IQueryable<T>. Therefore any additional query expressions will be executed against the query results using LINQ to Objects.

Query Composition with raw SQL queries was introduced in EF Core.

answered Oct 07 '19 at 15:33

David Browne - Microsoft

80,331
6
39
67

Can I use reflection somehow to construct the tree_view entity on the fly and attach it to the context so I could trick entity framework into constructing the query? Through dynamic linq or otherwise? – Krk Črn Oct 07 '19 at 16:06

score 0 · Accepted Answer · answered Oct 08 '19 at 13:38

I managed to do it.

Using Dynamic type generation to create a type (NewDynamicType) from fields which I got selecting top 1 from my TREE_VIEW. Attached the NewDynamicType to the db context via DbModelBuilder.RegisterEntityType in OnModelCreating.
With System.Linq.Dynamic then I could construct IQueryable selecting from context.Set(NewDynamicType) any fields the user wants to filter by.
Join into final_query like in my question.

Now my HTML form gets fields from the database view and in each distibution I can have different filters defined without having to write any c#.

Mixing raw SQL with IQueryable for dynamic filter

2 Answers2