Linq Group By not taking inner entity

Question

I am using entity framework and doing a group by over a table. My query is a follows:-

var brokerPaymentLists = dbContext.BrokerPayments
    .Include("PaymentDetail")
    .Where(bp => bp.IdPaymentStatus == (long)EntityModel.Additions.Variables.PaymentStatus.ALLOTED)
    .GroupBy(bp => bp.IdBroker,
    (key, g) => new
    {
        IdBroker = key.Value,
        BrokerPayments = g.ToList()
    }).ToList();

I have included PaymentDetail but after grouping by i can see that the paymentdetail for each item in the BrokerPayments i null. Any suggestion why this is the case, also how can i do the group by such that I can my my paymentDetail insisde each of the BrokerPayments;

Answer 1

The eagerly loading by using Include requires the shape of the data to do not be changed since the Include is applied. In your case this means the query must return IQueryable<BrokerPayments>. But the GroupBy operator changes the shape because it returns IQueryable<IGrouping<TKey, TSource>>. Same will happen with projections and custom joins.

As a workaround you can execute grouping in LINQ to Objects like:

var brokerPaymentLists = dbContext.BrokerPayments
    .Include("PaymentDetail")
    .Where(bp => bp.IdPaymentStatus == (long)EntityModel.Additions.Variables.PaymentStatus.ALLOTED)
    .AsEnumerable()
    .GroupBy(bp => bp.IdBroker,
    (key, g) => new
    {
        IdBroker = key.Value,
        BrokerPayments = g
    });

NOTE: pay attention that the query exectuion will not be defferd