CODEWITHSUNDEEP
c#entity-frameworklinq
Jorge Santos
Jorge Santos

Reputation: 636

LinQ query group the same table twice

I need some help.
The problem: I have User and a MedalTransaction class. A user can assign medals and receive them as well. MedalTransaction has 2 properties called Origin and Destination that are User Id's.

What I've tried:

var q = (from user in companyUsers
         join mt in Context.MedalTransactions
         on user.Id equals mt.Destination into medalTransactions

         from mt in medalTransactions.DefaultIfEmpty()

         group new { user, mt } by new { mt.Destination } into receivedMedals

         select new User
         {
             Id = receivedMedals.Select(u => u.user.Id).FirstOrDefault(),
             Name = receivedMedals.Select(u => u.user.Name).FirstOrDefault(),
             Company = receivedMedals.Select(u => u.user.Company).FirstOrDefault(),

             TotalAcquiredMedals = receivedMedals
                .Where(m => m.mt.Destination == m.user.Id && m.mt.Origin != systemId)
                .Sum(m => m.mt.Quantity)                       

         })
        .OrderByDescending(u => u.TotalAcquiredMedals);

This works, but only for the acquired medals. I wanted to set the other property TotalAssignedMedals on the user instance creation. I have tried multiple group by but never got the results I wanted, I don't even know if it is possible to do this.

Thank you for your help

EDIT Question solved, final code:

var q = (from user in companyUsers

                 join mt in Context.MedalTransactions
                 on user.Id equals mt.Destination into receivedMedals

                 join mt2 in Context.MedalTransactions
                 on user.Id equals mt2.Origin into assignedMedals

                 from mtReceived in receivedMedals.DefaultIfEmpty()
                 from mtAssigned in assignedMedals.DefaultIfEmpty()

                 group new { user, mtReceived, mtAssigned } by new { user.Id } into medalTransactions

                 select new User
                 {
                     Id = medalTransactions.Select(_ => _.user.Id).FirstOrDefault(),
                     Name = medalTransactions.Select(_ => _.user.Name).FirstOrDefault(),
                     Company = medalTransactions.Select(_ => _.user.Company).FirstOrDefault(),

                     TotalAcquiredMedals = medalTransactions
                        .DistinctBy(m => m.mtReceived.Id)
                        .Where(m => m.mtReceived.Origin != systemId)
                        .Sum(m => m.mtReceived.Quantity),

                     TotalAssignedMedals = medalTransactions
                        .DistinctBy(m => m.mtAssigned.Id)
                        .Where(m => m.mtAssigned.Destination != systemId)
                        .Sum(m => m.mtAssigned.Quantity)

                 })
                .OrderByDescending(u => u.TotalAcquiredMedals);

Upvotes: 1

Views: 340

Answers (1)

the_joric
the_joric

Reputation: 12226

maybe something like that

from user in companyUsers

join mt in Context.MedalTransactions
on user.Id equals mt.Destination into destinations

join mt2 in Context.MedalTransactions
on user.Id equals mt2.Origin into origins

from mtd in mt.DefaultIfEmpty()
from mto in mt2.DefaultIfEmpty()

group new { user, mtd, mto } by new { user.Id } into medals
select new {user.Id, user.Name, ..., 
    TotalAcquiredMedals = mtd.Where(_ => _ != null).Distinct().Count(), 
    TotalAssignedMedals = mto.Where(_ => _ != null).Distinct().Count()
}

Upvotes: 1

Related Questions