Mixing raw SQL with IQueryable for dynamic filter

c# entity-framework-6 sql system.linq.dynamic

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.

1
0
10/7/2019 3:25:34 PM

Accepted Answer

I managed to do it.

  1. 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.
  2. With System.Linq.Dynamic then I could construct IQueryable selecting from context.Set(NewDynamicType) any fields the user wants to filter by.
  3. 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#.

0
10/8/2019 1:38:04 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow