Linq to fill nested objects with pivot

Question

I have two tables as below

Person{
    int id;
    string name;
}
PersonAddresses{
    int id;
    string AddressType;
    string City;
    string State;
}

Here AddressType specifies "BusinessAddress","HomeAddress" etc. I want to Get the result as below.

class PersonData{
    int id;
    string Name;
    Address BusinessAddress;
    Address HomeAddress;
    ...
}
class Address{
    string City;
    string State;
}

Can anyone tell me how to do this using LINQ without multiple selects?

Answer 1

What you are looking for is Group join combined with sub-selects to get Home and Business addresses from a group:

var query =
    from person in Persons
    join address in PersonAddresses
    on person.id equals address.id into personData
    select new PersonData()
    {
        id = person.id,
        Name = person.name,
        HomeAddress = (
            from data in personData
            where data.AddressType == "HomeAddress"
            select new Address()
            {
                City = data.City,
                State = data.State
            }).FirstOrDefault(),
        BusinessAddress = (
            from data in personData
            where data.AddressType == "BusinessAddress"
            select new Address()
            {
                City = data.City,
                State = data.State
            }).FirstOrDefault(),
    };

This runs as single SQL query:

SELECT 
    [Extent1].[id] AS [id], 
    [Extent1].[name] AS [name], 
    [Limit1].[id] AS [id1], 
    [Limit1].[City] AS [City], 
    [Limit1].[State] AS [State], 
    [Limit2].[id] AS [id2], 
    [Limit2].[City] AS [City1], 
    [Limit2].[State] AS [State1]
    FROM   [dbo].[Person] AS [Extent1]
    OUTER APPLY  (SELECT TOP (1) 
        [Extent2].[id] AS [id], 
        [Extent2].[City] AS [City], 
        [Extent2].[State] AS [State]
        FROM [dbo].[PersonAddresses] AS [Extent2]
        WHERE ([Extent1].[id] = [Extent2].[id]) AND (N'HomeAddress' = [Extent2].[AddressType]) ) AS [Limit1]
    OUTER APPLY  (SELECT TOP (1) 
        [Extent3].[id] AS [id], 
        [Extent3].[City] AS [City], 
        [Extent3].[State] AS [State]
        FROM [dbo].[PersonAddresses] AS [Extent3]
        WHERE ([Extent1].[id] = [Extent3].[id]) AND (N'BusinessAddress' = [Extent3].[AddressType]) ) AS [Limit2]