Linq join where ?

Question

Hi. I got:

• entity1 - ID, OwnerCode
• entity2 - OwnerCode, DepartmentCode

Also I have some DepartmentCode

Now i want get something like this(sql syntax):

Select e1.ID 
from entity1 e1 
join entity2 e2 on e1.OwnerCode = e2.OwnerCode and e2.DepartmentCode=7

via Linq

I wrote:

var q = from e1 in entityes1 
        join e2 in entityes2 on e1.OwnerCode equals e2.OwnerCode ... 

But how insert DepartmentCode=7 here I don't understand.

Answer 1

That's not really a factor in joining the tables, it's a factor in selecting records, so it really should be a where clause:

var q = from e1 in entityes1
        join e2 in entityes2 on e1.OwnerCode equals e2.OwnerCode
        where e2.DepartmentCode == 7
        select e1.ID;

var id = q.Single(); 

Answer 2

After joining the tables you have to place where clause, and then select id that you need. You will get it by executing Single method against the IQueriable object. Example below:

var q = from e1 in entityes1 
        join e2 in entityes2 on e1.OwnerCode equals e2.OwnerCode
        where e2.DepartmentCode == 7
        select e1.ID;

var id = q.Single();

Answer 3

Didn't test but that should do:

var q = from e1 in entityes1 
        join e2 in entityes2 
           on e1.OwnerCode equals e2.OwnerCode
        where e2.DepartmentCode==7

Answer 4

I really don't like that construction, and there's probably another way to write the query you're going for.... but if you already have a working query and are merely trying to translate it, there's a way to do what you're asking.

var q =
  from e1 in entityes1  
  join e2 in entityes2 on
    new { OC = e1.OwnerCode, DC = 7 }
  equals
    new { OC = e2.OwnerCode, DC = e2.DepartmentCode }