COUNT DISTINCT with Linq against Oracle
Posted: Tue 29 May 2012 11:57
Using dotConnect for Oracle 6.60.283.0 and Entity Framework 4.1, I want to do a COUNT(DISTINCT foo), or something equivalent. I'm aware that there's no out-of-the-box support for this in Entity Framework, but I've seen people suggest the following idiom:
(For the record, the entity "RetailerDelivery" is mapped to a table called "LVLEVERANSER", "RetailerNumber" is mapped to a field called "LVKNR", and "Date" is mapped to "LVDAT". The above is a big simplification of my real query, but it should get to the heart of the problem.)
I've tested this against MSSQL, where it generates a query that looks like this:
Note the double subquery. On MSSQL, you can use values from the outermost query inside a doubly nested subquery, so the test [Distinct1].[LVKNR] = [Extent2].[LVKNR] works fine. But Oracle doesn't seem to support this, so when similar SQL is generated, it fails. You don't even need to include any tables in the query to get to the root of it; this simple pair of queries should also illustrate the difference:
MSSQL (works):
Oracle (fails with ORA-00904: "BAR"."COL": invalid identifier):
I think this is expected behavior from Oracle, and I'm not sure if there's anything you guys can do about it on your end. I found this old forum post about the same issue: http://forums.devart.com/viewtopic.php?t=17320, but there didn't seem to be a resolution to it.
My question is basically: Do you know of some alternative way I can achieve a COUNT DISTINCT directly with Linq To Entities? Also, is there perhaps some way you can make dotConnect tell me that what I'm trying to do is not supported instead of giving a pretty obscure error message?
Code: Select all
from r in RetailerDeliveries
group r by r.RetailerNumber into grouped
select new
{
DistinctCount = grouped.Select(d => d.Date).Distinct().Count()
}
I've tested this against MSSQL, where it generates a query that looks like this:
Code: Select all
SELECT
[Distinct1].[LVKNR] AS [LVKNR],
(SELECT
COUNT(1) AS [A1]
FROM ( SELECT DISTINCT
[Extent2].[LVDAT] AS [LVDAT]
FROM [INFOSOFT].[LVLEVERANSER] AS [Extent2]
WHERE [Distinct1].[LVKNR] = [Extent2].[LVKNR]
) AS [Distinct2]) AS [C1]
FROM ( SELECT DISTINCT
[Extent1].[LVKNR] AS [LVKNR]
FROM [INFOSOFT].[LVLEVERANSER] AS [Extent1]
) AS [Distinct1]
MSSQL (works):
Code: Select all
select
(select Col from (select '1' as Col where Col = Bar.Col) Foo)
from (select '1' as Col) Bar;
Code: Select all
select
(select Col from (select '1' as Col from dual where Col = Bar.Col) Foo)
from (select '1' as Col from dual) Bar;
My question is basically: Do you know of some alternative way I can achieve a COUNT DISTINCT directly with Linq To Entities? Also, is there perhaps some way you can make dotConnect tell me that what I'm trying to do is not supported instead of giving a pretty obscure error message?