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

This article mainly explains "is it practical for Linq to update data", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "is it practical for Linq to update data?"

The website of WEB 2.0 is based on database and data access, which is also the foundation of all operations, and Linq, one of the highlights of VS 2008, happens to do this, so my development starts from Linq and database. There is a lot of online teaching about Linq. I am not going to repeat it. I will only write down the problems I have encountered. Why is it so hard for Linq to update data?

The full name of Linq is Language Integrated Query, which means that Linq appears in front of us as a query language. Linq has done a lot of optimization in the query, we do not have to try to assemble SQL statements, assemble entities, etc., all operations in Linq are strongly typed, we use C # code to easily write beautiful SQL statements.

So how does Linq update data as a query language? Generally speaking, updates to Linq will appear in the following ways (as written in most tutorials)

Var ctx = new MyDataContext (); var user = ctx.Users.Where (u = > u.UserId = = userId). Single (); user.UserName = "New UserName"; ctx.SubmitChanges ()

This is the C # code, but what is done behind it? Linq will generate a SQL statement for us similar to the following

First step, query

SELECT UserId, UserName, FirstName, LastName, CreatTime From User WHERE UserId = @ userId

Part II, update

UPDATE User SET UserName = @ newUserName WHERE UserId = @ oldUserId, userName = @ oldUserName, FirstName = @ oldFirstName, LastName = @ oldLastName

What did you find? First of all, Linq will take out all the fields, when user.UserName = "New UserName", record that the UserName field has been updated, UPDATE will only update the UserName, but put the values of all previous fields in the WHERE statement as a condition.

Are you kidding?! This kind of efficiency is really too poor, isn't it?

Efficiency aside, let's take a look at another kind of update data. There is a field that records the number of times the page has been visited, which we usually use.

UPDATE POST SET Views = Views + 1 WHERE PostId = @ PostId

But if we write down the following C# code

Var ctx = MyDataContext (); var post = ctx.Posts.Where (p = > p.PostId = @ postId) .Single (); post.Views++ ctx.SubmitChanges ()

What does Linq do to update the data? Same as above! Take out all the fields, add one to View, use all fields as conditions (including Views), and update them back.

Imagine that such a frequently used counter is very likely to cross SELECT and UPDATE in two operations. can the latter be updated successfully?

This is Microsoft's explanation: if someone else updates this row in your Linq update data, then this line is not the one you need. In order to prevent such conflicts, put all the fields in the WHERE statement, which is by design.

You can update the data in other ways, but this method doesn't perform well in the current version.

System.Data.Linq.Table has an Attach method with three overloads to update data directly. Let's take a look at one by one.

Attach (T entity) var ctx = new MyDataContext (); var newUser = newUser (); newUser.UserId = new Guid ("xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"); / / assume newUser.UserName = "New UserName" passed as a parameter; ctx.Users.Attach (newUser); ctx.SubmitChanges (); / / end

Running has no effect at all, and SQL Profiler has no record.

Attach (T entity, T original) var ctx = new MyDataContext (); var newUser = newUser (); newUser.UserId = new Guid ("xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"); / / suppose newUser.UserName = "New UserName" passed in as a parameter; var user = ctx.User.Where (u = > u.UserId = newUser.UserId). Single (); ctx.Users.Attach (newUser, user); ctx.SubmitChanges (); / / end

Runtime hint: Cannot add an entity with a key that is already in use.

Attch (T entity, bool asmodified) var ctx = new MyDataContext (); var newUser = newUser (); newUser.UserId = new Guid ("xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"); / / assume newUser.UserName = "New UserName" passed as a parameter; ctx.Users.Attach (newUser, true); ctx.SubmitChanges (); / / end

Runtime hint: An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.

What shall I do? The prompt says "declares a version member", which usually refers to the TimeStamp field in SQL SERVER. Just add a field to the table you need to update and mark it as TimeStamp. But this is still a waste for us, and there are still restrictions on TimeStamp in the WHERE statement.

You can also avoid update checking by setting the UpdateCheck.Never property on the field, but if you update the datasheet, add stored procedures, and need to regenerate the dbml, you need to manually reset it.

Linq update data does not even have an operation like Update Web Reference in the Web reference to make it easy to update dbml after the data table is updated, and it is not available in this version, all you can do is delete the original table, refresh the Server Exploer, re-drag and drop it into dbml's design view, or write a script to ask SQLMETAL to do this for you.

At this point, I believe you have a deeper understanding of "is it practical for Linq to update data?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.