CODEWITHSUNDEEP
c#sqlentity-frameworklinq
gina
gina

Reputation: 179

LINQ /Entity Framework complex query: take all rows

First, excuse me for the title as I couldnt find a better meaningful title for the problem that I am trying to solve.

I have 3 tables, Companies, Devices, and MEasures, A company can have many devices, and the measures table has the temperature data of each device every 5 minutes.

I drawed a table with this: enter image description here

The problem is:

I need to make a LINQ/EF query that returns me: 1. The last temperature of every single device, per company.

So if you see the tablees in the picture, it should return this:

enter image description here

My model in C# is like this:

public class Medida
    {
        [Key]
        [DisplayName("Medida")]
        public int MedidaID { get; set; }

        public decimal Temperatura { get; set; }
        public int Humedad { get; set; }

        public DateTime FechaHora { get; set; }

        [DisplayName("Dispositivo")]        
        public int DispositivoID { get; set; }
        public virtual Dispositivo Dispositivo { get; set; }



    }

Devices:

    [Table("Dispositivo")]
    public class Dispositivo  // 
    {
        [Key]
        [Column(Order = 0)]

        public int DispositivoID { get; set; }

        [StringLength(100)]
        [Required(ErrorMessage = "El nombre  es requerido")]
        public string Nombre { get; set; }

        public string ClaveDispositivo { get; set; }

        public bool Activo { get; set; }
        [Required(ErrorMessage = "El nombre Empresa es requerido")]
        [Display(Name = "Empresa")]
        public int EmpresaID { get; set; }

        public virtual Empresa Empresa { get; set; }

        public virtual ICollection<Medida> Medidas { get; set; }
    }
}

And the company table

  public partial class Empresa
    {
        [Key] //clave primaria en la tabla
        [Display(Name = "Empresa")]
        public int EmpresaID { get; set; }//metodos de acceso de lectura y escritura a la propiedad

        [StringLength(100)] // no debe pasarse de 100 caracteres
        [Required(ErrorMessage = "El nombre empresa es requerido")]
        [DisplayName("Nombre Empresa")]
        public string Nombre { get; set; }

        [StringLength(100)]
        //[Required(ErrorMessage = "El código es requerido")]
        [DisplayName("Código")]
        public string Codigo { get; set; } 

        [Display(Name = "Correo")]
        [Required(ErrorMessage = "El correo es requerido")]
        [EmailAddress(ErrorMessage = "dirección de correo invalido")]

        public string Correo { get; set; }
        public bool Activo { get; set; }
        public bool Notificar_Correo { get; set; }
        public bool Notificar_SMS { get; set; }

        public virtual ICollection<Dispositivo> Dispositivos { get; set; }
        public virtual ICollection<Usuario> Usuarios { get; set; }
    }

And finally this is the code I am trying to implement where I need to restrict the rows returned:

 public void TakeLastTemp()
            {

                using (ZigBeeContext db = new ZigBeeContext())
                {
                    var lista = db.Medidas
                                 .Include(e => e.Dispositivo);

Upvotes: 0

Views: 746

Answers (3)

Nagashree Hs
Nagashree Hs

Reputation: 853

It is always easy if you decide the query first in sql and then think of implementing using LINQ or EF.

Results from tables in the image can be retrieved from the query below in SQL.

SELECT m.* FROM dbo.Measures m
JOIN 
(SELECT IdDevice,MAX(datetime) AS sdatetime FROM dbo.Measures
GROUP BY IdDevice) s
ON s.IdDevice = m.IdDevice AND m.[datetime] = s.sdatetime

In EF, include cannot be done this way measure.include(device) as it is device which contains many measures according to table design.

As the data needed is from measures table, LINQ query can be used as follows. I have used the table names and column names according to the image.

var subquery = db1.Measures.GroupBy(row => new { id = row.IdDevice }).Select(Finalrow => new
            {
                IdDevice = Finalrow.Key.id,
                datetime = Finalrow.Max(d => d.datetime)
            });
var mainquery = db1.Measures.Join(subquery, m => m.IdDevice, s => s.IdDevice, (m, s) => new { m, s }).Where(deviceid => deviceid.m.datetime == deviceid.s.datetime).Select(Finalrow => new
            {
                id = Finalrow.m.IdMeasure,
                temp = Finalrow.m.temp,
                deviceid = Finalrow.m.IdDevice,
                date = Finalrow.m.datetime
            });

Hope this helps. Let me know in case of issues.

Upvotes: 1

Gert Arnold
Gert Arnold

Reputation: 109119

You can order the measurements by their FechaHora datetime property in descending order and take the first of them.

from e in db.Empresas
from d in e.Dispositivos 
let lastMeasurement = d.Medidas.OrderByDescending(m => m.FechaHora)
                               .FirstOrDefault()
select new
{
    Empresa = c.Nombre,
    Dispositivo = d.Nombre,
    lastMeasurement.FechaHora,
    lastMeasurement. ... // etc.
}

Remember that the whole LINQ statement is translated into SQL, so you don't have to wory about null references in lastMeasurement.FechaHora etc. The only thing is that if there are devices without measurements, you will have to convert non-nullable type to nullable:

(DateTime?)lastMeasurement.FechaHora

Upvotes: 2

Sergey K
Sergey K

Reputation: 4114

I would recomend to change a little your database table structure add additional field LastTemperature in your Device Table, and change the code which updates temperature in table Measurements adding new row in this table and update LastTemperature filed in Device table, then you can easily query it with Linq. It will simplify your query. Have got the idea?

Upvotes: 1

Related Questions