COUNT DISTINCT with Linq against Oracle

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
magnus
Posts: 11
Joined: Mon 22 Aug 2011 11:07

COUNT DISTINCT with Linq against Oracle

Post by magnus » 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:

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: COUNT DISTINCT with Linq against Oracle

Post by Shalex » Thu 31 May 2012 12:53

magnus wrote:Do you know of some alternative way I can achieve a COUNT DISTINCT directly with Linq To Entities?
We do not know a workaround.
magnus wrote: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.

Post Reply