Custom OR function in linq

Question

I have a linq to sql expression and I want to OR some expressions in it.

I have two tables called A and B, and I have a relation in a table called AB with the fields A_Id, B_Id and a number called State.

User can create a request with some pairs of Bs with specific States. And I should return all As that match these pairs.

Example:

A_Id | B_Id | State
-------------------
1    | 101  | 1001
2    | 102  | 1002
3    | 103  | 1003
3    | 101  | 1003

and the request has these pairs:

B_Id: 101 and State: 1001
OR
B_Id: 103 and State: 1003

and the number of pairs could be more.

I have this query for only one pair of B and State:

int b_Id = 101;
int state = 1001;
var query = dbContext.As.Where(a => dbContext.ABs.Any(ab => ab.A_Id == a.Id && ab.B_Id == b_Id && ab.State == state);

If I have more pairs I should OR them like this:

... (ab.B_Id == b_Id[0] && ab.State == state[0]) || (ab.B_Id == b_Id[1] && ab.State == state[1]) || ...

But I may have more pairs and I coudn't handle it like that.

How can I have something like OR that could pass more pairs to get complete result?

@Liam That's not a duplicate - OP wants an OR that is driven by a table of unbounded size, coming from request. — Sergey Kalinichenko, Jan 03 '18 at 14:23
@Amy I want to `||` some expressions inside something like a `for` — Mahdi Ataollahi, Jan 03 '18 at 14:27
You could use [`Predicate Builder`](http://www.albahari.com/nutshell/predicatebuilder.aspx) to dynamically generate your query. — Bradley Uffner, Jan 03 '18 at 14:33

Bradley Uffner · Accepted Answer · 2018-01-03T15:07:13.003

You can use the PredicateBuilder from LinqKit to dynamically generate the Where predicate for your query.

As a quick (LINQPad) demo:

void Main()
{
    var data = new List<DataRow>()
    {
        new DataRow { A = 1, B = 101, State = 1001 },
        new DataRow { A = 2, B = 102, State = 1002 },
        new DataRow { A = 3, B = 103, State = 1003 },
        new DataRow { A = 4, B = 104, State = 1004 },
    };

    var searchTerms = new List<SearchData>()
    {
        new SearchData {B=101, State=1001},
        new SearchData {B=103, State=1003},
    };

    var predicate = PredicateBuilder.New<DataRow>();
    foreach (var term in searchTerms)
    {
        predicate = predicate.Or(p=> p.B == term.B && p.State == term.State);
    }

    var query = data.Where(predicate);

    query.Dump();
}

public class DataRow
{
    public int A { get; set; }
    public int B { get; set; }
    public int State { get; set; }
}

public class SearchData
{
    public int B { get; set; }
    public int State { get; set; }
}

You will need to slightly modify this if used in Entity Framework by calling AsExpandable on the first table, like MyDbContext.MyTable.AsExpandable().Where().

Custom OR function in linq

1 Answers1