Replace the SQL query with the LINQ query-CodePudding

I have the SQL query which checks today's day to be checked against the field in the table that stores 3 letter char like below

If today is Tuesday I need to return the record. I have the SQL query like

  SELECT TOP 1 [EndTime],[StartTime],[OrderDay]
         FROM[dbo].[Settings]
         where SUBSTRING(DATENAME(weekday, getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'), 0, 4) = OrderDay

Since the table is in the Azure SQL and my application run ins Eastern Time Zone I am doing like SUBSTRING(DATENAME(weekday, getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'), 0, 4) now I am using the EF core so I want to change the above query to the LINQ query like below

  settings_data = from s in _context.Settings
                  where SUBSTRING(DATENAME(weekday, getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'), 0, 4) = s.OrderDay
                  select s;

It says SUBSTRING() does not exist in the current context. Any help is greatly appreciated

CodePudding user response：

You can't do that in LinQ as you can't use integrated functions in select or where.

You have to do something like:

string myCondition = DateTime.UtcNow.DayOfWeek.ToString().Substring(0,3);

  settings_data = from s in _context.Settings
                  where  s.OrderDay = myCondition 
                  select s;

You can use DateTime.Now or DateTime.UtcNow for UTC real time. DayOfWeek will get you the name of the day, and then for Substring: 0 is the start and 3 is the lenght.

Update:

To get Eastern Standard Time you can use:

var timeUtc = DateTime.UtcNow;
TimeZoneInfo easternZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
DateTime easternTime = TimeZoneInfo.ConvertTimeFromUtc(timeUtc, easternZone);

and then:

string myCondition = easternTime.DayOfWeek.ToString().Substring(0,3);

Test it: https://dotnetfiddle.net/YFwnZo