Get table values in a collection for the same value in any one row using LINQ

Question

I have a table with column having multiple identical values which associates with other row values.

• ID HValue Limit
• 0005 0 350.00
• 0005 1 0.00
• 0005 2 350.00
• 0005 3 350.00
• 0025 0 20.00
• 0025 1 0.00

I executed the stored proc and stored these values in a DataTable object. Now I would like to write a Linq query which will get the list Collection of the values with same ID.

Like: for 0005 - 1st list [0,1,2,3] 2nd list [350,0,350,0]

I have 2 questions, 1. Is it possible to get this type of result with linq query? 2. If possible, what is the best way to write it?

Any suggestions will greatly be appreciated

Answer 1

You can do this with a grouping. An IGrouping represents a key and an IEnumerable collection of items that are associated with that key. So an IGrouping could ideally represent the data you're looking for:

Group1: Key = 0005 Collection = [0, 1, 2, 3]

Query this in linq using a group into clause (assuming your dataset is myDataSet):

var groups = from row in myDataSet
group tempid by row.id into g
select new {id=tempid, HValue = g.HValue, Limit=g.Limit, IsEligible=g.IsEligible};

Answer 2

var groups = table
    .AsEnumerable()
    .GroupBy(row => row.Field<int>("ID"))
    .Select(grp => new { Id = grp.Key, Values = grp.Select(r => r.Field<int>("HValue")) });

foreach(var group in groups)
{
    int id = group.Id;
    IEnumerable<int> values = group.Values;
}

Answer 3

You can group by ID filtering by count > 1 and select the ID and each property projection list you want as a anonymous type.

    [TestMethod]
    public void TestMethod()
    {

        var list = 
            new[]
                {
                    new {Id = 0005, HValue = 0, Limit = 350}, 
                    new {Id = 0005, HValue = 1, Limit = 0}, 
                    new {Id = 0005, HValue = 2, Limit = 350}, 
                    new {Id = 0005, HValue = 3, Limit = 350}, 
                    new {Id = 0025, HValue = 0, Limit = 20}, 
                    new {Id = 0025, HValue = 1, Limit = 0}
                };

        var query =
            from row in list
            group row by row.Id
            into groupedById
            where groupedById.Count() > 1
            select
                new
                    {
                        Id = groupedById.Key,
                        HValueCollection = groupedById.Select(x => x.HValue),
                        LimitCollection = groupedById.Select(x => x.Limit)
                    };

        Assert.AreEqual(2, query.Count());

        var firstResult = query.First();
        Assert.AreEqual(0005, firstResult.Id);
        Assert.AreEqual(4, firstResult.HValueCollection.Distinct().Count());
        Assert.AreEqual(2, firstResult.LimitCollection.Distinct().Count());

        var lastResult = query.Last();
        Assert.AreEqual(0025, lastResult.Id);
        Assert.AreEqual(2, lastResult.HValueCollection.Distinct().Count());
        Assert.AreEqual(2, lastResult.LimitCollection.Distinct().Count());
    }

Cheers!