Conditional Linq Queries

Question

We're working on a Log Viewer. The use will have the option to filter by user, severity, etc. In the Sql days I'd add to the query string, but I want to do it with Linq. How can I conditionally add where-clauses?

Answer 1

Just use C#'s && operator:

var items = dc.Users.Where(l => l.Date == DateTime.Today && l.Severity == "Critical")

Edit: Ah, need to read more carefully. You wanted to know how to conditionally add additional clauses. In that case, I have no idea. :) What I'd probably do is just prepare several queries, and execute the right one, depending on what I ended up needing.

Answer 2

if you want to only filter if certain criteria is passed, do something like this

var logs = from log in context.Logs
           select log;

if (filterBySeverity)
    logs = logs.Where(p => p.Severity == severity);

if (filterByUser)
    logs = logs.Where(p => p.User == user);

Doing so this way will allow your Expression tree to be exactly what you want. That way the SQL created will be exactly what you need and nothing less.

Answer 3

You could use an external method:

var results =
    from rec in GetSomeRecs()
    where ConditionalCheck(rec)
    select rec;

...

bool ConditionalCheck( typeofRec input ) {
    ...
}

This would work, but can't be broken down into expression trees, which means Linq to SQL would run the check code against every record.

Alternatively:

var results =
    from rec in GetSomeRecs()
    where 
        (!filterBySeverity || rec.Severity == severity) &&
        (!filterByUser|| rec.User == user)
    select rec;

That might work in expression trees, meaning Linq to SQL would be optimised.

Answer 4

Well, what I thought was you could put the filter conditions into a generic list of Predicates:

    var list = new List<string> { "me", "you", "meyou", "mow" };

    var predicates = new List<Predicate<string>>();

    predicates.Add(i => i.Contains("me"));
    predicates.Add(i => i.EndsWith("w"));

    var results = new List<string>();

    foreach (var p in predicates)
        results.AddRange(from i in list where p.Invoke(i) select i);

That results in a list containing "me", "meyou", and "mow".

You could optimize that by doing the foreach with the predicates in a totally different function that ORs all the predicates.

Answer 5

It isn't the prettiest thing but you can use a lambda expression and pass your conditions optionally. In TSQL I do a lot of the following to make parameters optional:

WHERE Field = @FieldVar OR @FieldVar IS NULL

You could duplicate the same style with a the following lambda (an example of checking authentication):

MyDataContext db = new MyDataContext();

void RunQuery(string param1, string param2, int? param3){

Func checkUser = user =>

((param1.Length > 0)? user.Param1 == param1 : 1 == 1) &&

((param2.Length > 0)? user.Param2 == param2 : 1 == 1) &&

((param3 != null)? user.Param3 == param3 : 1 == 1);

User foundUser = db.Users.SingleOrDefault(checkUser);

}

Answer 6

When it comes to conditional linq, I am very fond of the filters and pipes pattern.
http://blog.wekeroad.com/mvc-storefront/mvcstore-part-3/

Basically you create an extension method for each filter case that takes in the IQueryable and a parameter.

public static IQueryable<Type> HasID(this IQueryable<Type> query, long? id)
{
    return id.HasValue ? query.Where(o => i.ID.Equals(id.Value)) : query;
}

Answer 7

Another option would be to use something like the PredicateBuilder discussed here. It allows you to write code like the following:

var newKids  = Product.ContainsInDescription ("BlackBerry", "iPhone");

var classics = Product.ContainsInDescription ("Nokia", "Ericsson")
                  .And (Product.IsSelling());

var query = from p in Data.Products.Where (newKids.Or (classics))
            select p;

Note that I've only got this to work with Linq 2 SQL. EntityFramework does not implement Expression.Invoke, which is required for this method to work. I have a question regarding this issue here.

Answer 8

I had a similar requirement recently and eventually found this in he MSDN. CSharp Samples for Visual Studio 2008

The classes included in the DynamicQuery sample of the download allow you to create dynamic queries at runtime in the following format:

var query =
db.Customers.
Where("City = @0 and Orders.Count >= @1", "London", 10).
OrderBy("CompanyName").
Select("new(CompanyName as Name, Phone)");

Using this you can build a query string dynamically at runtime and pass it into the Where() method:

string dynamicQueryString = "City = \"London\" and Order.Count >= 10"; 
var q = from c in db.Customers.Where(queryString, null)
        orderby c.CompanyName
        select c;

Answer 9

I ended using an answer similar to Daren's, but with an IQueryable interface:

IQueryable<Log> matches = m_Locator.Logs;

// Users filter
if (usersFilter)
    matches = matches.Where(l => l.UserName == comboBoxUsers.Text);

 // Severity filter
 if (severityFilter)
     matches = matches.Where(l => l.Severity == comboBoxSeverity.Text);

 Logs = (from log in matches
         orderby log.EventTime descending
         select log).ToList();

That builds up the query before hitting the database. The command won't run until .ToList() at the end.

Answer 10

The Dynamic LINQ library is exactly what you're looking for.

Example:

var query =
    context.MyTable.Where(
        "Column1 < {0} and Column2 == {1}", 5, "StringValue"
    );