LINQ-to-SQL update query

Question

I'm having a problem with Link to SQL and updating a record, I think the problem is to with the current transaction based on the fact that I am looping through a connected data context:

    Using db = New PostcodeLookupModelContainer()

        Dim Stores = From b In db.lkpStores Where b.storeId ' = iStoreID '  Order By b.storeId

        For Each store In Stores

            Debug.Print(store.StorePostcode)

            Dim newStore As New lkpStores()
            newStore.depotId = store.depotId
            newStore.StorePostcode = store.StorePostcode
            newStore.depotId = store.depotId
            newStore.DepotDistance = store.DepotDistance

            db.lkpStores.Attach(newStore)
            newStore.DepotDistance = 50

            db.SaveChanges()

        Next store

    End Using

The line when I get the error is db.SaveChanges() and the error is 'New transaction is not allowed because there are other threads running in the session.'

Do you get the error on the 2nd time through the loop? – ChrisF Dec 21 '12 at 15:07 — ChrisF, Dec 21 '12 at 15:07

Phil Murray · Answer 1 · 2012-12-21T15:13:23.913

0

Don't think you need to attach the new object as it already exists.

db.lkpStores.Attach(newStore)

Just update the store object from the ForEach loop and call submit

   Using db = New PostcodeLookupModelContainer()

        Dim Stores = From b In db.lkpStores Where b.storeId ' = iStoreID '  Order By b.storeId

        For Each store In Stores
            store.DepotDistance = 50
            db.SaveChanges()
        Next store

    End Using

You could also refactor the code

   Using db = New PostcodeLookupModelContainer()

        Dim store = (From b In db.lkpStores Where b.storeId).SingleOrDefault 'Assumming the storeID is unique

        if store isnot nothing then
            store.DepotDistance = 50
            db.SaveChanges()
        end if

    End Using

edited Dec 21 '12 at 15:13

answered Dec 21 '12 at 15:06

Phil Murray

6,396
9
45
95

I thought that also and tried it, it gives me the same error: 'New transaction is not allowed because there are other threads running in the session.' – MAO Dec 21 '12 at 15:10
Yes - the comment below fixed it, I need to change it to a list with ToList() – MAO Dec 21 '12 at 15:13

score 0 · Accepted Answer · answered Dec 21 '12 at 15:09

0

try to convert your "Dim Stores = From b In db.lkpStores Where b.storeId ' = iStoreID ' Order By b.storeId" to List() then loop it

answered Dec 21 '12 at 15:09

Behruz Tolibov

455
4
7

LINQ-to-SQL update query

2 Answers2