Linq Query Help.

[Satish]

Hi everyone,
I need some help constructing a linq query on datatables.
I have 2 datatables as follows

RangeTable
MinValue MaxValue
0.00 0.050
0.051 0.10
0.101 0.15
0.151 0.20
......

ValueTable
Value
0.01
0.02
0.06
0.11
0.15
0.16
....

I want to join these 2 tables and find the count for values for each
range like this

MinValue MaxValue Count
0.00 0.050 2
0.051 0.10 1
0.101 0.15 2

Is it possible to write a query to get this result?
Thanks.

 

[Jon Skeet [C# MVP]]

Hi everyone,
I need some help constructing a linq query on datatables.
I have 2 datatables as follows

RangeTable
MinValue MaxValue
0.00 0.050
0.051 0.10
0.101 0.15
0.151 0.20
.....

ValueTable
Value
0.01
0.02
0.06
0.11
0.15
0.16
...

I want to join these 2 tables and find the count for values for each
range like this

MinValue MaxValue Count
0.00 0.050 2
0.051 0.10 1
0.101 0.15 2

Is it possible to write a query to get this result?

It's certainly reasonably simple to get an inefficient O(N*M) solution.
Short example which doesn't use datatables, but the code would be
similar:

using System;
using System.Linq;
using System.Collections.Generic;

public class Test
{
static void Main()
{
var ranges = new[]
{
new { MinValue = 0m, MaxValue = 0.05m },
new { MinValue = 0.051m, MaxValue = 0.1m },
new { MinValue = 0.101m, MaxValue = 0.15m },
new { MinValue = 0.151m, MaxValue = 0.2m }
};

decimal[] values = {0.01m, 0.02m, 0.06m, 0.11m, 0.15m, 0.16m};

var query = from range in ranges
let Count = values.Where
(value => range.MinValue <= value &&
value <= range.MaxValue).Count()
select new { range.MinValue,
range.MaxValue,
Count };

foreach (var item in query)
{
Console.WriteLine(item);
}
}
}

It's no doubt possible with just query expression syntax, and indeed it
feels like it order to be feasible with a neater query anyway, but
that's basically what you want to do.


One comment: it would be more robust to make MaxValue exclusive and set
the next MinValue equal to the previous MaxValue. As it is, you don't
have any slots for (say) 0.0505.