COUNT DISTINCT with Linq against Oracle

[magnus]

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:

Code: Select all

from r in RetailerDeliveries
group r by r.RetailerNumber into grouped
select new
{
    DistinctCount = grouped.Select(d => d.Date).Distinct().Count()
}

(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:

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]

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):

Code: Select all

select
    (select Col from (select '1' as Col where Col = Bar.Col) Foo)
    from (select '1' as Col) Bar;

Oracle (fails with ORA-00904: "BAR"."COL": invalid identifier):

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;

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?

[Shalex]

Do you know of some alternative way I can achieve a COUNT DISTINCT directly with Linq To Entities?

We do not know a workaround.

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?

Throwing the "not supported" exception is not correct behaviour as there is at least one version (10.2.0.1) of Oracle server which executes such queries successfully because there are no strict limitations for nesting and visibility of identificators in SQL in the 10.2.0.1 version.