Entity Framework Core Tips: Detect a Select without Where Clause Using DbComand Interceptor

What are Entity Framework Core Interceptors

Entity Framework Core (EF Core) interceptors enable interception, modification, and/or suppression of EF Core operations. This includes low-level database operations such as executing a command, as well as higher-level operations, such as calls to SaveChanges.

What is a DbCommandInterceptor

An IDbCommandInterceptor can be used to capture or modify SQL before it is sent to the database. This example shows how to capture an SQL Select statement that has no WHERE clause and might cause bad performance to your application if the target table has too many rows.

Environment Setup

• Create a Console Application using Visual Studio 2022 or Visual Studio Code. In this article, I’ll be using Visual Studio 2022.
• Add Microsoft.EntityFrameworkCore.Tools and Microsoft.EntityFrameworkCore.Sqlite or Microsoft.EntityFrameworkCore.SqlServer Nuget packages. I’ll be using SqlLite for this demo.

• Configure DbContext as below:

public class Blog
{
    public int BlogId { get; set; }

    public string Url { get; set; }

    public List<Post> Posts { get; } = new();
}

public class Post
{
    public int PostId { get; set; }

    public string Title { get; set; }

    public string Content { get; set; }

    public int BlogId { get; set; }

    public Blog Blog { get; set; }
}

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    public string DbPath { get; }

    public BloggingContext()
    {
        var folder = Environment.SpecialFolder.LocalApplicationData;
        var path = Environment.GetFolderPath(folder);
        DbPath = System.IO.Path.Join(path, "blogging.db");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite($"Data Source={DbPath}");
    }
}

• Create SelectWithoutWhereCommandInterceptor.cs.

public class SelectWithoutWhereCommandInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        CheckCommand(command);

        return result;
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
    {
        CheckCommand(command);

        return new ValueTask<InterceptionResult<DbDataReader>>(result);
    }

    private void CheckCommand(DbCommand command)
    {
        if (command.CommandText.Contains("SELECT COUNT(*)", StringComparison.OrdinalIgnoreCase))
        {
            return;
        }

        if (command.CommandText.Contains("SELECT", StringComparison.OrdinalIgnoreCase))
        {
            if (command.CommandText.Contains("WHERE", StringComparison.OrdinalIgnoreCase))
            {
                return;
            }

            if (command.CommandText.Contains("OFFSET", StringComparison.OrdinalIgnoreCase))
            {
                return;
            }

            if (command.CommandText.Contains("FETCH", StringComparison.OrdinalIgnoreCase))
            {
                return;
            }

            var stackTrace = string.Join("\n", Environment.StackTrace.Split('\n')
                .Select(x => x));

            Log(command.CommandText, stackTrace);
        }
    }

    private void Log(string commandText, string stackTrace)
    {
        File.AppendAllText("../../../logs.txt", $"SELECT WITHOUT WHERE: {Environment.NewLine} {commandText} {Environment.NewLine} {stackTrace} {Environment.NewLine}");
    }
}

• Back to BloggingContext.cs and modify the OnConfiguring method.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlite($"Data Source={DbPath}");
    optionsBuilder.AddInterceptors(new SelectWithoutWhereCommandInterceptor());
}

• Go to Program.cs, write problematic code but looks working just fine.

using SelectWithoutWhereDetection;

using var db = new BloggingContext();

var blogs = db.Blogs.ToList().Where(x => x.BlogId == 1).ToList();

You notice that the code tries to ToList() first before applying the Where filtering. This is a very common mistake when developer writing filtering code but EF Core cannot transtrate to SQL then he/she tries to workaround by forcing EF Core to load entire data into Memory first.

• Add, Run Migration, Run the Application, and open the logs.txt file.

SELECT WITHOUT WHERE:
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";

SELECT WITHOUT WHERE:
SELECT "b"."BlogId", "b"."Url"
FROM "Blogs" AS "b"
at System.Environment.get_StackTrace()
at SelectWithoutWhereDetection.Interceptors.SelectWithoutWhereCommandInterceptor.CheckCommand(DbCommand command) in D:\Phong.NguyenDoan\GitHub\blog.nashtechglobal.com\src\blog.nashtechglobal.com\SelectWithoutWhereDetection\Interceptors\SelectWithoutWhereCommandInterceptor.cs:line 46
at SelectWithoutWhereDetection.Interceptors.SelectWithoutWhereCommandInterceptor.ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult1 result) in D:\Phong.NguyenDoan\GitHub\blog.nashtechglobal.com\src\blog.nashtechglobal.com\SelectWithoutWhereDetection\Interceptors\SelectWithoutWhereCommandInterceptor.cs:line 10 at Microsoft.EntityFrameworkCore.Diagnostics.Internal.RelationalCommandDiagnosticsLogger.CommandReaderExecuting(IRelationalConnection connection, DbCommand command, DbContext context, Guid commandId, Guid connectionId, DateTimeOffset startTime, CommandSource commandSource) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.InitializeReader(Enumerator enumerator)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator) at Microsoft.EntityFrameworkCore.Storage.NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.MoveNext()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Program.
$(String[] args) in D:\Phong.NguyenDoan\GitHub\blog.nashtechglobal.com\src\blog.nashtechglobal.com\SelectWithoutWhereDetection\Program.cs:line 5

You see that the query tried to load the entire Blogs table which you might not expect.

• Back to Program.cs, fix the buggy code by removing the 1st ToList().

using SelectWithoutWhereDetection;

using var db = new BloggingContext();

var blogs = db.Blogs.Where(x => x.BlogId == 1).ToList();

• Run the Application and check the logs.txt file again, you will no longer see the query being logged.

Conclusion

ORM is great, It can reduce the time writing code but using it without understanding how it works can lead to bad performance which might not be easy to identify at the build time or even at the runtime but with a small dataset.

In this article, we know one use case of using an interceptor and how simple it is to set up.

The source code for this demo is published at https://github.com/phongnguyend/blog.nashtechglobal.com.