How to convert this SQL into a Linq statement?

Question

I am developing a functionality that adds or removes roles to certain user(s). I have a problem converting this SQL code into LINQ. I already tested the SQL and it returns the correct results on SQL Server Management Studio.

Since I am a beginner with LINQ, I find it hard to convert my SQL statements.

/* This sql code works well - returns the users which DO NOT have the role yet in the given company. */
SELECT *
FROM SP.AppUser au 
left join
( 
  SELECT distinct auc.AppUserID
   FROM SP.AppUserCompany auc
   inner join SP.AppUserCompanyRole aucr ON aucr.AppUserCompanyID = auc.AppUserCompanyID 
   where auc.CompanyID = 2 and AppRoleID = 4
) AS exist ON exist.AppUserID = au.AppUserID
Where au.AppUserID IN(2088, 38, 1926, 2059, 2058, 1925, 2097, 2061, 2072, 2064) 
and exist.AppUserID is null

// Sample code in C# to represent the entities:
AppUser stanley = new AppUser() { AppUserID = 1, FirstName = "Stanley" };
AppUser jerson = new AppUser() { AppUserID = 2, FirstName = "Jerson" };
AppUser philip = new AppUser() { AppUserID = 3, FirstName = "Philip" };
AppUser samantha = new AppUser() { AppUserID = 4, FirstName = "Samantha" };

AppUserCompany auc1 = new AppUserCompany() { AppUserCompanyID = 1, CompanyID = 1, AppUser = stanley };
AppUserCompany auc2 = new AppUserCompany() { AppUserCompanyID = 2, CompanyID = 2, AppUser = stanley };
AppUserCompany auc3 = new AppUserCompany() { AppUserCompanyID = 3, CompanyID = 1, AppUser = jerson };
AppUserCompany auc4 = new AppUserCompany() { AppUserCompanyID = 4, CompanyID = 1, AppUser = philip };
AppUserCompany auc5 = new AppUserCompany() { AppUserCompanyID = 5, CompanyID = 2, AppUser = jerson };
AppUserCompany auc6 = new AppUserCompany() { AppUserCompanyID = 6, CompanyID = 3, AppUser = stanley };

AppUserCompanyRole aucr1 = new AppUserCompanyRole() { AppUserCompanyRoleID = 1, AppUserCompany = auc1, AppRoleID = 1 };
AppUserCompanyRole aucr2 = new AppUserCompanyRole() { AppUserCompanyRoleID = 2, AppUserCompany = auc2, AppRoleID = 1 };
AppUserCompanyRole aucr3 = new AppUserCompanyRole() { AppUserCompanyRoleID = 3, AppUserCompany = auc3, AppRoleID = 1 };
AppUserCompanyRole aucr4 = new AppUserCompanyRole() { AppUserCompanyRoleID = 4, AppUserCompany = auc4, AppRoleID = 1 };
AppUserCompanyRole aucr5 = new AppUserCompanyRole() { AppUserCompanyRoleID = 5, AppUserCompany = auc5, AppRoleID = 1 };
AppUserCompanyRole aucr6 = new AppUserCompanyRole() { AppUserCompanyRoleID = 6, AppUserCompany = auc6, AppRoleID = 1 };

List<AppUser> users = new List<AppUser> { stanley, jerson, philip, samantha };
List<AppUserCompany> appUserCompanies = new List<AppUserCompany> { auc1, auc2, auc3, auc4, auc5, auc6 };
List<AppUserCompanyRole> appUserCompanyRoles = new List<AppUserCompanyRole> { aucr1, aucr2, aucr3, aucr4, aucr5, aucr6 };

// Here is the LINQ code that I tried to get the users "who already have role" in a particular company
var ids = new List<int> { 2088, 38, 1926, 2059, 2058, 1925, 2097, 2061, 2072, 2064 };
var usersToDeleteRole = (from user in appUserDataBinding.Model
  where ids.Contains(user.AppUserID)
  join auc in appUserCompanyDataBinding.Model on user.AppUserID equals auc.AppUserID into groupJoin1
     from subAuc in
     (from auc in groupJoin1
       join aucr in appUserCompanyRoleDataBinding.Model on auc.AppUserCompanyID equals aucr.AppUserCompanyID
        where auc.CompanyID == 2 && aucr.AppRoleID == 4
     select auc)
orderby user.LastName
select user).ToList();

My expected result will be a LINQ statement that returns the list of users which who DO NOT have the role yet in a particular given company. The SQL statement I had above worked on SSMS but I cannot make it in LINQ in C# - it means that I need a "negated" version of my LINQ code above.

Thank you for the help. Cheers.

Answer 1

You can use let statement for you sub queries

public class AppUser
    {
        public int AppUserID { get; set; }
        public string FirstName { get; set; }
    }

    public class AppUserCompany
    {
        public int AppUserCompanyID { get; set; }
        public int CompanyID { get; set; }
        public AppUser AppUser { get; set; }
    }

    public class AppUserCompanyRole
    {
        public int AppUserCompanyRoleID { get; set; }
        public int AppRoleID { get; set; }
        public AppUserCompany AppUserCompany { get; set; }
    }


    class Program
    {
        static void Main(string[] args)
        {

            // Sample code in C# to represent the entities:
            AppUser stanley = new AppUser() { AppUserID = 1, FirstName = "Stanley" };
            AppUser jerson = new AppUser() { AppUserID = 2, FirstName = "Jerson" };
            AppUser philip = new AppUser() { AppUserID = 3, FirstName = "Philip" };
            AppUser samantha = new AppUser() { AppUserID = 4, FirstName = "Samantha" };

            AppUserCompany auc1 = new AppUserCompany() { AppUserCompanyID = 1, CompanyID = 1, AppUser = stanley };
            AppUserCompany auc2 = new AppUserCompany() { AppUserCompanyID = 2, CompanyID = 2, AppUser = stanley };
            AppUserCompany auc3 = new AppUserCompany() { AppUserCompanyID = 3, CompanyID = 1, AppUser = jerson };
            AppUserCompany auc4 = new AppUserCompany() { AppUserCompanyID = 4, CompanyID = 1, AppUser = philip };
            AppUserCompany auc5 = new AppUserCompany() { AppUserCompanyID = 5, CompanyID = 2, AppUser = jerson };
            AppUserCompany auc6 = new AppUserCompany() { AppUserCompanyID = 6, CompanyID = 3, AppUser = stanley };

            AppUserCompanyRole aucr1 = new AppUserCompanyRole() { AppUserCompanyRoleID = 1, AppUserCompany = auc1, AppRoleID = 1 };
            AppUserCompanyRole aucr2 = new AppUserCompanyRole() { AppUserCompanyRoleID = 2, AppUserCompany = auc2, AppRoleID = 1 };
            AppUserCompanyRole aucr3 = new AppUserCompanyRole() { AppUserCompanyRoleID = 3, AppUserCompany = auc3, AppRoleID = 1 };
            AppUserCompanyRole aucr4 = new AppUserCompanyRole() { AppUserCompanyRoleID = 4, AppUserCompany = auc4, AppRoleID = 1 };
            AppUserCompanyRole aucr5 = new AppUserCompanyRole() { AppUserCompanyRoleID = 5, AppUserCompany = auc5, AppRoleID = 1 };
            AppUserCompanyRole aucr6 = new AppUserCompanyRole() { AppUserCompanyRoleID = 6, AppUserCompany = auc6, AppRoleID = 1 };

            List<AppUser> users = new List<AppUser> { stanley, jerson, philip, samantha };
            List<AppUserCompany> appUserCompanies = new List<AppUserCompany> { auc1, auc2, auc3, auc4, auc5, auc6 };
            List<AppUserCompanyRole> appUserCompanyRoles = new List<AppUserCompanyRole> { aucr1, aucr2, aucr3, aucr4, aucr5, aucr6 };
            var ids = new List<int> { 2088, 38, 1926, 2059, 2058, 1925, 2097, 2061, 2072, 2064 };

            var result = from au in users
                         let appUserIDs = (from auc in appUserCompanies
                                           join aucr in appUserCompanyRoles on auc.AppUserCompanyID equals aucr.AppUserCompany.AppUserCompanyID
                                           where auc.CompanyID == 2 && aucr.AppRoleID == 4 && auc.AppUser.AppUserID == au.AppUserID
                                           select auc.AppUser.AppUserID).Distinct()
                         where  appUserIDs.Count() == 0  && ids.Contains(au.AppUserID)
                         select au;