How to get related data with linq?

c# database entity-framework-core linq

Question

I have a Personnel table and PersonnelDrivingLicense table. There is more than one record in the PersonnelDrivingLicense table related to one Personnel.

I tried to get Personnel and PersonnelDrivingLicense data with linq but I am getting 2 record instead 1.

Here is my linq query:

from p in Personnel
join pdl in PersonnelDrivingLicense on p.Id equals pdl.PersonnelId
select new Personnel
{
    Id = p.Id,
    PersonnelDrivingLicense = new List<PersonnelDrivingLicense>
    {
        new PersonnelDrivinLicense
        {
             Id = pdl.Id,
             DrivingLicenseClass = pdl.DrivingLicenseClass
        }
    }
}

This linq returns below result:

Id: 1,
PersonnelDrivingLicense:
    Id: 1,
    DrivingLicenseClass: B

Id: 1,
PersonnelDrivingLicense:
    Id: 2,
    DrivingLicenseClass: C

The correct result should be below:

Id: 1,
PersonnelDrivingLicense:
    Id: 1,
    DrivingLicenseClass: B

    Id: 2,
    DrivingLicenseClass: C

How can I get desired result above? How should I write correct linq query?

Thanks.

1
1
9/26/2019 7:39:58 AM

Popular Answer

You should group the returned Personnel objects by Id. Try this:

from p in Personnel
join pdl in PersonnelDrivingLicense on p.Id equals pdl.PersonnelId
group p by p.Id into g
select new Personnel
{
    Id = g.Key,
    PersonnelDrivingLicense = g.Select(x => x.PersonnelDrivingLicense).ToList()
}
1
9/26/2019 8:00:40 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow