Quick Tip: Tracing Calls with Query Tags in Entity Framework Core 2.2
Today's quick tip demonstrates a technique on how developers can pass along application-specific data through Entity Framework to the database.
Over the past years of my time with Entity Framework, I've run across a number of DBAs (Database Administrators) who DO NOT like Entity Framework at all.
One reason they don't like it is because of the lack of control they have when using LINQ (am I wrong?). They also feel it's not an efficient way to retrieve data.
Three other reasons I can think of are:
- Hard to read or sloppy SQL
- Retrieving every single column from a query
- Not knowing what application sent the SQL
Number three was a primary concern with a DBA I worked with in a past life. He despised Entity Framework because if there was an issue with a query, there was no way to tell, from a database perspective, which application issued the SQL command.
While there are ways to get around this like running stored procedures with Entity Framework, for simple selects, it's a little different.
How can you tell which application sent the SQL command?
Enter Query Tags
There is good news and bad news.
The good news is the introduction of Query Tags to Entity Framework Core 2.2.
The bad news is it's only for Entity Framework Core 2.2 and higher. This would've been an awesome feature in previous versions of EF.
Query Tags are a new feature in EF Core 2.2 which does exactly what you'd expect. It adds a comment to your SQL command when sent to the database.
For example, the following query:
var nearestFriends = (from f in context.Friends.TagWith("This is my spatial query!") orderby f.Location.Distance(myLocation) descending select f).Take(5).ToList();
translates into the following SQL statement:
-- This is my spatial query!
SELECT TOP(@__p_1) [f].[Name], [f].[Location] FROM [Friends] AS [f] ORDER BY [f].[Location].STDistance(@__myLocation_0) DESC
The .TagWith() adds a specific comment to the query sent to the database. To narrow down which application sent the SQL command, you could include:
- An application string w/ version
- A method name
- Parameter list
- User Name
I wouldn't send a date/time stamp since those are automatically recorded when the command is executed.
The one limitation with Query Tags is that they aren't parameterizable. It only accepts string literals.
Conclusion
With Query Tags, this helps DBAs know where the problem resides by looking at the comment passed along with the query. Whatever information you can send along with your query, the more it'll help the DBAs in the long run.
This quick tip gives developers one less reason for DBAs to dislike Entity Framework.
Do you think Query Tags help? What other issues do DBAs dislike about Entity Framework? What are the solutions? Post your comments below and let's discuss.
Reference:
