/ EntityFramework

Entity Framework Composable Queries using LinqKit

One of the biggest drawbacks to SQL is the inability to reuse pieces of a query. Technically you can create views but then you wind up joining views to views consuming views and performance goes down the drain.

What if there was away to write a query once and then reuse that query anywhere that you needed that data and have the query injected into each of the queries. Changing one query suddenly changes all of them. Which maintains the DRY principal and once you have a query figured out nobody else has to rewrite the same thing.

With Entity Framework this seems like a simple solution. You write a query in a method and call the method inside of Entity Framework. Seems to make logical since, but when you run it you get an error informing you that there is an unknown method in the query. So now what...

Enter LinqKit https://github.com/scottksmith95/LINQKit...
LinqKit is a project that intercepts an expression and modifies it from something that entity framework will throw an error on to something that it can use.
it allows you to compose queries and reuse them so that you can apply the DRY prinicpal to your queries

For this example we are going to have a parent
each parent has a collection of children
each child has a collection of schools that are the schools that the child has attened

public class Parent
{
   public int Id{get;set;}
   public string name{get;set;}
   public virtual ICollection<Child> Children {get;set;}
}
public class Child
{
    public int Id{get;set;}
    public int ParentId {get;set;}
    public int Age{get;set;}
    [ForeignKey(nameof(ParentId))]
    public Parent Parent{get;set;}

    // many to many
    public ICollection<School> School{get;set;}
}

public School
{
    public Id{get;set;}
    public ICollection<Child> Children{get;set;}
}

So we have a parent with a list of children
Now we want to get the children that are still less then 18
The query would like like

var minors = Db.Parents.Select(parent=> new {parent = parent, child = parent.Children.Where(child=> child.Age < 18});

For each of the Parents we project to an anonymous object that holds the parent and a collection of their children that are less than 18.

Here is the same query with LinqKit

public static class ParentCompositions
{
    public static Expression<Func<Parent,IQueryable<Child>>> Minors = 
    parent=>parent.Children.Where(child=> child.< 18);
}

Db.Parents.AsExpandable().Select(parent=>new {parent = parent, child = ParentCompositions.Minors.Invoke(parent)}

As you can see we the compositon class now has a field that holds an expression representation of the same query we used earlier. The query calls AsExpandable on on the IQueryable Parents. AsExpandable is the method the LinqKit uses to intercept the query. Then in the select method we call Invoke which lets LinqKit know that it needs to intercept it. The SQL generated between the two examples will be exactly the same.

The cool thing about it is now anytime we need minor children for a parent, all we have to do is call the composition.

Now most of the time we don't get away with querying only one or two tables. So Lets get all of the parents and there children that are in high school and still a minor

public class Compositions
{
     public class ChildSchoolQueryResult
     {
       public Child Child {get;set;}
       public School School {get;set;}
     }

     public class ParentsWithChildrenInSchoolQueryResult
     {
        public Parent Parent {get;set;}
        IQueryable<ChildSchoolQueryResult> ChildrenSchools{get;set}
     }
     public static Expression<Func<Child, ChildSchool>> LastSchoolAttended = 
        child => new ChildSchool { 
              Child = child,
              School = child.Schools.FirstOrDefault(school => school.Id == 
                                             child.Schools.Max(s2=>s2.Id)
                 };

     public static Expression<Func<Parent, ParentsWithChildrenInSchoolQueryResult>> 
     SchoolDependants= p=> 
            new ParentsWithChildrenInSchoolQueryResult{
               parent = p,
               ChildrenInSchool = 
               LastSchoolAttended.Invoke(ParentCompositions.Minors.Invoke(p).AsExpandable)
};

var HighSchoolDependants = Db.Parents.Select(parent=>
    new { 
    Parent = parent, 
    children = p.children.where(c=> c.Age <= 18)
        .Select(child=> 
             new {
                     Child = child,
                     School = child.Schools.FirstOfDefault(
                       school => school.id == child.schools.Max(school2=>school2.Id)})
                }
       });
    }
}).Where(p=>p.Children.Any(p=>p.School.Name == "High School");
// LinqKit
var HighSchoolDependants = Db.Parents.Select(parent => Compositions.SchoolDependants.Invoke(parent))
.where(p=>p.ChildrenSchools.Any(s=>School.Name == "High School"));
}

This is a good example of the two styles and their pros and cons.
Pros
Change the Composition changes the result everywhere
Complicated queries only get wrote once

Cons
Thought must be taken to create compositions correctly

Without LinqKit
Pros
No extra library
All logic is in one place

Cons
Logic has to be duplicated
Complicated Queries must be recopied

Jeff Tyler

Jeff is a C# developer who has been developing software since 2007. In his free time, he enjoys playing around with new technologies or being out doors hunting, fishing or just hanging out.

Read More