c# - 使用 DataAccess (TELERIK OPEN ACCESS ORM) 在 c# 中将 SQL 语句转换为 LINQ

Question

美好的一天!有人可以帮我如何将此 sql 语句转换为 linq 吗？我正在使用 Telerik 数据访问。

谢谢。

SELECT pay.Cutoff, 
       emp.Id, 
       emp.LastName, 
       job.Rate * 25 AS FixBIR,

       (SELECT COUNT(*) AS MonthsWorked 
       FROM payroll AS pay3
       WHERE YEAR(pay3.DateGenerated) = 2014
             AND pay3.EmployeeId = 1 
             AND pay3.Cutoff = 1
       ORDER BY MONTH(pay3.DateGenerated) ASC) * (job.Rate * 25)  
       AS MonthsWorked_FixBIR_TODATE

FROM employee AS emp 
INNER JOIN payroll AS pay 
ON emp.Id = pay.EmployeeId
INNER JOIN job 
ON emp.JobId = job.Id
WHERE pay.Cutoff = 1 
AND pay.PayrollMonth = 'August' 
AND Year(pay.DateGenerated) = 2014

Answer 1

我见过这个现有的 thread在这里 stackoverflow.com这导致LINQPad和 Linqer这是将 SQL 查询转换为 LINQ 语法的一种更快、更有效的方法。

但举个例子:

SQL:

select DeliveryNote.DeliveryNoteNumber, COUNT(Distinct(SalesOrderLine.ProductID)), Sum(DeliveryNoteLine.Quantity)
from
SalesOrder 
inner join SalesOrderLine on SalesOrderLine.SalesOrderID = SalesOrder.ID and SalesOrder.IsLatestRevision = 1
inner join DeliveryNoteLine on DeliveryNoteLine.SalesOrderLineRootID = SalesOrderLine.RootID
inner join DeliveryNote on DeliveryNote.ID = DeliveryNoteLine.DeliveryNoteID and DeliveryNote.IsLatestRevision = 1
group by
DeliveryNoteNumber
order by
DeliveryNoteNumber

LINQ:

var query =
from so in SalesOrders 
join sol in SalesOrderLines on so.ID equals sol.SalesOrderID
join dnl in DeliveryNoteLines on sol.RootID equals dnl.SalesOrderLineRootID
join dn in DeliveryNotes on dnl.DeliveryNoteID equals dn.ID
where so.IsLatestRevision == 1 && dn.IsLatestRevision == 1
group new {
dn.DeliveryNoteNumber
, dnl.Quantity } by dn.DeliveryNoteNumber into g
orderby g.Key
select new
{
    DeliveryNoteNumber = g.Key,
    ProductCount = (
        from sol1 in SalesOrderLines 
        join dnl1 in DeliveryNoteLines on sol1.RootID equals dnl1.SalesOrderLineRootID
        where dnl1.DeliveryNote.DeliveryNoteNumber == g.Key
        select sol1.ProductID
    ).Distinct().Count()
    ,   
    QuantitySum = g.Sum( x => x.Quantity )
};