I need to group this data list to new collection list by Date and UserId

new collection list in C# Lambda

The first me code : var groupTranDate = db.System_Transactions.GroupBy(g => new { g.Transaction_Date,g.User_Id }).ToList();

true result, but i can't get the data to new collection Please click here to view result image for my code

CodePudding user response：

You must understand, GroupBy return IEnumerable<IGrouping<TKey, TSource>>, therefore you want cast IGrouping to IEnumerable

var groupTranDate = db.System_Transactions
                                .GroupBy(g => new MyGroupedClass 
                                {
                                    UserId = g.UserId, 
                                    Transaction_Date = g.Transaction_Date
                                )})
                                .Select(x => x.Where(x => x))
                                .Select(x => x);

How to get a new collection:

List<MyGroupedClass> ts = new List<MyGroupedClass>();

foreach(var item in groupTranDate)
{
    ts.AddRange(item);
}

CodePudding user response：

Seems reasonable, I might just go a step further and turn the resulting list of the group into a class designed for the job:

record X(
  DateTime Date, 
  int UserId, 
  int? TypeId1,
  int? TypeId2,
  int? TypeId3,
  int? TypeId4
){
    public X(IGrouping<(DateTime T,int U),System_Transaction> y):this(y.Key.T, y.Key.U, null, null, null, null){
        foreach(var x in y){
            _ = x.Type_Id switch {
                1 => TypeId1 = x.Value,
                2 => TypeId2 = x.Value,
                3 => TypeId3 = x.Value,
                4 => TypeId4 = x.Value,
                _ => throw new NotImplementedException($"Type Id {x.Type_Id} is not handled")
            };
         }
    }

Then make it in your grouping

var groupTranDate = db.System_Transactions
  .GroupBy(g => (g.Transaction_Date,g.User_Id))
  .Select(g => new X(g))
  .ToList();

The record is like a class, it's just simpler to write out; it has a constructor that can convert the grouping generated by GroupBy; basically the constructor first calls the other constructor (that you can't see because C# generates it behind the scenes) passing the transaction date and the user is- that gets those properties set. Next the constructor loops over the grouping (which behaves like a list of YourClass, whatever your class is called - please change the type name to your true class name) and if the Type_Id is 1 then property TypeId1 ends up assigned the Value etc At the end of the loop, however many items there were (between 1 and 4) you'll have the TypeIdX properties set to the values (or remain null if there was no value)

The only adjustment needed to the LINQ query is to change the grouping from an anonymous type to a tuple, because ATs are a pain to work with outside of the context they're created in, and to use a Select to convert the grouping to our record X

If you want that record in something more familiar/a class that works in an older .NET, it would look like:

class X(
  public DateTime Date {get;set;}, 
  public int UserId {get;set;},
  public int? TypeId1 {get;set;},
  public int? TypeId2 {get;set;},
  public int? TypeId3 {get;set;},
  public int? TypeId4 {get;set;}
){
    public X(IGrouping<(DateTime T,int U),System_Transaction> y){
        Date = T;
        UserId = U;
        foreach(var x in y){
            if(x.Type_Id == 1) TypeId1 = x.Value;
            else if(x.Type_Id == 2) TypeId2 = x.Value;
            else if(x.Type_Id == 3) TypeId3 = x.Value;
            else if(x.Type_Id == 4) TypeId4 = x.Value;
            else throw new NotImplementedException($"Type Id {x.Type_Id} is not handled");
         }
    }

There are ways to get cute with dictionaries etc to avoid having to write out a huge if/switch block in a loop like there is there, but you still have to write out the properties, unless you're planning on making the table a display-only thing that can be driven by a dictionary with N keys, rather than an object you refer to in code as some X with a property TypeId4..