程式筆記

Linq to sql在下where條件時, 好像無法像直接下SQL一樣, 在Linq裡先轉成日期

cast([InvoiceDate] as date)) = '20230927'

因SQL欄位有時間, 在Linq裡下CompareTo可能會有時間差

Organization.ExpirationDate.Value.CompareTo(checkDateEnd)>0

替代做法

#lambda
.Where(x=>((x.InvoiceDate.Value.Year==yyyy) && 
        x.InvoiceDate.Value.Month==mm) &&
        x.InvoiceDate.Value.Day==dd)

#轉成SQL後
        AND ((CONVERT(NVarChar,DATEPART(Year, [InvoiceDate]))) = 2023) 
		AND ((CONVERT(NVarChar,DATEPART(Month, [InvoiceDate]))) = 9) 
		AND ((CONVERT(NVarChar,DATEPART(Day, [InvoiceDate]))) = 27)

#lambda 其他做法
                DateTime checkDateStart = DateTime.Parse($"{yyyy}/{mm}/{dd} 00:00:00.000");
                DateTime checkDateEnd = DateTime.Parse($"{yyyy}/{mm}/{dd} 23:59:59.998");
                
                       .Where(
                            x => (checkDate <= x.InvoiceDate.Value &&
                               checkDate >= x.InvoiceDate.Value)
                        )

EF做法: 

	var orders = db.Orders.Where(x =＞ 
					SqlFunctions.DatePart("year", x.OrderDate) == 2020 
				 ＆＆ SqlFunctions.DatePart("month", x.OrderDate) == 4
				 ).ToList();