Linq query with many to many relations using entity framework

Question

I have two tables: tbA and tbB .Between them I have a relationship n to n, so a table tbAB was generated in the database. I am using an Entity Framework Database First, then when I mapped these tables , it does not generate a specific entity for tbAB. Thus , I'm not seeing how I can create a query relating the two tables if I can't call directly thetbAB. What I want to do in SQL would be as follows :

 SELECT * 
 FROM tbA
 INNER JOIN tbAB 
 ON tbAB.idA = tbA.idA
 INNER JOIN tbB
 ON tbB.idB = tbAB.idB

That's what I'm trying to do with Linq:

  var table = (from l in db.tbA
                                            join k in db.tbB on l.? equals k.?
                                            where ?.IDCONCESSAO == objectTbB.IDCONCESSAO
                                            select l).ToList();

The question is how can I do this in a Linq expression ?

Thanks in advance.

Please rename tables to generic table A and TableB, easier to read! especially for non italian(i think) speaking? And include generic Ids — Michal Ciechan
– Michal Ciechan, Commented Mar 9, 2015 at 20:33
EF will do it for you as long as you have navigation properties — Michal Ciechan
– Michal Ciechan, Commented Mar 9, 2015 at 21:18
Is tbAB a temporary table or view, or does it truly exist as a static table? — JNYRanger
– JNYRanger, Commented Mar 10, 2015 at 13:10

ocuenca · Accepted Answer · 2015-03-10 14:46:40Z

Following the model proposed by @Michal, you could do this:

var query= from a in db.TableAs
           from b in a.TableBs
           where b.Id==10
           select new{A_Id=a.Id,a.Name, B_Id=b.Id,b.Price,...};

In the select you can choose the properties you need from both entities(I also select a Name from TableA and a Price from TableBto help you understand better this example).From each direction of the relationship, you don’t ever interact with the junction table, you just follow a relationship from each direction as if it were a one-to-many. The query that I show above will be translated in a sql query where the joins between the tables will be made this way:

{SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Join1].[Id] AS [Id1], 
[Join1].[Price] AS [Price]
FROM  [dbo].[TableAs] AS [Extent1]
INNER JOIN  (SELECT [Extent2].[TableA_Id] AS [TableA_Id], [Extent3].[Id] AS [Id], [Extent3].[Price] AS [Price]
    FROM  [dbo].[TableBTableAs] AS [Extent2]
    INNER JOIN [dbo].[TableBs] AS [Extent3] ON [Extent3].[Id] = [Extent2].[TableB_Id] ) AS [Join1] ON [Extent1].[Id] = [Join1].[TableA_Id]
WHERE 10 = [Join1].[Id]}

Michal Ciechan · Accepted Answer · 2015-03-09 21:22:56Z

1

    public void Test()
    {
        var db = new DbContext();

        // This will automatically do you inner join for you.
        db.TableAs.Include(a => a.TableBs);
    }

Context:

    public class DbContext
    {
        public IDbSet<TableA> TableAs { get; set; }
        public IDbSet<TableB> TableBs { get; set; }
    }

Models:

    public class TableA
    {
        public int Id { get; set; }
        public virtual List<TableB> TableBs { get; set; }
    }
    public class TableB
    {
        public int Id { get; set; }

        public virtual List<TableA> TableAs { get; set; }
    }

answered Mar 9, 2015 at 21:22

Michal Ciechan

13.9k12 gold badges81 silver badges123 bronze badges

1 Comment

Jessica Over a year ago

Thx Michal, but how I do a restriction like this: SELECT * FROM tbA INNER JOIN tbAB ON tbAB.idA = tbA.idA INNER JOIN tbB ON tbB.idB = tbAB.idB where tbAB.idB = 10

Irfan TahirKheli · Accepted Answer · 2015-03-10 13:16:57Z

0

  var table = from a in db.tbA
              join ab in db.tbAB on a.idA  equals ab.idA
              join b in db.tbB on ab.idB  equals b.idB
              where a.Anything = 10
              select a;

  var results = table.ToList();

answered Mar 10, 2015 at 13:16

Irfan TahirKheli

3,6621 gold badge25 silver badges36 bronze badges

2 Comments

pseudocoder Over a year ago

There is no db.tbAB. EF detects n-n relationships (where there is a tie table containing only keys) and represents them as just relationships even though there is a table in SQL. If you add non-key fields then there will be an entity representing that table.

Irfan TahirKheli Over a year ago

Yeh but when you add these tables to designer . it will show the appropriate table name with db entity object. this code is just to show how to relate those 3 tables to each other. @pseudocoder

Collectives™ on Stack Overflow

Linq query with many to many relations using entity framework

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related