Linq 外连接，两侧均默认

Question

我的外连接有问题。我发现这篇 stackoverflow 文章非常有用： linq-full-outer-join 但是当检索数据时出现错误时，我的连接遇到问题：

NotSupportedException: Unsupported overload used for query operator 'DefaultIfEmpty'.

这是代码：

var query =
        from i2 in (from sel in (from i in (from a1 in Activities
                    where a1.ActivityDate >= DateTime.Parse("4/15/2011")
                    && a1.ActivityDate < DateTime.Parse("4/19/2011")
                    select new { 
                        a1.ActivityDate,
                        a1.RecID} )
        group i by new { i.RecID }
        into g
        select new {
            g.Key.RecID,
            MaxDate = g.Max(i => i.ActivityDate)})
        join a in Activities on 1 equals 1 
        where (sel.MaxDate == a.ActivityDate && sel.RecID == a.RecID) 
        select new { 
            a.RecID,  
            a.UserName, 
            ActivityDate = a.ActivityDate.Date, 
            Sum1 = (a.StatusID == 7) ? 1 : 0,
            Sum2 = (a.StatusID == 5) ? 1 : 0,
            Sum3 = (a.StatusID == 4) ? 1 : 0})
        group i2 by new {
                UserName = i2.UserName,
                ActivityDate = i2.ActivityDate
                }
        into g2 
        select new {
            JoinId = (string)(g2.Key.ActivityDate + "_" + g2.Key.UserName),
            ActivityDate = (DateTime)g2.Key.ActivityDate,
            UserName = (string)g2.Key.UserName,
            Sum1 = (int)g2.Sum(i2 => i2.Sum1),
            Sum2 = (int)g2.Sum(i2 => i2.Sum2),
            Sum3 = (int)g2.Sum(i2 => i2.Sum3),
        };
    var query2 = from s in ProdHistories
    where s.CompletedDate >= DateTime.Parse("4/15/2011") 
    && s.CompletedDate <= DateTime.Parse("4/19/2011")
    select new { 
        JoinId = (string)(s.CompletedDate + "_" + s.UserName),
        CompletedDate = (DateTime)s.CompletedDate,
        UserName = (string)s.UserName,
        Type1 = (int)s.Type1,
    Type2 = (int)s.Type2,
            Type3 = (int)s.Type3,
            Type4 = (int)s.Type4,
            Type5 = (int)s.Type5,
            Type6 = (int)s.Type6,
            Type7 = (int)s.Type7,
            Type8 = (int)s.Type8,
            };

    var joinLeft = from ph in query2
        join act in query 
        on ph.JoinId equals act.JoinId
        into temp 
        from act in temp.DefaultIfEmpty(new { 
            JoinId = (string)ph.JoinId,
            ActivityDate = (DateTime)ph.CompletedDate,
            UserName = (string)ph.UserName, 
            Sum1 = default(int),
            Sum2 = default(int),
            Sum3 = default(int),
             })
        select new { ph.UserName, 
            ph.CompletedDate,
            ph.Type1,
            ph.Type2,
            ph.Type3,
            ph.Type4,
            ph.Type5,
            ph.Type6,
            ph.Type7,
            ph.Type8,
            act.Sum1,
            act.Sum2,
            act.Sum3};

    query.Dump(); // successfully dumps (in LinqPad) data - no nulls
    query2.Dump(); // successfully dumps (in LinqPad) data - no nulls
    joinLeft.Dump(); // raises: NotSupportedException: Unsupported overload used for query operator 'DefaultIfEmpty'.

我尝试了相同的外部连接子句并使用固定数据，它有效：

 var query = new[]
 {
      new {     JoinId = "12345", ActivityDate = DateTime.Parse("1/1/2011"), UserName = "UID1", Sum1 = 10, Sum2 = 11, Sum3 = 12 },
      new {     JoinId = "23456", ActivityDate = DateTime.Parse("1/2/2011"), UserName = "UID2", Sum1 = 20, Sum2 = 21, Sum3 = 22 },
      new {     JoinId = "34567", ActivityDate = DateTime.Parse("1/3/2011"), UserName = "UID3", Sum1 = 30, Sum2 = 31, Sum3 = 32 },
 };

 var query2 = new[]
 {
      new {     JoinId = "12345", CompletedDate = DateTime.Parse("1/1/2011"), UserName = "UID1", Type1 = 110, Type2 = 111, Type3 = 112, Type4 = 113, Type5 = 114, Type6 = 115, Type7 = 116, Type8 = 117 },
      new {     JoinId = "23456", CompletedDate = DateTime.Parse("1/2/2011"), UserName = "UID2", Type1 = 210, Type2 = 211, Type3 = 212, Type4 = 213, Type5 = 214, Type6 = 215, Type7 = 216, Type8 = 217 },
      new {     JoinId = "45678", CompletedDate = DateTime.Parse("1/5/2011"), UserName = "UID4", Type1 = 310, Type2 = 311, Type3 = 312, Type4 = 313, Type5 = 314, Type6 = 315, Type7 = 316, Type8 = 317 },
 };

 var joinLeft = from ph in query2
           join act in query 
           on ph.JoinId equals act.JoinId
           into temp 
           from act in temp.DefaultIfEmpty(new { 
                JoinId = (string)ph.JoinId,
                ActivityDate = (DateTime)ph.CompletedDate,
                UserName = (string)ph.UserName, 
                Sum1 = default(int),
                Sum2 = default(int),
                Sum3 = default(int),
                 })
           select new { ph.UserName, 
                ph.CompletedDate,
                ph.Type1,
                ph.Type2,
                ph.Type3,
                ph.Type4,
                ph.Type5,
                ph.Type6,
                ph.Type7,
                ph.Type8,
                act.Sum1,
                act.Sum2,
                act.Sum3};

 joinLeft.Dump();

结果：

UserName CompletedDate    Type1 Type2 Type3 Type4 Type5 Type6 Type7 Type8 Sum1 Sum2 Sum3 
UID1 1/1/2011 12:00:00 AM 110   111   112   113   114   115   116   117   10   11   12
UID2 1/2/2011 12:00:00 AM 210   211   212   213   214   215   216   217   20   21   22
UID4 1/5/2011 12:00:00 AM 310   311   312   313   314   315   316   317   0    0    0

我看到 另一篇 stackoverflow 文章，其中 Jon Skeet 使用 IEnumerable 作为在另一个上下文中解决此错误的一部分，但我不太确定如何应用它。

感谢您提供任何线索！

Answer 1

我认为 您从中获取代码的帖子也回答了您的问题（粗体）是我的）。

由于它位于 LINQ to Objects 中，所以它的工作方式与编写的一样。 如果 LINQ to SQL
或者其他，采用默认值的 DefaultIfEmpty() 重载可能
不工作。然后你必须使用条件运算符
有条件地获取值。

即，

var leftOuterJoin = 来自firstNames中的第一个
                    加入姓氏中的最后一个
                    第一个 ID 等于最后一个 ID
                    进入温度
                    从 temp.DefaultIfEmpty() 中的最后一个开始
                    选择新的
                    {
                        第一个.ID,
                        名字 = 名字.Name,
                        姓氏 = 最后一个 != null ?姓氏：默认（字符串），
                    };

来自 Robb 的更新

这就是答案，我可以通过使用 ToList() 将 IQueryable 转换为列表来使我的代码正常工作。

我对更新的评论

如果您不希望 join 在数据库上运行并且您愿意在对象上执行它们，那么这绝对是另一个选择。然而，如果确实有很多对象（有多少，这取决于），这可能会影响性能。遵循通常的优化口号：测量、测量、再测量。如果不用担心，ToList 就可以正常工作。