CODEWITHSUNDEEP
c#linq
jaykzoo
jaykzoo

Reputation: 77

Getting current and previous value

In the LINQ query below, I would like to add a property that gets the previous Department as well as the current Department.

The LINQ query below returns:

EffectiveDate EmployeeID  Department
11/4/2012     10          0000 
1/14/2013     10          9121 
2/2/2016      10          9123

How can I show the previous Department next to current Department like this?

EffectiveDate EmployeeID  Department PreviousDepartment
11/4/2012     10          0000       null
1/14/2013     10          9121       0000
2/2/2016      10          9123       9121

Here's the current query

var users = from s in userTable
            where s.EmployeeID == "10"
            group new {s} by new { s.EmployeeID, s.Department} into g
            select new 
            {
                EffectiveDate = g.Max(m => m.s.EffectiveDate), 
                EmployeeID = g.Key.EmployeeID, 
                Department = g.Key.Department
                //PreviousDepartment = ???
            };

Upvotes: 3

Views: 2411

Answers (5)

Charlieface
Charlieface

Reputation: 72050

Most of the other answers force the IEnumerable to be iterated fully, in some cases twice. It is possible to do this without any extra iteration, in other words: inline with the LINQ iteration flow.

public static IEnumerable<(T current, T previous)> WithLag<T>(this IEnumerable<T> source)
{
    var previous = default(T);
    foreach (var item in source)
    {
        yield return (item, previous);
        previous = item;
    }
}

You can then use this in normal LINQ fashion (you need method syntax):

var users = userTable
            .Where(s => s.EmployeeID == "10")
            .GroupBy(s => new { s.EmployeeID, s.Department})
            .WithLag()
            .Select(gLag => new 
            {
                EffectiveDate = gLag.current.Max(m => m.s.EffectiveDate), 
                EmployeeID = gLag.current.Key.EmployeeID, 
                Department = gLag.current.Key.Department
                PreviousDepartment = gLag.previous.Key.Department
            });

Upvotes: 1

Shashwat Gupta
Shashwat Gupta

Reputation: 657

A Method Syntax for achieving it.

var result = userTable.Select((x, i) => { Department j = null ; if (i>0) j = userTable.ElementAt(i-1).Department;  return new { x.EffectiveDate , x.EmployeeID  , x.Department , j};});

Upvotes: 0

Ian Mercer
Ian Mercer

Reputation: 39277

You can generate a current and previous pair using the Zip method in LINQ. One downside is that it does enumerate the input twice (which may be an issue if it's coming from a database), but on the other hand it does not hold the whole of the input in memory and it's lazy (i.e you don't have to consume it all).

For example, using int values as a demo: 

[TestMethod]
public void CurrentAndPrevious()
{
    var input = new int[] { 1, 2, 3, 4, 5 };

    var output = Enumerable.Repeat(0,1)   // an initial zero value
         .Concat(input)                   // followed by the list
                                          // zipped with the list
         .Zip(input, (x, y) => new {current = y, previous = x});

    // a test that passes (using FluentAssertions syntax)
    string.Join(",", output.Select(x => $"({x.current},{x.previous})"))
       .Should().Be("(1,0),(2,1),(3,2),(4,3),(5,4)");
}

Upvotes: 1

Jason Boyd
Jason Boyd

Reputation: 7029

If you use Interactive Extensions (NuGet package Ix-Main) you could do the following:

var users = 
    userTable
    .Where(x => x.EmployeeID == "100")
    .GroupBy(x => new { x.EmployeeID, x.Department })
    .Select(x => new
        {
            EffectiveDate = x.Max(m => m.EffectiveDate),
            EmployeeID = x.Key.EmployeeID,
            Department = x.Key.Department
        })
    .OrderBy(x => x.EffectiveDate)
    .AsEnumerable()
    .Scan(
        new
        {
            EffectiveDate = default(DateTime),
            EmployeeID = default(string),
            Department = default(string),
            PreviousDepartment = default(string)
        },
        (a, x) => new
        {
            EffectiveDate = x.EffectiveDate,
            EmployeeID = x.EmployeeID,
            Department = x.Department,
            PreviousDepartment = a.Department
        });

The benefit is that you stick with the declarative programming style. The downside is that it is more verbose and possibly harder to read than operating on the data in memory. Oh, and you may have to install a NuGet package. For me it is less of an issue because I don't think I start any project without adding Ix-Main.

Ix-Main, never leave home without it.

Upvotes: 0

Yacoub Massad
Yacoub Massad

Reputation: 27871

One way to do it is to store the data in memory, and then modify it like this:

var users = from s in userTable
            where s.EmployeeID == "10"
            group new {s} by new { s.EmployeeID, s.Department} into g
            select new MyClass
            {
                EffectiveDate = g.Max(m => m.s.EffectiveDate), 
                EmployeeID = g.Key.EmployeeID, 
                Department = g.Key.Department
                PreviousDepartment = null
            };

var result = users.ToList();

for(int i = 1; i < result.Count; i++)
{
    result[i].PreviousDepartment = result[i-1].Department;
}

Please note that the code is generating new instances of MyClass instead of an anonymous type since anonymous type properties are read-only. Make sure that you create such class with the correct properties.

Upvotes: 3

Related Questions