# LINQ problem, joining 2 tables and an id

• ### Question

• Hi all,

I have a problem with a query.

The scenario is this: I got a table (I'll call it t1) with exercises and an id for equipment. I also got a table (I'll call it t2) for linking an equipment to an exercise. I want a list with every exercise that isn't linked with my id in t2 (edited).

Example: My id is 4

In t1 I have 10 exercises => 1 to 10

In t2 I have a column exerciseId and equipmentId

Exercise Equipment

--------- ------------

1 4

2 4

3 4

8 4

So 4,5,6,7,9 and 10 isn't associated with equipment 4 and I want to have those in my var query.

At the moment I got this:

var query = from t1 in db.exercise

where (from t2 in db.exerciseequipment

where t2.equipmentid == equipmentid

select t2.exerciseid).Contains(t1.exerciseid)

select t1;

This show 0 result, while t2 is empty.

• Edited by Wednesday, May 11, 2011 3:47 PM incomplete information
Wednesday, May 11, 2011 3:38 PM

• I found a solution, but it's probably not a good solution:

var oef = (from t1 in db.oefening

select t1).ToList();

var oeftoe = (from t1 in db.oefeningtoestel

where t1.toestelId == toestelId

select t1).ToList();

for (int teller = 0; teller < oef.Count(); teller++)

{

oefening oefening = oef[teller];

for (int teller2 = 0; teller2 < oeftoe.Count(); teller2++)

{

oefeningtoestel oefeningtoestel = oeftoe[teller2];

if (oefening.oefeningId == oefeningtoestel.oefeningId && oefeningtoestel.toestelId == toestelId)

{

oef.RemoveAt(teller);

}

}

}

Wednesday, May 11, 2011 4:11 PM

### All replies

• I found a solution, but it's probably not a good solution:

var oef = (from t1 in db.oefening

select t1).ToList();

var oeftoe = (from t1 in db.oefeningtoestel

where t1.toestelId == toestelId

select t1).ToList();

for (int teller = 0; teller < oef.Count(); teller++)

{

oefening oefening = oef[teller];

for (int teller2 = 0; teller2 < oeftoe.Count(); teller2++)

{

oefeningtoestel oefeningtoestel = oeftoe[teller2];

if (oefening.oefeningId == oefeningtoestel.oefeningId && oefeningtoestel.toestelId == toestelId)

{

oef.RemoveAt(teller);

}

}

}

Wednesday, May 11, 2011 4:11 PM
• Thank you Kenny for sharing your solution to us.

Good day,

Jackie Sun [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Tuesday, May 17, 2011 7:17 AM