How to join 3 tables with linq

Question

I am trying to join 3 tables in a query with Linq to get data from all 3 tables. Below is an image of the table schemes:

The query should select: SewagePlantName, CompanyName and Duty

In addition I need to restricts the SewagePlantId to a list of Ids that are given as:

            var sewagePlantIds = UnitOfWork.GetAll<UserGroup>()
            .Where(group => group.Id == webAppPrincipal.GroupId)
            .SelectMany(group => group.SewagePlantId).Select(sewageplant => sewageplant.Id).ToList();

I have difficulties with the order of joining the 3 tables and where/how to restrict the SewagePlantId to the given list.

That's a bad design. Use *relations* between the *entities* and let the ORM do whatever joins are needed. `SewagePlant` should have a `Company` property. `Company` should have a `Duties` collection. Load a single `Company` and you get access to all related objects — Panagiotis Kanavos, Jan 30 '17 at 11:17
But in this case SewagePlant has n companies and not the other way around. — Manu, Jan 30 '17 at 12:29
So make it a collection as well. Just don't *abuse* your ORM. Create the proper relations, *don't* try to join as if you were using SQL. — Panagiotis Kanavos, Jan 30 '17 at 12:33

score 24 · Accepted Answer · edited Mar 10 '20 at 08:24

24

Can you try something similar to it please for joining part

from d in Duty
join c in Company on d.CompanyId equals c.id
join s in SewagePlant on c.SewagePlantId equals s.id
select new
  {
      duty = s.Duty.Duty, 
      CatId = s.Company.CompanyName,
      SewagePlantName=s.SewagePlant.SewagePlantName
      // other assignments
  };

edited Mar 10 '20 at 08:24

Moch Yusup

1,266
14
14

answered Jan 30 '17 at 11:10

Bits_Please

546
1
5
13

7

At the second join I get an err: -> The type of one of the expressions in the join clause is incorrect. Typ inference failed in the call to 'Join'. – Manu Jan 30 '17 at 12:07
1

I could only get this to work using the where like https://stackoverflow.com/a/806883/37055 – Chris Marisic May 16 '22 at 19:00

score 5 · Answer 2 · edited Jun 21 '17 at 03:12

var obj = from trns in context.tblPartyRegistrations
          join st in context.tblSellingTrans
          on trns.PartyRegId equals st.Fk_PartyRegId
          join pt in context.tblPartyRemainings
          on trns.PartyRegId equals pt.fk_PartyId
          select new
          {
              trns.Name,
              trns.PhoneNo,
              trns.Address,
              st.RecivedAmount,
              st.Date,
              st.CustomerType,
              st.MilkRate,
              st.Mltr,
              st.Mkg,
              st.NtAmnt,
              st.RemaningAmount,
              pt.Remainingammount
          };

How to join 3 tables with linq

2 Answers2

Linked

Related