Leonardo Bruno Lima

Is there any way to update or delete without performing the select first for now I doní»t think so, but in the future


Re: LINQ Project General Update / Insert without Select

Matt Warren - MSFT

Not in the way you can use SQL to send a UPDATE/DELETE command directly, since there are no operations that have immediate effect on the database; they are pooled until you call SubmitChanges.

There is a way using table.Attach/table.Remove. This is meant for multi-tier scenarios where you've round-tripped the object's data including original and current state (or enough info for the operation.) You can then attach a new instance to a data context and then optional modify it or remove it. Then when you call SubmitChange the modification actions become an UPDATE and the remove operation becomes a DELETE.






Re: LINQ Project General Update / Insert without Select

Leonardo Bruno Lima

Hi Matt,

Thank you for your response. Let me show how Ií»m doing it in WCF environment:

Sub ChangeBook(ByVal _book as book) Implements IWCFBook.ChangeBook

Dim dc As New LibraryDataContext

Dim actualBook = (From B In dc.Books _
Where B.ID = _book.ID).FirstOrDefault()

actualBook.Title = _book.Title
actualBook.Editor = _book.Editor
...

dc.SubmitChanges()

End Sub

Can I do simply this way

Dim dc As New LibraryDataContext

dc.Attach(_book)
dc.SubmitChanges()

Thanks in advance,

P.S.: Sorry for my bad English.






Re: LINQ Project General Update / Insert without Select

DeBiese

I'm having more or less the same problem/question as Leonardo.

I'm not working in WCF environment currently, but I do separate my data handling from my ui.

So when I get some data, I put it in a List and give that List to my ui classes. In the dataclass,

I do a dispose of my datacontext.

If I now go back to the dataclass with some updated objects in my list, I have to requery to get

the correct object and then assign each possibly changed property to its new value.

It would be nice to have something like an Update method on the tables. Sort of like the Add that

is available but in this case for an update. Where LINQ in the background searches for the object

and changes the required values.

For removing objects, we have the same story actually. Once de datacontext has been disposed,

you cannot create a new one and tell that one: datacontext.table.remove(object). Although you have

the object in a list, an error is thrown stating that you are trying to use an object after it has been disposed.

I do understand why the error appears. But it's frustrating to have requery for your object (that you have right

there with you) for a simple update or a delete.





Re: LINQ Project General Update / Insert without Select

Matt Warren - MSFT

Leonardo Bruno Lima wrote:

Hi Matt,

Thank you for your response. Let me show how Ií»m doing it in WCF environment:

Sub ChangeBook(ByVal _book as book) Implements IWCFBook.ChangeBook

Dim dc As New LibraryDataContext

Dim actualBook = (From B In dc.Books _
Where B.ID = _book.ID).FirstOrDefault()

actualBook.Title = _book.Title
actualBook.Editor = _book.Editor
...

dc.SubmitChanges()

End Sub

Can I do simply this way

Dim dc As New LibraryDataContext

dc.Attach(_book)
dc.SubmitChanges()

Thanks in advance,

P.S.: Sorry for my bad English.

Leonardo, both of these approaches have problems. The first one above may appear to work, yet it is doing so at the expense of defeating optimistic concurrency. Why this is true will take a little bit of explanation.

First, let me remind anyone that might be reading that optimistic concurrency is the way we avoid doing pessimistic concurrency (locking server tables/rows) and still get a semblance of correctness out of the changes we make. Optimistic concurrency works by verifying that the state of the data in the database is the same when you are attempting to update it as when you first read it. In other words, "no one else has touched it" since you took your snapshot and made business logic level decisions on how to change it. Notice how Optimistic Concurrency provides us the same correctness as Pessimistic Concurrency, since both guarantee that nothing has been changed by someone else.

How we make this 'verification' is significant. In general, this is done by remembing the object's original state and forming an update/delete statement with a verification predicate that asserts that the current data in the database matches the original values we saw. This works because the update/delete statements in SQL are atomic. (Transactions are a must too, but isolation levels can vary.)

The policy on exactly how you want this verification to be made can be controlled in the mapping using the UpdateCheck setting. You can decide which fields in the database row should be checked for changes by others and which should be ignored. These settings are defaulted for you when you first create the mapping in the designer or using SQLMetal.

When you look at your ChangeBook method above, you see that the method gets handed a book instance that has already been modified. Yet, since calling Attach directly doesn't seem to allow you to send the update you've requeried for the object using a new datacontext and applied the changes from the original book instance to this newly retrieved instance. This fools LINQ to SQL into sending your update command, however Optimistic Concurrency has been defeated because your business logic (prior to calling ChangeBook) made those changes based on the original state of the object, and since these original values are not known by LINQ to SQL the wrong information is being used to make the Optimistic Concurrency check.

So what shoud you do That's easy. Make sure the DataContext has all the information it needs to do its job. It needs to know both the original state of the object and your newly modified state. This works by default if you simply keep the DataContext around from the time you retrieve the data to the time you attempt to submit your changes. If this is not an option (and I the only case I consider not be an option is serialization out of process) then you must somehow do your own change tracking and then present both sets of information (original & current) to the DataContext when you want to re-attach the data. The mechanism available in Beta 1 to do this is limited. You must attach the object in the original state and then modify it by assigning the members their new values. Beta 2 will have some additional overloads that provide other options, however, you cannot escape the need to provide both pieces of information in order to get LINQ to SQL to function correctly.






Re: LINQ Project General Update / Insert without Select

Leonardo Bruno Lima

Thanks Matt,

I understand and I will try to do this way.

But if we can change an option like: "CheckConcurrency = False". So, in this case Linq will look for based on the primary key and (if find it) change the item. Is it a bad ideia