How to write this Linq SQL as a Dynamic Query (using strings)?
- by Dr. Zim
Skip to the "specific question" as needed. Some background:
The scenario: I have a set of products with a "drill down" filter (Query Object) populated with DDLs. Each progressive DDL selection will further limit the product list as well as what options are left for the DDLs. For example, selecting a hammer out of tools limits the Product Sizes to only show hammer sizes.
Current setup: I created a query object, sent it to a repository, and fed each option to a SQL "table valued function" where null values represent "get all products".
I consider this a good effort, but far from DDD acceptable. I want to avoid any "programming" in SQL, hopefully doing everything with a repository. Comments on this topic would be appreciated.
Specific question:
How would I rewrite this query as a Dynamic Query? A link to something like 101 Linq Examples would be fantastic, but with a Dynamic Query scope. I really want to pass to this method the field in quotes "" for which I want a list of options and how many products have that option.
from p in db.Products
group p by p.ProductSize into g
select new Category {
PropertyType = g.Key,
Count = g.Count() }
Each DDL option will have "The selection (21)" where the (21) is the quantity of products that have that attribute. Upon selecting an option, all other remaining DDLs will update with the remaining options and counts.
Edit: Additional notes:
.OrderBy("it.City") // "it" refers to the entire record
.GroupBy("City", "new(City)") // This produces a unique list of City
.Select("it.Count()") //This gives a list of counts... getting closer
.Select("key") // Selects a list of unique City
.Select("new (key, count() as string)") // +1 to me LOL. key is a row of group
.GroupBy("new (City, Manufacturer)", "City") // New = list of fields to group by
.GroupBy("City", "new (Manufacturer, Size)") // Second parameter is a projection
Product
.Where("ProductType == @0", "Maps")
.GroupBy("new(City)", "new ( null as string)")// Projection not available later?
.Select("new (key.City, it.count() as string)")// GroupBy new makes key an object
Product
.Where("ProductType == @0", "Maps")
.GroupBy("new(City)", "new ( null as string)")// Projection not available later?
.Select("new (key.City, it as object)")// the it object is the result of GroupBy
var a = Product
.Where("ProductType == @0", "Maps")
.GroupBy("@0", "it", "City") // This fails to group Product at all
.Select("new ( Key, it as Product )"); // "it" is property cast though
What I have learned so far is LinqPad is fantastic, but still looking for an answer. Eventually, completely random research like this will prevail I guess. LOL.
Edit:
