CODEWITHSUNDEEP
c#linq
antonio_mg
antonio_mg

Reputation: 492

LINQ Grouping By with Left Join Results

If I got this list

PersonPhone

------------------------------------------------------------
| **PersonId**  |**Name**  | **PhoneId** | **PhoneNumber** |
------------------------------------------------------------
|             1 | John Doe |           1 | 111-55-5855     |
------------------------------------------------------------
|             1 | John Doe |           2 | 111-55-5521     |
------------------------------------------------------------
|             2 | Mary Jane|           3 | 254-565-855     |
------------------------------------------------------------
|             3 | J. Watson|         NULL|             NULL|
------------------------------------------------------------

I need to mapping to this object:

public class PersonContactInfo {
    public int Id { get; set; }
    public string Name { get; set; }
    public List<string> Phones { get; set; }
}

With LINQ, how can I get one row for each person, with his phones in a List and paging?

I already have a query which result is like the PersonPhone result set, but I don't know how to grouping by PersonId and then join all the PhoneNumbers to List, and paging. For example, if I want a page size of three, how to make a sql query to get John Doe, Mary Jane and J. Watson with their phones, if the actual query returns 4 rows?

Note: I'm not (and can't) using Entity Framework, what I'm doing is and sql query that populate a list of PersonPhone, just like EF does.

Upvotes: 3

Views: 75

Answers (1)

ocuenca
ocuenca

Reputation: 39326

Applying a group by:

var query=    PersonPhoneSet
             .GroupBy(p=>new {p.PersonId, p.Name})
             .Select(g=> new PersonContactInfo 
                         {
                             Id=g.Key.PersonId,
                             Name=g.Key.Name,
                             Phones= g.Select(p=>p.PhoneNumber).ToList()
                         }
                     );

Upvotes: 1

Related Questions