How do I get the maximum value of one group and the minimum value of another group.

[ingezone]

Hello

You can help me transform my query to use with linq2db.

                 TABLE 1
+------------+--------------+---------------+-------------+-----------------+
| --  Code -- | - IdGroup1 - | - IdGroup2 - | -  Client - | - NameProtocol -|
+ ------------+--------------+--------------+-------------+-----------------+
|    ZZ-100   |      11      |      22      |       1     |       OSM1      |
+ ------------+--------------+--------------+-------------+-----------------+
|    ZZ-200   |     100      |      110     |       1     |       OSM1      |
+ ------------+--------------+--------------+-------------+-----------------+
|    ZZ-300   |      500     |      550     |       1     |       OSM1      |
+------------+--------------+---------------+-------------+-----------------+

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

                                      TABLE 2
+----------+-----------------------+----------------+----------------+--------------+
| - Level -| ------- Date -------- | -- IdGroup1 -- | -- IdGroup2 -- | -- Status -- |
+ ---------+-----------------------+----------------+----------------+--------------+
|    65    |  2020-09-16 02:30:40  |       11       |       22       |      0       |
v ---------+-----------------------+----------------+----------------+--------------+
|    70    |  2020-09-16 02:30:30  |       11       |       22       |      1       |
+ ---------+-----------------------+----------------+----------------+--------------+
|    80    |  2020-09-16 02:30:20  |       11       |       22       |      1       |
+ ---------+-----------------------+----------------+----------------+--------------+
|    100   |  2020-09-16 02:30:10  |       11       |       22       |      0       |
+ ---------+-----------------------+----------------+----------------+--------------+
|    110   |  2020-09-16 02:30:00  |       11       |       22       |      0       |
+ ---------+-----------------------+----------------+----------------+--------------+
|    650   |  2020-09-16 02:40:40  |       100      |      110       |      0       |
+ ---------+-----------------------+----------------+----------------+--------------+
|    700   |  2020-09-16 02:40:30  |       100      |      110       |      1       |
+ ---------+-----------------------+----------------+----------------+--------------+
|    800   |  2020-09-16 02:40:20  |       100      |      110       |      1       |
+ ---------+-----------------------+----------------+----------------+--------------+
|   1000   |  2020-09-16 02:50:00  |       500      |      550       |      1       |
+ ---------+-----------------------+----------------+----------------+--------------+

My query obtains the maximum or minimum value of the LEVEL field, with the following conditions and for that subqueries were resolved, which first returns the following:

• Get the amount of Status = 0,
• Get the amount of Status = 1,
• Get the maximum value of LEVEL for Status = 1,
• Get the minimum value of LEVEL for Status = 0

var dateFilter = DateTime.Parse ("2020-09-16 03:00:00");

var queryCondition = table2
                        .Where(hn => hn.Date <= dateFilter)
            .Join(table1.Where(w => (w.Client == 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.h.Status, gb.pt.h.Level, gb.pt.p.code })
            .Select(s => new
                    {
                    Code = s.Key.code,
                    Cnt_0 = (s.Key.Status == 0) ? 1 : 0,
                    Cnt_1 = (s.Key.Status == 1) ? 1 : 0,
                    Max_1 = (s.Key.Status == 1) ? (double?)(s.Key.Level) : (double?)null,
                    Min_0 = (s.Key.Status == 0) ? (double?)(s.Key. Level) : (double?)null
                        })
            .GroupBy(hn => new { hn.Code })
            .Select(g => new
                    {
                    Code = g.Key.Code,
                    Static = g.Sum(p => p.Cnt_0),
                    Dinamic = g.Sum(p => p.Cnt_1),
                    MaxDinamic = (double?)(g.Max(p => p.Max_1)),
                    MinStatic = (double?)(g.Min(p => p.Min_0))
                    })
                .ToList();

var queryNF = queryCondition
                .OrderBy(ob => ob.Code)
                .Select(qC => new
                    {
                    Code = qC.Code,
                    Level = qC.Static >= qC.Dinamic ? qC.MinStatic :
                        qC.Static < qC.Dinamic ? qC.MaxDinamic : null
                    })
                .ToList();

[sdanyliv]

Too late here, but I think I understand correctly your query:

var queryCondition =
   from t1 in Table1
   join t3 in Table3 on t1.NameOfProtocol equals t3.NameOfProtocol
   join t2 in Table2 on new { t1.IdGroup1, t1.dGroup2 } equals  new { t2.IdGroup1, t2.dGroup2 }
   where t3.Protocol == "TCP"
   group t2 by new { t1.Code } into g
   select new 
   {
      g.Key.Code,
      Static     = g.CountExt(_ => _.Status == 0 ? 1 : (int?)null),
      Dinamic    = g.CountExt(_ => _.Status == 1 ? 1 : (int?)null),
      MaxDinamic = g.Max(_ => _.Status == 1 ? _.Level : (double?)null)
      MinStatic  = g.Min(_ => _.Status == 0 ? _.Level : (double?)null),
   };

var queryNF = from ob in queryCondition
    order by ob.Code
    select new 
    {
        ob.Code,
        Level = ob.Static >= ob.Dinamic ? ob.MinStatic : ob.Static < ob.Dinamic ? ob.MaxDinamic : null
    };

var result = queryNF.ToLinqToDB().ToList();

[ingezone]

Once again, thank you very much. This works 100% as I need. 👍

[sdanyliv]

URW, closing then.