Skip to contentSkip to author details


A 2-post collection

Entity Framework Composable Queries using LinqKit

Written by Jeff Tyler
 EntityFramework  C#  LinqKit  SQL  Queries  Composable

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
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;}
    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 == 

     public static Expression<Func<Parent, ParentsWithChildrenInSchoolQueryResult>> 
     SchoolDependants= p=> 
            new ParentsWithChildrenInSchoolQueryResult{
               parent = p,
               ChildrenInSchool = 

var HighSchoolDependants = Db.Parents.Select(parent=>  
    new { 
    Parent = parent, 
    children = p.children.where(c=> c.Age <= 18)
             new {
                     Child = child,
                     School = child.Schools.FirstOfDefault(
                       school => == 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.
Change the Composition changes the result everywhere
Complicated queries only get wrote once

Thought must be taken to create compositions correctly

Without LinqKit
No extra library
All logic is in one place

Logic has to be duplicated
Complicated Queries must be recopied

Upgrading ASP.NET Identity in NuGet

Written by Michael Earls
 .NET  Code-First  EntityFramework  Identity

This morning, when I went to upgrade the Identity system in my MVC application from NuGet, I ran into issues. When I ran the application that attempted to access roles, I got an error that the model backing the dbContext had changed and that I needed to run a code first migration.

I have very little experience with code-first EntityFramework as I have always used database-first. I just find it easier to set up my database with all the foreign keys and then create my data model from that. I haven’t yet learned the benefits of code-first.

So, to continue, I found the following two resources on the web that pointed me in the right direction:

Upgrading from EntityFramework Identity 1.0 to 2.0


Automatic Code-First Migrations

Within those articles, I found the following package manager console commands that helped me:

Enable-Migrations –EnableAutomaticMigrations Add-Migration Nuget-Update-ASPNet-Identity

Since I had two contexts, it gave me a message that I needed to choose one or the other and even told me the exact command to use to do it. I just copied and pasted the command in the console and ran it.

Once I told it which context was my identity context, I then had to update the database. That was simple enough as all I had to do was use the following command:


That updated the identity database and allowed me to run my application again.

Now, whenever the identity system changes, I can easily update the data model. Though, I’m not quite sure what to do in situations where the changes are destructive (I could lose data). I need to learn more about this.