How do I filter results from an association table that is linked to only one other table?

Question

So I can't figure out how to properly construct my EF query to get the rows I want. I'm only working with two tables here.

The Entity Framework designer creates this edmx diagram for my db structure. The loop at the top represents the association table that I'm having trouble filtering.

The SQL 2005 db diagram looks like this. The GeoBoundaryAssociation table has a many-to-many relationship from both of it's only two columns back to the GeoBoundary table's primary key GeoID.

I can write the SQL query to get the rows I want rather easily. I only need to pass one variable to get my results.

select g.geoid, g.name
from geoboundaryassociation a
    inner join geoboundary g on g.geoid = a.geoidto
where a.geoidfrom = 29

When I try to write my EF query I can't seem to filter rows from either of the two columns in the Association table. Any help would be greatly appreciated.

As a short term fix I have added an ID column to the GeoBoundaryAssociation table in order to get EF to generate an Entity so I can filter it directly. But I'm sure there must be a better way to do this.

Answer 1

Try:

var query = context.GeoBoundaries
                   // Try either g.GeoBoundaries or g.GeoBoundary1
                   .Where(g => g.GeoBoundaries.Any(a => a.GeoID == 29);
                   .Select(g => new { g.GoeID, g.Name });