using equal and not equal in a linq join

Question

I am trying to convert following SQL query to linq;

select Or.Amount, Usr.Name, Usr.Email
from [order] as Or
left join vw_AllUsers as Usr on Usr.UserId = Or.UserId and Usr.RoleName <> 'Admin'

I couldn't find a way to use equal and not equal in the same join.. If Usr.RoleName <> 'Admin' was Usr.RoleName = 'Admin', the linq statement could be written like that

var result =  from Or in context.orders
              join Usr in context.vw_AllUsers on 
              new { userid = Or.UserId, role = "Admin"}
              equals
              new { userid = Usr.UserId, role = Usr.RoleName}
              select ........

or i can handle it in where part of the linq to get same result, as follows

where !Usr.RoleName.Equals("Admin")

but is it possible to handle this in join part of the linq ?

Thanks in advance

Answer 1

Not equal join could be achieved like,

var result =  from Or in context.orders
          join Usr in context.vw_AllUsers on 
          new { userid = Or.UserId, IsNotAnAdmin = true}
          equals
          new { userid = Usr.UserId, IsNotAnAdmin = (Usr.RoleName != "Admin") }
          select ........

The sql equivalent for the above linq will have a case statement in the join condition.