CODEWITHSUNDEEP
c#mysqllinq
user4625532
user4625532

Reputation:

LINQ Join through 3 tables to Sum one column

I'm trying to get the total number of sales per City and display the result in a DataGrid. So far I've lost more hair than made progress. I'm new to LINQ and I'm probably stressing over nothing. Here's what I've got:

var sales = from sale in db.SalesOrderHeaders
                    join cust in db.Customers on sale.CustomerID equals cust.CustomerID
                    join cad in db.CustomerAddresses on cust.CustomerID equals cad.CustomerID
                    join ad in db.Addresses on cad.AddressID equals ad.AddressID
                    group sale.TotalDue by ad.City into g
                    select new { City = ad.City, Sales = g.Sum() };

And here's the dbml. Its the TotalDue Column I'm tryin to sum and sort by City in the Address Table. enter image description here

Would Appreciate any input!

Upvotes: 4

Views: 2486

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109079

You don't need the joins, because you can use navigation properties, and you can start the query at the other end:

from address in db.Address
select new 
       {
           City = address.City,
           Sales = (from ca in address.CustomerAddresses
                    // where ca.AddressType == someVariable  
                    from soh in ca.Customer.SalesOrderHeaders
                    select soh.TotalDue).Sum()
       }

The navigation properties don't show in the dbml diagram, but they are there in the c# classes.

You can add the where clause (where ca.AddressType == someVariable) if you want the sum for a specific address type.

Upvotes: 0

Aducci
Aducci

Reputation: 26644

I think you need to use City = g.Key instead of City = ad.City

But you most likely do not need to use the join syntax at all, since you have already set up the relationships in your database. You can just use the navigation properties on the objects 

var sales = from ad in db.Addresses
            from cad in ad.CustomerAddresses
            from sale in cad.Customer.SalesOrderHeaders
            group sale.TotalDue by ad.City into g
            select new
            {
                City = g.Key, 
                Sales = g.Sum()
            };

I assumed you want to group by theCustomerAddress, maybe you want to use the ShipToAddress or BillToAddress?

Upvotes: 1

Related Questions