CODEWITHSUNDEEP
c#sqllinq
Detilium
Detilium

Reputation: 3034

Making 1 table join on 2 other tables with LINQ

I'm trying to join 1 table onto 2 other tables with LINQ, but I can't seem to figure out how this is done.

I can make it work writing pure SQL statements within Visual studio, I'm just not sure how to convert this into LINQ.

Here's my SQL statement:

SELECT c.CustomerId, c.CustomerName, pw.Number, pc.Number FROM Customers as c
LEFT JOIN Tasks as k ON k.Id = c.Task_Id
LEFT JOIN Workers as w ON w.Id = k.Worker_Id
LEFT JOIN PersonNumbers as pw ON pw.Person_Id = w.Id
LEFT JOIN Chiefs as ch ON ch.Id = k.Chief_Id
LEFT JOIN PersonNumbers as pc ON pc.Person_Id = ch.Id

Perhaps this requires a bit of explanation.

We have a bunch of Customers and these customers can have some tasks. Within a task you will have workers and chiefs. Within the PersonNumbers table, I have some extra information about workers and chiefs, and this is the information that I need.

Upvotes: 0

Views: 81

Answers (1)

juharr
juharr

Reputation: 32266

You should be able to do something like the following assuming all your joins are based on foreign keys that should result in navigation properties in your entities. The DefaultIfEmpty is what makes everything a left join.

var results = from c in db.Customers
              from k in c.Tasks.DefaultIfEmpty()
              from w in k.Workers.DefaultIfEmpty()
              from pw in w.Persons.DefaultIfEmpty()
              from ch in k.Chiefs.DefaultIfEmpty()
              from pc in ch.Persons.DefaultIfEmpty()
              select new
              {
                  c.CustomerId, 
                  c.CustomerName, 
                  pw.Number, 
                  pc.Number
              };

If you don't have the navigation properties then you'll have to use join.

var results = from c in db.Customers
              join xk in db.Tasks on xk.Id equals c.Task_Id 
              from k in xk.DefaultIfEmpty()
              join xw in db.Workers on xw.Id equals k.Worker_Id
              from w in xw.DefaultIfEmpty()
              join xpw in db.Persons on xpw.Person_Id equals w.Id 
              from pw in xpw.DefaultIfEmpty()
              join xch in db.Chiefs on xch.Id equals k.Chief_Id 
              from ch in xch.DefaultIfEmpty()
              join xpc in db.Persons on xpc.Person_Id euals ch.Id
              from pc in xpc.DefaultIfEmpty()
              select new
              {
                  c.CustomerId, 
                  c.CustomerName, 
                  pw.Number, 
                  pc.Number
              };

Upvotes: 1

Related Questions