Replace the SQL query with the LINQ query

Question

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

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K
– Dale K, Commented Nov 15, 2021 at 0:00
@DaleK Sure Thanks !I just wanted to show the value in the DB how it looks — user4912134
– user4912134, Commented Nov 15, 2021 at 0:01
Just show as tabular data, and for the definition show the DDL. — Dale K
– Dale K, Commented Nov 15, 2021 at 0:11

Leandro Bardelli · Accepted Answer · 2021-11-15 01:41:37Z

1

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

edited Nov 15, 2021 at 1:41

answered Nov 15, 2021 at 1:36

Leandro Bardelli

11.7k16 gold badges88 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user4912134 Over a year ago

Thank you !!! Is there a way to make the field s.OrderDay to uppercase in the where clause

Leandro Bardelli Over a year ago

@user4912134 if you really don't care about the uppercase you can use StringComparer.OrdinalIgnoreCase or you can use Equals and try to get that. In my knowledge is better (if you can) make a view or a store procedure into the database and get the data as you need it and then use the linQ over the view or store procedure. Because you can't use ToUpper neither :S If this answer helps you please dont forget to upvote or mark it as answer, or if you need more help ask me here and I will glad to answer you :)

user4912134 Over a year ago

Thank you !! I am running in to another issue which is not associated with it, but can you please tell me if you can help me with it stackoverflow.com/questions/69969014/…

Leandro Bardelli Over a year ago

@user4912134 no problem. Sorry Ive to sleep very late at night yesterday. Let me see if I know, what is about it

Collectives™ on Stack Overflow

Replace the SQL query with the LINQ query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related