20

In the post here I learned how to build a dynamic query using the deferred execution of Linq. But the query is actually using an AND concatenation of the WHERE condition.

How can I achieve the same query but with an OR logic?

Due to the Flags enum, the query should search for Username, WindowsUsername or both:

public User GetUser(IdentifierType type, string identifier)
{
    using (var context = contextFactory.Invoke())
    {
        var query = from u in context.Users select u;

        if (type.HasFlag(IdentifierType.Username))
            query = query.Where(u => u.Username == identifier);

        if (type.HasFlag(IdentifierType.Windows))
            query = query.Where(u => u.WindowsUsername == identifier);

        return query.FirstOrDefault();
    }
}
Community
  • 1
  • 1
Thomas Zweifel
  • 627
  • 1
  • 6
  • 19

2 Answers2

24

With LINQKit's PredicateBuilder you can build predicates dynamically.

var query = from u in context.Users select u;
var pred = Predicate.False<User>();

if (type.HasFlag(IdentifierType.Username))
    pred = pred.Or(u => u.Username == identifier);

if (type.HasFlag(IdentifierType.Windows))
    pred = pred.Or((u => u.WindowsUsername == identifier);

return query.Where(pred.Expand()).FirstOrDefault();
// or return query.AsExpandable().Where(pred).FirstOrDefault();

This is what the Expand is for:

Entity Framework's query processing pipeline cannot handle invocation expressions, which is why you need to call AsExpandable on the first object in the query. By calling AsExpandable, you activate LINQKit's expression visitor class which substitutes invocation expressions with simpler constructs that Entity Framework can understand.

Or: without it an expression is Invoked, which causes an exception in EF:

The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

Later addition:

There is an alternative predicate builder that does the same but without Expand: http://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • Thats amazing stuff! Works perfect! – Thomas Zweifel Jan 31 '13 at 10:13
  • Is there an option which does not require 3rd party libraries? (I'd prefer to avoid my company's administrative hassles for using 3rd party libraries) – Zarepheth Jun 16 '16 at 16:39
  • @Zarepheth This "universal predicate builder" is hardly a third-party library. It's part of your own code base. Just give the guy credits in a comment :) – Gert Arnold Jun 16 '16 at 18:43
  • I'd have to use the `LINQKit` library with this solution - or follow the second link to another option. However, I continued my search and installed the `Microsoft.Linq.Dynamic` NuGet package, which met my needs. (Freebies from Microsoft are generally permitted without extra red-tape). – Zarepheth Jun 16 '16 at 20:19
  • I should have followed that 2nd link; that's a relatively small class I could have copied into my code. Oh well, I've got the Microsoft package now. – Zarepheth Jun 16 '16 at 20:22
-2

This should help..

Contains Query on multiple columns

It also appears that there is a fundamental problem in the table design (Please correct me if I am wrong). What is the purpose of the IdentifierType in your database?

Community
  • 1
  • 1
activebiz
  • 6,000
  • 9
  • 41
  • 64
  • The IdentifierType is not part of the DB. It just tells the function which columns should be included in the WHERE condition. – Thomas Zweifel Jan 31 '13 at 10:14