如何写这个LINQ的SQL作为一个动态查询（使用字符串）？ [英] How to write this Linq SQL as a Dynamic Query (using strings)?
问题描述
根据需要跳转到了具体问题。一些背景：
场景：我有一整套的产品以深入过滤器（查询对象），填入的DDL。每一个进步的DDL的选择将进一步限制产品清单，以及什么选项，留下的DDL。例如，选择一锤出来的工具，限制了产品的规格，只显示锤尺寸
当前设置：我创建了一个查询对象，它发送到存储库，并送入每个选项到SQL的表值函数，其中空值代表让所有的产品。
我认为这是一个很好的努力，但远DDD接受。我想避免任何SQL编程，希望做一个资料库的一切。 。关于这个主题的意见，将不胜感激。
具体的问题：
将如何我重写此查询为的动态查询？链接到像 101 LINQ的例子将是非常美妙的，但用动态查询范围。我真的想通过这种方法领域的引号对，我想一个选项列表和多少产品有这样的选择。
从p在db.Products
群p由p.ProductSize为G
选择新类别{
属性类型= g.Key，
计数= g.Count（ ）}
每个DDL选项将选择（21），其中（21）是所有具有该属性的产品数量。 。在选择一个选项，所有其他剩余的DDL将与其余选项和计数更新
编辑：其他注意事项：
.OrderBy（it.City）//它指的是整个记录
.GroupBy（城市，新（市））//这将产生市
。选择的唯一列表（it.Count（））//这给计数的列表...越来越近
。选择（ 键）//选择的独特的城市
。选择列表（新的（键，计数（）作为字符串））// +1对我大声笑。关键是一群
.GroupBy的一排（新（市，生产商），城市）//字段组由
.GroupBy新=列表（城市，新（制造商，大小））//第二个参数是投影
产品
。凡（ProductType == @ 0，地图）
.GroupBy（「新（市），新（null作为字符串））//投影不可用以后呢？
。选择（新的（key.City，it.count（）作为字符串））// GROUPBY新做密钥的对象
产品
。凡（ProductType == @ 0，地图）
.GroupBy（新（市），新（null作为字符串））//后来投影不可用？
。选择（新的（key.City，它作为对象））//它的对象是GROUPBY
VAR一个=产品
。凡结果（ ProductType == @ 0，地图）
.GroupBy（@ 0，它，城市）//这不组产品在所有
。选择（新的（键，它作为产品））; //它是房地产投虽然
我迄今已学到的的 LinqPad 是太棒了，但仍然在寻找一个答案。最终，像这样完全随机的研究将占上风，我猜。 。LOL
编辑：
乔恩斯基特有一个梦幻般的想法：投什么我需要为 IGrouping<字符串，产品与GT; 。感谢乔恩斯基特！一旦你投的对象，你可以通过设置枚举，并在一个单独的列表喂结果。
我不知道如何做到这一点使用的查询语法（如上），但使用方法的语法，我们可以使用一个表达式来; Func键<如下所示。此示例工程对AdventureWorks的SQL Server数据库（产品表），并允许您使用字符串（在此示例中，我选择大小）
<$来指定哪些列组p $ p>
使用系统;
使用System.Linq的;使用System.Linq.Expressions
;
命名空间LinqResearch
{
公共类节目
{
[STAThread]
静态无效的主要（）
{
串columnToGroupBy =大小;
//生成动态表达式来; Func键<产品，串>>
ParameterExpression p值= Expression.Parameter（typeof运算（产品），P）;
VAR选择= Expression.Lambda< Func键<产品，串>>（
Expression.Property（P，columnToGroupBy），
p
）;使用
（LinqDataContext的DataContext =新LinqDataContext（））
{
/ *使用选择上面这caluclated自动$当查询运行* /
编译b $ b VAR的结果= DataContext的
。产品
.GroupBy（选择）
。选择（（组）=>新建{
键= group.Key，
计数= group.Count（）
}）;
的foreach（VAR导致的结果）
Console.WriteLine（{0}：{1}，result.Key，result.Count）;
}
Console.ReadKey（）;
}
}
}
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:
Jon Skeet had a fantastic idea: cast what I need as IGrouping<string, Product>. Thanks to Jon Skeet! Once you cast the object, you can enumerate over the sets and feed the results in to a separate List.
I'm not sure how to do this using Query syntax (as above), but using Method syntax, we can use an Expression<Func< as shown below. This sample works against the AdventureWorks SQL Server database (Products table), and allows you to specify which column to group by using a string (in this sample I have chosen Size)
using System;
using System.Linq;
using System.Linq.Expressions;
namespace LinqResearch
{
public class Program
{
[STAThread]
static void Main()
{
string columnToGroupBy = "Size";
// generate the dynamic Expression<Func<Product, string>>
ParameterExpression p = Expression.Parameter(typeof(Product), "p");
var selector = Expression.Lambda<Func<Product, string>>(
Expression.Property(p, columnToGroupBy),
p
);
using (LinqDataContext dataContext = new LinqDataContext())
{
/* using "selector" caluclated above which is automatically
compiled when the query runs */
var results = dataContext
.Products
.GroupBy(selector)
.Select((group) => new {
Key = group.Key,
Count = group.Count()
});
foreach(var result in results)
Console.WriteLine("{0}: {1}", result.Key, result.Count);
}
Console.ReadKey();
}
}
}
这篇关于如何写这个LINQ的SQL作为一个动态查询（使用字符串）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
