none
Convert to LINQ RRS feed

  • Question

  • I cant seem to get this SQL statement converted to LINQ. Of course that use of aggregated functions will prevent a successful conversion, so what will be a LINQ alternative to something similar?
    Hassan Gulzar
    Wednesday, December 15, 2010 3:21 PM

All replies

  • Hi Hassan,

    Could you add your SQL statement into the text please?

    Thanks,

    John.

    Wednesday, December 15, 2010 3:33 PM
  • Hi Hassan,

    Could you add your SQL statement into the text please?

    Thanks,

    John.


    Duh!

    SELECT 
     dbo.Job.BatchID,
     COUNT(dbo.Job.OID) AS Total,
     COUNT(CASE WHEN JobStatusID = 1 THEN 1
        END) AS [ReadyToBook],
     COUNT(CASE WHEN JobStatusID = 2 THEN 1
        END) AS Pending,
     COUNT(CASE WHEN JobStatusID = 3 THEN 1
        END) AS Booked,
     COUNT(CASE WHEN JobStatusID = 4 THEN 1
        END) AS Cancelled,
     COUNT(CASE WHEN JobStatusID = 6 THEN 1
        END) AS [CallBack],
     CAST(FLOOR(CAST(dbo.JobBatch.BatchDate AS FLOAT)) AS DATETIME) AS [BatchDate]
    FROM
     dbo.Job
     INNER JOIN dbo.JobBatch ON dbo.Job.BatchID = dbo.JobBatch.OID
     INNER JOIN dbo.____ ON dbo.Job.PharmacyID = dbo.____.OID
     INNER JOIN dbo.Contact ON dbo.____.ContactID = dbo.Contact.OID
     INNER JOIN dbo.Address ON dbo.Contact.OID = dbo.Address.ContactID
     INNER JOIN dbo.City ON dbo.Address.CityID = dbo.City.OID
     INNER JOIN dbo.County ON dbo.City.CountyID = dbo.County.OID 
     AND Job.UserID = 16
     GROUP BY Job.BatchID, CAST(FLOOR(CAST(dbo.JobBatch.BatchDate AS FLOAT)) AS DATETIME) 
     HAVING 1 = 1 
     ORDER BY CAST(FLOOR(CAST(dbo.JobBatch.BatchDate AS FLOAT)) AS DATETIME) DESC, Total DESC 
    


    Hassan Gulzar
    Wednesday, December 15, 2010 3:46 PM
  • Hello Hassan,

    Thank you for your feedback.

    How about you trying a tool called “Linqer”,  It can transfer SQL query to LinQ query and the opposite way. Please click here to download it and it can help your problem.

    I hope this will help resolve your problem. If anything is unclear, please free feel to let us know.

     

    Thank you for your understanding and support.

    I look forward to hearing from you.

     

    Best Regards,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 23, 2010 2:39 AM
    Moderator