• Home
  • Post
  • Accurate tagging of queries in the Entity Framework Core

Accurate tagging of queries in the Entity Framework Core

Sometimes we may write queries in the EF and retrieve a lot of data from the database, and as the project gets bigger, the number of queries increases, making it difficult or impossible to find heavy queries. If you get these queries in SQL Profiler, it is almost impossible to find a written query equivalent to EF in your code. In EF Core 2.2, a new feature called WithTag EF queries have been added that can be labeled with queries, and this tag will be sent to SQL along with the query, and when you look at SQL Profiler, you will also see the label used for the query, which makes it easier. Found queries written in EF. This feature creates a better connection between the DBA and the programmer, making it easier to find queries that are misspelled or read a lot of data from the database, and the problem can be solved in practice sooner. This feature is used as an extension method to EF Core, which can be used as follows.

var result = await _context.Blogs
    .Where(i => i.Url.StartsWith("http://dotnetdocs.ir"))
    .TagWith("Looking for dotnetdocs.ir")
    .ToListAsync();

Now if the above query is executed, the following output can be downloaded from SQL Profiler.

-- Looking for dotnetdocs.ir

SELECT [b].[Id], [b].[Url]
FROM [Blogs] AS [b]
WHERE [b].[Url] IS NOT NULL AND ([b].[Url] LIKE N'http://dotnetdocs.ir%')

This makes it easier to access queries. But it can also be more accurate. Using Caller Attributes , you can get the exact location of the code. To do this, we have written a method extension that sends three attributes of file path, line number and method name along with a tag to SQL.  

public static IQueryable<T> TagWithSource<T>(
    this IQueryable<T> queryable,
    [CallerLineNumber] int lineNumber = 0,
    [CallerFilePath] string filePath = "",
    [CallerMemberName] string memberName = "")
{
    return queryable.TagWith($"{memberName}  - {filePath}:{lineNumber}");
}
public static IQueryable<T> TagWithSource<T>(this IQueryable<T> queryable,
    string tag,
    [CallerLineNumber] int lineNumber = 0,
    [CallerFilePath] string filePath = "",
    [CallerMemberName] string memberName = "")
{
    return queryable.TagWith($"{tag}{Environment.NewLine}{memberName}  - {filePath}:{lineNumber}");
}

Now if you run the query again:

class Program
{
    static void Main(string[] args)
    {
        using (ApplicationDbContext dbContext = new ApplicationDbContext())
        {
            var data = dbContext.Blogs
                .Where(i => i.Url.StartsWith("http://dotnetdocs.ir"))
                .TagWithSource("Looking for dotnetdocs.ir")
                .ToListAsync();
        }
    }
}

 You can see the following information in SQL Profiler. 

-- Looking for dotnetdocs.ir
-- Main  - C:\Users\Farhad\source\repos\TaggingWithEFCore\TaggingWithEFCore.ConsoleApplication\Program.cs:17

SELECT [b].[Id], [b].[Url]
FROM [Blogs] AS [b]
WHERE [b].[Url] IS NOT NULL AND ([b].[Url] LIKE N'http://dotnetdocs.ir%')

;)

Powered by Froala Editor

Posted on Tuesday, September 22, 2020  |    Author : Farhad Zamani  |  Views : 2954  |  Status : Completed
Tags : EntityFramework Core SQL
Comments