ORA-00907: Distinct, join and group by in LINQ C#

Question

I'm getting the error code ORA-00907, when executing the linq query below. It seems to be Oracle specific. The problem seems to be the "group by" subquery.

Lets say I have these two tables: USER and ADDRESS, with columns: USER{userid, addressid}, ADDRESS{addressid, streetname}

Table ADDRESS contains several rows with the same addressid, so I guess I would like to group the ADDRESS-table (DISTINCT) on the addressid so I only get one match with addressid in USER-table, it should also be a LEFT JOIN, so if there is no match I still get the USER-record.

I have tried several different approaches, My code (example):

List<MyObject> result =
(
    from u in context.USER.Where(i => i.userid > 100)

    join a in (from address in context.ADDRESS group address by address.addressid)
    on u.addressid equals a.FirstOrDefault().addressid into joinedaddress
    from lfjoinedaddress in joinedaddress.DefaultIfEmpty()

    join email in context.EMAIL on u.userid equals email.userid into jemail
    from lfjemail in jemail.DefaultIfEmpty()

    select new MyObject()
    {
       UserId = u.userid,
       StreetName = lfjoinedaddress.streetname,
       UserEmail = lfjemail.emailaddress
    }
).ToList();

Someone know how to achieve this, by rewriting the query so it works against Oracle.

UPDATE:

This is the generated sql-query, except the "email":

SELECT 
1 AS "C1", 
"Extent1"."USERID" AS "USERID", 
"Extent1"."ADDRESSID" AS "ADDRESSID"
FROM  (SELECT 
      "USER"."USERID" AS "USERID", 
      "USER"."ADDRESSID" AS "ADDRESSIF",       
      FROM "EXT"."USER" "USER") "Extent1"
LEFT OUTER JOIN  (SELECT "Distinct1"."ADDRESSID" AS "ADDRESSID1", "Limit1"."ADDRESSID" AS "ADDRESSID2", , "Limit1"."STREETNAME" AS "STREETNAME1"
    FROM    (SELECT DISTINCT 
        "Extent2"."ADDRESSID" AS "ADDRESSID"
        FROM (SELECT 
      "ADDRESS"."ADDRESSID" AS "ADDRESSID", 
      "ADDRESS"."STREETNAME" AS "STREETNAME",       
      FROM "EXT"."ADDRESS" "ADDRESS") "Extent2" ) "Distinct1"
    OUTER APPLY  (SELECT "Extent3"."ADDRESSID" AS "ADDRESSID",  "Extent3"."STREETNAME" AS "STREETNAME"
        FROM (SELECT 
      "ADDRESS"."ADDRESSID" AS "ADDRESSID", 
      "ADDRESS"."STREETNAME" AS "STREETNAME",       
      FROM "EXT"."ADDRESS" "ADDRESS") "Extent3"
        WHERE ("Distinct1"."ADDRESSID" = "Extent3"."ADDRESSID") AND (ROWNUM <= (1) ) ) "Limit1"
    OUTER APPLY  (SELECT "Extent4"."ADDRESSID" AS "ADDRESSID", , "Extent4"."STREETNAME" AS "STREETNAME"
        FROM (SELECT 
      "ADDRESS"."ADDRESSID" AS "ADDRESSID", 
      "ADDRESS"."STREETNAME" AS "STREETNAME",       
      FROM "EXT"."ADDRESS" "ADDRESS") "Extent4"
        WHERE ("Distinct1"."ADDRESSID" = "Extent4"."ADDRESSID") AND (ROWNUM <= (1) ) ) "Limit2" ) "Apply2" ON ("Extent1"."ADDRESSID" = "Apply2"."ADDRESSID2") OR (("Extent1"."ADDRESSID" IS NULL) AND ("Apply2"."ADDRESSID3" IS NULL))))

Would this be a similar question that might help? http://stackoverflow.com/questions/10266128/entity-framework-left-outer-joins-and-group-into-throws-ora-00907-missing-rig?rq=1 — Dennis_E, Jun 27 '14 at 09:26
@Dennis_E: yes that is the same problem. But I cant find a workaround. — Filip, Jun 27 '14 at 10:40
@DanielKelley: The question is now updated with the sql-query — Filip, Jun 27 '14 at 10:40
FirstOrDefault() doesn't guarantee any order and I see no order by statement within the sub-query; therefore, your query is randomly grabbing an address where the addressid matches. Unless this is a simplified example, you have a data modelling issue as well. And once that's fixed, you probably won't even need the offending group by statement anymore. — Sybeus, Jun 29 '14 at 02:19
@Sybeus: Okay. In my situation, I just need to get one item that matches addressid, I don't care about the order. I'm not the database owner, so I cannot control the way it's modeled. What I want is just "DISTINCT" on ADDRESSID. — Filip, Jun 30 '14 at 11:26

Sybeus · Accepted Answer · 2014-07-01T19:45:57.960

1

DISTINCT is applied to tuples not an individual value within a tuple. If STREETNAME is always the same per ADDRESSID in table ADDRESS, then you want DISTINCT tuples of (ADDRESSID, STREETNAME). Which you could simply do with selecting the distinct columns of context.ADDRESS as your subquery and omit the .FirstOrDefault().

join a in 
(
    from address in context.ADDRESS
    select new
    {
        address.addressid,
        address.streetname
    }
).Distinct()
on u.addressid equals a.addressid into joinedaddress
from lfjoinedaddress in joinedaddress.DefaultIfEmpty()

If STREETNAME is not always the same per ADDRESSID, then you don't want DISTINCT at all.

edited Jul 01 '14 at 19:45

answered Jun 30 '14 at 17:45

Sybeus

1,169
11
18

Thanks for your answer. In Solution #1, the problem is that I also have other columns that might be different even if ADDRESSID and STREETNAME is always the same (my example seems to be too simplified). Lets say I have two rows where ADDRESSID and STREETNAME have the same values. And in a third column the values are different between the two rows. So unfortunately the .Distinct() doesn't work in this case. When I run the code from Solution #2, I get this exception: The method First can only be used as a final query operation. Consider instead use the method FirstOrDefault in this instance – Filip Jul 01 '14 at 13:42
Updated answer to explicitly define ADDRESSID and STREETNAME as the distinct tuple. Shouldn't matter about additional columns in table now. I realize now that while my 2nd solution works in some LINQ providers, it will not work with Entity Framework which appears you require; therefore, I have removed the 2nd solution from my answer. – Sybeus Jul 01 '14 at 19:48

ORA-00907: Distinct, join and group by in LINQ C#

1 Answers1