CODEWITHSUNDEEP
c#linq-to-sql
musefan
musefan

Reputation: 48435

Creating a common predicate function

Firstly, I am not sure what terms to use to ask this question, which is probably why I have not found an answer from searching myself.

So I am working with Linq to SQL (C#, .Net 4) and I want to get a list of all users that match a criteria, the basics of which I would do something like this:

var users = DataContext.Users.Where(x => x.Criteria1 == "something");

but in this case there are a few fields I want to match, the thing is these particular fields are a common check and I would like to be able to create a dedicating function that I can use within any of my user queries to check for this match.

To try and explain that a bit better lets give an example: Lets say a user has 5 flags, and I want a common check to see if any of those flags are set. So I could write my query like so:

var users = DataContext.Users.Where(x => x.Flag1 || x.Flag2 || x.Flag3 || x.Flag4 || x.Flag5);

But what I would like to do is seperate out that "5 flag check" so I can use it in other queries too, ultimately I would like to use something like:

var users = DataContext.Users.Where(x => x.Criteria1 == "something" && CheckForFlags(x));

I have tried this by having a function like this:

static bool CheckForFlags(User user)
{
   return user.Flag1 || user.Flag2 || user.Flag3 || user.Flag4 || user.Flag5;
}

but I get an error:

"Method 'Boolean CheckForFlags(User)' has no supported translation to SQL."

...which makes sense, but it there something I can do to make this work the way I want it to? Or is this a restriction because I am using Linq to SQL and is in fact something that would work with Linq to Objects?

Upvotes: 31

Views: 31300

Answers (4)

Adam Maras
Adam Maras

Reputation: 26883

The neat thing about how LINQ to SQL handles expressions is that you can actually build out expressions elsewhere in your code and reference them in your queries. Why don't you try something like this:

public static class Predicates
{
    public static Expression<Func<User, bool>> CheckForFlags()
    {
        return (user => user.Flag1 || user.Flag2 || user.Flag3 ||
                        user.Flag4 || user.Flag5);
    }

    public static Expression<Func<User, bool>> CheckForCriteria(string value)
    {
        return (user => user.Criteria1 == value);
    }
}

Once you have your predicates defined, it's very easy to use them in a query.

var users = DataContext.Users
    .Where(Predicates.CheckForFlags())
    .Where(Predicates.CheckForCriteria("something"));

Upvotes: 58

Bobson
Bobson

Reputation: 13716

I think this will work, and I think I've used it in my Linq-to-SQL project, but I can't find an example offhand. If it doesn't, let me know.

Rather than creating a function, create a new property on the Users object:

partial class Users {
   bool CheckForFlags
    {
       get { 
          return Flag1 || Flag2 || Flag3 || Flag4 || Flag5;
       }
    }
}

Then you should be able to do

var users = DataContext.Users.Where(x => x.CheckForFlags);

Upvotes: 0

ean5533
ean5533

Reputation: 8994

To the best of my knowledge there are two possible ways to do this.

The quick-n-easy way to is filter your results after the SQL executes, with something like this:

var users = DataContext.Users.Where(x => x.Criteria1 == "something");
    .ToEnumerable()
    .Where(x => CheckForFlags(x));

However this is very poor in terms of performance. It will return ALL rows from the database matching only the first criteria, and then filter the results in memory on the client. Functional, but far from optimal.

The second, much more performant option is to create a UDF on the database itself and call it from LINQ. See for example this question. The obvious downside is that it moves code into the database, which no one likes to do (for lots of valid reasons).

There may very well be other viable solutions, but those are the only two I know of.

Upvotes: 0

JoshVarty
JoshVarty

Reputation: 9426

Have you tried PredicateBuilder? I haven't used it in over a year, but I found it effective when writing "Or Where" Queries.

http://www.albahari.com/nutshell/predicatebuilder.aspx

An example from their page:

IQueryable<Product> SearchProducts (params string[] keywords)
{
  var predicate = PredicateBuilder.False<Product>();

  foreach (string keyword in keywords)
  {
    string temp = keyword;
    predicate = predicate.Or (p => p.Description.Contains (temp));
  }
  return dataContext.Products.Where (predicate);
}

Upvotes: 4

Related Questions