SQL to LINQ Convertion error: DISTINCT an Agregate count cannot be converted to LINQ

Asked Feb 11 '16 at 12:16

Active Feb 11 '16 at 12:16

Viewed 126 times

I have a SQL query and I am trying to convert it to LINQ. But when I do so, I am getting this error:

SQL cannot be converted to LINQ: DISTINCT an Agregate count cannot be converted to LINQ

SELECT 
ST_CN_NO,
DN_DT,
count(ST_CN_NO) as totlines,
count(distinct LOC_CODE) as totloc,
sum(SN_QTY)as totscnqty,
sum(SAP_QTY) as totmpqty
from physical_count 
where 1=1  
group by ST_CN_NO,DN_DT  
order by physical_count.ST_CN_NO

So, how can I convert count(distinct LOC_CODE) to LINQ? Is there any way to do so?

This is my LINQ query whithout adding count(distinct LOC_CODE):

from t in db.PHYSICAL_COUNT
where
  1 == 1
group t by new {
  t.ST_CN_NO,
  t.DN_DT
} into g
orderby
  g.Key.ST_CN_NO
select new {
  ST_CN_NO = (Int32?)g.Key.ST_CN_NO,
  g.Key.DN_DT,
  totlines = (Int64?)g.Count(),
  totscnqty = (Int32?)g.Sum(p => p.SN_QTY),
  totmpqty = (Int32?)g.Sum(p => p.SAP_QTY)
}

asked Feb 11 '16 at 12:16

Limna

1

I think `totloc = g.Select(p => p.LOC_CODE).Distinct().Count();` would work. However I doubt it gets translated back to the same SQL. – juharr Feb 11 '16 at 12:22
Yeah, just realized that, fixed it. – juharr Feb 11 '16 at 12:24
Is there a reason for the WHERE clause? It seems to do nothing in the SQL and in the LINQ. – Andy Nichols Feb 11 '16 at 12:26
1

@AndyNichols If I had to guess that SQL was generated by something that dynamically added clauses, and the `1=1` was just a default clause for when none are added. – juharr Feb 11 '16 at 12:27
1

BTW what exactly is giving you that error? I assume it's some type of SQL to Linq converter. – juharr Feb 11 '16 at 12:29
Possible duplicate of [LINQ to SQL using GROUP BY and COUNT(DISTINCT)](http://stackoverflow.com/questions/448203/linq-to-sql-using-group-by-and-countdistinct) – Ivan Stoev Feb 11 '16 at 12:34
I get it in Linqer – tintyethan Apr 01 '22 at 13:35

SQL to LINQ Convertion error: DISTINCT an Agregate count cannot be converted to LINQ

0 Answers0