C#, Linq to Sql: Why can I not use an expression to filter SubEntities?

Question

I have made myself an ExpressionBuilder class that helps me put together expressions that can be used as a predicate when doing Linq to Sql queries. It has worked great. However, I just discovered Expressions can only be used to filter on Tables, and not on EntitySets??Why on earth is this the case?

For example if I have Company and an Employee with a Salary. I could create these two expressions:

Expression<Func<Company, bool>> cp = x => x.Name.StartsWith("Micro");
Expression<Func<Employee, bool>> ep = x => x.Name.StartsWith("John");

I would then expect to be able to do the following, however it only partially works:

var companies = dataContext.Companies
    .Where(cp)                                // Goes fine
    .Select(x => new 
        {
            x.Name,
            SumOfSalaries = x.Employees
                .Where(ep)                    // Causes compile-time error
                .Sum(y => y.Salary),
        }
    .ToList();

Also, if I do a ep.Compile() it compiles, but then I get an error when running the query.

Why is this the case? Am I missing something? I don't find this logical. Can I fix this somehow? Or do you have a good workaround?

I know that I in this case could just use Where(x => x.Name.StartsWith("John")) instead, but the problem is that the expressions I need are not that trivial. They are longer strings of AndAlsos and OrElses.

Answer 1

If you are going to pass a lambda expression to a LINQ to SQL provider don't create it as an Expression<T> - let the provider do that for you.

Answer 2

The following works for me - note both are compiled:

var companies = dataContext.Companies.Where(cp.Compile())
                .Select(x => new
                                 {
                                     x.Name,
                                     SumOfSalaries = x.Employees
                                        .Where( ep.Compile() )
                                        .Sum(y => y.Salary),
                                 }

                 ).ToList();

The expression parser seems to be losing type information in there somewhere after the first where clause when you put in the second. To be honest, I'm not sure why yet.

Edit: To be clear, I do understand that EntitySet doesn't support passing an expression into the where clause. What I don't completely understand is why it fails when you add the Where(ep.Compile()).
My theory is that in compiling the first where (Where(cp.Compile()), Linq2Sql quits parsing the expression - that it can't parse the ep.Compile() against an entityset, and is unable to decide to break up the query into two until you compile the first where clause.

Answer 3

I think you need to rewrite your query. Another way to ask for the details you want, is: "Give me the sum of the salaries for the selected employees in the selected companies, organized by the company name".

So, with the employee salaries in focus, we can write:

    //Expression<Func<Employee, bool>> ep = x => x.Name.StartsWith("John");
    //Expression<Func<Company, bool>> cp = x => x.Name.StartsWith("Micro");

    Expression<Func<Employee, bool>> ep = x => x.Name.StartsWith("John");
    Expression<Func<Employee, bool>> cp = x => x.Company.Name.StartsWith("Micro");

    var salaryByCompany = dataContext.Employees
        .Where(ep)
        .Where(cp)
        .GroupBy(employee => employee.Company.Name)
        .Select(companyEmployees => new
                                        {
                                            Name = companyEmployees.Key,
                                            SumOfSalaries = companyEmployees.Sum(employee => employee.Salary)
                                        });

    var companies = salaryByCompany.ToList();

Answer 4

I asked the same question but I don't have an answer yet either. I agree, it makes no sense. Let me know if you get an answer to this.