使用LINQ TO SQL 中的左外连接中DEFAULTIFEMPTY为什么赋不上默认值

Question

进行SQL SERVER左外连接想用DEFAULTIFEMPTY()试着赋予默认值，结果赋不上是什么原因呢？

            Location defaultLoc = new Location { Id = String.Empty, Name = "黑凤梨", Postcode = String.Empty };
            var re = from u in _context.UserMsg
                     join l in _context.Location
                     on u.LocationId equals l.Id
                     into LocGroup
                     from item in LocGroup.DefaultIfEmpty(defaultLoc)
                     where u.Status == "0"
                     select new
                     {
                         PerId = u.Id,
                         PerName = u.Name,
                         PerAge = u.Age,
                         PerLocation = item.Name
                     };
            var rre = from u in _context.UserMsg
                      join l in _context.Location
                      on u.LocationId equals l.Id
                      into LocGroup
                      select new { UserName = u.Name,Loc = LocGroup };
            List<Location> gg = new List<Location>();
            LogHelper.Logger.Info(JsonConvert.SerializeObject(gg));
            LogHelper.Logger.Info(JsonConvert.SerializeObject(gg.DefaultIfEmpty(defaultLoc)));
            LogHelper.Logger.Info(JsonConvert.SerializeObject(rre));
            LogHelper.Logger.Info(JsonConvert.SerializeObject(re));

Answer 1

在LINQ TO SQL中使用LEFT OUTER JOIN时，如果默认值在LEFT OUTER JOIN操作之前定义，那么可能无法正确地分配默认值。这是因为它需要将DEFAULTIFEMPTY函数绑定到正确的LEFT OUTER JOIN子句上。为了解决这个问题，你可以使用lambda表达式来定义默认值。请注意，lambda表达式应该在LEFT OUTER JOIN操作之前定义。以下是示例代码：

Location defaultLoc = new Location { Id = String.Empty, Name = "黑凤梨", Postcode = String.Empty };
var re = from u in _context.UserMsg
         join l in _context.Location
         on u.LocationId equals l.Id into LocGroup
         from item in LocGroup.DefaultIfEmpty()
             .Select(l => new Location { Id = String.Empty, Name = "黑凤梨", Postcode = String.Empty })
         where u.Status == "0"
         select new
         {
             PerId = u.Id,
             PerName = u.Name,
             PerAge = u.Age,
             PerLocation = item.Name
         };

在上面的代码中，我们使用了Select操作，使用lambda表达式来定义默认值。这将确保我们的LEFT OUTER JOIN中的DEFAULTIFEMPTY函数被正确绑定到LEFT OUTER JOIN子句上。