Dynamic linq with C#

Question

Inside the new in Linq. I need to create the dynamic properties based on a data table how can I do this?

var query = (from indicador in tableIndicado.AsEnumerable()
    group indicador by indicador.Field<string>("IND_CODUSU") into g
    select new {

      #region Privot Table mensal

      // Janeiro
      Jan_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 1).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Jan_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 1).Sum(x => x.Field<decimal?>("MTA_META1")),
      Jan_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 1).Sum(x => x.Field<decimal?>("MTA_META2")),

      // Fevereiro
      Fev_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 2).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Fev_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 2).Sum(x => x.Field<decimal?>("MTA_META1")),
      Fev_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 2).Sum(x => x.Field<decimal?>("MTA_META2")),

      // Março
      Mar_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 3).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Mar_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 3).Sum(x => x.Field<decimal?>("MTA_META1")),
      Mar_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 3).Sum(x => x.Field<decimal?>("MTA_META2")),

      // Abril
      Abr_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 4).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Abr_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 4).Sum(x => x.Field<decimal?>("MTA_META1")),
      Abr_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 4).Sum(x => x.Field<decimal?>("MTA_META2")),

      // Maio
      Mai_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 5).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Mai_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 5).Sum(x => x.Field<decimal?>("MTA_META1")),
      Mai_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 5).Sum(x => x.Field<decimal?>("MTA_META2")),

      // Junho
      Jun_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 6).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Jun_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 6).Sum(x => x.Field<decimal?>("MTA_META1")),
      Jun_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 6).Sum(x => x.Field<decimal?>("MTA_META2")),

      // Julho
      Jul_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 7).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Jul_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 7).Sum(x => x.Field<decimal?>("MTA_META1")),
      Jul_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 7).Sum(x => x.Field<decimal?>("MTA_META2")),

      // Agosto
      Ago_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 8).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Ago_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 8).Sum(x => x.Field<decimal?>("MTA_META1")),
      Ago_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 8).Sum(x => x.Field<decimal?>("MTA_META2")),

      // Setembro
      Set_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 9).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Set_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 9).Sum(x => x.Field<decimal?>("MTA_META1")),
      Set_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 9).Sum(x => x.Field<decimal?>("MTA_META2")),

      // Outubro
      Out_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 10).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Out_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 10).Sum(x => x.Field<decimal?>("MTA_META1")),
      Out_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 10).Sum(x => x.Field<decimal?>("MTA_META2")),

      // Novembro
      Nov_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 11).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Nov_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 11).Sum(x => x.Field<decimal?>("MTA_META1")),
      Nov_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 11).Sum(x => x.Field<decimal?>("MTA_META2")),

      // Dezembro
      Dez_Valor = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 12).Sum(x => x.Field<decimal?>("MTA_INDVAL")),
      Dez_Meta1 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 12).Sum(x => x.Field<decimal?>("MTA_META1")),
      Dez_Meta2 = g.Where(x => x.Field<DateTime>("MTA_DTINIANO").Month == 12).Sum(x => x.Field<decimal?>("MTA_META2")),

      #endregion
  });

return dataset; 

Answer 1

First of all I started by expressing better the intention of my code, making a grouping of sums per month:

var query = g.GroupBy(x => x.Field<DateTime>("MTA_DTINIANO").Month, 
   group => group, 
   (month, group) => new {
       Valor = group.Sum(x => x.Field<decimal?>("MTA_INDVAL"),
       Meta1 = group.Sum(x => x.Field<decimal?>("MTA_META1"),
       Meta2 = group.Sum(x => x.Field<decimal?>("MTA_META2")   
   }
);

And instead of having 36 fields I put the query result in a dictionary that I could index per month

query.ToDictionary(x => x.Key);