LINQ to SQL create query from string

Question

I have a dbml context query that looks something like this:

var SQLQueryResult = (from activeTable in context.activeTabless
                      where (
                               activeTable .AssignedTo == "Person1" ||
                               activeTable .AssignedTo == "Person2" ||
                               activeTable .AssignedTo == "Person3")
                    select new { ... });

My question is, how can I update the where field so that it can have any number of or (not just three as above) based on a user selection?

Let's say the number can come from a list or array. That's simple with straight SQL but not sure how to do it via Linq to SQL.

Jens Kloster · Accepted Answer · 2013-05-31 11:54:01Z

6

var persons = new []{"Person1", "Person2", "Person3"};
var SQLQueryResult = (from activeTable in context.activeTabless
                      where ( persons.Contains(activeTable .AssignedTo))
                    select new { ... });

You can check if something exists in a collection using the .Contains() extension method of IEnumerable.

edited May 31, 2013 at 11:54

Jens Kloster

11.3k5 gold badges44 silver badges55 bronze badges

answered May 31, 2013 at 11:50

nvoigt

77.7k28 gold badges103 silver badges156 bronze badges

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

2 Comments

Giedrius Over a year ago

It is a solution, but you need to watch out for at least two things: 1) it does not work if persons contains more than 2100 items on SQL server 2) it works terribly slow on ansi columns

sd_dracula Over a year ago

This is it. Thanks. And it should be good as it won't ever reach 2100 items

Ion Sapoval · Accepted Answer · 2013-05-31 11:52:04Z

1

You can create your query dynamically by using Expressions for being able to build where predicates. More details and a sample you can find here: Linq dynamic queries

answered May 31, 2013 at 11:52

Ion Sapoval

6355 silver badges8 bronze badges

Comments

Ondrej Svejdar · Accepted Answer · 2013-05-31 11:56:25Z

You can use predicate builder (utility class):

using System;
using System.Linq;
using System.Linq.Expressions;

public static class PredicateBuilder {
  public static Expression<Func<T, bool>> Make<T>() { 
    return null; 
  }

  public static Expression<Func<T, bool>> Make<T>(this Expression<Func<T, bool>> predicate) {
    return predicate;
  }

  public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> orExpression) {
    if (expr == null) {
      return orExpression;
    }
    var invokedExpr = Expression.Invoke(orExpression, expr.Parameters.Cast<Expression>());
    return Expression.Lambda<Func<T, bool>>(Expression.Or(expr.Body, invokedExpr), expr.Parameters);
  }

  public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr, Expression<Func<T, bool>> andExpression) {
    if (expr == null) {
      return andExpression;
    }
    var invokedExpr = Expression.Invoke(andExpression, expr.Parameters.Cast<Expression>());
    return Expression.Lambda<Func<T, bool>>(Expression.And(expr.Body, invokedExpr), expr.Parameters);
  }
}

Usage:

public IEnumerable<Squad> GetSquadsByIDs(IEnumerable<int> squadIDs) {
  if (squadIDs == null || !squadIDs.Any()) {
    throw new ArgumentNullException("squadIDs");
  }
  var condition = PredicateBuilder.Make<Squad>(s => false);
  foreach (var squadID in squadIDs) {
    int squadIDValue = squadID;
    condition = PredicateBuilder.Or<Squad>(condition, s => s.SquadID == squadIDValue);
  }
  var db = m_DalContextProvider.GetContext();
  return db.Squads.Where(condition);
}

this solution has a fake recursion problem, described here: sharpit.apphb.com/Posts/Post/33

Collectives™ on Stack Overflow

LINQ to SQL create query from string

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related