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);
}

Klinges

you can use DynamicLinq or use Expression class and build your custom predicate – Lurcher

Can the user have a CounselorID == UserID without having the role Counselor? – Dennet

possibly duplicate LINQ - dynamic WHERE clause – Lurcher

Answer 1

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);

Marvelous

Thanks @dcastro, this helped lead me to albahari.com/nutshell/predicatebuilder.aspx – Klinges

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. – Doble

@TheMuffinMan oops, x was supposed to be p. Fixed now – Marvelous

Answer 2

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)
    );

Demitasse

Answer 3

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)

Tripitaka