Grouping without duplicate supplier string
Can't get data from NorthWind using Linq-to-SQL from three tables:
-
Suppliers
-
Products
-
Categories
I would like to receive suppliers of all products that fall under category c categoryId > 3
. The result set would require 1 row for each vendor and then some set of children containing a row for each product including category information. The idea is that this result will be returned as a json value from the ajax call.
Below is the simplest version of my efforts:
from sups in Suppliers
join prods in Products on sups.SupplierID equals prods.SupplierID
join cats in Categories on prods.CategoryID equals cats.CategoryID
where ( cats.CategoryID > 3)
group sups by sups.SupplierID into g
select g
In LinqPad, it looks like the result set contains many duplicate providers. Any thoughts?
Editor: Thanks to Adduci's answer, I ended up working on the following LINQ statement:
from sups in Suppliers
join prods in Products on sups.SupplierID equals prods.SupplierID
join cats in Categories on prods.CategoryID equals cats.CategoryID
where cats.CategoryID > 3
group new { sups, prods, cats } by new { sups.SupplierID, sups.CompanyName } into g
select new
{
g.Key,
ProductInfo = from x in g
select new
{
ProductProperty = x.prods.ProductName,
CategoryProperty = x.cats.CategoryName
}
}
The optional by new { sups.SupplierID, sups.CompanyName }
completes the result set, including the supplier fields. Nice!
source to share
The first step is to join the three tables together using the anonymous class
group new { sups, prods, cats }
Instead of select g
which is ( IGrouping<...>
), you must explicitly define the properties you want:
from sups in Suppliers
join prods in Products on sups.SupplierID equals prods.SupplierID
join cats in Categories on prods.CategoryID equals cats.CategoryID
where cats.CategoryID > 3
group new { sups, prods, cats } by sups.SupplierID into g
select new
{
Supplier = g.Key,
ProductInfo = from x in g
select new
{
ProductProperty = x.prods.Prop1,
CategoryProperty = x.cats.Prop1
}
}
This will prevent the return of unused information from the database.
source to share
This is often the case when joining tables. You can usually fix this with Distinct()
.
Something like that:
(from sups in Suppliers
join prods in Products on sups.SupplierID equals prods.SupplierID
join cats in Categories on prods.CategoryID equals cats.CategoryID
where ( cats.CategoryID > 3)
group sups by sups.SupplierID into g
select g).Distinct()
source to share