DEV Community: Vitalii Yavorskyi

Entity Framework Core is slow or Blind Engineers

Vitalii Yavorskyi — Sun, 04 Jan 2026 21:06:17 +0000

Entity Framework Core is one of the most productive tools in the .NET ecosystem.
It allows teams to move fast, model domains cleanly, and ship features quickly. You should not dive into details how database works, which allows you to write code fast. And it's an advantage and disadvantage at the same time.

EF Core works great — until the project grows.

More data.
More relations.
More edge cases.
More performance-sensitive paths.

At that point, problems start appearing — and EF often gets blamed. Someone starts to add additional indexes, which just temporarily masks the issue.

But the real root cause: engineers have no idea how EF query is converted into SQL.

In many mature codebases, I’ve seen LINQ queries written without any consideration for how they translate to SQL.

At a small scale, this often goes unnoticed:

Few records
Low concurrency
Acceptable latency

But as data grows, those “innocent” queries turn into:

Multiple joins you didn’t expect
N+1 queries
Full table scans
Excessive memory usage
Unoptimized Execution plan

EF Core doesn’t hide SQL — it generates it.
If you don’t understand the generated SQL, you are effectively coding blind.

A Real Example from Production

A few years ago, while investigating performance issues in a production system, I found legacy code that did the following:

Loaded a few hundred entities into memory
Enabled change tracking
Updated them in a loop
Called SaveChangesAsync()

var cutoff = nowUtc.AddDays(-90);

    var users = await db.Users
        .Where(u =>
            u.Status == UserStatus.Active &&
            (u.LastLoginUtc == null || u.LastLoginUtc < cutoff))
        .ToListAsync(ct);

    foreach (var u in users)
    {
        u.Status = UserStatus.Archived;
        u.UpdatedAtUtc = nowUtc;
    }

    await db.SaveChangesAsync(ct);

All of this — just to update a small subset of fields.

At that time, the correct approach was a set-based SQL operation (a stored procedure).
Later, EF Core introduced ExecuteUpdateAsync, which solves this problem cleanly and efficiently.

await db.Users
        .Where(u =>
            u.Status == UserStatus.Active &&
            (u.LastLoginUtc == null || u.LastLoginUtc < cutoff))
        .ExecuteUpdateAsync(setters => setters
            .SetProperty(u => u.Status, UserStatus.Archived)
            .SetProperty(u => u.UpdatedAtUtc, nowUtc),
            ct);

But the root cause was not “EF is slow”.
The root cause was using an ORM abstraction without understanding its cost.

EFCore is cross-database and flexible. It's designed to support a wide range of use cases, and it cannot be perfectly optimized for every scenario out of the box. And it shouldn’t be.

Expecting EF to magically generate optimal SQL for every complex business case is unrealistic.

The Responsibility Is on Us. As software engineers, especially at senior levels, we must:

Understand how LINQ translates to SQL
Know when tracking is needed — and when it’s not
Recognize when a query should be "set-based", compiled, or raw query
Use the right tool for the job

Sometimes EF Core is the best solution. Sometimes stored procedure, view or raw query is better suite.

Do not blame tool, if you do not understand how it works.

EF Query vs SQL View in Entity Framework – Which One Would You Choose?

Vitalii Yavorskyi — Mon, 16 Jun 2025 12:36:54 +0000

Hi folks!
I had few performance issue in my past projects using Entity Framework Core to retrieve and summarize data. Sometimes my EF queries were not converted into SQL in the best way, and it causes performance issues. I would like to share with you some example of task and will be happy to hear your thoughts about it.

Task requirements

There is a simple SQL Server database that contains 3 tables:

The query should return all orders (including OrderType name and count of items where ItemType == 1) if the order contains any item where ItemType = 1 and with Order.CreatedAt > 12/05/2024.

Option 1: EF Query

Here is the simple EF query for it:

var test = await _context.Orders
    .Include(t => t.OrderType)
    .Where(t => t.CreatedAt > new DateTime(2024, 12, 05)
             && t.Items.Any(i => i.ItemType == 1))
    .Select(t => new
    {
        t.Id,
        t.Address,
        t.CreatedAt,
        CategoryName = t.OrderType.Name,
        ItemCount = t.Items.Count(i => i.ItemType == 1),
    })
    .OrderBy(t => t.CreatedAt)
    .Take(1000)
    .ToListAsync(cancellationToken);

Pros:

Easy to refactor or extend
Fully integrated with EF and C#
Strongly typed and expressive

Cons:

Translates into a large SQL query under the hood
May become inefficient for large datasets
Harder to optimize with SQL indexes or query plans

And this query demonstrates performance issue (5.7s). When I look at the generated SQL query, I understood that using OrderItems is not an optimal solution. For some reason, this table is used twice in this query:

SELECT [o2].[Id], [o2].[Address], [o2].[CreatedAt], [o1].[Name] AS [CategoryName], (
    SELECT COUNT(*)
    FROM [OrderItems] AS [o3]
    WHERE [o2].[Id] = [o3].[OrderId] AND [o3].[ItemType] = 1) AS [ItemCount]
FROM (
    SELECT TOP(@__p_0) [o].[Id], [o].[Address], [o].[CreatedAt], [o].[TypeId]
    FROM [Orders] AS [o]
    WHERE [o].[CreatedAt] > '2024-12-05T00:00:00.0000000' AND EXISTS (
        SELECT 1
        FROM [OrderItems] AS [o0]
        WHERE [o].[Id] = [o0].[OrderId] AND [o0].[ItemType] = 1)
    ORDER BY [o].[CreatedAt]
) AS [o2]
INNER JOIN [OrderTypes] AS [o1] ON [o2].[TypeId] = [o1].[Id]
ORDER BY [o2].[CreatedAt]

Here is an execution plan for it:
Note: I did not create indexes to see the difference between the different approaches.

Option 2: SQL View + EF

I was not able to change this query for better performance and that's why I created SQL view that does the aggregation directly in the database (as I wanted):

CREATE VIEW dbo.vw_Orders_ItemsCount
AS
SELECT 
    t.id as OrderId, 
    t.address, 
    c.Name AS OrderType, 
    t.CreatedAt, 
    i.ItemType,
    COUNT(i.id) AS ItemsCount
FROM dbo.Orders AS t
JOIN dbo.OrderTypes AS c ON c.Id = t.TypeId
JOIN dbo.OrderItems AS i ON i.OrderId = t.Id
GROUP BY 
    t.id, 
    t.address, 
    c.Name, 
    t.CreatedAt,
    i.ItemType;
GO

And here is simple EF query for it:

        var test = await _context.OrderItemTypeView
                        .Where(v => v.CreatedAt > new DateTime(2024, 12, 05) && v.ItemType == 1)
                        .OrderBy(v => v.CreatedAt)
                        .Take(1000)
                        .ToListAsync(cancellationToken);

It provides 2x better execution time - 3.1s. (I had not created any non-clustered indexes)

Pros:

Simpler LINQ in C#
Easier to optimize with indexes, execution plans, etc.

Cons:

Less flexibility (changing logic requires DB migration)
View becomes an additional layer to maintain

What Do You Think?

Is there a better way to write the EF query (without SQL view) above to make it cleaner or more performant?

.NET EF Core: When to Use (or don't) .AsSplitQuery()

Vitalii Yavorskyi — Mon, 02 Jun 2025 20:31:52 +0000

Entity Framework Core is a fantastic and powerful tool — much like a BMW M4. If you’re just cruising slowly, everything works perfectly. But when you want to push it hard on the track, you need to understand the physics and specifications of the car. If you don’t know how to handle a 500-horsepower machine, it can hurt you.

Simple Query with JOINs

When you write a query like this in EF Core:

var result = await _context.TestTables
                .Include(t => t.Category)
                .Include(b => b.TestTableItems)
                .Where(b => b.CreatedAt > startDate && b.CategoryId == 3)
                .OrderBy(b => b.CreatedAt)
                .Take(100)
                .ToListAsync(cancellationToken);

EF Core by default generates one large SQL query using multiple JOINs. That’s great for minimizing database round-trips, but there’s a catch...

SELECT [t1].[TestTableId], [t1].[CategoryId], [t1].[CreatedAt], [t1].[Name], [t0].[Id], [t0].[CreatedAt], [t0].[Name], [t2].[ItemId], [t2].[CreatedAt], [t2].[TestTableId], [t2].[Title]
      FROM (
          SELECT TOP(100) [t].[TestTableId], [t].[CategoryId], [t].[CreatedAt], [t].[Name]
          FROM [TestTables] AS [t]
          WHERE [t].[CreatedAt] > '2025-05-05T00:00:00.0000000' AND [t].[CategoryId] = 3
          ORDER BY [t].[CreatedAt]
      ) AS [t1]
      INNER JOIN [TestTableCategories] AS [t0] ON [t1].[CategoryId] = [t0].[Id]
      LEFT JOIN [TestTableItems] AS [t2] ON [t1].[TestTableId] = [t2].[TestTableId]
      ORDER BY [t1].[CreatedAt], [t1].[TestTableId], [t0].[Id]

SQL server returns a big dataset with all required properties. In my example, code contains limit 100 entities - .Take(100). But SQL Server returns 600+ records due to a phenomenon known as 'Cartesian Explosion'. It's because every record of "TestTables" contains multiple items in "TestTableItems".

In most scenarios, it won't be an issue. But when entities grows and more .Include() sections are added - it can cause some issues:

Memory bloat due to row duplication
Slower query materialization
Increased deserialization overhead

`AsSplitQuery()` helps

This instructs EF Core to split the query into separate SQL commands — one for the main entity, and one per collection navigation:

var result = await _context.TestTables
                .Include(t => t.Category)
                .Include(b => b.TestTableItems)
                .Where(b => b.CreatedAt > startDate
                    && b.CategoryId == 3)
                .AsSplitQuery()
                .OrderBy(b => b.CreatedAt)
                .Take(100)
                .ToListAsync(cancellationToken);

And this EF query is converted into two separate SQL queries:

-- The First Query:
SELECT [t1].[TestTableId], [t1].[CategoryId], [t1].[CreatedAt], [t1].[Name], [t0].[Id], [t0].[CreatedAt], [t0].[Name]
      FROM (
          SELECT TOP(@__p_0) [t].[TestTableId], [t].[CategoryId], [t].[CreatedAt], [t].[Name]
          FROM [TestTables] AS [t]
          WHERE [t].[CreatedAt] > '2025-05-05T00:00:00.0000000' AND [t].[CategoryId] = 3
          ORDER BY [t].[CreatedAt]
      ) AS [t1]
      INNER JOIN [TestTableCategories] AS [t0] ON [t1].[CategoryId] = [t0].[Id]
      ORDER BY [t1].[CreatedAt], [t1].[TestTableId], [t0].[Id]

-- The Second:
      SELECT [t2].[ItemId], [t2].[CreatedAt], [t2].[TestTableId], [t2].[Title], [t1].[TestTableId], [t0].[Id]
      FROM (
          SELECT TOP(@__p_0) [t].[TestTableId], [t].[CategoryId], [t].[CreatedAt]
          FROM [TestTables] AS [t]
          WHERE [t].[CreatedAt] > '2025-05-05T00:00:00.0000000' AND [t].[CategoryId] = 3
          ORDER BY [t].[CreatedAt]
      ) AS [t1]
      INNER JOIN [TestTableCategories] AS [t0] ON [t1].[CategoryId] = [t0].[Id]
      INNER JOIN [TestTableItems] AS [t2] ON [t1].[TestTableId] = [t2].[TestTableId]
      ORDER BY [t1].[CreatedAt], [t1].[TestTableId], [t0].[Id]

As you can see, the second query avoids duplicating information from [TestTables], potentially reducing memory usage when there are a lot of properties in the TestTables.
You can find more in Microsoft arcticle "Single vs. Split Queries"

Caution

As you saw above, EF Core will repeat the base query for each collection navigation. That means if your base query is expensive, using split queries might actually make things slower, not faster.

For example, this part of the query is reused:

FROM (
          SELECT TOP(@__p_0) [t].[TestTableId], [t].[CategoryId], [t].[CreatedAt]
          FROM [TestTables] AS [t]
          WHERE [t].[CreatedAt] > '2025-05-05T00:00:00.0000000' AND [t].[CategoryId] = 3
          ORDER BY [t].[CreatedAt]
      ) AS [t1]

If your filter or sort involves heavy computation, indexing, or joins — that cost gets multiplied for each split query. So always benchmark both approaches (.AsSingleQuery() vs .AsSplitQuery()) with real production-like data before making a decision.

When to Use vs. When to Avoid

Use AsSplitQuery() When:

Queries involve multiple Include() statements
You experience Cartesian Explosion due to related collections (e.g., one-to-many or many-to-many)
The primary entity has many properties and duplicating them across joined rows leads to high memory usage
You want to reduce materialization time and improve overall performance for large result sets

Avoid AsSplitQuery() When:

Queries are simple with minimal joins
Main Entity has only few properties (columns)
Included tables has small amount of records, so JOINs are efficient

Conclusion

Like a high-performance car, Entity Framework Core can either take you from 0 to 100 smoothly — or leave you spinning out in a corner. Tools like AsSplitQuery() help avoid pitfalls like Cartesian explosions, but understanding when and how to use them is the key to writing efficient, scalable data access code.
Don’t forget: performance isn’t about one trick. It’s about using the right combination of tools.

So next time you’re optimizing EF Core, remember: don’t just step on the gas — know how to handle the machine. 🏎️

SQL Project in VS: Why column order matters?

Vitalii Yavorskyi — Fri, 16 May 2025 19:45:44 +0000

Many legacy projects use separate SQL projects to maintain SQL database code in Visual Studio. SQL Projects provide a great way to manage your database schema as code and CI/CD integration. But subtle details can make a big impact - like how you add new columns to existing tables.

Why Column Order Matters?

When using SQL Projects, any schema change (no matter how small) is interpreted by the SSDT build process and turned into deployment scripts. Here is an example:

There is an existing table TestTable123

CREATE TABLE [dbo].[TestTable123]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(10) NULL,
    [Description] NVARCHAR(100) NULL,
    [StatusId] INT NULL
)

You add a new column into the middle of the existing table

CREATE TABLE [dbo].[TestTable123]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(10) NULL,
    [Description] NVARCHAR(100) NULL,
    [Created] DATETIME NULL,             -- new column
    [StatusId] INT NULL                  -- existing column
)

When you deploy this change to your database, SSDT treats it as a breaking change and uses the following SQL code:

-- create the temp table
CREATE TABLE [dbo].[tmp_ms_xx_TestTable123] (
    [Id]          INT            NOT NULL,
    [Created]     DATETIME       NULL,
    [Name]        NVARCHAR (10)  NULL,
    [Description] NVARCHAR (100) NULL,
    [StatusId]    INT            NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

-- copies all data into new temp table
IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[TestTable123])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_TestTable123] ([Id], [Name], [Description], [StatusId])
        SELECT   [Id],
                 [Name],
                 [Description],
                 [StatusId]
        FROM     [dbo].[TestTable123]
        ORDER BY [Id] ASC;
    END

-- remove the existing table
DROP TABLE [dbo].[TestTable123];

-- rename the new temp table 
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_TestTable123]', N'TestTable123';

This approach:

Is slow for large tables,
Requires more transaction log space,
Risks of locking or downtime.

Best Practice: Append Columns

Whenever possible, add new columns at the end of the table. This helps SSDT generate a simple ALTER TABLE ... ADD statement, which:

Is fast,
Non-blocking (in most cases),
Doesn’t require temp tables,
Keeps deployments safer and more predictable.

Disable column order setting

You can add next "IgnoreColumnOrder" setting into .sqlproj file. It will ignore column order during your release.

<PropertyGroup>
  <IgnoreColumnOrder>true</IgnoreColumnOrder>
</PropertyGroup>

Just because SSDT simplifies your deployments doesn't mean you shouldn't understand how it works under the hood.

Knowing why certain practices—like adding columns at the end—matter can help you avoid performance issues, deployment delays, and production downtime.

SSDT is a powerful tool, but it's only as effective as the developer using it.