将sql查询转换为linq

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.

我试图将以下mysql查询转换为linq。但是我没有过去使用linq中的连接的经验。我可以得到一些关于我如何处理这个的提示。

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

2 个解决方案

#1

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

LINQ中的内连接很简单,但左连接有点棘手,涉及临时LINQ变量。

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

显然,你有这里的MSDN文档:http://msdn.microsoft.com/en-us/library/bb397895.aspx

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

关于同一主题的另一篇SO帖子:LINQ中的LEFT OUTER JOIN

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

以及JOINS如何在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:

我将留下SELECT和WHERE块让你弄清楚,但这里大致是你的联接应该最终看起来如何:

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

这里最重要的部分是.DefaultIfEmpty()子句,因为如果WHERE不起作用,那么就会创建返回null的'左连接'行为

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

这也省略了group by子句,你可以通过谷歌搜索“LINQ Group By”找到一些好的资源。

#2

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
}

```

#1