.net LINQ to SQL 查询中的 C# 动态 WHERE 子句

C# Dynamic WHERE clause in a LINQ to SQL Query

提问by Mark Withers

I would like to execute a LINQ query with a dynamic where clause depending on how many different options a user has entered for their criteria.

我想使用动态 where 子句执行 LINQ 查询，具体取决于用户为其条件输入的不同选项的数量。

Is this possible?

这可能吗？

I have posted code below of how I would like it to work.

我已经在下面发布了我希望它如何工作的代码。

Anyone got any suggestions?

有人有什么建议吗？

P.S. I have attempted using the .Contains method (generating a WHERE IN on SQL, however the number of parameters was above the 2100 threshold and caused the error "LINQ The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100".

PS 我曾尝试使用 .Contains 方法（在 SQL 上生成 WHERE IN，但是参数数量高于 2100 阈值并导致错误“LINQ 传入表格数据流 (TDS) 远程过程调用 (RPC) 协议流是不正确。此 RPC 请求中提供的参数过多。最大为 2100"。

private struct ProductStruct
        {
            public long ProductID;
        }

        private struct FilterStruct
        {
            public long ProductTypeFieldID;
            public long ValueNumber;
        }

List filterList = new List();
filterList.Add(new FilterStruct { ProductTypeFieldID = 3, ValueNumber = 195 });
filterList.Add(new FilterStruct { ProductTypeFieldID = 8, ValueNumber = 55 });

List productList = new List();

productList = (from pfv in dC.ProductFieldValues
                           where
                                foreach (FilterStruct filter in filterList)
                                {
                                    pfv.ProductTypeFieldID == filter.ProductTypeFieldID
                                    && pfv.ValueNumber == filter.ValueNumber
                                }
                           select new ProductStruct
                           {
                               ProductID = pfv.ProductID
                           }).ToList();

EDIT

编辑

This looks as if it could be handy, but doesnt work with a dynamic where in?

这看起来好像很方便，但在动态中不起作用？

 private void Option2()
        {
            try
            {
                LinqDataDataContext dataConnection = new LinqDataDataContext(ConnectionString);

                List filterList = new List();
                    filterList.Add(new FilterStruct { ProductTypeFieldID = 3, ValueNumber = 195 });
                    filterList.Add(new FilterStruct { ProductTypeFieldID = 8, ValueNumber = 55 });

                string whereClause = null;
                foreach (FilterStruct filter in filterList)
                {
                    if (whereClause != null)
                        whereClause += "AND ";

                    whereClause += string.Format("ProductID IN (SELECT ProductID FROM ProductFieldValue WHERE ProductTypeFieldID = {0} AND ValueNumber = {1})", filter.ProductTypeFieldID, filter.ValueNumber);

                }


                List productList = new List();
                    productList = (from pfv in dataConnection.ProductFieldValues.Where(whereClause)
                                    select new ProductStruct
                                          {
                                          ProductID = pfv.ProductID
                                          }).ToList();

            } 
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }


thanks in advance

回答by Joshua

From the accepted answer here: How do I build up LINQ dynamically

从这里接受的答案：How do I build up LINQ dynamic

This works well for .AsQueryable() classes, but .Enumerable() classes can be used if you use "as iQueryable()"

这适用于 .AsQueryable() 类，但如果您使用“as iQueryable()”，则可以使用 .Enumerable() 类

回答by Manu

You could use a JOIN clause:

您可以使用 JOIN 子句：

from pfv in dC.ProductFieldValues
join filter in filterList on (
    pfv.ProductTypeFieldID == filter.ProductTypeFieldID 
    && pfv.ValueNumber == filter.ValueNumber
)
select new ProductStruct                           
{                               
    ProductID = pfv.ProductID                           
}

回答by TGnat

Here is a simple example using IQueryable in VB.

这是一个在 VB 中使用 IQueryable 的简单示例。

Private Function GetZeroScoreWhere(ByVal qry As IQueryable(Of ScoreTest), ByVal score As ScoreTest) As IQueryable(Of ScoreTest)
    If score.CallType = ScoreTest.CallTypeEnum.XXX Then
        Return qry.Where(Function(c) c.AvgScore.Value = 0 Or c.Zero.Trim <> String.Empty)
    End If

    Return qry.Where(Function(c) c.ValidScore.Value = 0)
End Function

The same code in C#:

C#中的相同代码：

private IQueryable<ScoreTest> GetZeroScoreWhere(IQueryable<ScoreTest> qry, ScoreTest score)
{
    if(score.CallType == ScoreTest.CallTypeEnum.XXX)
    {
        Return qry.Where(c => c.AvgScore.Value == 0 || c.Zero.Trim != String.Empty)
    }

    Return qry.Where(c => c.ValidScore.Value == 0)    
}

回答by Nick

The answer I got in a similar question worked for me:

我在类似问题中得到的答案对我有用：

How do you add dynamic 'where' clauses to a linq query?

如何向 linq 查询添加动态“where”子句？

(The answer was use a Predicate Builder)

（答案是使用Predicate Builder）