Convert SQL to LINQ

I'm having a hard time with this SQL Query. I need to convert this to LINQ but n o luck so far. Linqer simply fails.

SELECT dbo.County.Name AS 开发者_C百科County,
       dbo.JobBooking.OID AS BookingID,
       dbo.Job.OID AS JobID,
       CASE JobBooking.JobBookingStatusID WHEN 2 THEN 1 ELSE 0 END AS Booked
FROM   dbo.JobBooking RIGHT OUTER JOIN
       ((((dbo.Branch INNER JOIN
       dbo.Job ON dbo.Branch.OID = dbo.Job.BranchID) INNER JOIN
       dbo.AddressInfo ON dbo.Branch.AddressInfoID = dbo.AddressInfo.OID) 
       INNER JOIN
       dbo.City ON dbo.AddressInfo.CityID = dbo.City.OID) INNER JOIN
       dbo.County ON dbo.City.CountyID = dbo.County.OID)
       ON dbo.JobBooking.JobID = dbo.Job.OID

Any help will be greatly appreciated!

Regards.

---

Because you really wanted your answer in a badly formatted, stupidly long, VB.Net one liner...

Dim results = db.Branches.Join(db.Jobs,
                               Function(n) n.OID,
                               Function(n) n.BranchID,
                               Function(nBranch, nJob) New With {.Branch = nBranch,
                                                                 .Job = nJob}).Join(db.AddressInfos,
                                                                                    Function(n) n.Branch.AddressInfoID,
                                                                                    Function(n) n.OID,
                                                                                    Function(n, nAddressInfo) New With {.Branch = n.Branch,
                                                                                                                        .Job = n.Job,
                                                                                                                        .AddressInfo = nAddressInfo}).Join(db.Cities,
                                                                                                                                                                   Function(n) n.AddressInfo.CityID,
                                                                                                                                                                   Function(n) n.OID,
                                                                                                                                                                   Function(n, nCity) New With {.Branch = n.Branch,
                                                                                                                                                                                                .Job = n.Job,
                                                                                                                                                                                                .AddressInfo = n.AddressInfo,
                                                                                                                                                                                                .City = nCity}).Join(db.Counties,
                                                                                                                                                                                                                     Function(n) n.City.CountyID,
                                                                                                                                                                                                                     Function(n) n.OID,
                                                                                                                                                                                                                     Function(n, nCounty) New With {.Branch = n.Branch,
                                                                                                                                                                                                                                                    .Job = n.Job,
                                                                                                                                                                                                                                                    .AddressInfo = n.AddressInfo,
                                                                                                                                                                                                                                                    .City = n.City,
                                                                                                                                                                                                                                                    .County = nCounty}).GroupJoin(db.JobBookings,
                                                                                                                                                                                                                                                                                  Function(n) n.Job.OID,
                                                                                                                                                                                                                                                                                  Function(n) n.JobID,
                                                                                                                                                                                                                                                                                  Function(n, nJobBooking) New With {.County = n.County.Name,
                                                                                                                                                                                                                                                                                                                     .BookingID = nJobBooking.OID,
                                                                                                                                                                                                                                                                                                                     .JobID = n.Job.OID,
                                                                                                                                                                                                                                                                                                                     .Booked = nJobBooking.DefaultIfEmpty IsNot Nothing AndAlso nJobBooking.DefaultIfEmpty.JobBookingStatusID = 2})

In all honesty, I've no idea if that would work, but I think it should. Now for an answr that hopefully helps. I restructured your SQL to make it easier to translate to Linq, perhaps you could confirm it still works?

SELECT  dbo.County.Name AS County,
        dbo.JobBooking.OID AS BookingID,
        dbo.Job.OID AS JobID,
        CASE JobBooking.JobBookingStatusID WHEN 2 THEN 1 ELSE 0 END AS Booked
FROM    dbo.Branch INNER JOIN dbo.Job ON dbo.Branch.OID = dbo.Job.BranchID
        INNER JOIN dbo.AddressInfo ON dbo.Branch.AddressInfoID = dbo.AddressInfo.OID
        INNER JOIN dbo.City ON dbo.AddressInfo.CityID = dbo.City.OID
        INNER JOIN dbo.County ON dbo.City.CountyID = dbo.County.OID
        LEFT OUTER JOIN dbo.JobBooking  ON dbo.JobBooking.JobID = dbo.Job.OID

Assuming it does indeed still work, break down the query into smaller parts, step by step if needs be. I'm doing it all in lambda (because my primary language is VB and online translators aren't too clever at translating the ol From x In source... syntax.

var branchesAndJobs = db.Branches.Join(db.Jobs, n => n.OID, n => n.BranchID, (n, nJob) => new {Branch = n, Job = nJob})
var branchesJobsAndAddressInfos = branchesAndJobs.Join(db.AddressInfos, n => n.Branch.AddressInfoID, n => n.OID, (n, nAddressInfo) => new {Branch = n.Branch, Job = n.Job, AddressInfo = n.AddressInfo})
// etc through all the normal joins

Then the left outer join (originally a right outer) uses a GroupJoin and DefaultIfEmpty...

var results = allOtherData.GroupJoin(db.JobBookings, n => n.Job.OID, n => n.JobID, (n, nJobBooking) => new {County = n.County.Name, BookingID = nJobBooking.DefaultIfEmpty == null ? null : nJobBooking.DefaultIfEmpty.OID, etc = n.etc})

I'm afraid my syntax is likely to be wildly out, but hopefully close enough for you to work from? Good luck!!

---

LINQ doesn't support RIGHT OUTER JOINs. You would need to convert this to a left outer instead using the DefaultIfEmpty extension method.

In this case, rather than trying to configure a bunch of joins, either break the query into logical chunks, or change your thinking around to using the object graph instead. Object graphs by default use outer joins rather than inner joins so you don't need DefaultIfEmpty when using the object graph. See if the following helps at all:

Dim query = From branch In Branches
            From job In branch.Jobs
            From booking In job.JobBookings
            Select County = branch.AddressInfo.City.County.Name,
                   BookingID = booking.BookingID,
                   JobID = job.OID,
                   BookedFROM = (booking.JobBookingStatusID = 2)

This isn't exactly the same as I'm returning a boolean for BookedFROM rather than 0/1 integer, but suspect you are wanting a boolean here based on the alias.

Also, realize in your database model, you may get multiple rows because a city may belong to multiple counties which could throw off your expected results.