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.

Have you tried to change Expression> ep = x => x.Name.StartsWith("John"); to *not* use "x"? I've noticed that when using arguments with simple names, I can't chain together the same name with different types. — JustLoren, Sep 30 '09 at 13:56
to not use x? how would you do that? and in what way would that make a difference? I can't really see it anyways... — Svish, Sep 30 '09 at 14:18
I posted an answer on Ecyrb's question that might solve this problem: http://stackoverflow.com/questions/1424251/entitysett-wheremypredicate-throws-notsupportedexception/7804517#7804517 — Merlyn Morgan-Graham, Oct 18 '11 at 08:48

score 0 · Answer 1 · answered Sep 30 '09 at 13:55

0

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.

answered Sep 30 '09 at 13:55

Andrew Hare

344,730
71
640
635

But I am not passing it as a lambda expression, however that is what it wants. Which is the problem, since I don't have the predicate as a lambda expression, but as an Expression>. – Svish Sep 30 '09 at 14:05

Philip Rieck · Answer 2 · 2009-09-30T15:59:39.917

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.

Sure this doesn't cause the whole query to run locally? Or is it still done on the SQL server? — Svish, Sep 30 '09 at 15:04

score 0 · Answer 3 · answered Oct 12 '09 at 12:39

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();

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

3 Answers3