CODEWITHSUNDEEP
c#sql.netlinqlambda
Vsln
Vsln

Reputation: 73

Linq select all numbers that are in any interval from another list of intervals

I am trying write linq lambda select to get all records from database that has a number which is in any of the specific intervals from other list or database table. Lets say i have a table "SOURCE" +------+------+-------+------+------+ | Col1 | Col2 | reqNr | col5 | col6 | +------+------+-------+------+------+ | x | x | 9 | x | x | | x | x | 14 | x | x | | x | x | 19 | x | x | | x | x | 24 | x | x | +------+------+-------+------+------+ and I have already selected table "INTERVALS" +------+----+ | from | to | +------+----+ | 1 | 3 | | 5 | 10 | | 15 | 30 | +------+----+ to object list "intervalList" in c#. How can write ".Where()" part in linq sql statement to get all records from "SOURCE" that has "ReqNr" column value which falls in any of the intervals from "intervalList" list. For example: .where(w => intervalList.any(w.ReqNr > intervalList.from && w.ReqNr < intervalList.to))

Upvotes: 3

Views: 700

Answers (3)

Yacoub Massad
Yacoub Massad

Reputation: 27861

As I understand your question, you need the query to be executed on the database side, and the intervals list is a list in memory.

You need a SQL query that looks like this:

SELECT * FROM SOURCE WHERE 
    ((1 < reqNr) AND (3 > reqNr))
    OR ((5 < reqNr) AND (10 > reqNr))
    OR ((15 < reqNr) AND (30 > reqNr))

As you can see, the query itself is dynamic. The number and content of the OR conditions depend on the intervals list (which is an in memory list).

To generate such a dynamic query you need to build an Expression<Func<Item,bool>> dynamically and pass it to the Where method. (Item is the name of the entity of the Source table).

You can use LinqKit to create such dynamic expression like this:

Expression<Func<Item, bool>> condition =
    intervals
        .Select(
            interval =>
                (Expression<Func<Item, bool>>)
                    (x => interval.From < x.reqNr && interval.To > x.reqNr))
        .Aggregate(LinqKit.PredicateBuilder.Or);

var result = context.SourceTable
    .Where(condition)
    .ToList();

By the way, this solution is complex only because the intervals list is in memory. An easier solution is to query the database directly using the intervals stored in the database like this:

var result =
    context.SourceTable
        .Where(x =>
            context.Intervals.Any(interval =>
                interval.From < x.reqNr && interval.To > x.reqNr))
        .ToList();

Upvotes: 1

xGeo
xGeo

Reputation: 2139

You can do this:

var result = iRec
  .Where(r => intervalList.Any(i => i.From <= r.reqNr && i.To >= r.reqNr))
  .ToList();

You can check it here.

Upvotes: 2

Jenish Rabadiya
Jenish Rabadiya

Reputation: 6766

Apply where and Any as inner predicate to where method like below:

var result = SOURCE
  .Where(d => INTERVALS.Any(e => e.from<d.reqNr && e.to>d.reqNr))
  .ToList();

Upvotes: 1

Related Questions