Select [with aggregated functions] group by in Lambda

Question

How do I convert the following select

SELECT     TOP (60) COUNT(*) AS QTD, SUM(Duration) AS Total, AVG(Duration) AS Media, MIN(Duration) AS Menor, LEFT(TextData, 80) AS TextData
FROM         Traces WITH (nolock)
WHERE     (StartTime >= DATEADD(D, - 7, GETDATE())) AND (DataBaseName IN ('database_1', 'database_2'))
GROUP BY TextData, DataBaseName
HAVING      (COUNT(*) > 1)
ORDER BY Total DESC

I’m trying it this way:

var lambda = ctx.Traces.Where(q => q.DataBaseName == "database_1" || q.DataBaseName == "database_2")
         .GroupBy(a => new { a.TextData, a.DataBaseName })
         .Select(b => new { 
                 QTD = b.Count(), 
                 Total = b.Sum(b1 => b1.Duration),
                 Media = b.Average(b1 => b1.Duration),
                 Menor = b.Min(b1 => b1.Duration),
                 textData = b.Select(b2 => b2.TextData) //aqui o problema
                          })
          .OrderByDescending(b => b.Total)
          .Take(10)
          .ToList();

but I cannot recover Textdata

it returns something like:

System.Collections.Generic.List`1[System.String]

the other fields are ok.

Answer 1

Put it like this:

var lambda = ctx.Traces
         .Where(q => q.DataBaseName == "database_1" || q.DataBaseName == "database_2")
         .GroupBy(a => new { a.TextData, a.DataBaseName })
         .Select(b => new { 
                 QTD = b.Count(), 
                 Total = b.Sum(b1 => b1.Duration),
                 Media = b.Average(b1 => b1.Duration),
                 Menor = b.Min(b1 => b1.Duration),
                 textData = EntityFunctions.Left(b.Key.TextData, 80)})
          .OrderByDescending(b => b.Total)
          .Take(10)
          .ToList();

or

var lambda = ctx.Traces
         .Where(q => q.DataBaseName == "database_1" || q.DataBaseName == "database_2")
         .GroupBy(a => new { a.TextData, a.DataBaseName })
         .Select(b => new { 
                 QTD = b.Count(), 
                 Total = b.Sum(b1 => b1.Duration),
                 Media = b.Average(b1 => b1.Duration),
                 Menor = b.Min(b1 => b1.Duration),
                 textData = b.Key.TextData.Substring(0,80)})
          .OrderByDescending(b => b.Total)
          .Take(10)
          .ToList();

Key = (a => new { a.TextData, a.DataBaseName }), that is, it is the grouping that you set up may be b.Key.TextData and b.Key.DataBaseName