如何将此 Linq SQL 编写为动态查询(使用字符串)?

How to write this Linq SQL as a Dynamic Query (using strings)?(如何将此 Linq SQL 编写为动态查询(使用字符串)?)

问题描述

根据需要跳到特定问题".一些背景:

Skip to the "specific question" as needed. Some background:

场景:我有一组产品，其中包含填充了 DDL 的向下钻取"过滤器(查询对象).每个渐进式 DDL 选择将进一步限制产品列表以及为 DDL 留下的选项.例如，从工具中选择锤子会将产品尺寸限制为仅显示锤子尺寸.

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.

当前设置:我创建了一个查询对象，将其发送到存储库，并将每个选项提供给 SQL表值函数"，其中空值表示获取所有产品".

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".

我认为这是一个很好的努力，但远不能接受 DDD.我想避免在 SQL 中进行任何编程"，希望能用存储库做所有事情.对此主题的评论将不胜感激.

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.

具体问题:

我将如何将此查询重写为 动态查询?像 101 Linq 示例 之类的链接会很棒，但是动态查询范围.我真的想将引号 "" 中的字段传递给此方法，我想要一个选项列表以及有多少产品具有该选项.

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() }

每个 DDL 选项都有选择 (21)"，其中 (21) 是具有该属性的产品数量.选择一个选项后，所有其他剩余的 DDL 将更新为剩余的选项和计数.

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.

附加说明:

.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

到目前为止我学到的是 LinqPad 很棒，但仍在寻找答案.最终，我猜这样完全随机的研究会占上风.哈哈.

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.

Jon Skeet 有一个绝妙的主意:将我需要的内容转换为 IGrouping.感谢乔恩·斯基特！转换对象后，您可以枚举集合并将结果输入到单独的列表中.

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.

推荐答案

我不知道如何使用 Query 语法(如上所述)来做到这一点，但是使用 Method 语法，我们可以使用 Expression

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 编写为动态查询(使用字符串)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！