CODEWITHSUNDEEP
.netsql-server-2005linq-to-sqlc#-3.0
Mark Withers
Mark Withers

Reputation: 1482

C# Dynamic WHERE clause in a LINQ to SQL Query

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

Upvotes: 3

Views: 22589

Answers (4)

Joshua
Joshua

Reputation: 1961

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

linq using string as where statement

Upvotes: 4

Nick
Nick

Reputation: 5696

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)

Upvotes: 0

TGnat
TGnat

Reputation: 4011

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

Upvotes: 0

Manu
Manu

Reputation: 29163

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                           
}

Upvotes: 0

Related Questions