CODEWITHSUNDEEP
c#linqentity-framework
Magpie
Magpie

Reputation: 7173

How to force Linq to join tables

I'm using EF 4.1 and a database I didn't design. I have two tables

TableA
[
  TableAKey,
  OtherField
]

TableB
[
  TableBKey,
  TableAKey, <-- Foreign Key
  OtherField
]

The problem I have is that the foreign key does not get nulled when the parent record is deleted. This is something I have no control over and can't change. When selecting from TableB I want to exclude all the rows with missing TableA records.

In SQL this is easy with a simple INNER JOIN. However the linq below doesn't join the tables. I guess it's optimising the SQL. The below code generates SQL that has no mention of TableA. Maybe because I don't return anything from it.

(from b in TableB
join a in TableA on b.TableAKey equals a.TableAKey
where b.OtherField == something
select b).ToList();

Is there any way to force a SQL join or a better way of doing this.

Cheers

Upvotes: 0

Views: 239

Answers (2)

Arthis
Arthis

Reputation: 2293

and this :

var qa = from a in TableA
         select a.id;
var q= from b in TableB
       where qa.Contains(b.TableAKey)

it should result in a :

Select * from MyTable where idA in (Select id from tableA)

Upvotes: 1

Yahia
Yahia

Reputation: 70369

the generated sql depends alot on your db provider... try

(from a in TableA
from b in TableB
where b.OtherField == something && a.TableAKey == b.TableAKey select b).ToList();

Upvotes: 0

Related Questions