I’m trying to implement DateDiff in LINQ. I would like to get all records with the same date and ignoring the timestamp.

Here is my code snippet which doesn’t work

 public int tblExpReceiptFileUniqueID(string chCreateStaffCode, string vcFileName,
                                                long intFileSize, DateTime? sdCreateDate)
        {
            var expReceiptFile = from ef in DataContext.tblExpReceiptFiles orderby ef.intFileID
                                 where ef.chCreateStaffCode == chCreateStaffCode
                                 && ef.vcFileName == vcFileName
                                 && ef.bintFileSize == intFileSize
                                 && ((DateTime)ef.sdCreateDate).Date == sdCreateDate.Value.Date
                                 select ef;

            tblExpReceiptFile expReceiptFileRec = expReceiptFile.First();

            return expReceiptFileRec.intFileID;
        }

I’m trying to cast the column to “DateTime” and use the “Date” property but I got this error
“The Member ‘System.DateTime.Date’ has no supported translation to SQL”

I thought of “DATEDIFF” function in SQL server and I’m trying to get my head around in implementing this using LINQ and I found that we can use a library called “SQLClient”

using System.Data.Linq.SqlClient;

Code Snippet which is working

    public int tblExpReceiptFileUniqueID(string chCreateStaffCode, string vcFileName,
                                                long intFileSize, DateTime? sdCreateDate)
        {
            var expReceiptFile = from ef in DataContext.tblExpReceiptFiles orderby ef.intFileID
                                 where ef.chCreateStaffCode == chCreateStaffCode
                                 && ef.vcFileName == vcFileName
                                 && ef.bintFileSize == intFileSize
                                 && SqlMethods.DateDiffDay(ef.sdCreateDate, sdCreateDate.Value.Date) == 0
                                 select ef;

            tblExpReceiptFile expReceiptFileRec = expReceiptFile.First();

            return expReceiptFileRec.intFileID;
        }