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

本文关键字：转换 语句 SQL LINQ 中的 DataAccess TELERIK OPEN ORM ACCESS 使用 | 更新日期: 2023-09-27 18:27:53

您好！有人能帮我把这个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

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

我在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 )
};

答案如下：）

EntitiesModel model = new EntitiesModel();
var rolls = from pay in model.Payrolls
            where pay.Cutoff == 1 && pay.PayrollMonth == "August" && pay.DateGenerated.year == 2014
            select new
            {
                 cutoff= pay.Cutoff,
                 lname = pay.Employee.LastName,    
                 fixBir= pay.Employee.Job.Rate * 25,
                 MonthsWorked_FixBIR_TODATE = (from pay2 in model.Payrolls
                              where pay2.DateGenerated.Year == int.Parse(yearField.Text)
                              && pay2.EmployeeId == pay.EmployeeId && pay2.Cutoff == 1
                              select pay2).Count() * (pay.Employee.Job.Rate * 25)
            };

以下是使用结果查询的代码。

foreach (var r in rolls)
{
    Debug.WriteLine("Cutoff: "+r.cutoff);
    Debug.WriteLine("Lastname: "+r.lname);
    Debug.WriteLine("Fix BIR rate: "+r.fixBir);
    Debug.WriteLine("Fix BIR to date: "+r.MonthsWorked_FixBIR_TODATE );
}
相关文章：