CODEWITHSUNDEEP
c#sqllinq
Bill789
Bill789

Reputation: 27

How do I get a list of distinct rows from the result of multiple joins in Linq?

I have performed a few joins which have produced multiple columns with the same CustomerId. I would like to filter the result to only display rows with distinct customer. It does not matter which duplicate rows are removed. Tried using group by but couldn't get it to work. After this is filtered I would like to do a select to create new CustomerDTO. This is my code, any assistance is appreciated.

var query = (from customer in db.Customers
                                     join person in db.People
                                     on customer.PersonId equals person.BusinessEntityId
                                     join phone in db.PeoplePhones
                                     on person.BusinessEntityId equals phone.BusinessEntityId into phoneNumbers
                                     from phone1 in phoneNumbers.DefaultIfEmpty()
                                     join email in db.EmailAddresses
                                     on person.BusinessEntityId equals email.BusinessEntityId into emailAddresses
                                     from email1 in phoneNumbers.DefaultIfEmpty()
                                 
                                     select  new CustomerDTO
                                     {
                                         Id = customer.CustomerId,
                                         Title = person.Title,
                                         FirstName = person.FirstName,
                                         LastName = person.LastName,
                                         PhoneNumber = phoneNumbers.FirstOrDefault().PhoneNumber,
                                         EmailAddress = emailAddresses.FirstOrDefault().Email,
                                         Branch = customer.Branch.Name

                                     }).ToList();

Upvotes: 0

Views: 395

Answers (2)

Enigmativity
Enigmativity

Reputation: 117009

Can you give this a go and let me know if it works?

var query =
(
    from customer in db.Customers
    join person in db.People on customer.PersonId equals person.BusinessEntityId
    join phone in db.PeoplePhones on person.BusinessEntityId equals phone.BusinessEntityId into phoneNumbers
    join email in db.EmailAddresses on person.BusinessEntityId equals email.BusinessEntityId into emailAddresses
    group new { customer, person, phoneNumbers, emailAddresses } by customer.CustomerId into gs
    from g in gs.Take(1)
    from pn in g.phoneNumbers.DefaultIfEmpty().Take(1)
    from e in g.emailAddresses.DefaultIfEmpty().Take(1)
    select new CustomerDTO
    {
        Id = g.customer.CustomerId,
        Title = g.person.Title,
        FirstName = g.person.FirstName,
        LastName = g.person.LastName,
        PhoneNumber = pn.PhoneNumber,
        EmailAddress = e.Email,
        Branch = g.customer.Branch.Name
    }
).ToList();

Upvotes: 1

Joe_DM
Joe_DM

Reputation: 1003

I think you're on the right track using a group by. Does something like this do what you need to?

var query = (from customer in db.Customers
         join person in db.People on customer.PersonId equals person.BusinessEntityId
         join phone in db.PeoplePhones on person.BusinessEntityId equals phone.BusinessEntityId into phoneNumbers
         from phone1 in phoneNumbers.DefaultIfEmpty() join email in db.EmailAddresses on person.BusinessEntityId equals email.BusinessEntityId into emailAddresses
         from email1 in phoneNumbers.DefaultIfEmpty()
         
         group new {customer, person, phoneNumbers, emailAddresses} by customer.CustomerId into g
         
         select new CustomerDTO
         {
             Id = g.FirstOrDefault().customer.CustomerId,
             Title = g.FirstOrDefault().person.Title,
             FirstName = g.FirstOrDefault().person.FirstName,
             LastName = g.FirstOrDefault().person.LastName,
             PhoneNumber = g.FirstOrDefault().phoneNumbers.FirstOrDefault().PhoneNumber,
             EmailAddress = g.FirstOrDefault().emailAddresses.FirstOrDefault().Email,
             Branch = g.FirstOrDefault().customer.Branch.Name
         }).ToList();

Since it looks like you're using linq-to-sql or EntityFramework, This should be a good solution to do the grouping on the SQL server side. Otherwise it's totally possible to do the grouping in memory after executing the .ToList()

Upvotes: 0

Related Questions