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
LINQ only supports equality joins, there's not a way to use a different operator in the join
itself.
As you point out, you can just use a where
statement for the same effect. If you didn't have an equality comparison to join on, you can use multiple from
clauses.
The
equals
operator
A join
clause performs an equijoin. In other words, you can only base matches on the equality of two keys. Other types of comparisons such as "greater than" or "not equals" are not supported. To make clear that all joins are equijoins, the join
clause uses the equals
keyword instead of the == operator. The equals
keyword can only be used in a join
clause and it differs from the == operator in one important way. With equals
, the left key consumes the outer source sequence, and the right key consumes the inner source. The outer source is only in scope on the left side of equals
and the inner source sequence is only in scope on the right side.
Non-Equijoins
You can perform non-equijoins, cross joins, and other custom join operations by using multiple from
clauses to introduce new sequences independently into a query. For more information, see How to: Perform Custom Join Operations (C# Programming Guide).