how sql query convert in Linq [closed]

Question

I am trying to convert an SQL query to Linq

The query is :

 SELECT Distinct(InvoiceID)
 FROM Invoices
 WHERE ((DATEDIFF(day, InvoiceDate, GETDATE()) > PaymentDays)  AND
 status= 'false') 

I have written the following code but not getting proper result....

var outstanding = db.Invoices.Where(t =>
     (t.InvoiceDate.Value.Subtract(DateTime.Today)).Days > t.PaymentDays.Value 
    && !(bool)t.Status)

In my opinion, DATEDIFF doesnt have proper equivalent in LINQ to SQL

Answer 1

I see a few problems with the code you've written.

• LinqToSql wont support DateTime.Subtract, for this sort of operation you'll need SqlMethods.DateDiffDay ^docs.
• The string "false" cannot be cast to a boolean, you need to either use bool.Parse or just compare to the string "false" as you did in your original.
• your original gets a distinct list of invoice id's. This is totally missing from your LINQ attempt. Append .Select(t => t.InvoiceID).Distinct() to your LINQ.