Linq/EF, Eager loading and GROUP BY issues

Question

I'm having issues with GROUP BY and eager loading. I try to explain what im doing. I'm querying a datacontext ctx for events

The event class has the following properties

string Description
DateTime Date
bool IsDeleted
Guid SubjectId 
string Title 
DateTime Created
Guid ForProjectId 

Person TriggeredBy
List<Guid> Targets 

There are muttiple events with the same SubjectId and i would like to end up having events with unique SubjectIds and that are the newest in the group. I end up with the following query.

var events = from x in
             (from e in ctx.Events
              .Include("TriggeredBy")
              .Include("Targets")
              group e by e.SubjectId
              into g
              select new 
                     { 
                       GroupId = g.Key, 
                       EventsWithSameSubjectId = g, 
                     }
              )
              select x.EventsWithSameSubjectId
              .OrderByDescending(y => y.Created).FirstOrDefault();

The query compile fine and returns the right resulting set. But the included properties are always null.

When i strip the query to see if eagor loading is working properly....

var events =  (from e in ctx.Events.OfType<DataNotificationEvent>()
              .Include("TriggeredBy")
              .Include("Targets")
              select e).ToList();

This return the events with all the included properties.

Is this a known issue / bug with Linq / EF or is there any way i can get rid of this error.

Regards

Vincent Ottens

Answer 1

You're projecting onto an anonymous type, so Include() isn't going to work like that. Because what you've done with the group and projecting into the anonymous type is to change the shape of the query. That tosses out the eager loading. Reading this article might help.

Answer 2

Thnx for the quick answer. You pointed me in the right direction. Here is the solution i came up with:

using MyFunc = Func<ExtendedCoreContext, Guid, IQueryable<DataNotificationEvent>>;

private static readonly MyFunc GetMentionsNewCompiledQuery = 

    CompiledQuery.Compile<ExtendedCoreContext, Guid, IQueryable<DataNotificationEvent>>(

        (ctx, personId) => ((ObjectQuery<DataNotificationEvent>)(
            from x in (
                from e in ctx.Events.OfType<DataNotificationEvent>()
                group e by e.SubjectId
                into g
                select g.OrderByDescending(p => p.Created).FirstOrDefault()
            )
            orderby x.Created descending
            where x.Targets.Any(t => t.Id == personId)
            select x
        ))
          .Include(EntityProperties.Event.TriggeredBy)
          .Include(EntityProperties.DataNotificationEvent.Targets)

    );