Shulou(Shulou.com)06/01 Report--

EntityFramework.Extended how to use EF to achieve the specified field update, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Today, when migrating legacy code that uses stored procedures in a project to a new schema, I encountered a problem-- how to update specified fields in the database with EF (updating the FaceUrl and AvatarUrl fields in the Users table according to UserId)?

The code that originally called the stored procedure:

Public bool UpdateAvatar (Guid userId, string faceUrl, string avatarUrl)

{

DbCommand command = _ db.GetStoredProcCommand ("User_UpdateFaceAvatar")

_ db.AddInParameter (command, "@ FaceUrl", DbType.String, faceUrl)

_ db.AddInParameter (command, "@ AvatarUrl", DbType.String, avatarUrl)

_ db.AddInParameter (command, "@ UserId", userId)

Return _ db.ExecuteNonQuery (command) > 0

}

The SQL statement used in the stored procedure:

UPDATE Users SET FaceUrl=@FaceUrl,AvatarUrl=@AvatarUrlWHERE [UserId] = @ UserId

In the new architecture, the database is accessed by Entity Framework and encapsulated by IUnitOfWork interface, and the operation of the application layer to the database is completed through the IUnitOfWork interface.

The IUnitOfWork API is defined as follows:

Public interface IUnitOfWork: IDisposable

{

IQueryable Set () where T: class

T Add (T entity) where T: class

IEnumerable AddRange (IEnumerable entities) where T: class

T Attach (T entity) where T: class

T Remove (T entity) where T: class

IEnumerable RemoveRange (IEnumerable entities) where T: class

Bool Commit ()

Task CommitAsync ()

}

If you do not add a new interface method to IUnitOfWork, you can use EF's change tracking to complete the update operation of the specified field.

Implementation code in Application.Services:

Public async Task UpdateFaceAvatar (Guid userId, string faceUrl, string avatarUrl)

{

Var user = await _ userRepository.GetByUserId (userId)

User.FaceUrl = faceUrl

User.AvatarUrl = avatarUrl

Return await _ unitOfWork.CommitAsync ()

}

The advantage of using ef change tracking is that if the value of the property has not been changed, the database update operation will not be triggered, and the disadvantage is that a query operation will be performed before each update.

For the application scenarios of updating FaceUrl and AvatarUrl here, you know clearly that the data has changed before the update, and you can update the database directly. Ef change tracking's pre-update query is not only unnecessary, but also adds additional overhead.

So try to find a new solution.

The first attempt is EF's DbEntityEntry, which is not abstract implementation code:

Public class EfUnitOfWork: DbContext, IUnitOfWork

{

Public async Task UpdateAsync (User user)

{

Base.Set () Attach (user)

Base.Entry (user) .property (x = > x.FaceUrl) .IsModified = true

Base.Entry (user) .property (x = > x.AvatarUrl) .IsModified = true

Return (await base.SaveChangesAsync ()) > 0

}

}

Use the code:

Await UpdateAsync (new User {UserId = userId, FaceUrl = faceUrl, AvatarUrl = avatarUrl})

However, based on this implementation, it is difficult to abstract a useful interface method.

Then I suddenly thought of the EntityFramework.Extended I used in a project some time ago. It implements an elegant EF extension for Update operations, so why not just use it?

/ / example of using an IQueryable as the filter for the updatevar users = context.Users.Where (u = > u.FirstName = = "firstname"); context.Users.Update (users, u = > new User {FirstName = "newfirstname"})

Therefore, if the treasure is realized based on EntityFramework.Extended.

First, add an interface method of UpdateAsync () to IUnitOfWork:

Public interface IUnitOfWork: IDisposable {Task UpdateAsync (IQueryable source, Expression updateExpression) where T: class;}

Then, in the implementation of IUnitOfWork.UpdateAsync (), call the UpdateAsync extension method of EntityFramework.Extended to complete the Update operation:

Using EntityFramework.Extensions

Public class EfUnitOfWork: DbContext, IUnitOfWork

{

Async Task IUnitOfWork.UpdateAsync (IQueryable source

Expression updateExpression)

{

Return (await source.UpdateAsync (updateExpression)) > 0

}

}

Then, the implementation code in Application.Services is changed to this:

Public async Task UpdateFaceAvatar (Guid userId, string faceUrl, string avatarUrl)

{

IQueryable userQuery = _ userRepository.GetByUserId (userId)

Return await _ unitOfWork.UpdateAsync (

UserQuery

X = > new User {FaceUrl = faceUrl, AvatarUrl = avatarUrl}

);

}

(note: the return type of _ userRepository.GetByUserId here needs to be changed to IQueryable, which once again verifies the necessity for Repository to return IQueryable. Related blog posts: development Notes: whether UnitOfWork is used and what collection type Repository returns)

Run a unit test to verify it.

Unit test code:

[Fact]

Public async Task UpdateFaceAvatar ()

{

Var result = await _ userService.UpdateFaceAvatar (userId, faceUrl, avatarUrl)

Assert.True (result)

}

Test results:

1 passed, 0 failed, 0 skipped, took 11.38 seconds (xUnit.net 1.9.1 build 1600).

The test passed!

Use SQL Profiler to look at the actual SQL statements executed in the database:

Exec sp_executesql N'UPDATE [dbo]. [Users] SET

[FaceUrl] = @ p__update__0

[AvatarUrl] = @ p__update__1

FROM [dbo]. [Users] AS J0 INNER JOIN (

SELECT

AS [C1]

[Extent1]. [UserId] AS [UserId]

FROM [dbo]. [Users] AS [Extent1]

WHERE [Extent1]. [UserId] = @ p__linq__0

) AS J1 ON (J0. [UserId] = J1. [UserId])', N'@p__linq__0 uniqueidentifier,@p__update__0 nvarchar (24), @ p__update__1 nvarchar (24)'

@ p__linq__0='BD42420B-63CF-DD11-9E4Dmur001CF0CD104B copyright, paired updatebooks, updatebooks, 0Nongles, 201508101807442.pngbooks, packs, updatebooks, 1cards, 20150810180743.png'.

This is the SQL statement we expect.

Finally, it is verified that adding IUnitOfWork.UpadteAsync () interface, based on EntityFramework.Extended, and using EF to update the specified fields in the database, this method is completely feasible in practical development.

As a result, there is one less reason to use stored procedures.

After reading the above, have you mastered how EntityFramework.Extended uses EF to update specified fields? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.