C# Dynamic WHERE clause in a LINQ to SQL Query

Question

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.

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

private struct ProductStruct
{
    public long ProductID;
}

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

List<FilterStruct> filterList = new List<FilterStruct>();
filterList.Add(new FilterStruct { ProductTypeFieldID = 3, ValueNumber = 195 });
filterList.Add(new FilterStruct { ProductTypeFieldID = 8, ValueNumber = 55 });
 
List<ProductStruct> productList = new List<ProductStruct>();
 
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<FilterStruct> filterList = new List<FilterStruct>();
            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<ProductStruct> productList = new List<ProductStruct>();
            productList = (from pfv in dataConnection.ProductFieldValues.Where(whereClause)
                            select new ProductStruct
                                  {
                                  ProductID = pfv.ProductID
                                  }).ToList();
    } 
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

Glorfindel · Accepted Answer · 2019-04-15 04:49:16Z

4

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

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

edited Apr 15, 2019 at 4:49

Glorfindel

22.8k13 gold badges97 silver badges124 bronze badges

answered Aug 20, 2009 at 13:25

Joshua

1,9611 gold badge19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tvanfosson Over a year ago

I think the idea to use Dynamic LINQ and build the where clause as a string has merit.

tvanfosson Over a year ago

You might want to post a summary here, so your answer stands on its own.

Manu · Accepted Answer · 2009-08-20 13:25:39Z

0

You could use a JOIN clause:

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

edited Aug 20, 2009 at 13:25

answered Aug 20, 2009 at 13:20

Manu

29.2k28 gold badges79 silver badges84 bronze badges

1 Comment

tvanfosson Over a year ago

I think this would only work if the original request was materialized and you did the join with LINQ to Objects. I would assume that the OP would want the filtering to be done on the SQL server otherwise he could just iterate through them and remove the ones he doesn't want.

TGnat · Accepted Answer · 2009-08-20 13:41:24Z

0

Here is a simple example using IQueryable in VB.

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#:

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

edited Aug 20, 2009 at 13:41

answered Aug 20, 2009 at 13:33

TGnat

4,0118 gold badges38 silver badges49 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:32:53Z

0

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

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

(The answer was use a Predicate Builder)

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Aug 20, 2009 at 14:46

Nick

5,69610 gold badges56 silver badges73 bronze badges

Collectives™ on Stack Overflow

C# Dynamic WHERE clause in a LINQ to SQL Query

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related