Linq SubQuery for Unrelated entity variable

Question

Is it possible to convert/duplicate the following with 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)
    )
)

Specifically the CASE SubQuery? I added a [NotMapped] CanEdit value to the Users entity class, however I'm unsure how to populate it with one sql query instead of doing the initial get first after which looping through and updating the CanEdit.

I've had a look through StackOverflow.

Any assistance would be appreciated.

EDIT: Looking at your answers I see how how to do it, but I realized I made a stuff up in the question. I orignally created the above query as an example to my issue, but I see now it doesn't directly relate to my issue. I have updated the SQL part...

The idea is that we might have many Ranks with the lowest rank being "Unset" Which would be level 0. So If I play a Game that another user plays and I my Rank set on Level 2, I can assign the Rank to this user to 1. But on a different Game I might also be "Unset" (level 0) and thus wouldn't be able to edit his/her rank.

@CodingYoshi: This is what I've attempted

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) };

Answer 1

Perhaps something like this:

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;}
}

UPDATE: First of all, no need to edit your question because other people answered your initial question and now their and my initial response make no sense. You should have started a new question or updated this one.

Here is the code for your edited question:

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
    });

Hope this helps

Answer 2

I modeled the database as classes to get syntax correct. Not sure is I got everything correct but it is close

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            int UserID = 1;
            int ViewerUserID = 2;

            DataBase db = new DataBase();

            var resutls = (from subU in db.subU
                           join subUt in db.subUT on subU.Id equals subUt.UserTypeID
                           select new { subU = subU, subUt = subUt })
                           .Where(x => ((x.subU.Id == ViewerUserID) && (x.subUt.Security > x.subU.Security)) || x.subU.Id == -1)
                           .ToList();
        }
    }
    public class DataBase
    {
        public List<Users> subU { get; set; }
        public List<UserTypes> subUT { get; set; }

    }
    public class Users
    {
        public int Id { get; set; }
        public int Security { get; set; }
    }
    public class UserTypes
    {
        public int UserTypeID { get; set; }
        public int Security { get; set; }
    }
}

Answer 3

A few problems:

1) What's with all the joins? You should have navigation properties set up properly in your model configurations.

2) Your resulting query's CanEdit property relies on the game rank level for a specified viewerUserId but it appears that specifying the viewerUserId is optional. What's the default behavior if no viewer user is specified or found? I'll assume CanEdit should be false.

public class YourResultClass()
{
    public string GameName { get; set; }
    public string UserName { get; set; }
    public string RankName { get; set; }
    public bool CanEdit { get; set; }
}

var targetUserGameRanksQuery = context.Users
    .Where(u => !u.IsDeleted 
        && u.UserId == userId)
    // flatten for use in subsequent join
    .SelectMany(u => u.UserGameRanks);

IQueryable<YourResultClass> query = null;

if(viewerUserId.HasValue)
{
    var viewerGameRanksQuery = context.Users
        .Where(u => !u.IsDeleted 
            && u.UserId == viewerUserId)
        // flatten for use in subsequent join
        .SelectMany(u => u.UserGameRanks);

    var joinQuery = targetUserGameRanksQuery // outer source `o`
        .Join(viewerGameRanksQuery, // inner source `i`
            o => o.GameId,
            i => i.GameId,
            (o, i) => new
            {
                GameName = o.Game.Name,
                TargetUserName = o.Username,
                TargetRankName = o.Rank.Name,
                CanEdit = i.Rank.Level > o.Rank.Level,
                Visibility = o.VisibilityId
            });

    query = joinQuery
        .Where(at =>
            at.Visibility == Visibility.RegisteredUsers

            || (at.Visibility == Visibility.Hidden
                && userId == viewerUserId.Value)

            || at.Visibility == Visibility.Public )
        .Select(at =>
            new YourResultClass()
            {
                GameName = at.GameName,
                UserName = at.UserName,
                RankName = at.RankName,
                CanEdit = at.CanEdit,
            });
}
else
{
    query = targetUserGameRanksQuery
        .Where(ugh => ugr.VisibilityId == Visibility.Public)
        .Select(ugh => new YourResultClass()
        {
            GameName = ugr.Game.Name,
            UserName = ugr.Username,
            RankName = ugr.Rank.Name,
            CanEdit = false,
        });
}