Clever Geek Handbook

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

I am trying to write a linq lambda select to get all records from a database that has a number that is in any of certain ranges from another list or database table. Let's 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 the table "INTERVALS" +------+----+ | from | to | +------+----+ | 1 | 3 | | 5 | 10 | | 15 | 30 | +------+----+

in the "intervalList" objects in C #. How to write the ".Where ()" part in linq sql expression to get all records from "SOURCE" with column value "ReqNr" that falls in any of the intervals from the list of intervals. For example:.where(w => intervalList.any(w.ReqNr > intervalList.from && w.ReqNr < intervalList.to))

+3


source to share


3 answers


You can do it:

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


You can check it out here .

+1


source


Apply where and Any as an internal predicate to the method as shown below:



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


source


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

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 request itself is dynamic. The number and content of OR conditions depends on the bins list (which is an in-memory list).

To create such a dynamic query you need to dynamically build Expression<Func<Item,bool>>

and pass it to the method Where

. ( Item

is the name of the Source table object).

You can use LinqKit to create a 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 difficult only because the list of intervals is in memory. A simpler solution is to query the database directly using the intervals stored in the database, such as:

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


source






More articles:


All Articles