Linq - Filter rows based on prior data

Question

Have a scenario where I'm to get data from an oracle database. Row is of format - Id, Name, Amount, Data-1, Data-2, Date amongst other columns.

Table is populated with external data and Rows are created for each entry so same Name could have multiple entries

For a given Name, I need to select rows only when Amount changes.

So data could be:

var data = new []
{
    new { Id = 1, Name = "John", Amount = 1000, Data_1 = 123, Data_2 = 234, Date = new DateTime(2018, 1, 1) },
    new { Id = 2, Name = "John", Amount = 1000, Data_1 = 123, Data_2 = 234, Date = new DateTime(2018, 1, 2) },
    new { Id = 3, Name = "John", Amount = 2000, Data_1 = 123, Data_2 = 234, Date = new DateTime(2018, 1, 3) },
    new { Id = 4, Name = "John", Amount = 2000, Data_1 = 112, Data_2 = 113, Date = new DateTime(2018, 1, 4) },
};

I need to select ids - 1 and 3. Id-1 being the first row and Id-3 since the amount column value changed from te previous row id-2.

Please let me know if I've not explained the issue properly.

Thanks for your help.

score 2 · Answer 1 · answered Aug 17 '18 at 07:33

You could do it this way:

var data = new []
{
    new { Id = 1, Name = "John", Amount = 1000, Data_1 = 123, Data_2 = 234, Date = new DateTime(2018, 1, 1) },
    new { Id = 2, Name = "John", Amount = 1000, Data_1 = 123, Data_2 = 234, Date = new DateTime(2018, 1, 2) },
    new { Id = 3, Name = "John", Amount = 2000, Data_1 = 123, Data_2 = 234, Date = new DateTime(2018, 1, 3) },
    new { Id = 4, Name = "John", Amount = 2000, Data_1 = 112, Data_2 = 113, Date = new DateTime(2018, 1, 4) },
};

var result = data.Aggregate(data.Take(0).ToList(), (a, x) =>
{
    if (!a.Any() || a.Last().Amount != x.Amount)
    {
        a.Add(x);
    }
    return a;
});

That produces:

`data.Take(0).ToList()` - good approach to get intellisense ;) — Fabio, Aug 17 '18 at 07:39
@Fabio - It's also a fabulous technique to create an empty list of an anonymous type. — Enigmativity, Aug 17 '18 at 07:51

score -1 · Answer 2 · answered Aug 17 '18 at 07:11

-1

Group by the column that will you want to avoid selecting as duplicate and then select the first item in the groups ordering by Id . In you case , i think it is Name , Amount

Ref : How to get first record in each group using Linq

answered Aug 17 '18 at 07:11

Sarav

139
1
13

The requirement is "I need to select rows only when Amount changes" - getting the first item on each group won't do that. – Enigmativity Aug 17 '18 at 07:16
Won’t grouping by amount and taking first item in each group work out then ? If I undersatand your question correctly . If you want to be like name can be repeated with same amount , but you need to select only when amount changes among the same name rows . This will work out – Sarav Aug 17 '18 at 07:20
But if the amount reverts back to 1000 I feel that the OP would want that record to be returned. – Enigmativity Aug 17 '18 at 07:25
@Enigmativity oh then yes it won’t work in that case – Sarav Aug 17 '18 at 07:26
Then a recursive function skip until when first amount is equal and then partition the list from there, take first of the list as new amount and call the same function again ( use iterator) . Just an idea again – Sarav Aug 17 '18 at 07:29

Linq - Filter rows based on prior data

2 Answers2