1

I am using Linq2DB for PostgreSQL. I need to group the data by week in Linq. Below is my code:

Func<DateTime,int> weekProjector = d => CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(d,
                                                         CalendarWeekRule.FirstFourDayWeek,
                                                         DayOfWeek.Sunday);

var ddquery = from t in db.VLogData
              group t by weekProjector(t.NewTime);

foreach (var f in ddquery)
{
    f.Key.ToString();
}

But it show the error or foreach there

(xxx.Controllers.DashboardController+<>c__DisplayClass1_0).weekProjector, selectParam.NewTime)' cannot be converted to SQL.'

May I know what is the issue there?

Selim Yildiz
  • 5,254
  • 6
  • 18
  • 28
kchong Hoh
  • 31
  • 6
  • 1
    "cannot be converted to SQL." Isn't that clear? What should `weekProjector(t.NewTime)` look like in SQL? – Gert Arnold May 21 '20 at 20:29
  • When I answer such questions, I request expected SQL. If it is doable via SQL, then it is not a problem to explain for linq2db how to generate appropriate SQL. – Svyatoslav Danyliv May 23 '20 at 13:01

1 Answers1

2

weekProjector is a client-side function, but you pass server-side data to it as input. To make it work you can:

  • perform grouping on client by pasting AsEnumerable() call between Select and GroupBy calls (worst option ever)
  • tell linq2db which aggregation logic to use by applying Sql.ExpressionAttribute or Sql.ExtensionAttribute on weekProjector with SQL expression and IsAggregate=true set for attribute
  • same as previous, but by using ExpressionMethodAttribute to define aggregation logic using C# expressions