LINQ table join with entity Framework

Question

In my database I have ... TableA, TableB and TableC

TableB has just 2 columns, the primary key of TableA and TableC, so it really defines a one to many relationship between the two tables

What I want to do using SQL is:

SELECT * 
FROM TablesA a
JOIN TablesB b ON a.AID = b.AID
WHERE b.BID = 1

In the Entity Framework it doesn't create a TablesB for me to do the join, I think because TableB only has foreign keys!

So how can I do it?

http://stackoverflow.com/questions/21051612/entity-framework-join-3-tables — Erçin Dedeoğlu, Feb 11 '17 at 05:59

score 8 · Answer 1 · answered Sep 21 '09 at 06:06

8

Doing a join with LINQ is fairly straightforward

from a in TablesA
join b in TablesB
on a.AID equals b.AID
into joined
where b.BID == 1
select joined

I think the real question may be - why don't you have an entity class for TablesB? We may need more information to answer that.

answered Sep 21 '09 at 06:06

Rex M

142,167
33
283
313

I think it's because TableB is made up of foreign keys so therefor it doesn't get shown! – Sep 21 '09 at 06:16

score 3 · Answer 2 · answered Sep 21 '09 at 06:51

3

When you import the tables from database, entity framework gets rid of the TableB table and shows TableA and TableC to have many to many relationships. TableA has a navigation property TableCs and vice versa. So all you need to use these navigation properties, for sample:

var tableARow= db.TableA.First(s => s.Id == 1); 
if (!tableARow.TableCs.IsLoaded) { tableARow.TableCs.Load(); }

or

var tableARow= db.TableA.Include("TableCs").First(s => s.Id == 1);

answered Sep 21 '09 at 06:51

Puzzled

378
2
10

Thanks Puzzled, that looks more like it. Just one thing. With the s.Id == 1, it's actually the ID of tableC that I need to set to 1, not TableA. How would I do that? So it's a TableA entity i want to return, but i want to fill TableC having TableC.ID = 1 .... making sense? – Sep 21 '09 at 07:12
A list of rows from table A which have rows from table C with Id = 1: var tablearows = db.TableC.Include("TableAs").Where(c => c.Cid == 1).Select(c => c.TableAs).ToList(); – Puzzled Sep 21 '09 at 08:30

LINQ table join with entity Framework

2 Answers2