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();

Christos · Answer 1 · 2020-09-20T20:13:48.100

3

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();

edited Sep 20 '20 at 20:13

answered Sep 20 '20 at 05:32

Christos

53,228
8
76
108

I tried it but it gives me the following error: CS0834 A lambda expression with a statement body cannot be converted to an expression tree. – ingezone Sep 20 '20 at 16:13
The query I am using in EF Core 3.1 and it gives me the following error: InvalidOperationException: Client side GroupBy is not supported. – ingezone Sep 20 '20 at 19:21
@ingezone I think we found the guilty here :) https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client – Christos Sep 20 '20 at 19:34
@ingezone Try the updated one and please let me know. – Christos Sep 20 '20 at 19:38
@ingezone If this don't work, we could fetch all the data in the client and perform all the operations from join and afterwards in the client. However keep in mind that this is not an optimal solution here. In fact, if the table contains many data or it is expected in the future to grow substantially in size, then this approach would cause definetely problems from degraded performance to not being able to process the data and the application would crash. – Christos Sep 20 '20 at 19:39
There is a more efficient way to implement it ? – ingezone Sep 20 '20 at 20:07
@ingezone You could move the whole logic in the database - a rather old technique of doing things - in a stored procedure. Then from your code you would call the stored procedure and consume its results. Doing that only the data you need would be put on the wire. However this is not a silver bullet, it has also its cons...and of that cons is that you place application logic -which at this point isn't too complex- at the persistence layer. – Christos Sep 20 '20 at 20:10
That is, the only option for improvement would be with stored procedures, it would no longer be possible with C # code, right? – ingezone Sep 20 '20 at 20:14
@ingezone This is the only alternative option I can think of. Yeah, the C# code would be minimal, just call the stored procedure and consume the return results. – Christos Sep 20 '20 at 20:16
I thank you very much for all the help you gave me, you are a great collaborator. – ingezone Sep 20 '20 at 20:26
@ingezone You are very welcome ! I am glad that I helped :) – Christos Sep 21 '20 at 04:30
I can force the answer in case a group has null value. In my example I have three groups ZZ-100, ZZ-200, ZZ-300, therefore I have an answer with 3 rows. But what if the ZZ-200 group has no data, it will return only the ZZ-100 and Z-300 rows. I need it to always return the 3 rows with or without data. Is it possible? – ingezone Sep 23 '20 at 19:58
@ingezone Yes, it is possible. That you are looking for is how you are going to make a `left-outer-join`. Please check this https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins and this Q/A https://stackoverflow.com/questions/19293844/linq-join-iquery-how-to-use-defaultifempty. – Christos Sep 24 '20 at 18:59
You are absolutely right, I was studying left-outer-join and it is the solution. – ingezone Sep 25 '20 at 06:18
Please can you help me to convert "My Updated" query using (left-outer-join). I tried to do it but it doesn't work for me. In my updated query I am using 3 join, you can see above in my original question that I update it. – ingezone Sep 25 '20 at 06:48
1

Why stored procedure? View should be enough. And btw linq is limited in what you can do, so you would end up with views anyway, sooner or later. They are both practical and powerful. – Antonín Lejsek Sep 26 '20 at 20:43
@AntonínLejsek +1 You are correct. Also a view it could be used for that purpose. – Christos Sep 27 '20 at 05:09

Svyatoslav Danyliv · Accepted Answer · 2020-09-26T20:30:02.697

3

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.

edited Sep 26 '20 at 20:30

answered Sep 25 '20 at 17:21

Svyatoslav Danyliv

21,911
3
16
32

When the record does not exist, it should show null values. Because the CODE exists in table 1. – ingezone Sep 26 '20 at 07:27
I tried both options and they worked, but they don't deliver the answer I need. Please see my desired answer above in the issue. – ingezone Sep 26 '20 at 07:40
Updated answer. If you need more details, just type comment. – Svyatoslav Danyliv Sep 26 '20 at 15:32
Please can you add the following filter by date. where t2.date <= DateTime.Parse ("2020-09-16 03:00:00") – ingezone Sep 26 '20 at 20:23
Once you add the filter by date, the CODE 'ZZ-200' should appear and the columns that accompany it VALUE and DATE should be shown with null values. – ingezone Sep 26 '20 at 20:24
Updated answer to such requirement. – Svyatoslav Danyliv Sep 26 '20 at 20:24
Instead of getting the minimum, which I must modify to get the maximum value of the VALUE. – ingezone Sep 26 '20 at 20:39
In such case change .OrderBy(t2.Value) to .OrderByDesc(t2.Value) – Svyatoslav Danyliv Sep 26 '20 at 20:44
PERFECT, I tried both options and it works 100% as Needed. I am very grateful :) – ingezone Sep 26 '20 at 20:56
**It is possible to convert to Lambda expression.** – ingezone Sep 26 '20 at 21:00
@ Svyatoslav, can you help me with the latter. I summarize below, I had to make 3 queries, well 2 of them are already ready. The third I need to modify it using your method, you can help me modify it. – ingezone Sep 26 '20 at 21:36
Post an issue in GitHub https://github.com/linq2db/linq2db.EntityFrameworkCore And I'll answer. – Svyatoslav Danyliv Sep 26 '20 at 21:40
@SvyatoslavDanyliv nice answer, +1. – Christos Sep 27 '20 at 05:13

score 1 · Answer 3 · answered Sep 20 '20 at 07:00

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();

I tried it but it gives me the following error: CS0834 A lambda expression with a statement body cannot be converted to an expression tree. — ingezone, Sep 20 '20 at 15:46

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

3 Answers3

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