Getting the Most Out of Entity Framework Core (Part 2) with Query Tags

“Experts often possess more data than judgment.” Colin Powell

“It is a capital mistake to theorize before one has data.” Arthur Conan Doyle

This is the second post in the series. To read the first episode go to Getting the Most Out of Entity Framework Core - Part 1.

I love databases! I am not a DBA, Database Developer or a Data Expert but I do love data and what it brings to the applications I develop. With that I also the data experts I work with on projects. They are my partners in crime and I use them to make the systems we develop better. I also try to make their work lives better by learning from them and doing as much as I can before I go to them for help.

In this series, we have looked at the features of Entity Framework Core that will make you a better developer. Now I want to show you how to make your relationship with your database and (if you have them) the data professionals that support your SQL Server databases.

The feature we will look at in this part of the Entity Framework Core series is Query Tags. It is a very easy feature to use in your new projects and also to refactor in existing but more important Query Tags will make your DBA’s life so much better! After you read this post please go over to my friend and PASS President Grant Fritchey’s blog post about how to work with Query Tags when passed to SQL Server.

Using Query Tags to Help Your DBA Monitor the SQL Server Database

Using Query Tags will match your Entity Framework Core query to the generated SQL SELECT statement that is passed to the SQL Server for execution. By using the TagWith() method that was added to Entity Framework Core 2.2 the text will be added to the MSSQL logs for diagnostic and investigation purposes.

The following example shows how to work with Query Tags and the TagWith() method in your Entity Framework Core 2.2 queries.

using (var db = new AdventureWorksContext())
{
var workorders = db.WorkOrder
.GroupBy(o => new {o.ProductID, o.ScrapReasonID})
.Select(g => new
{
g.Key.ProductID,
g.Key.ScrapReasonID,
Sum = g.Sum(o => o.ScrappedQty),
Min = g.Min(o => o.ScrappedQty),
Max = g.Max(o => o.ScrappedQty),
Avg = g.Average(o => o.ScrappedQty)
})
.TagWith(“Description: WorkOrder Query from Query Tag demo”)
.TagWith(“Query located: query_tags.Program.Main method”
.TagWith(
@”Parameters:
None”);
}

The first thing to notice is that we pass a string to the TagWith() method that will be added to the MSSQL database logs. Here are the things to remember using the WithTag() method:

The content below is what we will see in the MSSQL log files.

-- Description: WorkOrder Query from Query Tag demo

-- Query located: query_tags.Program.Main method

-- Parameters:
– None

SELECT [o].[WorkOrderID], [o].[DueDate],
[o].[EndDate], [o].[ModifiedDate],
[o].[OrderQty], [o].[ProductID],
[o].[ScrapReasonID], [o].[ScrappedQty],
[o].[StartDate], [o].[StockedQty]
FROM [Production].[WorkOrder] AS [o]
ORDER BY [o].[ProductID], [o].[ScrapReasonID]

By adding a simple method to our Entity Framework Core 2.2 LINQ queries, we are not only passing additional information to our MSSQL databases but we are also making the lives of our team members who work on those databases better. Keep that in mind the next time you write Entity Framework Core LINQ queries in your .NET Core applications.

Remember to also read Grant Fritchey’s blog post “Combine Extended Events and TagWith to Monitor Entity Framework”.

“I think you can have a ridiculously enormous and complex data set, but if you have the right tools and methodology then it’s not a problem.” Aaron Koblin

上一篇

Getting the Most Out of Entity Framework Core – Part 3