How can I get the minimum values ​and date of each group?

Question

I have two MySQL tables that are related. My purpose is to obtain the minimum values ​of each group and the dates associated with each minimum value found.

                              TABLE 1
> ------------+--------------+--------------+-----------+-----------------+
> --  Code -- | - IdGroup1 - | - IdGroup2 - | - State - | - NameProtocol -|
> ------------+--------------+--------------+-----------+-----------------+
>    ZZ-100   |   11111111   |    1110000   |     1     |       OSM1      |
> ------------+--------------+--------------+-----------+-----------------+
>    ZZ-200   |   55555555   |    5550000   |     1     |       OSM1      |
> ------------+--------------+--------------+-----------+-----------------+
>    ZZ-300   |   99999999   |    9990000   |     1     |       OSM1      |

Table 1 and 3 are related.

                             TABLE 3
> ---------------------+-------------------+----------------+
> --  NameProtocol  -- | -- Description -- | -- Protocol -- |
> ---------------------+-------------------+----------------+
>         ATC0         |        d1         |      UDP       |
> ---------------------+-------------------+----------------+
>         OSM1         |        d2         |      TCP       |
> ---------------------+-------------------+----------------+

Table 2 is where the new values ​are recorded.

                                TABLE 2
> ---------+-----------------------+----------------+----------------+
> - Value -| ------- Date -------- | -- IdGroup1 -- | -- IdGroup2 -- |
> ---------+-----------------------+----------------+----------------+
>    10    |  2020-08-16 02:30:10  |    99999999    |     9990000    |
> ---------+-----------------------+----------------+----------------+
>    15    |  2020-08-16 02:31:10  |    99999999    |     9990000    |
> ---------+-----------------------+----------------+----------------+
>    20    |  2020-08-16 02:32:10  |    99999999    |     9990000    |
> ---------+-----------------------+----------------+----------------+
>    115   |  2020-08-16 02:31:20  |    55555555    |     5550000    |
> ---------+-----------------------+----------------+----------------+
>    120   |  2020-08-16 02:32:20  |    55555555    |     5550000    |
> ---------+-----------------------+----------------+----------------+
>    90    |  2020-08-16 02:35:20  |    11111111    |     1110000    |
> ---------+-----------------------+----------------+----------------+
>    100   |  2020-08-16 02:30:20  |    11111111    |     1110000    |

I have done many tests and I still manage to get the correct answer, the best approximation was obtained with the following query:

var query = Table2                                              //Outer Table
            .Join(Table1,                                       //Inner Table to join
                         p => new { p.IdGroup1, p.IdGroup2 },   //Condition from outer table
                         e => new { e.IdGroup1, e.IdGroup2 },   //Condition from inner table
                         (p, e) => new {                        //Result
                                          Code = e.Code,                                          
                                          Value = p.Value,
                                          Date = p.Date })         
            .GroupBy(gb => new { gb.Code })           
            .OrderBy(ob => ob.Key.Code)
            .Select(s => new {  Code = s.Key.Code, 
                                Value = (double?)s.Min(a => a.Value),
                                Date = "?" })  // TODO: The date remains to be implemented.
            .ToList();

The result of my query:

> -------------+-------------+------------+
> --  Code  -- | -- Value -- | -- Date -- | 
> -------------+-------------+------------+
>    ZZ-100    |      90     |     ?      | 
> -------------+-------------+------------+
>    ZZ-200    |     115     |     ?      |
> -------------+-------------+------------+
>    ZZ-300    |      10     |     ?      |

I just need to add the dates for each minimum value found. What should I do to be able to integrate it into my query?

Code:

var query = Table1                      
        .Join(Table2.Where(w => (w.State == 1)),            
                         h => new { h.IdGroup1, h.IdGroup2 },   
                         p => new { p.IdGroup1, p.IdGroup2 },   
                         (h, p) => new { h, p })                    
        .Join(Table3.Where(w => (w.Protocol == "TCP")),
                                     pt => pt.p.NameProtocol,
                                     p => p.NameProtocol,
                                     (pt, p) => new { pt, p })          
        .GroupBy(gb => new { gb.pt.p.Code })            
        .OrderBy(ob => ob.Key.Code)
        .Select(s => new {  Code = s.Key.Code, 
                            Value = (double?)s.Min(a => a.pt.h.Value) })
        .ToList();

Answer 1

Most effective query for such request is usage of Window Functions, which is not supported by EF and, I think, this will never happen. So just use SQL and run it via Dapper, whatever.

SELECT 
    s.Code,
    s.Value,
    s.Date
FROM
    (
        SELECT 
           t1.Code,
           t2.Value,
           t2.Date,
           ROW_NUMBER() OVER (PARTITION BY t1.Code ORDER BY t2.Value) AS RN
        FROM TABLE1 t1
        JOIN TBALE3 t3 ON t3.NameOfProtocol = t1.NameOfProtocol
        LEFT JOIN TABLE2 t2 ON t1.IdGroup1 = t2.IdGroup1 AND t1.IdGroup2 = t2.IdGroup2 AND t2.FechaCaudalHistorico <= @dateFilter
        WHERE t3.Protocol = 'TCP'
    ) s
WHERE s.RN = 1

If you are not a pure EF Core adept and still needs LINQ, you can try linq2db.EntityFrameworkCore extension which has this possibility and query can be written via LINQ:

var dateFilter = DateTime.Parse ("2020-09-16 03:00:00");
var rnQuery =
   from t1 in Table1
   join t3 in Table3 on t1.NameOfProtocol equals t3.NameOfProtocol
   from t2 in Table2.Where(t2 => t1.IdGroup1 == t2.IdGroup1 && t1.IdGroup2 == t2.IdGroup2 && t2.FechaCaudalHistorico <= dateFilter)
      .DefaultIfEmpty()
   where t3.Protocol == "TCP"
   select new 
   {
      t1.Code,
      Value = Sql.ToNullable(t2.Value),
      Date  = Sql.ToNullable(t2.Date),
      RN    = Sql.Ext.RowNumber().Over().PartitionBy(t1.Code).OrderBy(t2.Value).ToValue()
   };

var query = from s in rnQuery
   where s.RN == 1
   select new 
   {
      s.Code,
      s.Value,
      s.Date,
   };

// switch to alternative LINQ parser
query = query.ToLinqToDB();

var result = query.ToList();

As result you will have identical SQL as described above.

Answer 2

You could try the following:

.Select(s => 
{
    var values = s.OrderBy(x=>x.Value);
    var firstValue = values.First();
    return new 
    {
        Code = s.Key.Code;
        Value = (double?)firstValue.Value;
        Date = firstValue.Date;
    }
})

Essentially, we order the items in each group based on the Value property. The one with the minimum value would be the first element after the Ordering. Then we pick that element and read its Value and its Date and we are done.

Update

One fast solution it would be to call ToList, after the Join. This would bring all the data in application's memory and you would perform there all the required processing.

.Join(Table1
      , p => new { p.IdGroup1, p.IdGroup2 }
      , e => new { e.IdGroup1, e.IdGroup2 }
      , (p, e) => new 
        {
           Code = e.Code,                                          
           Value = p.Value,
           Date = p.Date 
    })
   .ToList()  
   .GroupBy(gb => new { gb.Code })
   .OrderBy(ob => ob.Key.Code)
   .Select(s => 
   {
       var values = s.OrderBy(x=>x.Value);
       var firstValue = values.First();
       return new 
       {
           Code = s.Key.Code;
           Value = (double?)firstValue.Value;
           Date = firstValue.Date;
       }
   }).ToList();

Answer 3

you could do this :

var query = Table2                                              //Outer Table
    .Join(Table1,                                       //Inner Table to join
        p => new { p.IdGroup1, p.IdGroup2 },   //Condition from outer table
        e => new { e.IdGroup1, e.IdGroup2 },   //Condition from inner table
        (p, e) => new
        {                        //Result
            Code = e.Code,
            Value = p.Value,
            Date = p.Date
        })
    .GroupBy(gb => new { gb.Code })
    .OrderBy(ob => ob.Key.Code)
    .Select(s =>
    {
        var min = s.FirstOrDefault(x=> x.Value == s.Min(a => a.Value));
        return new {Code = s.Key.Code, Value = (double?)min.Value, Date = min.Date};
    })  // TODO: The date remains to be implemented.
    .ToList();