时间戳

首页 > 解决方案 > 不相关实体变量的 Linq 子查询

c# - 不相关实体变量的 Linq 子查询

问题描述

是否可以使用 Linq 转换/复制以下内容？

DECLARE @UserID INT = 1, @ViewerUserID INT = 1002;

SELECT UGR.*,
CASE
WHEN (
    SELECT subR.[Level]
    FROM UserGameRanks subUGR WITH (NOLOCK)
    INNER JOIN Ranks subR WITH (NOLOCK) ON (subUGR.RankId = subR.Id)
    INNER JOIN Games subG WITH (NOLOCK) ON (subUGR.GameId = subG.Id)
    INNER JOIN Users subU WITH (NOLOCK) ON (subUGR.UserID = subU.Id)
    WHERE subUGR.IsDeleted = 0 AND subU.Id = @ViewerUserID AND subUGR.GameID = UGR.GameId
) > R.[Level] THEN 1
ELSE 0
END AS CanEdit
FROM UserGameRanks UGR WITH (NOLOCK)
INNER JOIN Ranks R WITH (NOLOCK) ON (UGR.RankId = R.Id)
INNER JOIN Games G WITH (NOLOCK) ON (UGR.GameId = G.Id)
INNER JOIN Users U WITH (NOLOCK) ON (UGR.UserID = U.Id)
WHERE UGR.IsDeleted = 0 AND U.Id = @UserID
AND
(
    (
        (@ViewerUserID <> - 1)
        AND
        (UGR.VisibilityId = 1)
    )
    OR
    (
        (UGR.VisibilityId = 2)
    )
    OR
    (
        (UGR.VisibilityId = 0)
        AND
        (@UserID = @ViewerUserID)
    )
)

特别是 CASE 子查询？我向用户实体类添加了一个 [NotMapped] CanEdit 值，但是我不确定如何使用一个 sql 查询填充它，而不是先执行初始获取，然后循环并更新 CanEdit。

我看过 StackOverflow。

任何援助将不胜感激。

编辑：看着你的答案，我知道该怎么做，但我意识到我在问题中编造了一个东西。我最初创建了上述查询作为我的问题的示例，但我现在看到它与我的问题没有直接关系。我已经更新了 SQL 部分...

这个想法是我们可能有许多排名，最低排名是“未设置”，这将是 0 级。所以如果我玩另一个用户玩的游戏并且我的排名设置为 2 级，我可以将排名分配给这个用户到 1。但在不同的游戏中，我也可能是“未设置”（0 级），因此无法编辑他/她的排名。

@CodingYoshi：这就是我尝试过的

var viewerQuery = from ugr in context.UserGameRanks
                               join r in context.Ranks on ugr.RankId equals r.Id
                               join g in context.Games on ugr.GameId equals g.Id
                               join u in context.Users on ugr.UserId equals u.Id
                               where (!ugr.IsDeleted) && (ugr.UserId == viewerUserId)
                               select new { UserGameRank = ugr };

            var query = from ugr in context.UserGameRanks
                        join r in context.Ranks on ugr.RankId equals r.Id
                        join g in context.Games on ugr.GameId equals g.Id
                        join u in context.Users on ugr.UserId equals u.Id
                        where (!ugr.IsDeleted) && (ugr.UserId == userId) &&
                        (
                            (viewerUserId != -1 && ugr.VisibilityId == Visibility.RegisteredUsers)
                            ||
                            (ugr.VisibilityId == Visibility.Public)
                            ||
                            (ugr.VisibilityId == Visibility.Hidden && userId == viewerUserId)
                        )
                        select new { GameName = g.Name, Username = ugr.Username, RankName = r.Name, CanEdit = (viewerQuery.Rank.Level > r.Level ? 1 : 0) };

标签： c#sqllinqsubquerysql-to-linq-conversion

解决方案

也许是这样的： 

var users = new List<User>
{
    new User { Id = 1, UserName = "User1", UserTypeId = 1 }, 
    new User { Id = 2, UserName = "User2", UserTypeId = 2 }, 
    new User { Id = 3, UserName = "User3", UserTypeId = 2 }, 
};

var userTypes = new List<UserType>
{
    new UserType { Id = 1, Type = "Admin", Security = 1 }, 
    new UserType { Id = 2, Type = "User", Security = 2  }
};

var userId = 1;

var ViewerUserID = 2;
var viewerSecurity = 
    (from u in users
    join ut in userTypes on u.UserTypeId equals ut.Id
    where u.Id == ViewerUserID
    select ut.Security).FirstOrDefault();

var res = 
    (from u in users
    join ut in userTypes on u.UserTypeId equals ut.Id
    where u.Id == userId || u.Id == -1
    select new
    {
        Id = u.Id,
        UserName = u.UserName,
        CanEdit = viewerSecurity > ut.Security ? 1 : 0
    });
}

class User {
    public int Id {get; set;}
    public string UserName {get; set;}
    public int UserTypeId {get; set;}
}

class UserType {
    public int Id {get; set;}
    public string Type {get; set;}
    public int Security {get; set;}
}

更新： 首先，无需编辑您的问题，因为其他人回答了您的初始问题，现在他们和我的初始回答毫无意义。您应该已经开始了一个新问题或更新了这个问题。

这是您编辑的问题的代码：

var res = 
    (from ugr in userGameRanks
    join r in ranks on ugr.RankId equals r.Id
    join g in games on ugr.GameId equals g.Id
    join u in users on ugr.UserId equals u.Id
    where !ugr.IsDeleted    // assuming IsDeleted is of type BIT in your DB
        && u.Id == userId
        && (
                (ViewerUserID != -1 && ugr.VisibilityId == 1)
                || ugr.VisibilityId == 2
                || (ugr.VisibilityId == 0 && userId == ViewerUserID)
            )
    let level =
        (from subUGR in userGameRanks
        join subR in ranks on subUGR.RankId equals subR.Id
        join subG in games on subUGR.GameId equals subG.Id
        join subU in users on subUGR.UserId equals subU.Id
        where !subUGR.IsDeleted 
            && subU.Id == ViewerUserID
            && subUGR.GameId == ugr.GameId
        select subR.Level).FirstOrDefault()

    select new 
    {
        ugr.RankId, 
        ugr.UserId,
        ugr.GameId,
        ugr.IsDeleted,
        ugr.VisibilityId,
        CanEdit = level > r.Level ? 1 : 0
    });

希望这可以帮助

推荐阅读