0

How I can migrate clickhouse countIf function to linq2db?

Example:

from =>

SELECT customer_id,
    countIf(customer_id, active_month between 0 and 1) as active_month1
FROM myt_table
GROUP BY customer_id

to =>

from x in my_table
group x by x.customerId into g
select new {
    CustomerId = g.Key,
    ActiveMonth1 = Sql.CountIf(x, p => p.customerId, p => p.ActiveMonth.between(1, 6)) 
}

I tried this option but it has problems

from x in MyTable
group x by x.CustomerId into g
select new {
    CustomerId = g.Key,
    FirstMonthCount = g.Count(p => 1 >= p.ActiveMonth && p.ActiveMonth <= 6)
}
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
user20785872
  • 1
  • `countIf(customer_id, active_month between 0 and 1) as active_month1` BTW it calculates `number of rows` where condition is true AND customer_id is not null. I guess you need/meant `countIf(active_month between 0 and 1)` – Denny Crane Dec 15 '22 at 20:07
  • You're right. I solved the problem by adding expression [Sql.Extension("countIf({predicate})", ServerSideOnly = true)] . Thanks for the clarification – user20785872 Dec 16 '22 at 14:50
  • Needs editing to add syntax highlighting: https://stackoverflow.com/editing-help – starball Dec 19 '22 at 01:47

1 Answers1

0

Try the following workaround:

from x in MyTable
group x by x.CustomerId into g
select new 
{
    CustomerId = g.Key,
    FirstMonthCount = g.Sum(p => 1 >= p.ActiveMonth && p.ActiveMonth <= 6 ? 1 : 0)
}
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32