Why does this LINQ join query work, but this other one doesn't?

Question

I've written two LINQ queries using the join method. Essentially, if I switch the order of the objects to be joined, the query no longer works and throws the error:

"Unable to create a constant value of type 'Domain.Entities.UsersSitesRole'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."

        var foo2 = //works
            from p in privilegesForUser
            join c in repository.Child on p.SiteId equals c.Child_SiteID
            select new { ChildID = c.Child_ChildID, name = c.Child_FirstName, site = c.Child_SiteID, p.PrivilegeLevel };

        var foo3 = //throws exception
            from c in repository.Child
            join p in privilegesForUser on c.Child_SiteID equals p.SiteId
            select new { ChildID = c.Child_ChildID, name = c.Child_FirstName, site = c.Child_SiteID, p.PrivilegeLevel };

The object privilegesForUser is a List of entities derived from my Entity Framework context (UsersSiteRole), and repository.Child is an IQueryable<Child> from my EF context as well.

Answer 1

This is caused by the way EF parses the expression tree it gets in the extension methods.

There are many cases, where a query is logically correct and executes just fine on IEnumerable (Linq to Objects) but fails in Linq to Entities. Basicly, it's pretty much impossible to compile just any logical expression tree into proper SQL statements (SQL is not ideal and far from object oriented world) and this is the case where EF gives up. In time, you get used to understanding what works and what doesn't.