I wasn't able to find anything on the Internet...

I have the query shown below which does a "Select new {...}". Inside the "Select new" there are two fields of interest which are "numbPlays" and "numbCompleted".

I'm trying to get in the "select new" an additional field called "lvlSuccessRate" which is basically a simple division of: "numbCompleted" / "numbPlays"

Is there a way I could reference these two fields instead of calculating them again? I haven't found the way to do it yet.

Thanks in advance :D

Added the full query as text as Narish suggested:

var query = from lvlData in _context.TMagnecubeCustomlevels
                            where (lvlData.DateDeleted == null)
                            select new {
                                Id = lvlData.Id,
                                isPrivate = lvlData.IsPrivate,
                                thisUserOwnsLvl = thisUserID == lvlData.FkUserCreatedBy ? true : false,
                                FkUserCreatedBy = lvlData.FkUserCreatedBy,
                                DateCreated = lvlData.DateCreated,
                                LvlTitle = lvlData.LvlTitle,
                                LvlDesc = 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(),
                                lvlSuccessRate = ((from tbl in _context.TMagnecubeCustomlevelsCompleteds
                                                  where (lvlData.Id == tbl.FkLevelId)
                                                  select tbl).Count() / (from tbl in _context.TMagnecubeCustomlevelsPlays
                                                  where (lvlData.Id == tbl.FkLevelId)
                                                  select tbl).Count()),
                                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
                            };

CodePudding user response：

Use the let keyword to create variables within your query, then reference those variables within your select.

from lvlData in _context.TMagnecubeCustomlevels
where (lvlData.DateDeleted == null)
let numbPlays = _context.TMagnecubeCustomlevelsPlays
    .Count(tbl => lvlData.Id == tbl.FkLevelId)
let numbCompleted = _context.TMagnecubeCustomlevelsCompleteds
    .Count(tbl => lvlData.Id == tbl.FkLevelId)
...
select
{
    ...
    numbPlays,
    numbCompleted,
    ...
    lvlSuccessRate = numbCompleted / numbPlays,
    ...
}