How to create an expression on a child collection in Entity Framework Code First

Question

I'm trying to convert a piece of logic used in many places into an expression that can be re-used, centrally maintained and executed in the context of SQL Server.

I can do this when querying tables directly on the EF context by passing them an expression. The issue I have is trying to do this on child collections of the table as they are expressed as an ICollection in EF Code First.

A simple example table defined in Code First;

public class Table
{
    public int TableId { get; set; }
    public virtual ICollection<ChildTable> Children { get; set; }
}

How I am querying and how I would like to query it;

var records = context
    .Table
    .Select(table => new
    {
        ChildRecordCount = table.Children.Count(child => !child.IsArchived), // This works.
        AltChildRecordCount = table.Children.Count(HowToExpressThisInCSharp()),// Cannot fathom how to do this.
    });

I've tried various methods (see below) that return a Func but they cause EF to fail as I would expect it to (SQL knows nothing of my C# method). So how does the simpler, inline method get translated to an expression tree (I can see it is executed against SQL Server)?

    static Func<Menu, bool> HowToExpressThisInCSharp()
    {
        return x => !x.IsArchived;
    }

Answer 1

The problem is that the expression in question is part of an expression tree (table => new { ... }) and is just "recorded" inside, thus causing unsupported exception at runtime. In general LINQ to Entities does not like expression composability and requires you to use "flat" expressions resolved at compile time.

The problem is addressed in LINQKit with Compile / Expand (and more generally AsExpandable). Applying it to your scenario would be something like this:

static Expression<Func<Menu, bool>> HowToExpressThisInCSharp()
{
    return x => !x.IsArchived;
}

var criteria = HowToExpressThisInCSharp();
var records = context
    .Table
    .AsExpandable()
    .Select(table => new
    {
        ChildRecordCount = table.Children.Count(child => !child.IsArchived),
        AltChildRecordCount = table.Children.Count(criteria.Compile()),
    });