How to retrieve one table row and list of rows that are connected to the first row in Linq .NET

Question

I have specific problem. I'm not too experienced with SQL so this may be a silly question. I have the following class

class StructuredResult
{
    public Pay Pay{ get; set; }
    public Processing Processing{ get; set; }
    public Member Member { get; set; }

    public StructuredResult()
    {
    }
}

And this is the code I'm getting this with

var allPaying = (from pay in Entities.Pays
                join prc in Entities.Processingss on pay.IDCalculation equals cal.IDCalculation
                join mbr in Entities.Members on pay.IDMember equals mbr.IDMember

                where pay.IDMember == IDMember
                orderby prc.DateFrom descending
                select new StructuredResult()
                {
                    Pay = pay,
                    Processing = prc,
                    Member = mbr
                }).ToList();

The code is working fine I changed things for posting here, maybe some name changes are not ok. What I need to get is this:

class StructuredResult
{
    public Pay Pay{ get; set; }
    public Processing Processing{ get; set; }
    public Member Member { get; set; }
    public List<PayDetail> PayDetails { get; set; }

    public StructuredResult()
    {
    }
}

Does anyone know if this is even possible? Pay detail is connected with Pay.ID = PayDetail.ID Thanks in advance

score 1 · Accepted Answer · edited Sep 29 '17 at 14:21

In order to also populate StructuredResult with a collection of PayDetails you need to use Group Join. See:

MSDN on GroupJoin
And last Linq to Entities join vs groupjoin.

So:

var allPaying = from pay in Entities.Pays
                join prc in Entities.Processingss on pay.IDCalculation equals cal.IDCalculation
                join mbr in Entities.Members on pay.IDMember equals mbr.IDMember
                join d in Entities.PayDetails on pay.IDMember equals d.ID into details

                where pay.IDMember == IDMember
                orderby prc.DateFrom descending

                select new StructuredResult()
                {
                    Pay = pay,
                    Processing = prc,
                    Member = mbr,
                    PayDetails = details
                };

I'd also recommend that since you are using EF look into Navigation Properties. By defining your classes a bit differently you will get much more capabilities with much less (no need for writing the joins for example)

And just a small tip - you are defining a default constructor with no logic in id. If this is true also in the real code then there is no need for it. It is defined automatically.

This appears to work. I tried with Entities.Pays.Include("PayDetails"), but it seams that it didn't load immediately. How I can disable lazy loading just for this? I want to have everything availlable there not lazy load later when necessary, because of lots of calculations, multiple queries to database takes time. With current what you wrote, works much better, from 130ms average to 5ms :) — Shakal187, Jul 08 '17 at 10:09
@Shakal187 - you can add `.ToList()` after `details` and also around the entire query. More than this should be in a separate question :) (and there are many other questions answering this follow-up too) Hope it helps — Gilad Green, Jul 08 '17 at 10:10

score 1 · Answer 2 · answered Jul 08 '17 at 09:14

This may help you:

var allPaying = (from pay in Entities.Pays
                 join prc in Entities.Processingss on pay.IDCalculation equals cal.IDCalculation
                 join mbr in Entities.Members on pay.IDMember equals mbr.IDMember

                 where pay.IDMember == IDMember
                 orderby prc.DateFrom descending
                 select new StructuredResult()
                 {
                     Pay = pay,
                     Processing = prc,
                     Member = mbr,
                     PayDetails = (from pd in Entities.PayDetails
                                   where pd.ID == pay.IDMember
                                   select pd).ToList())
                 }).ToList();

How to retrieve one table row and list of rows that are connected to the first row in Linq .NET

2 Answers2