C# SQL To Linq - Where Clause with multiple variables Comparison (var1+var2) !=(var1+var2)

I have these working in sql for large data set working great. However I'm having hard time converting to linq-I'm new to linq

Select * from table1 t1, table2 t2 where (t1.RoleId+t1.UserId)!=(t2.RoleId+t2.UserId).

On a side note, when the two variables are separated, I get undesired results.

Meaning the following: where (t1.RoleId != t2.RoleId && t1.UserId != t2.UserId)

In c# I have two anonymous lists. The last linq statement works great till nulls come into the picture. Nothing returns. I even thought of using a left join with no success.

So how would you tackle the above query with anonymous type lists?

Linq statments I have so far

var roleUserList =
(
       from rls in roleResouceList
       join user in userResourceList
       on rls.FullResource.ToUpper() equals user.FullResource.ToUpper()
       orderby rls.RoleID, user.UserID, rls.Res1, rls.Res2, rls.Res3                   
       select new
       {
            RoleID = rls.RoleID,
            UserID = user.UserID,
            ServerId = rls.ServerID,
            FullResource = rls.FullResourc开发者_运维技巧e,
            RlsRes1 = rls.Res1,
            RlsRes2 = rls.Res2,
            RlsRes3 = rls.Res3
        }).Distinct().ToList();


 var missingRoleUserList =
  (
          from rls in rlsCount
          join usr in usrCount
          on rls.Res1 equals usr.Res1
          where rls.Total > usr.Total
          select new
                   {
                        UserID = usr.UsrID,
                        RoleID = rls.RoleID
                    }).Distinct().ToList();

      List<string> outputRoleUserList =
      (
               from rls in roleUserList
               from mis in missingRoleUserList
               where (rls.RoleID + rls.UserID) != (mis.RoleID +mis.UserID)
               select rls.UserID + ",\"" + rls.RoleID
      ).DefaultIfEmpty().Distinct().ToList();

I'm not entirely certain that this is what you're looking for, but I'm going to give it a shot:

Try chaining your where clauses in Linq to SQL, and you may get a better result:

List<string> outputRoleUserList = 
           from rls in roleUserList
           from mis in missingRoleUserList
           where rls.RoleID != mis.RoleID
           where rls.UserID != mis.UserID
           select rls.UserID + ",\"" + rls.RoleID

This will actually generate SQL as follows:

rls.RoleId != mis.UserID AND rls.UserId != mis.UserID

However, you have already forced execution on roleUserList and missingRoleUserList, so what you're using in the third Linq statement is not really Linq to SQL but rather Linq to Objects, if I'm reading this correctly.

I'd be curious to see some additional information or clarification and then maybe I'll understand better what's going on!

EDIT: I realized another possibility, it's possible that the object.UserID or object.RoleID is throwing an internal NullPointerException and failing out because one of those values came back null. You could possibly solve this with the following:

List<string> outputRoleUserLIst2=roleUserList
    .Where(x => x != null && x.UserID != null && x.RoleID != null && missingRoleUserList
        .Where(y => y != null && y.UserID != null && y.RoleID != null && y.RoleID!=x.RoleID && y.UserID!=x.UserID)
        .FirstOrDefault()!=null)
    .Select(x => x.UserID + ",\"" + x.RoleID).Distinct().ToList();

This is not pretty, and this is the other Linq syntax (with which I am more comfortable) but hopefully you understand what I am going for here. I'd be curious to know what would happen if you dropped this into your program (If I've guessed all of your meanings correctly!). I'll look back in a bit to see if you have added any information!