LINQ to Entities - Support for Closures (Lambdas) ?

Question

Hey all

I've been working around a problem I have when using LINQ to Entities when using closures.

Apparently, L2E does not support closures. Meaning:

var users = from user in UserRepository.FindAll()
            select new UserDTO
            {
               UserId = user.UserId,
               Tickets = from ticket in TicketRepository.FindAll()
                         where ticket.User == user
                         select new TicketDTO
                         {
                           TicketId = ticket.TicketId
                         }
            };

(NOTE: The "where"-clause is where the problem exists. I am not allowed to compare an entity to another entity because they are not EF primitive types. Only things like Int32, Guid etc. is allowed.)

, is not valid because I cannot compare 'ticket.User' to 'user'

This is simply an example of the problem I have, and I realize that I could compare on the Id, since this a primitive type, as opposed to a closure.

In reality my scenario is alot more complex than this, but this is the scenario I need to solve for now.

A work-around I found online is using a subquery. That DOES work, but for my scenario it's not very effective.

Question:

Do any of you know if:

1. Entity Framework 4 will support Closures in LINQ to Entities?
2. There is a better solution to this problem than using sub-queries?
3. Any additional knowledge you have on this topic will be greatly appreciated!

Answer 1

This is not a problem directly related to closures. The problem is (probably) that you are mixing Entity Framework entities and your data transfer objects. The LINQ provider tries to convert the expression tree of your query into SQL statements and fails because it cannot separate the data transfer objects from the entities and the database, of course, cannot deal with the data transfer objects, too.

I suggest to make the separation much cleaner - at first fetch the data from the database using LINQ to Entity and maybe anonymous types if required, then switch to LINQ to Objects to construct data transfer objects from the retrieved data and all should be fine. Something like the following. (Just to note - I am (safely) assuming the repositories return IQueryable<T>s (else the whole stuff should not work at all).)

var result = UserRepository
      .FindAll()
      .Select(user => new
         {
            UserId    = user.UserId,
            TicketIds = TicketRepository
                           .FindAll()
                           .Where(ticket => ticket.User.UserId == user.UserId)
                           .Select(ticket => ticket.TicketId)
         });

Transforming this query result into data transfer objects is now straight forward. Note that the users are compared via the IDs because the Entity Framework does (not yet) support comparisons by reference.

Answer 2

The problem here is that L2E doesn't support reference equality of materialized objects vs. objects in the DB, so you need to compare based on the PK:

var users = from user in UserRepository.FindAll()
            select new UserDTO
            {
               UserId = user.UserId,
               Tickets = from ticket in TicketRepository.FindAll()
                         where ticket.User.UserId == user.UserId
                         select new TicketDTO
                         {
                           TicketId = ticket.TicketId
                         }
            };

(Presuming, here, that the PK of User is called UserId.)