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

There is an issue with eager loading when you combine it with grouping (and subselects and projections). Take a look at this question: http://stackoverflow.com/questions/4474951/entity-framework-include-is-not-working/4475504#4475504 The two linked articles in the answer might help you to find a workaround. — Slauma, Mar 17 '11 at 20:02

score 9 · Accepted Answer · answered Mar 18 '11 at 12:51

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.

score 2 · Answer 2 · edited Sep 25 '15 at 12:16

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)

    );

Linq/EF, Eager loading and GROUP BY issues

2 Answers2

Linked