question

GunjanArora-8600 avatar image
0 Votes"
GunjanArora-8600 asked GunjanArora-8600 commented

How can i write this in LINQ ?

Hi Team , I have the below collection in C# and I need the below expected result. Could you please what is the best way to achieve this in linq ?

EffectiveDate EmployeeID Department Addresss OfficeLocation
3-01-2021 10 0000 null Bangalore
2-01-2021 10 9121 Mumbai null
1-01-2021 10 9123 9121 Mumbai
30-12-2020 10 null null null
29-12-2020 10 null null Bangalore
28-12-2020 10 3121 Kolkatta Bangalore

Expected Result
EffectiveDate EmployeeID Department Addresss OfficeLocation
3-01-2021 10 0000 Mumbai Bangalore
2-01-2021 10 9121 Mumbai Mumbai
1-01-2021 10 9123 9121 Mumbai
30-12-2020 10 3121 Kolkatta Bangalore
29-12-2020 10 3121 Kolkatta Bangalore
28-12-2020 10 3121 Kolkatta Bangalore

dotnet-csharp
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you show us what you have tried so far?

0 Votes 0 ·

I tried something like this

var employees = from emp in EmployeeTable
where s.EmployeeID == 10
group new { emp} by new {s.EmployeeID,s.EffectiveDate} into g
select new EmployeeTable
{
EffectiveDate= g.EffectiveDate,
EmployeeID =g.key.EmployeeID,
.// so on
}
;
But this didn't work and don't know if this is the right way to do it.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Try something like this too:

 var results = from e1 in EmployeeTable
               let p = ( from e2 in EmployeeTable where e2.EmployeeID == e1.EmployeeID && e2.EffectiveDate <= e1.EffectiveDate select e2 )
               select new
               {
                  e1.EffectiveDate,
                  e1.EmployeeID,
                  e1.Department,
                  Address = ( from e2 in p
                              where e2.Address != null
                              orderby e2.EffectiveDate descending
                              select e2.Address ).FirstOrDefault( ),
                  OfficeLocation = ( from e2 in p
                                     where e2.OfficeLocation != null
                                     orderby e2.EffectiveDate descending
                                     select e2.OfficeLocation ).FirstOrDefault( ),
  };



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered GunjanArora-8600 commented

The following may assist but is not your data or class. The code is to give you ideas as the code below is pre-done hence why it does not match your's.

 public class Person
 {
     public int Id { get; set; }
     public string FirstName { get; set; }
     public int Total { get; set; }
     public override string ToString() => $"{FirstName},{Total}";
 }

Mocked

 public class Data
 {
     public static List<Person> Mocked()
     {
         var list = new List<Person>
         {
             new() {Id = 1, FirstName = "Sally", Total = 1},
             new() {Id = 2, FirstName = "Joe", Total = 2},
             new() {Id = 3, FirstName = "Bill", Total = 5},
             new() {Id = 4, FirstName = "Sally", Total = 3},
             new() {Id = 5, FirstName = "Joe", Total = 6}
         };
    
         return list;
     }
 }

Result container

 public class GroupItem
 {
     public int RowIndex { get; set; }
     public IGrouping<string, Person> Grouping { get; set; }
 }


GroupBy with where

 int personIdentifier = 2;
 List<GroupItem> results = Data.Mocked()
     .Where(person => person.Id == personIdentifier)
     .GroupBy(person => person.FirstName)
     .Select((people, index) => new GroupItem
     {
         RowIndex = index + 1, 
         Grouping = people
     })
     .ToList();
    
 foreach (var result in results)
 {
     Debug.WriteLine($"{result.Grouping.Key}");
     foreach (var person in result.Grouping)
     {
         Debug.WriteLine(person.Id);
     }
        
 }

Or without Where which you can do against the list of GroupItem in this case

 int personIdentifier = 2;
 List<GroupItem> results = Data.Mocked()
     .GroupBy(person => person.FirstName)
     .Select((people, index) => new GroupItem
     {
         RowIndex = index + 1, 
         Grouping = people
     })
     .ToList();
    
 foreach (var result in results)
 {
     Debug.WriteLine($"{result.Grouping.Key}");
     foreach (var person in result.Grouping)
     {
         Debug.WriteLine(person.Id);
     }
        
 }


· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried this but no luck ...my issue is if my latest column value is null then I have to search the older rows based on the effective date and get that value as the latest because the value have not changed.

The grouping is done on Effective date

0 Votes 0 ·

Assuming EffectiveDate is nulllable...

 public DateTime? EffectiveDate { get; set; }

Then

 List<GroupItem> results = Data.Mocked()
     .Where(person => person.EffectiveDate.HasValue)
     .GroupBy(person => person.FirstName)
     .Select((people, index) => new GroupItem
     {
         RowIndex = index + 1,
         Grouping = people
     })
     .ToList();

So then in this case we have all data except those with a null effective date

0 Votes 0 ·

Or this

 List<GroupItem> results = Data.Mocked()
     .GroupBy(person => person.FirstName)
     .Select((people, index) => new GroupItem
     {
         RowIndex = index + 1,
         Grouping = people
     })
     .ToList();
    
 foreach (var result in results)
 {
     Debug.WriteLine($"{result.Grouping.Key}");
     foreach (Person person in result.Grouping)
     {
         Debug.WriteLine(person.EffectiveDate.HasValue ? $"{person.Id} no date" : $"{person.Id} has date");
     }
 }
0 Votes 0 ·

The Effective Date can is not nullable .I have made some edit to the mockdata you shared with the expected output ...Hopefully that will give an idea about my requirement
///Input Collection
new() {Id = 1, FirstName = "Sally", Total = null,EffectiveDate=new DateTime(2021, 05, 28)},
new() {Id = 2, FirstName = "Joe", Total = null,EffectiveDate=new DateTime(2021, 05, 28) },
new() {Id = 4, FirstName = "Sally", Total = null, EffectiveDate=new DateTime(2021, 05, 27)},
new(){Id = 6, FirstName = "Sally", Total = 1, EffectiveDate=new DateTime(2021, 05, 26)},
new() {Id = 5, FirstName = "Joe", Total = 6,EffectiveDate=new DateTime(2021, 05, 27)},

0 Votes 0 ·
Show more comments