Dynamic Or Clause Linq

Question

Today we currently have a statement like this:

var Query = (from dp in db.Patients
            select dp);

var UserID = User.Identity.GetUserId();

if (User.IsInRole("Administrator"))
{
    Query = Query.Where(x => x.AdministratorID == UserID);
}
if (User.IsInRole("Counselor"))
{
    Query = Query.Where(x => x.CounselorID == UserID);
}
if (User.IsInRole("Physician"))
{
    Query = Query.Where(x => x.PhysicianID == UserID);
}

The problem is we have Users that can have multiple roles. If a User is both an Counselor and Physician we want the system to pull back all patients where CounselorID == UserID or PhysicianID == UserID.

How can this be accomplished dynamically if we don't know what role a user will have when the page is loaded?

The current .Where clause just uses an AND statement we need an OR statment.

Ideally there would be a solution like this:

if (User.IsInRole("Administrator"))
{
     Query = Query.Where(x => x.AdministratorID == UserID);
}
if (User.IsInRole("Counselor"))
{
     Query = Query.WhereOr(x => x.CounselorID == UserID);
}
if (User.IsInRole("Physician"))
{
     Query = Query.WhereOr(x => x.PhysicianID == UserID);
}

you can use [DynamicLinq](http://dynamiclinq.codeplex.com/) or use [Expression class](http://msdn.microsoft.com/ru-ru/library/system.linq.expressions.expression(v=vs.110).aspx) and build your custom predicate — Grundy, Jun 10 '14 at 12:34
Can the user have a `CounselorID == UserID` without having the role Counselor? — JensB, Jun 10 '14 at 12:36
possibly duplicate [LINQ - dynamic WHERE clause](https://stackoverflow.com/questions/848415/linq-dynamic-where-clause?rq=1) — Grundy, Jun 10 '14 at 12:38

dcastro · Accepted Answer · 2017-03-17T10:37:00.853

20

You can build a predicate incrementally.

Func<Pantient, bool> predicate = p => false;

if (User.IsInRole("Administrator"))
{
    var oldPredicate = predicate;
    predicate = p => oldPredicate(p) || p.AdministratorID == UserID;
}

if (User.IsInRole("Counselor"))
{
    var oldPredicate = predicate;
    predicate = p => oldPredicate(p) || p.CounselorID == UserID;
}


var query = db.Patients.Where(predicate);

edited Mar 17 '17 at 10:37

answered Jun 10 '14 at 12:35

dcastro

66,540
21
145
155

2

Thanks @dcastro, this helped lead me to http://www.albahari.com/nutshell/predicatebuilder.aspx – Rob Carroll Jun 10 '14 at 12:57
I'm confused on where the variable `x` is coming from? It seems like the predicate building would go in a separate method, but I'm having a hard time figuring out the appropriate method signature. – The Muffin Man Mar 16 '17 at 22:06
@TheMuffinMan oops, x was supposed to be p. Fixed now – dcastro Mar 17 '17 at 10:37
@dcastro that makes a lot more sense. Thanks! – The Muffin Man Mar 17 '17 at 15:10

score 7 · Answer 2 · answered Jun 10 '14 at 12:35

would this work?

var query = Patients.Where(
    x => (User.IsInRole("Administrator") && x.AdministratorID == UserID)
      || (User.IsInRole("Counselor") && x.CounselorID == UserID)
      || (User.IsInRole("Physician") && x.PhysicianID == UserID)
    );

score 1 · Answer 3 · answered Dec 19 '19 at 16:42

Just in case anyone else is looking a different solution i found linqkit very usefull and easy to use. So in your example the solution would be something like this:

var predicate = PredicateBuilder.New<Patients>(true);

if (User.IsInRole("Administrator"))
{
     predicate = predicate.Or(x => x.AdministratorID == UserID);
}

if (User.IsInRole("Counselor"))
{
     predicate  = predicate.Or(x => x.CounselorID == UserID);
}

if (User.IsInRole("Physician"))
{
     predicate  = predicate.Or(x => x.PhysicianID == UserID);
}

query = query.Where(predicate)

Dynamic Or Clause Linq

3 Answers3

Linked