Can someone explain why these two linq queries return different results?

Question

Hi folks,

I have two linq (to EF4) queries, which return different results. The first query contains the correct results, but is not formatted/projected right.

the second query is what i want but it missing some data.

Schema

Query 1

var xxxx = (from cp in _connectedClientRepository
            .GetConnectedClients(new[] { "LogEntry", "LogEntry.GameFile" })
            .AsExpandable()
            .Where(predicate)
            select cp)
    .ToList();

Notice the property GameFile . It is not null. This is great :) Notice the linq query? I'm eager loading a LogEntry and then eager loading a GameFile (for each eager loaded LogEntry).

This is what i'm after -> for each LogEntry that is eager loaded, please eager load the GameFile. But this projection result is wrong...

Ok.. next...

Query 2

var yyy = (from cp in _connectedClientRepository
            .GetConnectedClients(new[] { "LogEntry", "LogEntry.GameFile" })
            .AsExpandable()
            .Where(predicate)
        select cp.LogEntry)
    .ToList();

NOTE: the image above has a typo in it ... please note the include associations typed code is correct (ie. LogEntry.GameFile) while the image has it typo'd.

Correct projection now -> all LogEntries results. But notice how the GameFile property is now null? I'm not sure why :( I thought i correctly eager loaded the correct chain. So this is the correct projection but with incorrect results.

Obligatory Repository code.

public IQueryable<ConnectedClient> GetConnectedClients(
    string[] includeAssociations)
{
    return Context.ConnectedClients
        .IncludeAssociations(includeAssociations)
        .AsQueryable();
}

public static class Extensions
{
    public static IQueryable<T> IncludeAssociation<T>(
        this IQueryable<T> source, string includeAssociation)
    {
        if (!string.IsNullOrEmpty(includeAssociation))
        {
            var objectQuery = source as ObjectQuery<T>;

            if (objectQuery != null)
            {
                return objectQuery.Include(includeAssociation);
            }
        }

        return source;
    }

    public static IQueryable<T> IncludeAssociations<T>(
        this IQueryable<T> source, params string[] includeAssociations)
    {
        if (includeAssociations != null)
        {
            foreach (string association in includeAssociations)
            {
                source = source.IncludeAssociation(association);
            }
        }

        return source;
    }
}

Updates

• 1 : Fixed some typo's in noticed in the code samples.
• 2 : Added repository code to help anyone who is confused.

Answer 1

It seems you need one more repository method, that will do this for you; _connectedClientsRepository.GetLogEntriesOfConnectedClients().

Answer 2

I suspect Craig Stuntz' suggestion may work, but if it doesn't, the following should certainly work:

 var xxxx =_connectedClientRepository
        .GetConnectedClients(new[] { "LogEntry", "LogEntry.GameFile" })
        .AsExpandable()
        .Where(predicate)
        .ToList() // execute query
        .Select(cp => cp.LogEntry); // use linq-to-objects to project the result

Answer 3

Include() works on the query results, rather than the intermediate queries. You can read more about Include() in this post. So one solution is to apply the Include() to the whole query, like this:

var q = ((from cp in _connectedClientRepository.GetConnectedClients()
                                               .AsExpandable()
                                               .Where(predicate) 
          select cp.LogEntry) 
         as ObjectQuery).Include("GameFile").ToList();

That will probably work, but it's ugly. Can we do better?

I can think of two ways around this issue. Mostly, it depends upon whether or not you actually need entity types returned. I can't say whether this is the case without seeing the rest of your code. Generally, you need to return entity types when you are going to update (or otherwise modify) them. If you are selecting for display or calculation purposes, it's often a better strategy to return POCOs instead of entity types. You can do this with projection, and of course it works in EF 1. In this case, you would change your repository method to return POCO types:

 public IQueryable<ClientInfo> GetConnectedClients()
 {
      return from cp in _context.Clients
             where // ...
             select new ClientInfo
             {
                 Id = cp.Id,
                 ClientName = cp.ClientName,
                 LogEntry = new LogEntryInfo
                            {
                                LogEntryId = cp.LogEntry.LogEntryId,
                                GameFile = new GameFileInfo
                                           {
                                               GameFileId = cp.LogEntry.GameFile.GameFileId,
                                               // etc.
                                           },
                                // etc.
                            },
                  // etc.
             };
 }

Note that when you use projection there is no eager loading, no lazy loading, and no explicit loading. There is only your intention, expressed as a query. The LINQ provider will figure out what you need, even if you further compose the query outside the repository!

On the other hand, you might need to return entity types instead of POCOs, because you intend to update them. In that case, I would write a separate repository method for the LogEntries, as Tomas suggested. But I would only do this if I intended to update them, and I might write it as an update method, rather than a "Get" method.