convert a sql query to linq

Question

I am trying to convert the following mysql query to linq. However I do not have past experience working with joins in linq. Could I get some tips on how I can approach this.

SELECT db.jm_job.jobID
     , sum(MaterialPrice+LaborPrice+ExpensePrice) * db.jm_workorderdetail.quantity 
FROM db.jm_workorderdetail 
     left join db.jm_workorder 
         on db.jm_workorderdetail.WorkOrderID=db.jm_workorder.WorkOrderID
     left join db.jm_job 
         on db.jm_job.JobID=db.jm_workorder.JobID
group by db.jm_job.jobID

Your select list includes `* db.jm_workorderdetail.quantity` — Robert McKee, Oct 01 '14 at 20:44
Perhaps there is some esoteric syntax that I'm not aware of, but it appears that you are trying to give the output column of `sum(MaterialPrice+LaborPrice+ExpensePrice)` a name of `*`, and then I have no idea what it would do with `db.jm_workorderdetail.quantity`. Missing comma perhaps? — Robert McKee, Oct 01 '14 at 20:49
Yes i am multiplying the sum of those columns with the quantity column — user2433194, Oct 01 '14 at 20:49
It is selecting jobID and then next column is the sum of material price, labor price, expense price multiplied with the workorder.quantity column. — user2433194, Oct 01 '14 at 20:52
Please never just drop SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. Also, tell what type of LINQ you're targeting (to entities?), *and* show your own first efforts. — Gert Arnold, Oct 01 '14 at 20:53
do you see how much easier that is? please format a tiny bit... — DidIReallyWriteThat, Oct 01 '14 at 20:56

score 1 · Answer 1 · edited May 23 '17 at 12:13

Inner Joins in LINQ are simple enough, but a left join is a bit trickier, involving temporary LINQ variables.

Obviously, you have the MSDN documentation here: http://msdn.microsoft.com/en-us/library/bb397895.aspx

Another SO post on the same topic: LEFT OUTER JOIN in LINQ

And a bunch of examples of how JOINS work in LINQ: http://msdn.microsoft.com/en-us/vstudio/ee908647.aspx#leftouterjoin

I'll leave the SELECT and WHERE block for you to figure out, but here is roughly how your joins should end up looking:

var results = from wod in db.jm_workorderdetail
from wo in db.jm_workorder.Where(w => w.WorkOrderID == wod.WorkOrderID).DefaultIfEmpty()
from job in db.jm_job.Where(j => j.JobID == wo.JobID).DefaultIfEmpty()
select new {SELECT LOGIC GOES HERE}

The most important part here is the .DefaultIfEmpty() clause as that is what creates the 'left join' behavior of returning a null if the WHERE doesn't work

This also leaves out the group by clause, you can find some good resources on that by just googling 'LINQ Group By'

score 0 · Answer 2 · edited Oct 01 '14 at 21:29

0

SELECT db.jm_job.jobID, 
       sum( MaterialPrice + LaborPrice + ExpensePrice) * db.jm_workorderdetail.quantity 

  FROM db.jm_workorderdetail 

  LEFT JOIN db.jm_workorder 
    ON db.jm_workorderdetail.WorkOrderID = db.jm_workorder.WorkOrderID

  LEFT JOIN db.jm_job on db.jm_job.JobID=db.jm_workorder.JobID
  GROUP BY db.jm_job.jobID

Think it's something like this:

from detail in db.jm_workorderdetail

join wo in db.jm_workorder 
  on db.jm_workorderdetail.WorkOrderID equals db.jm_workorder.WorkOrderID

join job in db.jm_job 
  on db.jm_job.JobID equals db.jm_workorder.JobID

group jm_job by job.jobId into grp

select new 
{ 
    jobId = grp.key,
    something = (MaterialPrice + LaborPrice  + ExpensePrice) * wo.jm_workorderdetail.quantity
}

```

edited Oct 01 '14 at 21:29

Viper

2,216
1
21
41

answered Oct 01 '14 at 20:59

Robert McKee

21,305
1
43
57

This provides an INNER JOIN, not the left join that the asker requested. – guildsbounty Oct 01 '14 at 21:03
I suppose you are correct, but looking at the output, that is probably what he really wanted. I doubt you'd really want a null jobid with a total of all the unassigned workorderdetails. – Robert McKee Oct 01 '14 at 21:27

convert a sql query to linq

2 Answers2