Get number of days by comparing two datetime? objects in Linq to Entity using C#

Question

Hi iam developing a console app with which I generate excel sheet with data from SQL Tables, i have a specific requirement where i have to include the number of days by comparing two datetime? property,here is what i have tried so far

 var Result = (from a in Db.tbl_ApplicantMaster
                              join b in Db.tbl_App_Process on a.APP_ID equals b.APID
                              join c in Db.tbl_Process on b.ProcessID equals c.ID
                              join d in Db.tbl_Nationality on a.Nationality equals d.country_code
                              join e in Db.tbl_AgencyMaster on a.Agn_ID equals e.AgID orderby a.APP_ID
                              select new ExcelData
                              {
                                  Name = a.Name,
                                  AppId = a.APP_ID,
                                  ProcessName = c.Process,

                                 // No_of_Days =,
                                  StartDate = b.StartTime.ToString(),
                                  EndDate = b.EndTime.ToString(),
                                  Nationality = d.country_enName,
                                  Agency = e.AgencyName,

                                  No_of_Days = b.StartTime !=null && b.EndTime !=null? (Convert.ToDateTime(b.StartTime)- Convert.ToDateTime(b.EndTime)).Days : 0.0
}).ToList();

but when i write this or any other code to get the number of days my application stops without giving me any error message, what am i doing wrong? any help is appreciated,Thank you so much.

When you were typing up your question, did this come up as a suggestion: https://stackoverflow.com/questions/1607336/calculate-difference-between-two-dates-number-of-days. Now that may not work in an EF query. In that case, you may need to fall back to: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/date-and-time-functions — Flydog57, Dec 20 '21 at 17:36

score 0 · Accepted Answer · edited Dec 20 '21 at 18:03

0

It looks like the app was not running because LINQ to entity doesn't support the Convert method. All I had to use was the DbFunctions class.

No_of_Days = b.StartTime !=null && b.EndTime !=null? DbFunctions.DiffDays(b.StartTime, b.EndTime)  : 0

This is working perfectly.

edited Dec 20 '21 at 18:03

Sebastian 506563

6,980
3
31
56

answered Dec 20 '21 at 17:45

Ashwin

109
2
9

Get number of days by comparing two datetime? objects in Linq to Entity using C#

1 Answers1