CODEWITHSUNDEEP
linq-to-sql
Bee gud
Bee gud

Reputation: 147

How to do an update with linq to sql?

I have two tables in my DB

Class
Student

One Class has many Students

What's the best,more elegant way to update the students of one class, using Linq?

Upvotes: 0

Views: 134

Answers (5)

blueshift
blueshift

Reputation: 881

Assuming your Class and Student tables both have a timestamp field, Modified, you could try this:

Public Class ClassesConduit

    Public Function SaveClasses(ByVal theseClasses As IEnumerable(Of [Class])) As Boolean
        SaveClasses = False

        Dim newClasses = theseClasses.Where(Function(c) c.Modified Is Nothing)
        Dim updatedClasses = theseClasses.Where(Function(c) c.Modified IsNot Nothing)

        Using db As New ClassesDataContext
            db.DeferredLoadingEnabled = False
            db.Classes.InsertAllOnSubmit(newClasses)
            db.Classes.AttachAll(updatedClasses, True)

            SaveStudents(theseClasses.SelectMany(Function(c) c.Students), db)

            SaveClasses = (db.GetChangeSet.Inserts.Count + db.GetChangeSet.Updates.Count) > 0
            Try
                db.SubmitChanges()
            Catch ex As Exception
                Throw
            End Try
        End Using

    End Function

    Private Sub SaveStudents(ByVal theseStudents As IEnumerable(Of Student), ByRef db As ClassesDCDataContext)
        Dim newStudents = theseStudents.Where(Function(s) s.Modified Is Nothing)
        Dim updatedStudents = theseStudents.Where(Function(s) s.Modified IsNot Nothing)

        db.Students.InsertAllOnSubmit(newStudents)
        db.Students.AttachAll(updatedStudents, True)

    End Sub
End Class

Upvotes: 0

Adam Albrecht
Adam Albrecht

Reputation: 6870

As they said batch updates aren't (easily) possible, so iterating over each student and updating one at a time is probably the best solution.

This article does come up with a way to do batch updates, but it looks to be more trouble than it's worth.

Upvotes: 0

Robert Harvey
Robert Harvey

Reputation: 180787

DataContext dc = new DataContext();

foreach(var student in dc.Students.Where(SomeCondition))
{
   student.SomeField = SomeValue;
   student.SomeOtherField = SomeOtherValue;
}
dc.SubmitChanges();

Upvotes: 2

Justin Niessner
Justin Niessner

Reputation: 245389

If you are using LINQ to SQL, then your Class Linq class should have a Students collection.

You should be able to update them all using a simple foreach loop. Once your updates are completed, just call SubmitChanges() on your Data Context.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 837926

Batch update isn't possible in Linq, so you have to select the students you want to update, iterate over them, update the fields, then submit changes on your data context.

(There have been some attempts to get batch updates working, for example this, but it's not officially supported by Microsoft.)

Upvotes: 0

Related Questions