how can do the join in M:N relationship with linq fluent?

Question

In my database three tables:

• TableA(IDTableA,...)
• TableAB(IDTableA, IDTableB,...)
• TableB(IDTableB,...)

In my entity model I have only two entities:

TableA
{
    long IDTableA;
    ICollection<TableB> TableB;
}


TableB
{
    long IDTableB;
    ICollection<TableA> TableA;
}

I would like to know hou can do the join between the both tables and a condition in the where, something like this:

select TableA.*
from TableA, TableB, TableAB
where TableA.IDTableA = TableAB-IDTableAB
and TableB.IDTableB = TableAB.TableB
and Table.MyField > 10

I know know how can do it with query syntax, but I would like to know the wat to do it with linq fluent.

Thanks.

Answer 1

Answer hint from: How to join with an or clause in Fluent LINQ

Fluent syntax

var qJoin = context.User.SelectMany(
    u => context.Hierarchy
         .Where(h => u.OrganisationId == h.OrganisationId)
         ,(u, h) => new { u, h });

Query syntax

var qJoin = from u in context.User
         from h in context.Hierarchy
         where u.OrganisationId == h.OrganisationId
         select new { u, h };