CODEWITHSUNDEEP
c#.netentity-frameworkef-code-first
getit
getit

Reputation: 621

How do I query a join table in EF Code First?

I have a many to many relationship between tables A and B.

Since I used code first, the DB table 'AB' was automatically created. I don't have an entity model for it.

How can I, for example, query all the B's that belong to A.id=x?

Edit:

After I load either A or B, I can easily get references to lists B and A respectively.

My real problem is making a single query that excludes all B's that are already associated with A.

This is what I want to do:

query.Where(**b.ID NOT IN (SELECT B.ID FROM AB WHERE A=5)** )

I'm sure I could do this with a raw sql query, but I want to be consistent and use IQueryable/LINQ where I can.

Upvotes: 1

Views: 605

Answers (2)

Slauma
Slauma

Reputation: 177153

You can try this:

var bsNotAssociatedWithA5 = context.Bs
    .Where(b => !b.As.Any(a => a.Id == 5))
    .ToList();

It creates the following SQL:

SELECT 
[Extent1].[BId] AS [BId], 
[Extent1].[BColumn1] AS [BColumn1], 
// more columns
FROM [dbo].[Bs] AS [Extent1]
WHERE NOT EXISTS
    (SELECT 1 AS [C1]
     FROM [dbo].[ABs] AS [Extent2]
     WHERE ([Extent1].[BId] = [Extent2].[BId]) AND (5 = [Extent2].[AId]))

Edit

When using DbContext (EF >= 4.1) you can inspect the SQL by using ToString() of the IQueryable:

var bsNotAssociatedWithA5Query = context.Bs
    .Where(b => !b.As.Any(a => a.Id == 5));

string sql = bsNotAssociatedWithA5Query.ToString();

var bsNotAssociatedWithA5 = bsNotAssociatedWithA5Query.ToList();

Upvotes: 2

Peter Kiss
Peter Kiss

Reputation: 9329

A.Bs

If you have an instance of A you should have a property in the A class to retrive all the B-s from A.

Upvotes: 1

Related Questions