首页 文章

LINQ to SQL - 具有多个连接条件的左外连接

提问于
浏览
136

我有以下SQL,我试图将其转换为LINQ:

SELECT f.value
FROM period as p 
LEFT OUTER JOIN facts AS f ON p.id = f.periodid AND f.otherid = 17
WHERE p.companyid = 100

我已经看到左外连接的典型实现(即 into x from y in x.DefaultIfEmpty() 等),但我不确定如何引入其他连接条件( AND f.otherid = 17

EDIT

为什么 AND f.otherid = 17 条件是JOIN的一部分而不是WHERE子句?因为某些行可能不存在 f ,我仍然希望包含这些行 . 如果条件在WHERE子句中应用,在JOIN之后 - 那么我没有得到我想要的行为 .

不幸的是:

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in fg.DefaultIfEmpty()
where p.companyid == 100 && fgi.otherid == 17
select f.value

似乎等同于:

SELECT f.value
FROM period as p 
LEFT OUTER JOIN facts AS f ON p.id = f.periodid 
WHERE p.companyid = 100 AND f.otherid = 17

这不是我想要的 .

5 回答

  • 24

    在调用DefaultIfEmpty()之前,您需要介绍您的连接条件 . 我只想使用扩展方法语法:

    from p in context.Periods
    join f in context.Facts on p.id equals f.periodid into fg
    from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty()
    where p.companyid == 100
    select f.value
    

    或者您可以使用子查询:

    from p in context.Periods
    join f in context.Facts on p.id equals f.periodid into fg
    from fgi in (from f in fg
                 where f.otherid == 17
                 select f).DefaultIfEmpty()
    where p.companyid == 100
    select f.value
    
  • -1

    这也有效,...如果你有多个列连接

    from p in context.Periods
    join f in context.Facts 
    on new {
        id = p.periodid,
        p.otherid
    } equals new {
        f.id,
        f.otherid
    } into fg
    from fgi in fg.DefaultIfEmpty()
    where p.companyid == 100
    select f.value
    
  • 9

    我知道它“有点晚了”但是如果有人需要在_1772041中这样做(这就是为什么我最初发现这篇文章),这将是如何做到这一点:

    var results = context.Periods
        .GroupJoin(
            context.Facts,
            period => period.id,
            fk => fk.periodid,
            (period, fact) => fact.Where(f => f.otherid == 17)
                                  .Select(fact.Value)
                                  .DefaultIfEmpty()
        )
        .Where(period.companyid==100)
        .SelectMany(fact=>fact).ToList();
    
  • 5

    另一个有效选项是在 multiple LINQ clauses 之间传播连接,如下所示:

    public static IEnumerable<Announcementboard> GetSiteContent(string pageName, DateTime date)
    {
        IEnumerable<Announcementboard> content = null;
        IEnumerable<Announcementboard> addMoreContent = null;
            try
            {
                content = from c in DB.Announcementboards
                  //Can be displayed beginning on this date
                  where c.Displayondate > date.AddDays(-1)
                  //Doesn't Expire or Expires at future date
                  && (c.Displaythrudate == null || c.Displaythrudate > date)
                  //Content is NOT draft, and IS published
                  && c.Isdraft == "N" && c.Publishedon != null
                  orderby c.Sortorder ascending, c.Heading ascending
                  select c;
    
                //Get the content specific to page names
                if (!string.IsNullOrEmpty(pageName))
                {
                  addMoreContent = from c in content
                      join p in DB.Announceonpages on c.Announcementid equals p.Announcementid
                      join s in DB.Apppagenames on p.Apppagenameid equals s.Apppagenameid
                      where s.Apppageref.ToLower() == pageName.ToLower()
                      select c;
                }
    
                //CROSS-JOIN this content
                content = content.Union(addMoreContent);
    
                //Exclude dupes - effectively OUTER JOIN
                content = content.Distinct();
    
                return content;
            }
        catch (MyLovelyException ex)
        {
            throw ex;
        }
    }
    
  • 222

    在我看来,在尝试翻译它之前,考虑对SQL代码进行一些重写是有 Value 的 .

    就个人而言,我会写一个像union这样的查询(虽然我完全避免使用null!):

    SELECT f.value
      FROM period as p JOIN facts AS f ON p.id = f.periodid
    WHERE p.companyid = 100
          AND f.otherid = 17
    UNION
    SELECT NULL AS value
      FROM period as p
    WHERE p.companyid = 100
          AND NOT EXISTS ( 
                          SELECT * 
                            FROM facts AS f
                           WHERE p.id = f.periodid
                                 AND f.otherid = 17
                         );
    

    所以我想我同意@ MAbraham1答案的精神(尽管他们的代码似乎与这个问题无关) .

    但是,似乎查询明确地设计为生成包含重复行的单个列结果 - 实际上是重复的空值!很难不得出这种方法存在缺陷的结论 .

相关问题