2 minute read

.NET Core 2.2 introduce a small feature known as Query Tags. It allows you to annotate queries generated by EF Core. This is super useful for debugging purposes, after all one of the main complains you often hear about EntityFramework is the over completed SQL statements it generates.

I am currently working on a project called Chinook, it demonstrates how to build a JSON:API on .NET Core. The project uses EF Core to query a SQLite database. Here is an example of one of the LINQ queries used to get a collection of users.

public async Task<IEnumerable<Customer>> Handle(GetCustomerResourceCollectionCommand request, CancellationToken cancellationToken)
{
    return await _chinookDbContext.Customers.ToListAsync(cancellationToken);
}

Here is the SQL query generated by EF Core.

SELECT "c"."CustomerId", "c"."Address", "c"."City", "c"."Company", "c"."Country", "c"."Email", "c"."Fax", "c"."FirstName", "c"."LastName", "c"."Phone", "c"."PostalCode", "c"."State", "c"."SupportRepId"
FROM "customers" AS "c"

We can tag the original LINQ query with a message using the TagWith method.

public async Task<IEnumerable<Customer>> Handle(GetCustomerResourceCollectionCommand request, CancellationToken cancellationToken)
{
    var tagMessage = $"Calling from {nameof(GetCustomerResourceCollectionHandler)}";
    return await _chinookDbContext.Customers.TagWith(tagMessage).ToListAsync(cancellationToken);
}

and here is the SQL query generated by this LINQ query, as you can see it included our tag message.

-- Calling from GetCustomerResourceCollectionHandler
SELECT "c"."CustomerId", "c"."Address", "c"."City", "c"."Company", "c"."Country", "c"."Email", "c"."Fax", "c"."FirstName", "c"."LastName", "c"."Phone", "c"."PostalCode", "c"."State", "c"."SupportRepId"
FROM "customers" AS "c"

We can improve this code by recording the exact line number that generated the query. That can be accomplished by creating an extension method of IQueryable, like this.

public static class IQueryableTaggingExtensions
{
    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}");
    }
}

I copied this IQueryableTaggingExtensions class from Phil Scott. The original code exist in this Github repo, he also wrote up a post on Query Tags.

I’m going to update our LINQ query to use this new extension class.

public async Task<IEnumerable<Customer>> Handle(GetCustomerResourceCollectionCommand request, CancellationToken cancellationToken)
{
    var tagMessage = $"Calling from {nameof(GetCustomerResourceCollectionHandler)}";
    return await _chinookDbContext.Customers.TagWithSource(tagMessage).ToListAsync(cancellationToken);
}

Now the generated SQL statement looks like this.

-- Calling from GetCustomerResourceCollectionHandler
-- Handle  - C:\Users\Yunier\source\repos\Chinook\src\Chinook.Infrastructure\Handlers\GetCustomerResourceCollectionHandler.cs:27
SELECT "c"."CustomerId", "c"."Address", "c"."City", "c"."Company", "c"."Country", "c"."Email", "c"."Fax", "c"."FirstName", "c"."LastName", "c"."Phone", "c"."PostalCode", "c"."State", "c"."SupportRepId"
FROM "customers" AS "c"

As you can see our original message, the name of method and line number were included as a tag on the generated SQL statement.

Credits: Better Tagging of EF Core Queries