locked
mathematical operation between different tables columns RRS feed

  • Question

  • User-1316533924 posted

    Hi,
    I am a beginner in entity framework and I'm having difficulty to execute a sql query using linq or lambda,

    I need to generate a report and for this i need to do mathematical operations between columns from different tables for example:

    I have two tables generated by code first:

        public class AlimentoRefeicao
        {
            public int Id { get; set; }
            public virtual Alimento Alimento { get; set; }
            public virtual Refeicao Refeicao { get; set; }
            public double Quantidade { get; set; }
        }

    And my other table is:

        public class Alimento {
            public int Id { get; set; }
            public string Nome { get; set; }
            public Nullable<double> Valor_calorico { get; set; }
         }

    So i need I need to do a linq query as if it was this:

    select DISTINCT al.Refeicao_Id, a.Nome, (Quantidade * valor_calorico) as Calorias_Total_Consumida
     from Alimentoes as a, AlimentoRefeicaos as al where al.Alimento_Id = a.Id 
    

    I tried to do something like this:

      public List<AlimentoRefeicao> relatorioDia()
            {
                using (NutricaoContext mde = new NutricaoContext())
                {
                 var queryAlimentoRefeicao = mde.AlimentoRefeicao.Select(column => new { Quantidade = column.Quantidade * column.Alimento.Valor_calorico}).ToList();
                 return queryAlimentoRefeicao.Select(column => new AlimentoRefeicao { Quantidade = column.Quantidade}).ToList();
                };
            }

    But i get the error message: cannot implicitly convert type 'double?' to 'double'

    Tuesday, June 16, 2015 9:35 AM

Answers

  • User1577371250 posted

    Hi,

    try this

    public List<AlimentoRefeicao> relatorioDia()
    {
                using (NutricaoContext mde = new NutricaoContext())
                {
                 var queryAlimentoRefeicao = mde.AlimentoRefeicao.Select(column => new { Quantidade = column.Quantidade * column.Alimento.Valor_calorico}).ToList();
                 return queryAlimentoRefeicao.Select(column => new AlimentoRefeicao
    { Quantidade = column.Quantidade.HasValue ? column.Quantidade.Value : 0}).ToList(); }; }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 16, 2015 9:44 AM

All replies

  • User1577371250 posted

    Hi,

    try this

    public List<AlimentoRefeicao> relatorioDia()
    {
                using (NutricaoContext mde = new NutricaoContext())
                {
                 var queryAlimentoRefeicao = mde.AlimentoRefeicao.Select(column => new { Quantidade = column.Quantidade * column.Alimento.Valor_calorico}).ToList();
                 return queryAlimentoRefeicao.Select(column => new AlimentoRefeicao
    { Quantidade = column.Quantidade.HasValue ? column.Quantidade.Value : 0}).ToList(); }; }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 16, 2015 9:44 AM
  • User-1316533924 posted

    Lokesh thank you,

    this way works for a selection of a column in AlimentoRefeicao and a column in Alimento.
    I have another question, if I need to realize the amount of multiplication with another column in food would work.
    For example if the Alimento has a nutriotinal field more:

    public class Alimento {
             public int Id {get; set; }
             public string Nome{get; set; }
             public Nullable <double> Valor_calorico {get; set; }
             public Nullable <double> Proteina{get; set; }
          }

    And now I need to know the multiplication of Quantidade with Proteina. Something like this:

    select DISTINCT al.Refeicao_Id, a.Nome, (Quantidade * valor_calorico) as Calorias_Total_Consumida, (Quantidade * proteinas) as Proteinas_Total_Consumida
     from Alimentoes as a, AlimentoRefeicaos as al where al.Alimento_Id = a.Id 
    Refeicao_id  Nome       Calorias_Total_Consumida    Proteinas_Total_Consumida
    2 Banana 22575 7500 2 Maçã 10000 10000 4 Banana 15050 5000 5 Banana 15050 5000 5 Maçã 15000 15000 10 Acerola 20000 15000

    work using linq?
    Because I can only use the field Quantidade once

    Tuesday, June 16, 2015 6:20 PM
  • User1577371250 posted

    Hi,

    You can add a new column and do the calculations in the linq query.

    Wednesday, June 17, 2015 3:06 AM