SQL query running in SQL Server but not in ASP.NET MVC

Question

I am running query in SQL Server and it's running fine and returning data:

 var record = DbContext.Attendances
                       .SqlQuery("select id, checktype, locationinfo, latitude, longitude, employeeid, 
                                         activityid, DateNTime at time zone 'utc' at time zone 'pakistan standard time' as datentime " +
                                 "from(select *, CONVERT(varchar, DateNTime at time zone 'utc' at time zone 'pakistan standard time', 101) as tb from dbo.Attendances where EmployeeId = 3) as t "+
                                 "where tb = '03/05/2022'");

Exact same query is not working when I run it in the ASP.NET MVC app using .SqlQuery function. It gave me an error:

The 'DateNTime' property on 'Attendance' could not be set to a 'System.DateTimeOffset' value. You must set this property to a non-null value of type 'System.DateTime'.

Although I set DateNTime non-nulable.

Here is my table:

I cannot change DateTime to DateTime? because then I will have to change the code in many places.

Your query looks odd, look at "DateNTime at time zone 'utc' at time zone 'pakistan standard time' as datentime ", also why are you using SELECT .... FROM(SELECT...), why not just SELECT * FROM Attendance. The error message says that you are trying to set DateNTime to a DateTimeOffset when it requires a datetime. Do you have an update somewhere? — Steve Ford
– Steve Ford, Commented Mar 6, 2022 at 19:03

Amir Tajik · Accepted Answer · 2022-03-06 18:20:41Z

1

Update Your model and use ? for

public DateTime? DateNTime { get; set; }

answered Mar 6, 2022 at 18:20

Amir Tajik

112 bronze badges

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

2 Comments

Alamzaib Farooq Over a year ago

Is there any other solution? Because I have to change a lot of code to do this. Thanks.

Bechir Anoir Chabchoub Over a year ago

Well, sorry for the code changes but this is a must do in order to avoid having exceptions. unfortunately, sometimes code leading to exceptions needs to be changed. this is how it is. we can't change that fact by a dirty hack that won't be clear/helpfull and may lead to other issues.

Collectives™ on Stack Overflow

SQL query running in SQL Server but not in ASP.NET MVC

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related