CODEWITHSUNDEEP
c#linq
aleczandru
aleczandru

Reputation: 5449

Duplicate row removal

First of all I will post what I have done so far:

from person in Persons
join customer in Customers on person.BusinessEntityID equals customer.PersonID
join salesOrderHeader in SalesOrderHeaders on customer.CustomerID equals salesOrderHeader.CustomerID
where salesOrderHeader.SubTotal > 1000
join address in Addresses on salesOrderHeader.BillToAddressID equals address.AddressID
where address.City == "Melton"
select new {
                person.FirstName,
                person.LastName,
                salesOrderHeader.SubTotal,
            address.City
       }

This query works perfectly only the problem is that I am getting back a duplicate row on the firstname and lastname. I could solve this by adding the Distinct() extension method already tried that and it works but I am very curios if it can be done without any extension method.

So how can I get the resulted table back without any duplicated rows on the firstname and lastname?

EDIT: In this case I recieve back something like this:

enter image description here

I would like to get back only one person of the same type with the total value that he spent.Sorry for not explaining myself better the first time around forgot to mention that I want to get back the total value of the person

Upvotes: 2

Views: 114

Answers (1)

usr
usr

Reputation: 171178

What you need is a GroupBy and an aggregation.

from person in Persons
join customer in Customers on person.BusinessEntityID equals customer.PersonID
join salesOrderHeader in SalesOrderHeaders on customer.CustomerID equals salesOrderHeader.CustomerID
where salesOrderHeader.SubTotal > 1000
join address in Addresses on salesOrderHeader.BillToAddressID equals address.AddressID
where address.City == "Melton"
group salesOrderHeader by new { person.FirstName, person.LastName } into g //new
select new {
                g.Key.FirstName,
                g.Key.LastName,
                SubTotal = g.Sum(salesOrderHeader => salesOrderHeader.SubTotal),
            address.City
       }

GroupBy collapsed multiple rows into one, which is what you want here.

Upvotes: 1

Related Questions