c# Linq cross join rows on same table-CodePudding

I'm trying to do a cross join of rows with different categories (CarreraId) on a single table, and calculate the sum of points for the given combination. However, with this code, if there are no rows of a given category (Let's say CarreraId=6) the query return 0 items. I'd like the query to return the 72 combinations (in this example) and 0 for the values where a category (CarreraId) is not present.

Don't know how to handle this scenario. Also, I was wondering if there is a way to use Dynamic Linq to resolve this kind of problem.

Any help is appreciated.

var marcas = new List<Marca>
{
    new Marca(1, 1, 0),
    new Marca(1, 5, 1), 
    
    new Marca(2, 3, 0), 
    new Marca(2, 8, 5), 
    new Marca(2, 10, 0),    
    
    new Marca(3, 1, 0), 
    
    new Marca(4, 6, 5), 
    new Marca(4, 9, 2), 
    new Marca(4, 12, 1),
    
    new Marca(5, 7, 1), 
    new Marca(5, 11, 1),    
    
     new Marca(6, 1, 0),    // <-- Comment this line to reproduce the problem
     new Marca(6, 2, 0)     // <-- Comment this line to reproduce the problem

};


var resultado = from marca1 in marcas where marca1.CarreraId == 1 orderby marca1.ParticipanteId ascending 
                from marca2 in marcas where marca2.CarreraId == 2 orderby marca1.ParticipanteId ascending
                from marca3 in marcas where marca3.CarreraId == 3 orderby marca1.ParticipanteId ascending
                from marca4 in marcas where marca4.CarreraId == 4 orderby marca1.ParticipanteId ascending
                from marca5 in marcas where marca5.CarreraId == 5 orderby marca1.ParticipanteId ascending
                from marca6 in marcas where marca6.CarreraId == 6 orderby marca6.ParticipanteId ascending 
                select new {
                            Carrera1 = marca1.ParticipanteId,
                            Carrera2 = marca2.ParticipanteId,
                            Carrera3 = marca3.ParticipanteId,
                            Carrera4 = marca4.ParticipanteId,
                            Carrera5 = marca5.ParticipanteId,
                            Carrera6 = marca6.ParticipanteId,  // Tried to use this to no avail --> Carrera6 = (marca6?.Participante == null ? 0 : marca6.Participante),
                            Puntos = marca1.Puntos   marca2.Puntos   marca3.Puntos   marca4.Puntos   marca5.Puntos   marca6.Puntos // Tried this as well -->   marca6?.Puntos
                            };
resultado.ToList().ForEach(r => Console.WriteLine(r));


record Marca(int? CarreraId, int? ParticipanteId, int? Puntos);

CodePudding user response：

something like the below - you probably only need .defaultifempty on CarreraId == 6

  var resultado2 = from marca1 in marcas.Where(x => x.CarreraId == 1).DefaultIfEmpty()
                   orderby marca1.ParticipanteId ascending
                   from marca2 in marcas.Where(x => x.CarreraId == 2).DefaultIfEmpty()
                   orderby marca1.ParticipanteId ascending
                   from marca3 in marcas.Where(x => x.CarreraId == 3).DefaultIfEmpty()

                   orderby marca1.ParticipanteId ascending
                   from marca4 in marcas.Where(x => x.CarreraId == 4).DefaultIfEmpty()

                   orderby marca1.ParticipanteId ascending
                   from marca5 in marcas.Where(x => x.CarreraId == 5).DefaultIfEmpty()

                   orderby marca1.ParticipanteId ascending
                   from marca6 in marcas.Where(x => x.CarreraId == 6).DefaultIfEmpty()

                   orderby marca6 == null ? 0 : marca6.ParticipanteId ascending
                   select new
                   {
                     Carrera1 = marca1.ParticipanteId,
                     Carrera2 = marca2.ParticipanteId,
                     Carrera3 = marca3.ParticipanteId,
                     Carrera4 = marca4.ParticipanteId,
                     Carrera5 = marca5.ParticipanteId,
                     Carrera6 = (marca6 == null ? 0 : marca6.ParticipanteId),
                     Puntos = marca1.Puntos   marca2.Puntos   marca3.Puntos   marca4.Puntos   marca5.Puntos   (marca6 == null ? 0 : marca6.Puntos) // 
                   };
  resultado2.ToList().ForEach(r => Console.WriteLine(r));

CodePudding user response：

Solved using Aggregate function in Lambda style

var marcas = new List<Marca>
{
    new Marca(1, 1, 0),
    new Marca(1, 5, 1),

    new Marca(2, 3, 0),
    new Marca(2, 8, 5),
    new Marca(2, 10, 0),

    new Marca(3, 1, 0),

    new Marca(4, 6, 5),
    new Marca(4, 9, 2),
    new Marca(4, 12, 1),

    new Marca(5, 7, 1),
    new Marca(5, 11, 1),

    new Marca(6, 1, 0),
    new Marca(6, 2, 0)

};


var resultado = marcas.GroupBy(m => m.CarreraId);

var resultado1 = resultado.Skip(1)
                           .Aggregate(resultado.First()
                                        .Select(i => new List<Marca>() { i }),
                                        (previous, next) => previous
                                                            .SelectMany(m => next.Select(e => new List<Marca>(m) { e })));


record Marca(int? CarreraId, int? ParticipanteId, int? Puntos);