in a linq join, using equal and not equal

entity-framework linq

Question

The following SQL query needs to be converted 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 was unable to combine the joins equal and not the same. The linq statement may be written as follows if Usr.RoleName ==== "Admin" was really User Role Name = "Admin".

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

Alternatively, I may handle it in the following way to get the same result:

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

But may this be handled in the join section of the linq?

I appreciate you.

1
7
5/17/2012 7:39:40 PM

Accepted Answer

Only equality joins are supported by LINQ; there is no possibility to use another operator in thejoin itself.

As you note, you may just use awhere a similar impact statement. You may use multiple if you didn't have an equality comparison to join on.from clauses.

Through MSDN:

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

11
5/17/2012 7:50:49 PM

Popular Answer

A non-equal join might be made, 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 ........

The join condition of the sql equivalent of the aforementioned linq will include a case statement.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow