Expression expanding with Entity Framework Core

One of the greatest features I have discovered in the last few years is expression expanding for entity framework core. Usually when you create a projection with linq you will use the Select() method and either use an anonymous type or map the values you need from the database to a class. These projections are often faster because they don’t have to retrieve all the columns from the database table. What I always missed was to be able to reuse a projection inside another projection. This can be helpful if you have expandable structures you want to provide to your API.

Let’s assume we have a user table and a blog table in our database. If you try to reuse the projection for the user inside the projection of the blog you might do something like this:


private static UserModel GetUserModel(UserEntity entity)
{
    return new UserModel
    {
        Firstname = entity.Firstname,
        Lastname = entity.Lastname
    };
}

public async Task<List<BlogModel>> GetBlogs()
{
    var query = dbContext.Blogs
        .Select(blog => new BlogModel
        {
            Author = blog.Author,
            CreateUser = GetUserModel(blog.CreateUser)
        });

    return await query
        .ToListAsync();
}

If you take a closer look at the query object and analyze the generated SQL-statement, you will see that it will include a SELECT-statement including all the columns from the user table:


SELECT [b].[Author], [a].[Id], [a].[City], [a].[Company], [a].[ConcurrencyStamp],[a].[CreateDate], [a].[Email], [a].[EmailConfirmed], [a].[Firstname], [a].[LastLoginDate], [a].[Lastname], [a].[LockoutEnabled], [a].[LockoutEnd], [a].[NormalizedEmail], [a].[NormalizedUserName], [a].[PasswordHash], [a].[SecurityStamp],  [a].[UserName], 
FROM [Blogs] AS [b] 
INNER JOIN [Users] AS [a] ON [b].[CreateUserId] = [a].[Id]

We actually only needed the author of the blog and the first and lastname of the user. Since Entity Framework is not able to translate our LINQ expression it will just retrieve all the columns and then do the mapping in memory. This is not ideal because it’s loading unnecessary data into memory and will also slow down our query.

Solution

Fortunately there is a great library called LinqKit. It allows you to use a projection inside another projection, since it will dynamically compile the LINQ expression.
All you have to do is to install the Nuget-package “LinqKit.Microsoft.EntityFrameworkCore” and then in your database options enable expression expanding:


    dbContextOptionsBuilder.UseSqlServer(connectionString)
        .WithExpressionExpanding();

Now we are almost there but first we need to make some changes to our projections:


using LinqKit;

public static Expression<Func<UserEntity, UserModel>> GetModelProjection()
{
    return user => new UserModel()
    {
        Firstname = user.Firstname,
        Lastname = user.Lastname
    };
}

public async Task<List<BlogModel>> GetBlogs()
{
    var query = dbContext.Blogs
        .Select(blog => new BlogModel
        {
            Author = blog.Author,
            CreateUser = GetModelProjection().Invoke(blog.CreateUser)
        });

    return await query
        .ToListAsync();
}

As you can see the projection for the user is now a method returning an expression. In the projection of the blog we use Invoke() after getting the expression. This method is provided by LinqKit so don’t forget to add using statement to your file. Because we enabled expression expanding, Linqkit will recognize this expression invocation. If we again analyze the query object we will now see that the generated sql query only includes the columns we actually need:


    SELECT [b].[Author], [a].[Firstname], [a].[Lastname]
    FROM [Blogs] AS [b]
    INNER JOIN [Users] AS [a] ON [b].[CreateUserId] = [a].[Id]

This is great since we can now reuse the user projection in every other projection. We can also use it several times. We can include the update user as well or we can even use it for lists:


dbContext.Blogs
.Select(blog => new BlogModel
{
    Author = blog.Author,
    CreateUser = GetModelProjection().Invoke(blog.CreateUser),
    UpdateUser = GetModelProjection().Invoke(blog.UpdateUser),
    VisitorUsers = blog.VisitorUsers
        .Select(user => GetModelProjection().Invoke(user))
        .ToList()
});

But what if we need to pass another value from outside into the expression? This is also possible, let’s take a look at the code:


public static Expression<Func<UserEntity, string?, UserModel>> GetModelProjection()
{
    return (user, author) => new UserModel()
    {
        Firstname = user.Firstname,
        Lastname = user.Lastname,
        IsAuthor = user.Firstname == author
    };
}

public async Task<List<BlogModel>> GetBlogs()
{
    var query = dbContext.Blogs
        .Select(blog => new BlogModel
        {
            Author = blog.Author,
            CreateUser = GetModelProjection().Invoke(blog.CreateUser, blog.Author)
        });

    return await query
        .ToListAsync();
}

The method "GetModelProjection" returns a lambda-expression with two arguments. The first one is the entity and the second one is the value we pass in. We can now use this value in our query which will be beautifully translated to this:


SELECT [b].[Author], [a].[Firstname], [a].[Lastname], CASE
    WHEN [a].[Firstname] = [b].[Author] AND ([b].[Author] IS NOT NULL) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [IsAuthor]
FROM [Blogs] AS [b]
INNER JOIN [Users] AS [a] ON [b].[CreateUserId] = [a].[Id]

This feature is not only tied to classes. You can also use it for simple property types. Here is an example I find very useful:


public static Expression<Func<UserEntity, string>> GetFullNameProjection()
{
    return user => user.Firstname + " " + user.Lastname;
}

public static Expression<Func<UserEntity, string?, UserModel>> GetModelProjection()
{
    return (user, author) => new UserModel()
    {
        Firstname = user.Firstname,
        Lastname = user.Lastname,
        IsSystemUser = user.Firstname == author,
        FullName = GetFullNameProjection().Invoke(user)
    };
}

This example will concatenate the first name and the last name and provide it as the full name. If we ever need to show the last name before the first name we will only have to change it in one place. Again this will be perfectly translated into an sql query:


SELECT [b].[Author], [a].[Firstname], [a].[Lastname], CASE
    WHEN [a].[Firstname] = [b].[Author] AND ([b].[Author] IS NOT NULL) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END AS [IsAuthor], ([a].[Firstname] + N' ') + [a].[Lastname] AS [FullName]
FROM [Blogs] AS [b]
INNER JOIN [Users] AS [a] ON [b].[CreateUserId] = [a].[Id]

If you want to build even more advanced queries, you can pass your DbContext as parameter into your method and make a subquery, by returning an expression that is accessing that DbContext.


public static Expression<Func<UserEntity, string?, UserModel>> GetModelProjection(MyDbContext dbContext)
{
    return (user, author) => new UserModel()
    {
        Firstname = user.Firstname,
        Lastname = user.Lastname,
        IsAuthor = user.Firstname == author,
        TotalUserCount = dbContext.Users.Count()
    };
}

public async Task<List<BlogModel>> GetBlogs()
{
    var query = dbContext.Blogs
        .Select(blog => new BlogModel
        {
            Author = blog.Author,
            CreateUser = GetModelProjection(dbContext).Invoke(blog.CreateUser, blog.Author)
        });

    return await query
        .ToListAsync();
}

Conclusion

As you have seen, with Linqkit we can not only expand expressions, we can also make our code more reusable. Imagine that you have to change something in a projection which you use in multiple places across your application. You will only have to change it in one place. That’s why I recommend to create a static class for each Entity that you need projections for. This way the projections can be shared.
After using this feature for almost 2 years I can say that I would never want to miss it. My company also established it in every new application and our developers are loving it.