CODEWITHSUNDEEP
sql-servervb.netlinq
Rolan
Rolan

Reputation: 3004

Linq updates extra rows

I'm using vb.net entities to access data in a sql server 2014 database.

I have a function which receives an "owner" object and if the owner has multiple current entries in the table, it's supposed to update the older entries to have a status of previous.

Private Shared Sub CreatePreviousOwners(currentOwners As List(Of Owner), prop As compProp)
Dim names = prop.GetVendorsNames()
Using context = New GovernContext()
            Dim sameOwners = From v In currentOwners
                             Where names.Any(Function(o) o.Na_Id = v.Na_Id)
                             Select v
           If sameOwners.Count > 0 Then
                For Each owner In sameOwners
                    'Find all entries prior to the newest
                    Dim prevOwner = From p In context.Owner
                                    Where p.Status = "O" _
                                    And owner.Na_Id = p.Na_Id _
                                    And p.P_Id = prop.p_id _
                                    And p.As_Of_Date < owner.As_Of_Date 
                                    Select p
                    'Update all found entries to previous
                    For Each own As Owner In prevOwner
                        own.Status = "P"
                    Next
                Next
                context.SaveChanges()
            End If
        End Using
    End Sub

Here's a small dataset:

p_id    na_id   AS_OF_DATE              status
596     589494  2008-09-10 00:00:00.000 O
596     589494  2017-08-02 00:00:00.000 O
596     1453364 2017-08-02 00:00:00.000 P

When I step through the code, there is only one entry in the prevOwner sequence.

When I run the sql profiler to view the linq query, the update statement is missing any as_of_date condition. Here's the query from the profiler:

exec sp_executesql N'UPDATE [dbo].[Owner]
SET [Status] = @0
WHERE (([P_Id] = @1) AND ([Na_Id] = @2))
',N'@0 nvarchar(max) ,@1 int,@2 int',@0=N'P',@1=596,@2=589494

Is my sql malformed, or am I missing something?

Upvotes: 2

Views: 49

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109255

You seem to be expecting too much from Entity Framework. When EF updates database records it's always one record at a time. It doesn't do batch updates.

Here's what happens in your code (which I slightly modified):

Private Shared Sub CreatePreviousOwners(currentOwners As List(Of Owner), prop As compProp)
    Dim names = prop.GetVendorsNames()
    Using context = New GovernContext()

        'Runs a SQL SELECT query and creates a list of owners
        Dim sameOwners = (From v In currentOwners
                         Where names.Any(Function(o) o.Na_Id = v.Na_Id)
                         Select v).ToList()

        For Each owner In sameOwners
            'Find all entries prior to the newest

            'Runs another SQL SELECT query
            Dim prevOwner = From p In context.Owner
                            Where p.Status = "O" _
                            And owner.Na_Id = p.Na_Id _
                            And p.P_Id = prop.p_id _
                            And p.As_Of_Date < owner.As_Of_Date 
                            Select p
            'Update all found entries to previous

            'Executes the SELECT query and loops through the results
            For Each own As Owner In prevOwner

                'Adds a modified entry to EF's change tracker
                own.Status = "P"

            Next
        Next

        'Runs update statements for each modified entry in the change tracker
        context.SaveChanges()

    End Using
End Sub

As you see, at the time EF is ready for updating the database it doesn't have any knowledge as to how the modified entries were collected. The updates could be done much smarter, i.e. in statements that update batches of Owners that satisfy the condition p.As_Of_Date < owner.As_Of_Date, but collecting data and updating them are totally independent operations in EF.

That's why you only see UPDATE statements in which one record is identified by its primary key. You may see many of them.

There are third-party libraries that enable batch (or bulk) updates, inserts, and deletes.

Upvotes: 2

Related Questions