CODEWITHSUNDEEP
c#sqlentity-framework-6system.linq.dynamic
Krk Črn
Krk Črn

Reputation: 153

Mixing raw SQL with IQueryable for dynamic filter

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.

Upvotes: 6

Views: 7166

Answers (2)

Krk Črn
Krk Črn

Reputation: 153

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#.

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

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.

Upvotes: 0

Related Questions