Speeding Up Update and Delete Operations for Entity Framework

Published on November 22, 2013

Speeding Up Update and Delete Operations for Entity Framework

    The ORM Entity Framework has an Achilles heel. It consists in the fact that from CRUD operations only Create and Read are performed optimally. For Update and Delete in the box option, we must first read the entire record from the database and only then can we update it. And, yes, in order to delete a record, we must also read it first.

    Those. unfortunate programmer should write something like
    using (var ctx = new ShopEntities())
    {
        foreach (var u in ctx.Users)
        {
            ctx.Users.Remove(u);
        }
        ctx.SaveChanges();
    }


    But with the release of the EntityFramework.Extended package, the situation is fundamentally changing.

    So, install the package from the repository with the command “Install-Package EntityFramework.Extended”. Next, connect the namespace “EntityFramework.Extensions”.
    And the magic begins.

    Now the removal looks like this.
    using (var ctx = new ShopEntities())
    {
        var itemsDeleted = ctx.Users.Delete(u => u.Orders.Count > 10);
        //Осторожно, запрос уже улетел в БД 
        //и, несмотря на отсутствие вызова  ctx.SaveChanges(), данные были удалены
        Console.WriteLine("{0} users were deleted", itemsDeleted);
    }
    


    By the way, it will not be superfluous to see what flew to the server.
    It was such a request
    DELETE [dbo].[Users]
    FROM [dbo].[Users] AS j0 INNER JOIN (
    SELECT 
        [Project1].[ID] AS [ID]
        FROM ( SELECT 
            [Extent1].[ID] AS [ID], 
            (SELECT 
                COUNT(1) AS [A1]
                FROM [dbo].[Orders] AS [Extent2]
                WHERE [Extent1].[ID] = [Extent2].[UserID]) AS [C1]
            FROM [dbo].[Users] AS [Extent1]
        )  AS [Project1]
        WHERE [Project1].[C1] > 10
    ) AS j1 ON (j0.[ID] = j1.[ID])
    go
    


    As we can see, this is an honest (albeit clumsy) group delete request with a condition.

    Similarly with updating records. You no longer need to read data from the database before updating. At the same time, we can use existing data in records, and are not limited only by constants.
    using (var ctx = new ShopEntities())
    {
        var itemsUpdated = ctx.Users.Where(u => u.Orders.Count > 0).Update(u => new User { BonusCount = u.BonusCount + 1 });
        //Осторожно, запрос уже улетел в БД 
        //и, несмотря на отсутствие вызова  ctx.SaveChanges(), данные были обновлены
        Console.WriteLine("{0} users were updated", itemsUpdated);
    }
    


    We look at the SQL query in the profiler.
    UPDATE [dbo].[Users] SET 
    [BonusCount] = [BonusCount] + 1  
    FROM [dbo].[Users] AS j0 INNER JOIN (
    SELECT 
        [Project1].[ID] AS [ID]
        FROM ( SELECT 
            [Extent1].[ID] AS [ID], 
            (SELECT 
                COUNT(1) AS [A1]
                FROM [dbo].[Orders] AS [Extent2]
                WHERE [Extent1].[ID] = [Extent2].[UserID]) AS [C1]
            FROM [dbo].[Users] AS [Extent1]
        )  AS [Project1]
        WHERE [Project1].[C1] > 0
    ) AS j1 ON (j0.[ID] = j1.[ID])
    go
    


    These are the two main functions because of which it is worth installing this extension package.
    But there is also sugar. The creator of the package offers us to accumulate requests for sampling, then to execute them in one approach. To do this, before materializing, we mark the data as Future () and then, when materializing any of the objects, the rest will be automatically materialized.
    using (var ctx = new ShopEntities())
    {
        var alexUsers = ctx.Users.Where(u => u.Name == "Alex").Future();
        var usersWithOrders = ctx.Users.Where(c => c.Orders.Any()).Future();
        foreach (var item in alexUsers) //материализация всех данных была выполнена в этот момент за один round-trip к серверу.
        {
            Console.WriteLine("{0} {1}", item.ID, item.Name);
        }
        foreach (var item in usersWithOrders) //здесь нет выполнения SQL
        {
            Console.WriteLine("{0} {1}", item.ID, item.Name);
        }
    }
    


    But such was the SQL query
    
    -- Query #1
    SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[IsTop10] AS [IsTop10], 
        [Extent1].[BonusCount] AS [BonusCount]
        FROM [dbo].[Users] AS [Extent1]
        WHERE (N'Alex' = [Extent1].[Name]) AND ([Extent1].[Name] IS NOT NULL);
    -- Query #2
    SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[IsTop10] AS [IsTop10], 
        [Extent1].[BonusCount] AS [BonusCount]
        FROM [dbo].[Users] AS [Extent1]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[Orders] AS [Extent2]
            WHERE [Extent1].[ID] = [Extent2].[UserID]
        );
    go
    


    In addition to the Future extension, FutureCount, FutureFirstOrDefault, FutureValue are also available.

    But that is not all. Imagine that you have a module that processes frequent requests for rarely modified data. For example, user authorization. Want to cache the results? You are welcome. As you can see from the code, the cache is not limited by the context, but remains relevant even after its re-creation.

    for (int i = 0; i < 2; i++)
    {
        using (var ctx = new ShopEntities())
        {
            var alexUsers = ctx.Users.Where(u => u.Name == "Alex").FromCache();
            foreach (var item in alexUsers) //i == 0 запрос в БД ушёл, i == 1 запроса в БД не было
            {
                Console.WriteLine("{0} {1}", item.ID, item.Name);
            }
        }
    }   
    


    The FromCache method has an overload for specifying caching times.

    Thus, the installation and use of EntityFramework.Extended not only eliminates the childhood illness of EntityFramework, but also accelerates it in places of high load without going to the lower level of stored procedures.