CODEWITHSUNDEEP
linq-to-sql
mad moe
mad moe

Reputation: 322

LINQ to SQL Query Where Clause

I would like to create a single query that "adjusts" it's where clause based on a tuple. The first item in the tuple contains a enum value indicating the field in which to filter. The second tuple item is the filter value.

Notice the query below does not work:

        var query = from p in db.Categories
        where ( QueryBy.Item1 == CategoryFields.Name        && p.Name        == (string)(QueryBy.Item2) ) ||
              ( QueryBy.Item1 == CategoryFields.Id          && p.Id          == (long)(QueryBy.Item2)   ) ||
              ( QueryBy.Item1 == CategoryFields.Description && p.Description == (string)(QueryBy.Item2) ) ||
              ( QueryBy.Item1 == CategoryFields.SortOrder   && p.SortOrder   == (int)(QueryBy.Item2)    ) 
        select...

        if (query.Count() == 1) // ERRORS HERE CONVERSION OF INT

A similar query with only this where clause change will works:

        var query = from p in db.Categories
        where ( QueryBy.Item1 == CategoryFields.Name        && p.Name        == (string)(QueryBy.Item2) )
        select...

        if (query.Count() == 1) // Works HERE

Any idea what could be wrong? Can it be that LINQ where clause perform a short-circuit evaluation and thus the cast of item2 fails? Is there a better way to accomplish my overall goal of adjusting where clause?

Thanks in advance for your help!

Upvotes: 1

Views: 1108

Answers (1)

Steven
Steven

Reputation: 172835

LINQ to SQL isn't smart enough to optimize your query and generate it dynamically based on the value of your QueryBy.Item1. It will simply generate a SQL query let SQL server decide this for itself.

When you know that, the error makes sense, since it's impossible for one single value to be castable to both int, long, and string.

In your case you would be better of dynamically generating the right where clause. You can do this with the PredicateBuilder:

IQueryable<Category> query = db.Categories;

var whereClause = PredicateBuilder.False<Category>();

switch (QueryBy.Item1)
{
    case CategoryFields.Name:
        long id = (string)QueryBy.Item2;
        whereClause = whereClause.Or(p => p.Name == name);
        break;

    case CategoryFields.Id:
        string name = (string)QueryBy.Item2;
        whereClause = whereClause.Or(p => p.Id == id);
        break;

    case CategoryFields.Description:
        string des = (string)QueryBy.Item2;
        whereClause =
            whereClause.Or(p => p.Description == des);
        break;

    case CategoryFields.Id:
        string sort = (int)QueryBy.Item2;
        whereClause =
            whereClause.Or(p => p.SortOrder == sort);
        break;
}

query = query.Where(whereClause);

Upvotes: 4

Related Questions