1

Good day! can someone help me how to convert this sql statement to linq? I'm using Telerik Data Access.

Thank you.

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
jedion
  • 624
  • 1
  • 7
  • 18

2 Answers2

6

I've seen this existing thread here in stackoverflow.com which is leading to LINQPad and Linqer which is a quicker and efficient way to convert SQL queries to LINQ syntax.

But as an example:

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 )
};
Community
  • 1
  • 1
Jenny Casarino
  • 115
  • 1
  • 4
4

Here is the answer :)

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

Here is the code for using the result query.

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 );
}
jedion
  • 624
  • 1
  • 7
  • 18