Compiled Linq Queries with Built-in SQL functions
- by Brandi
I have a query that I am executing in C# that is taking way too much time:
string Query = "SELECT COUNT(HISTORYID) FROM HISTORY WHERE YEAR(CREATEDATE) = YEAR(GETDATE()) ";
Query += "AND MONTH(CREATEDATE) = MONTH(GETDATE()) AND DAY(CREATEDATE) = DAY(GETDATE()) AND USERID = '" + EmployeeID + "' ";
Query += "AND TYPE = '5'";
I then use SqlCommand Command = new SqlCommand(Query, Connection) and SqlDataReader Reader = Command.ExecuteReader() to read in the data. This is taking over a minute to execute from C#, but is much quicker in SSMS.
I see from google searching you can do something with CompiledQuery, but I'm confused whether I can still use the built in SQL functions YEAR, MONTH, DAY, and GETDATE.
If anyone can show me an example of how to create and call a compiled query using the built in functions, I will be very grateful! Thanks in advance.
