Transform Sql command into LAMBDA

Question

I’m learning a little about LAMBDA and tried to convert this query into LAMBDA, but I stopped.

I have 2 classes:

public class Voo {
    public string idVoo {get;set;}
    public DateTime dtVoo {get;set;}
    public string dsHoraInicial {get;set}
    public DateTime dtVooFim {get;set;}
    public string dsHoraFinal {get;set}
    public int idAeronave {get;set;}
    public virtual Aeronave Aeronave {get;set;}
}

public class Aeronave {
   public int idAeronave {get;set;}
   public string dsAeronave {get;set;}
   public string cdAeronave {get;set;} 
   public virtual ICollection<Voo> Voos {get;set;}
}

The user must inform a certain period with 2 dates as input and the query would make a grouping of cdAeronaves (aircraft code) and total flight hours performed by aircraft with that code.

Aeronave       Horas de Voo
----------------------
PR-QQ              0,7

PR-UU              2,9

Below follows the process I have to use to adapt in LAMBDA.

ALTER PROCEDURE 
    @dtInicial DATETIME,
    @dtFinal DATETIME
AS
BEGIN
    SELECT a.cdAeronave 'dsAeronave', a.idAeronave
        ,SUM(CONVERT(numeric(10,1),DATEDIFF(minute,Convert(DATETIME, v.dtVoo+v.dsHoraInicial), Convert(DATETIME, v.dtVooFim+v.dsHoraFinal))/60.2)) AS 'HDV'
    FROM Voo v
    JOIN Aeronave a ON a.idAeronave = v.idAeronave
    WHERE v.dtVoo BETWEEN @dtInicial AND @dtFinal
    GROUP BY a.cdAeronave, a.idAeronave
END

And below the attempt of the LAMBDA.

IQueryable<Voo> selecionar = hdva.Include(a => a.Aeronave)
                     .Where(v => v.dtVoo <= Convert.ToDateTime(v.dtVoo + v.dsHoraInicial))
                     .Where(v => v.dtVoo >= Convert.ToDateTime(v.dtVooFim + v.dsHoraFinal))                         
                     .Select(s => new
                            {
                                idAeronave = s.Aeronave.idAeronave,
                                dsAeronave = s.Aeronave.cdAeronave,

                                hdv = Sum(Convert.ToDecimal
                                                            (
                                                                 DbFunctions.DiffMinutes
                                                                                (
                                                                                Convert.ToDateTime(q.dtVoo + q.dsHoraInicial),
                                                                                Convert.ToDateTime(q.dtVooFim + q.dsHoraFinal)
                                                                                ) / 60
                                                            )
                                            )
                            })
                    .GroupBy(a => a.Aeronave.cdAeronave, a => a.Aeronave.idAeronave.ToString());

I know the SUM is wrong and that the 'q'(The name q does not exist in the current context) inside the SUM is not being called, but I’m not sure how to make it all work.

Answer 1

Assuming we have this sampling information:

List<Aeronave> aeronaves = new List<Aeronave>()
{
    new Aeronave()
    {
        cdAeronave = "001",
        dsAeronave = "001",
        idAeronave = 1
    },
    new Aeronave()
    {
        cdAeronave = "002",
        dsAeronave = "002",
        idAeronave = 2
    }
};

List<Voo> voos = new List<Voo>()
{
    new Voo()
    {
        Aeronave = aeronaves.First(r => r.idAeronave == 1),
        dsHoraInicial = "10:00",
        dsHoraFinal = "20:00",
        dtVoo = new DateTime(2019, 1, 10),
        dtVooFim = new DateTime(2019, 1, 11),
        idAeronave = 1,
        idVoo = "001"
    },
    new Voo()
    {
        Aeronave = aeronaves.First(r => r.idAeronave == 1),
        dsHoraInicial = "09:00",
        dsHoraFinal = "20:00",
        dtVoo = new DateTime(2019, 1, 9),
        dtVooFim = new DateTime(2019, 1, 9),
        idAeronave = 1,
        idVoo = "002"
    },
    new Voo()
    {
        Aeronave = aeronaves.First(r => r.idAeronave == 2),
        dsHoraInicial = "10:00",
        dsHoraFinal = "09:00",
        dtVoo = new DateTime(2019, 1, 8),
        dtVooFim = new DateTime(2019, 1, 9),
        idAeronave = 2,
        idVoo = "003"
    }
};

var aeronave = aeronaves.First(r => r.idAeronave == 1);
aeronave.Voos = voos.Where(r => r.idAeronave == 1).ToList();

aeronave = aeronaves.First(r => r.idAeronave == 2);
aeronave.Voos = voos.Where(r => r.idAeronave == 2).ToList();

We have then:

• idAeronave = 1 => 34:00 (idVoo 001) + 11am (idVoo 002) * 60 = 2,700m / 60,2 = 44,9 HDV
• idAeronave = 2 => 23h (idVoo 003) * 60 = 1,380m / 60,2 = 22,9 HDV

With the code below, in Query Expression, we have exactly this result (accounting for all dates, since 01/01/1900 at the present date, 27/02/2019):

DateTime dataInicial = DateTime.MinValue;
DateTime dataFinal = DateTime.Now;

var lista = from voo in voos
            join anave in aeronaves on voo.idAeronave equals anave.idAeronave into voonaves
            from anave in voonaves.DefaultIfEmpty()
            where voo.dtVoo >= dataInicial && voo.dtVoo <= dataFinal
            group anave by new { anave.cdAeronave, anave.idAeronave } into gnaves
            select new
            {
                dsAeronave = gnaves.Key.cdAeronave,
                idAeronave = gnaves.Key.idAeronave,
                HDV = gnaves.Max(r => r.Voos.Sum(x => Math.Round(DateTime.Parse(string.Format(@"{0} {1}", x.dtVooFim.ToShortDateString(), x.dsHoraFinal))
                    .Subtract(DateTime.Parse(string.Format(@"{0} {1}", x.dtVoo.ToShortDateString(), x.dsHoraInicial))).TotalMinutes / 60.2, 1)))
            };

Results:

[0] = { dsAeronave = "001", idAeronave = 1, HDV = 44.9 }
[1] = { dsAeronave = "002", idAeronave = 2, HDV = 22.9 }

Another perspective, Lambda Expression, with the same results:

DateTime dataInicial = DateTime.MinValue;
DateTime dataFinal = DateTime.Now;

var lista2 = aeronaves.GroupBy(r => new { r.cdAeronave, r.idAeronave })
            .Select(r => new
            {
                dsAeronave = r.Key.cdAeronave,
                idAeronave = r.Key.idAeronave,
                HDV = r.Max(x => x.Voos.Sum(y => Math.Round(DateTime.Parse(string.Format(@"{0} {1}", y.dtVooFim.ToShortDateString(), y.dsHoraFinal))
                    .Subtract(DateTime.Parse(string.Format(@"{0} {1}", y.dtVoo.ToShortDateString(), y.dsHoraInicial))).TotalMinutes / 60.2, 1)))
            });