如何在LINQ的Select new中复用已计算的字段？

方法1：用let关键字提前定义复用字段 #

在LINQ查询里，let能帮你提前算出需要重复使用的字段，后续直接引用就行，不用再写一遍查询逻辑。修改后的代码如下：

var query = from lvlData in _context.TMagnecubeCustomlevels
            where lvlData.DateDeleted == null
            // 先把要复用的字段算好存成变量
            let numbPlays = (from tbl in _context.TMagnecubeCustomlevelsPlays
                             where lvlData.Id == tbl.FkLevelId
                             select tbl).Count()
            let numbCompleted = (from tbl in _context.TMagnecubeCustomlevelsCompleteds
                                 where lvlData.Id == tbl.FkLevelId
                                 select tbl).Count()
            let isLikedByUser = (from tbl in _context.TMagnecubeCustomlevelsLikes
                                 where tbl.IsLike == true && lvlData.Id == tbl.FkLevelId && tbl.FkUser == thisUserID
                                 select tbl).Count()
            let numbLikes = (from tbl in _context.TMagnecubeCustomlevelsLikes
                             where tbl.IsLike == true && lvlData.Id == tbl.FkLevelId
                             select tbl).Count()
            select new {
                Id = lvlData.Id,
                isPrivate = lvlData.IsPrivate,
                thisUserOwnsLvl = thisUserID == lvlData.FkUserCreatedBy,
                FkUserCreatedBy = lvlData.FkUserCreatedBy,
                DateCreated = lvlData.DateCreated,
                LvlTitle = lvlData.LvlTitle,
                LvlDesc = lvlData.LvlDesc,
                isLikedByUser,
                numbLikes,
                numbPlays,
                numbCompleted,
                bestTime = (from tbl in _context.TMagnecubeCustomlevelsBesttimes
                            where lvlData.Id == tbl.FkLevelId
                            orderby tbl.BestTime
                            select tbl.BestTime).First(),
                bestTime_fkUserId = (from tbl in _context.TMagnecubeCustomlevelsBesttimes
                                     where lvlData.Id == tbl.FkLevelId
                                     orderby tbl.BestTime, tbl.DateLastUpdated
                                     select tbl.FkUser).First(),
                bestMoves = (from tbl in _context.TMagnecubeCustomlevelsBestmoves
                             where lvlData.Id == tbl.FkLevelId
                             orderby tbl.BestMoves
                             select tbl.BestMoves).First(),
                bestMoves_fkUserId = (from tbl in _context.TMagnecubeCustomlevelsBestmoves
                                      where lvlData.Id == tbl.FkLevelId
                                      orderby tbl.BestMoves, tbl.DateLastUpdated
                                      select tbl.FkUser).First(),
                // 直接用提前算好的变量，还要处理除以零和整数除法的问题
                lvlSuccessRate = numbPlays == 0 ? 0.0 : (double)numbCompleted / numbPlays,
                popularityRateLastWeek = (from tbl in _context.TMagnecubeCustomlevelsPlays
                                          where lvlData.Id == tbl.FkLevelId && tbl.DateLastUpdated >= lastWeekValue
                                          select tbl).Count() * 0.0035 +
                                          (from tbl in _context.TMagnecubeCustomlevelsLikes
                                           where tbl.IsLike == true && lvlData.Id == tbl.FkLevelId && tbl.DateLastUpdated >= lastWeekValue
                                           select tbl).Count() * 0.0065,
                popularityRateLastMonth = (from tbl in _context.TMagnecubeCustomlevelsPlays
                                          where lvlData.Id == tbl.FkLevelId && tbl.DateLastUpdated >= lastMonthValue
                                          select tbl).Count() * 0.0035 +
                                          (from tbl in _context.TMagnecubeCustomlevelsLikes
                                           where tbl.IsLike == true && lvlData.Id == tbl.FkLevelId && tbl.DateLastUpdated >= lastMonthValue
                                           select tbl).Count() * 0.0065,
                popularityRateLastYear = (from tbl in _context.TMagnecubeCustomlevelsPlays
                                           where lvlData.Id == tbl.FkLevelId && tbl.DateLastUpdated >= lastYearValue
                                           select tbl).Count() * 0.0035 +
                                          (from tbl in _context.TMagnecubeCustomlevelsLikes
                                           where tbl.IsLike == true && lvlData.Id == tbl.FkLevelId && tbl.DateLastUpdated >= lastYearValue
                                           select tbl).Count() * 0.0065
            };

方法2：分两次投影获取最终结果 #

如果不想用let，可以先投影出包含numbPlays和numbCompleted的中间匿名类型，再基于这个中间结果生成最终的匿名类型，这样就能直接引用已计算的字段：

var query = _context.TMagnecubeCustomlevels
    .Where(lvlData => lvlData.DateDeleted == null)
    .Select(lvlData => new {
        lvlData.Id,
        lvlData.IsPrivate,
        thisUserOwnsLvl = thisUserID == lvlData.FkUserCreatedBy,
        lvlData.FkUserCreatedBy,
        lvlData.DateCreated,
        lvlData.LvlTitle,
        lvlData.LvlDesc,
        isLikedByUser = (from tbl in _context.TMagnecubeCustomlevelsLikes
                         where tbl.IsLike == true && lvlData.Id == tbl.FkLevelId && tbl.FkUser == thisUserID
                         select tbl).Count(),
        numbLikes = (from tbl in _context.TMagnecubeCustomlevelsLikes
                     where tbl.IsLike == true && lvlData.Id == tbl.FkLevelId
                     select tbl).Count(),
        numbPlays = (from tbl in _context.TMagnecubeCustomlevelsPlays
                     where lvlData.Id == tbl.FkLevelId
                     select tbl).Count(),
        numbCompleted = (from tbl in _context.TMagnecubeCustomlevelsCompleteds
                         where lvlData.Id == tbl.FkLevelId
                         select tbl).Count(),
        bestTime = (from tbl in _context.TMagnecubeCustomlevelsBesttimes
                    where lvlData.Id == tbl.FkLevelId
                    orderby tbl.BestTime
                    select tbl.BestTime).First(),
        bestTime_fkUserId = (from tbl in _context.TMagnecubeCustomlevelsBesttimes
                             where lvlData.Id == tbl.FkLevelId
                             orderby tbl.BestTime, tbl.DateLastUpdated
                             select tbl.FkUser).First(),
        bestMoves = (from tbl in _context.TMagnecubeCustomlevelsBestmoves
                     where lvlData.Id == tbl.FkLevelId
                     orderby tbl.BestMoves
                     select tbl.BestMoves).First(),
        bestMoves_fkUserId = (from tbl in _context.TMagnecubeCustomlevelsBestmoves
                              where lvlData.Id == tbl.FkLevelId
                              orderby tbl.BestMoves, tbl.DateLastUpdated
                              select tbl.FkUser).First(),
        lastWeekValue,
        lastMonthValue,
        lastYearValue
    })
    .Select(intermediate => new {
        intermediate.Id,
        isPrivate = intermediate.IsPrivate,
        intermediate.thisUserOwnsLvl,
        intermediate.FkUserCreatedBy,
        intermediate.DateCreated,
        intermediate.LvlTitle,
        intermediate.LvlDesc,
        intermediate.isLikedByUser,
        intermediate.numbLikes,
        intermediate.numbPlays,
        intermediate.numbCompleted,
        intermediate.bestTime,
        intermediate.bestTime_fkUserId,
        intermediate.bestMoves,
        intermediate.bestMoves_fkUserId,
        // 直接引用中间结果里的已计算字段
        lvlSuccessRate = intermediate.numbPlays == 0 ? 0.0 : (double)intermediate.numbCompleted / intermediate.numbPlays,
        popularityRateLastWeek = (from tbl in _context.TMagnecubeCustomlevelsPlays
                                  where intermediate.Id == tbl.FkLevelId && tbl.DateLastUpdated >= intermediate.lastWeekValue
                                  select tbl).Count() * 0.0035 +
                                  (from tbl in _context.TMagnecubeCustomlevelsLikes
                                   where tbl.IsLike == true && intermediate.Id == tbl.FkLevelId && tbl.DateLastUpdated >= intermediate.lastWeekValue
                                   select tbl).Count() * 0.0065,
        popularityRateLastMonth = (from tbl in _context.TMagnecubeCustomlevelsPlays
                                  where intermediate.Id == tbl.FkLevelId && tbl.DateLastUpdated >= intermediate.lastMonthValue
                                  select tbl).Count() * 0.0035 +
                                  (from tbl in _context.TMagnecubeCustomlevelsLikes
                                   where tbl.IsLike == true && intermediate.Id == tbl.FkLevelId && tbl.DateLastUpdated >= intermediate.lastMonthValue
                                   select tbl).Count() * 0.0065,
        popularityRateLastYear = (from tbl in _context.TMagnecubeCustomlevelsPlays
                                   where intermediate.Id == tbl.FkLevelId && tbl.DateLastUpdated >= intermediate.lastYearValue
                                   select tbl).Count() * 0.0035 +
                                  (from tbl in _context.TMagnecubeCustomlevelsLikes
                                   where tbl.IsLike == true && intermediate.Id == tbl.FkLevelId && tbl.DateLastUpdated >= intermediate.lastYearValue
                                   select tbl).Count() * 0.0065
    });

关键注意点 #

• 必须判断numbPlays是否为0，不然会触发除以零的异常。
• 把numbCompleted转成double再做除法，避免整数除法导致结果失真（比如3除以5，整数除法会得到0，转成double后才能得到0.6）。

内容的提问来源于stack exchange，提问作者Alex Ibrahim Ojea