Post
Ask Question

EF Core Query Tags: What They Are and How to Use Them

EF Core query tags are a feature that allows you to add custom tags or comments to your LINQ queries. These tags are included in the generated SQL query sent to the database. Query tags are particularly useful for debugging, logging, and performance monitoring, as they help identify specific queries in logs or database traces.

How to Use Query Tags?

In EF Core, you can use the TagWith() method to add query tags to your LINQ queries. Here’s an example.

var users = context.People
                   .TagWith("This is my Users query!")
                   .Select(c => c.Name)
                   .Take(5)
                   .ToList();

In this snippet

  • We annotate the LINQ query with the tag "This is my spatial query!".
  • The query retrieves the nearest people based on their spatial location.
  • The generated SQL statement includes our query tag.

This LINQ query is translated to the following SQL statement.

-- This is my spatial query!

SELECT TOP (@__p_1) [p].[Name]
FROM [People] AS [p]

Cumulative Query Tags

You can call TagWith() multiple times on the same query, and the tags accumulate. For instance.

private static IQueryable<Person> GetNearestPeople(SpatialContext context, Point myLocation) =>
    from f in context.People.TagWith("GetNearestPeople")
    orderby f.Location.Distance(myLocation) descending
    select f;

private static IQueryable<T> Limit<T>(IQueryable<T> source, int limit) =>
    source.TagWith("Limit").Take(limit);

var results = Limit(GetNearestPeople(context, new Point(1, 2)), 25).ToList();

The resulting SQL includes both tags: "GetNearestPeople" and "Limit".

This LINQ query is translated to the following SQL statement.

-- GetNearestPeople

-- Limit

SELECT TOP(@__p_1) [p].[Id], [p].[Location]
FROM [People] AS [p]
ORDER BY [p].[Location].STDistance(@__myLocation_0) DESC

Multi-Line Query Tags

You can even use multi-line strings as query tags.

var users = context.People
    .TagWith(@"
        This is a multi-line string
        with additional context")
    .Select(c => c.Name)
    .Take(5)
    .ToList();

The corresponding SQL will include the entire multi-line comment.

This LINQ query is translated to the following SQL statement.

-- This is a multi-line string
-- with additional context

SELECT TOP (@__p_1) [p].[Name]
FROM [People] AS [p]

Why Use Query Tags?

  1. Debugging: When analyzing logs, query tags help you quickly identify specific queries and their purpose.
  2. Performance Tuning: By tagging queries related to specific features or bottlenecks, you can focus on optimizing critical parts of your application.
  3. Documentation: Query tags serve as self-documentation, making it easier for other developers (and future you) to understand the intent behind complex queries.

When to Use Query Tags?

  • During Development: Use query tags extensively during development and testing to gain insights into query behavior.
  • Production Troubleshooting: When investigating performance issues or unexpected behavior, query tags guide you to relevant queries in logs.

Remember that query tags are not parameterizable; they are treated as string literals in the generated SQL. Compiled queries with tag parameters are not allowed.

So, embrace query tags, annotate your queries, and illuminate the path to efficient data retrieval.

Tag Syntax and Restrictions

There are a few restrictions when it comes to using Query Tags in EF Core.

  1. The tag must be a string literal.
  2. The tag cannot contain any quotes.
  3. The tag cannot exceed 128 characters.

It is also important to note that Query Tags are only supported in some database providers. Query Tags are only supported in Microsoft SQL Server, SQLite, and PostgreSQL.

Limitations

  • Query tags aren’t parameterizable; EF Core treats them as string literals in the generated SQL.
  • Compiled queries that take query tags as parameters aren’t allowed.
  • Different databases have different maximum lengths for query tags.
  • Some databases do not support query tagging.
  • Adding tags can have a minor performance impact.
  • Depending on the database and configuration, tags might not be visible.
  • In complex queries, tags might only sometimes propagate.
  • If tags are generated dynamically and improperly handled, there’s a risk of SQL injection.
  • Not all database tools and libraries fully support query tags.
  • Using too many tags can increase log storage and analysis overhead.
  • Managing tags in dynamically generated queries can be challenging.
  • EF Core allows tagging the entire query, not specific parts.

Keep learning!


Similar Articles