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]

Answer 2

The simplest query I can come up with is like this

// by doing groupby and select you choose only one address of the type,
// if a person has many.
// If you can be sure that each person has only one address of each type
// then you can simplify these queries a little bit.
IQueryable<PersonAddresses> homeAddresses = from address in addresses
                                            where address.AddressType == "HomeAddress"
                                            group address by address.id into g
                                            select g.First();

IQueryable<PersonAddresses> businessAddresses = from address in addresses
                                                where address.AddressType == "BusinessAddress"
                                                group address by address.id into g
                                                select g.First();
IQueryable<PersonData> data = from person in persons
                              join tmp1 in homeAddresses on person.id equals tmp1.id into ha
                              join tmp2 in businessAddresses on person.id equals tmp2.id into ba
                              from homeAddress in ha.DefaultIfEmpty()
                              from businessAddress in ba.DefaultIfEmpty()
                              select new PersonData {
                                id = person.id,
                                Name = person.name,
                                HomeAddress = homeAddress == null
                                  ? null
                                  : new Address {
                                    City = homeAddress.City,
                                    State = homeAddress.State
                                  },
                                BusinessAddress = businessAddress == null
                                  ? null
                                  : new Address {
                                    City = businessAddress.City,
                                    State = businessAddress.State
                                  },
                              };

You can get the resulting sql query like this:

  string sql = ((System.Data.Entity.Core.Objects.ObjectQuery) data).ToTraceString();

You can simplify here and there, but it will have less sense, because you usually cannot completely expect your data to be correct - some list have duplicates, some values are missing, so my query covers corner cases.