Accelerate Update and Delete Operations for the Entity Framework

The ORM Entity Framework has an Achilles heel. It consists in the fact that of CRUD operations, only Create and Read are performed optimally. For Update and Delete in the version out of the box, 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. unhappy 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 package EntityFramework.Extended, the situation changes radically.

So, install the package from the repository with the command “Install-Package EntityFramework.Extended”. Next we include 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 see, this is an honest (albeit clumsy) request for group deletion with a condition.

Similarly with the update 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 to constants only.

 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 due to which it is worth installing this extension pack.
But there is more sugar. The creator of the package offers us to save the sample requests, then to carry them out for one approach. To do this, we will mark the data as Future () before materialization and then, if any of the objects materialize, the rest will be materialized automatically.

 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 this was a 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's not all. Imagine that you have a module that handles frequent requests for rarely changeable data. For example, user authorization. Want to cache results? You are welcome. As can be seen from the code, the cache is not limited to 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 to specify the exact cache time.

Thus, the installation and use of EntityFramework.Extended allows not only to eliminate childhood diseases of EntityFramework, but also to accelerate it in places of high load without going to the lower level of stored procedures.