Today I had a problem that with the help of my fellow developers at Spindogs, we found a solution, but I was wondering, is there another way of doing it?
The issue is that I have a column in a database table that has a DateTime value e.g. 2023-02-09 and I needed to query that table to find all dates by month, so the value 02.
I started with the following code:
var sql = scope.SqlContext.Sql().SelectAll()
.From<APIModel>()
.Where<APIModel>(o => o.Time.Month == DateTime.Now.Month);
But this gave the error :
System.InvalidOperationException: 'variable 'o' of type 'Our.Site.Core.API.Models.APIModel' referenced from scope '', but it is not defined'
The next attempt looked like this:
var month = DateTime.Now.Month.ToString();
var sql = scope.SqlContext.Sql().SelectAll()
.From<TidesAPIModel>()
.Where<TidesAPIModel>(o => o.Time.Month == month);
This was in the hope that comparing the database value to a string of month would fix things, it didn't. I got the same error as above.
Next attempt was similar to the above code but we changed the .SelectAll()
to .Select(*)
We also changed the '==' to '<' and to my surprise, it worked. Well, it sort of worked, I was now pulling back all the dates that were less than the current month. Not the exact results I wanted.
Then a suggestion was to just get the query working in Sql Server Management Studio - the SQL statement looked like this :
SELECT TOP (1000) [Id]
,[Time]
,[FreeFlowOpenTime]
,[FreeFlowCloseTime]
,[Day_Id]
,[CreatedOn]
,[UpdatedOn]
,[Height]
FROM [UMBRACO_DATABASE].[dbo].[TideTimes]
WHERE DATEPART(month, [Time]) = '2'
This brought back the results I was hoping for, all the data that had a month of 2 (February).
What we then tried back in code was to replicate this and we came up with the following :
var sql = scope.SqlContext.Sql().Select("*")
.From<TidesAPIModel>()
.Where("DATEPART(month, [Time]) = '"+ month +"'");
The value for 'month' gets passed so that I can query different months but Eureka! It worked.
So, it seems that the '==' was the issue. I need to read up about why it's an issue but if you've had this problem and used a different solution, I'd be really interested to hear from you.
UPDATE:
There is a safer way to do this with the use of parameters. The previous way was open to SQL Injection due to string concatenation, the "+ month +" bit in my code above.
The suggestion to use parameters came from Nik on Twitter after I tweeted that I'd written this blog.
and the code now looks like this:
var sql = scope.SqlContext.Sql().Select("*")
.From<TidesAPIModel>()
.Where("DATEPART(month, [Time]) = @0", month);
Thanks James, Nigel and Dom for troubleshooting this with me today. #h5yr
Banner image created with DALL-E
Published on: 09 February 2023