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

本文关键字：by in LINQ group and Distinct join ORA-00907 | 更新日期: 2023-09-27 18:10:34

在执行下面的linq查询时，我得到错误代码ORA-00907。这似乎是Oracle特有的。问题似乎出在"group by"子查询上。

假设我有两个表:USER和ADDRESS，各有列:用户{userid, addressid},地址{addressid, streetname}

表ADDRESS包含具有相同addressid的几行，所以我想我想在addressid上分组地址表(DISTINCT)，所以我只在USER-table中获得一个与addressid的匹配，它也应该是一个LEFT JOIN，所以如果没有匹配，我仍然得到USER-record。

我尝试了几种不同的方法，我的代码(示例):

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();

有人知道如何实现这一点，通过重写查询，使其对Oracle工作。

更新:

这是生成的sql查询，除了"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))))

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

DISTINCT应用于元组，而不是元组中的单个值。如果ADDRESSID在表ADDRESS中STREETNAME总是与ADDRESSID相同，那么您需要DISTINCT元组(ADDRESSID， STREETNAME)。您可以简单地选择context.ADDRESS的不同列作为子查询，并省略.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()

如果STREETNAME对于ADDRESSID并不总是相同的，那么您根本不需要DISTINCT。

相关文章：