How to write linq to get data from different tables?

Question

Suppose I have 3 tables: A(aid, ...), B(bid, ...) Relationship(aid, bid, ...) Then I have a aid as parameter, I want to get the result from b. If use SQL, will be like

select b.* from B join Relationship on b.bid = Relationship.bid
where relationship.aid = aid_param

how to write linq with same result as above SQL. (the foreign key was not created correctly in this case)?

Answer 1

var bQuery = from r in MyContext.Relationship
             where r.aid == aid_param
             select r.B;

If you don't have a relationship set in the model, then u can:

var bQuery = from b in myContext.B
             where myContext.Relationship
                .Any( r => r.aid == aid_param && b.bid == r.bid)
             select b;

Answer 2

Assuming that you've used the designer to add your tables to the LINQ data context and you have either foreign key relationships defined in the database or have hand-coded the associations in the designer, you should simply be able to reference the B table and it's EntitySet of Relationship properties, filtered by your parameter, and check if there are any matches.

var relationships = db.B.Any( b => b.Relationships.Where( r => r.aid == aid_param ) );

Answer 3

var output = from b
from Relationship
where (b.bid = Relationship.bid,
relationship.aid = aid_param)