Using peers and not peers in a linq join

Question

I am trying to convert the 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 could not find a way to use equal and not equal in the same connection. If Usr.RoleName <> 'Admin' was Usr.RoleName = 'Admin' , the linq operator could be written like this:

 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 process it where the linq part gets the same result as below.

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

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

Thanks in advance

Answer 1

LINQ only supports joining equalities, not using another operator in join .

As you point out, you can simply use the where clause for the same effect. If you did not have an equality mapping to join, you can use several from clauses.

From MSDN :

equals operator

Sentence

A join does equijoin. In other words, you can only use matches for the equality of two keys. Other types of comparisons, such as greater than or not equal, are not supported. To make it 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 differs from the == operator in one important way. With equals left key consumes the external source sequence, and the right key consumes the internal source. The external source is only in the area on the left side of the equals , and the internal source sequence is only in the area on the right side.

Non-equijoins

You can perform non-equijoins, cross joins, and other custom join operations using several from clauses to independently enter new sequences in the query. See the Practical Guide for more information . Performing custom merge operations (C # Programming Guide) .

Answer 2

An unequal connection can be achieved, for example,

 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 ........ 

In the SQL equivalent for the above, linq will have a case statement in the join condition.