Now that we’ve touched on “group by” lets throw in a couple of where clauses on the group by with any and all.
If you are really interested in all the data I have the code at the end of this post showing how I add all the products. Since this is just sample data I threw it in a list but chances are you’re going to be getting your list from some sort of DAL.
Let’s start with some lead up to “Any”.
Given:
string[] words = { "cow", "believe", "bread", "river", "receipt", "field" }; var wordsList = from w in words where w.Contains("ei") select w;
we can see this gives a list of words that have “ei” in them. But what if we don’t care for the list? What if we just want to know if any of the words have “ei”? We can simplify life by using the Any on a list from linq and get a boolean.
bool iAfterE = words.Any(w => w.Contains("ei"));
is true.
“No kidding”
“Oh great, folks, if you don’t know already this is Ivan.”
“So next you’re going to tell me I can use the All method on lists to tell me if all of the words contain “ei”? Come on and give your audience some credit. If you’re head wasn’t stuck so far…”
“Ivan, I get the point, I’ll make this easy for all of us and give them the code.”
“And don’t you think this whole talking to yourself think is getting old? I mean come on!”
“You think you can do better?”
“Listen to yourself. You’re talking to me who just happens to me you. What does me doing any better have to do with anything? I am you. Wow, pull yourself together and get this overwith. We have a database rewrite to review today, implement tomorrow and then a week to recode the app for the new database, time to move on.”
“Fine, here” (Note: I couldn’t do it, it looked like crap when I just put up all the code. I have to break it up a little bit so I’m ignoring Ivan because it was just a bad suggestion.)
string[] words = { "cow", "believe", "bread", "river", "receipt", "field" }; var wordsList = from w in words where w.Contains("ei") select w; bool iAfterE = words.All(w => w.Contains("ei"));
As I’m sure you know, iAfterE is false as not all the words have “ei” in them.
List<Product> products = GetProductList(); var productGroups = from p in products group p by p.Category into g where g.Any(p => p.UnitsInStock == 0) select new { Category = g.Key, Products = g }; Console.WriteLine("nFor Any:"); foreach (var group in productGroups) { Console.WriteLine("nFor Category {0}:", group.Category); foreach (var p in group.Products) { Console.Write("{0} with {1} units, ", p.ProductName, p.UnitsInStock); } }
In this first product group you can see that the where on the group by is only creating groups by category where any of the products in the category have 0 units in stock. This is key to the where with group by, only if the where is satisfied will the group be created. You will end up with:
For Any:
For Category Condiments:
Aniseed Syrup with 13 units, Chef Anton’s Cajun Seasoning with 53 units, Chef Anton’s Gumbo Mix with 0 units, Grandma’s Boysenberry Spread with 120 units, Northwoods Cranberry Sauce with 6 units, Genen Shouyu with 39 units,
For Category MeatPoultry:
Mishi Kobe Niku with 29 units, Alice Mutton with 0 units, Thüringer Rostbratwurst with 0 units, Perth Pasties with 0 units, Tourtière with 21 units,
For Category DairyProducts:
Queso Cabrales with 22 units, Queso Manchego La Pastora with 86 units, Gorgonzola Telino with 0 units, Mascarpone Fabioli with 9 units,
List<Product> products = GetProductList(); var productGroups = from p in products group p by p.Category into g where g.All(p=> p.UnitsInStock > 0) select new { Category = g.Key, Products = g }; Console.WriteLine("nFor All:"); foreach (var group in productGroups) { Console.WriteLine("nFor Category {0}:", group.Category); foreach (var p in group.Products) { Console.Write("{0} with {1} units, ", p.ProductName, p.UnitsInStock); } }
For the second product group it is looking to make sure that all products in the group of a UnitsInStock of greater then 0 thus giving you:
For All:
For Category Seafood:
Ikura with 31 units, Konbu with 24 units, Carnarvon Tigers with 42 units, Nord-Ost Matjeshering with 10 units, Inlagd Sill with 112 units, Gravad lax with 11 units,
For Category Produce:
Uncle Bob’s Organic Dried Pears with 15 units, Tofu with 35 units, Rössle Sauerkraut with 26 units,
For Category Beverages:
Chai with 39 units, Chang with 17 units, Guaraná Fantástica with 20 units, Sasquatch Ale with 111 units, Steeleye Stout with 20 units,
Well, that’s a quick write up on any and all with group by. Time to start reviewing ERDs.
Brian
And here is GetProductList:
private List<Product> GetProductList() { List<Product> products = new List<Product>(); products.Add(new Product { ProductId = 10, ProductName = "Ikura", Category = "Seafood", UnitPrice = 31.0000, UnitsInStock = 31 }); products.Add(new Product { ProductId = 13, ProductName = "Konbu", Category = "Seafood", UnitPrice = 6.0000, UnitsInStock = 24 }); products.Add(new Product { ProductId = 18, ProductName = "Carnarvon Tigers", Category = "Seafood", UnitPrice = 62.5000, UnitsInStock = 42 }); products.Add(new Product { ProductId = 30, ProductName = "Nord-Ost Matjeshering", Category = "Seafood", UnitPrice = 25.8900, UnitsInStock = 10 }); products.Add(new Product { ProductId = 36, ProductName = "Inlagd Sill", Category = "Seafood", UnitPrice = 19.0000, UnitsInStock = 112 }); products.Add(new Product { ProductId = 37, ProductName = "Gravad lax", Category = "Seafood", UnitPrice = 26.0000, UnitsInStock = 11 }); products.Add(new Product { ProductId = 7, ProductName = "Uncle Bob's Organic Dried Pears", Category = "Produce", UnitPrice = 30.0000, UnitsInStock = 15 }); products.Add(new Product { ProductId = 14, ProductName = "Tofu", Category = "Produce", UnitPrice = 23.2500, UnitsInStock = 35 }); products.Add(new Product { ProductId = 28, ProductName = "Rössle Sauerkraut", Category = "Produce", UnitPrice = 45.6000, UnitsInStock = 26 }); products.Add(new Product { ProductId = 3, ProductName = "Aniseed Syrup", Category = "Condiments", UnitPrice = 10.0000, UnitsInStock = 13 }); products.Add(new Product { ProductId = 4, ProductName = "Chef Anton's Cajun Seasoning", Category = "Condiments", UnitPrice = 22.0000, UnitsInStock = 53 }); products.Add(new Product { ProductId = 5, ProductName = "Chef Anton's Gumbo Mix", Category = "Condiments", UnitPrice = 21.3500, UnitsInStock = 0 }); products.Add(new Product { ProductId = 6, ProductName = "Grandma's Boysenberry Spread", Category = "Condiments", UnitPrice = 25.0000, UnitsInStock = 120 }); products.Add(new Product { ProductId = 8, ProductName = "Northwoods Cranberry Sauce", Category = "Condiments", UnitPrice = 40.0000, UnitsInStock = 6 }); products.Add(new Product { ProductId = 15, ProductName = "Genen Shouyu", Category = "Condiments", UnitPrice = 15.5000, UnitsInStock = 39 }); products.Add(new Product { ProductId = 9, ProductName = "Mishi Kobe Niku", Category = "MeatPoultry", UnitPrice = 97.0000, UnitsInStock = 29 }); products.Add(new Product { ProductId = 17, ProductName = "Alice Mutton", Category = "MeatPoultry", UnitPrice = 39.0000, UnitsInStock = 0 }); products.Add(new Product { ProductId = 29, ProductName = "Thüringer Rostbratwurst", Category = "MeatPoultry", UnitPrice = 123.7900, UnitsInStock = 0 }); products.Add(new Product { ProductId = 53, ProductName = "Perth Pasties", Category = "MeatPoultry", UnitPrice = 32.8000, UnitsInStock = 0 }); products.Add(new Product { ProductId = 54, ProductName = "Tourtière", Category = "MeatPoultry", UnitPrice = 7.4500, UnitsInStock = 21 }); products.Add(new Product { ProductId = 11, ProductName = "Queso Cabrales", Category = "DairyProducts", UnitPrice = 21.0000, UnitsInStock = 22 }); products.Add(new Product { ProductId = 12, ProductName = "Queso Manchego La Pastora", Category = "DairyProducts", UnitPrice = 38.0000, UnitsInStock = 86 }); products.Add(new Product { ProductId = 31, ProductName = "Gorgonzola Telino", Category = "DairyProducts", UnitPrice = 12.5000, UnitsInStock = 0 }); products.Add(new Product { ProductId = 32, ProductName = "Mascarpone Fabioli", Category = "DairyProducts", UnitPrice = 32.0000, UnitsInStock = 9 }); products.Add(new Product { ProductId = 1, ProductName = "Chai", Category = "Beverages", UnitPrice = 18.0000, UnitsInStock = 39 }); products.Add(new Product { ProductId = 2, ProductName = "Chang", Category = "Beverages", UnitPrice = 19.0000, UnitsInStock = 17 }); products.Add(new Product { ProductId = 24, ProductName = "Guaraná Fantástica", Category = "Beverages", UnitPrice = 4.5000, UnitsInStock = 20 }); products.Add(new Product { ProductId = 34, ProductName = "Sasquatch Ale", Category = "Beverages", UnitPrice = 14.0000, UnitsInStock = 111 }); products.Add(new Product { ProductId = 35, ProductName = "Steeleye Stout", Category = "Beverages", UnitPrice = 18.0000, UnitsInStock = 20 }); return products; }