Subhajit Mondal Subhajit Mondal - 1 year ago 43
C# Question

Linq query for below data

id vid Amount Date
a 1 10 Today
a 1 5 Yesterday
b 2 6 Today
b 2 7 Yesterday


How to fetch records using linq where amount for today is greater than yesterday for each vid group bi Id and vid?

Answer Source

If I have understood correctly, you have a class that looks like this:

class Data
{
    public string Id;
    public int Vid;
    public int Amount;
    public DateTime Date;
}

And an enumeration of instances of this class, which contains multiple instances that share the same Vid value, but different Amount and Date values. What you want is to get all instances which simultaneously have the greatest Date and Amount values of all other instances with the same Vid. Once you have that, you want to group the results by Id and Vid

In this case, you want the following query:

var myList = new List<Data>();
var result = myList.SelectMany(d1 => myList.Where(d2 => d2.Vid == d1.Vid && d1.Amount < d2.Amount && d1.Date < d2.Date)).OrderBy(data => data.Vid).ThenBy(data => data.Id).ToList();

What we are doing here is the following:

  • Iterate through all elements in myList.
    • For each element, find all other elements with the same Vid, but a larger Date and Amount.
  • Flatten all results into a single collection using SelectMany.
  • Order the resulting enumeration by Vid, and then by Id.
  • Convert the result into a list.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download