如何在Linq中转换SQL查询

I am trying to convert an SQL query to Linq

我正在尝试将SQL查询转换为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

在我看来,DATEDIFF在LINQ to SQL中没有适当的等价物

2 个解决方案

#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.

LinqToSql不支持DateTime.Subtract,对于这种操作,您需要SqlMethods.DateDiffDay文档。

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.

字符串“false”不能转换为布尔值,您需要使用bool.Parse或者只是比较原始字符串“false”。

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.

您的原件会获得一份明确的发票ID列表。您的LINQ尝试完全没有这个。追加。选择(t => t.InvoiceID).Distinct()到您的LINQ。

#2

DATEDIFF(day, InvoiceDate, GETDATE()) > PaymentDays

should translate to

应翻译成

DateTime.Today.AddDays(-1*PaymentDays) > InvoiceDate

UPDATE

this should fetch what you need

这应该取得你需要的东西

var outstanding = db.Invoices.Where(t => t.InvoiceDate.Value.AddDays(t.PaymentDays.Value) > DateTime.Today).Where(u => u.Status.Equals("false")).Select(v => v.InvoiceID).Distinct();

#1