Tech Off Thread

7 posts

Question about EF

Back to Forum: Tech Off
  • spivonious

    Maybe I'm missing something here, but is there any way to update the primary key of an entity?

    I know that PKs should be immutable, but I'm working with an existing database where some of the tables are set up with PKs that routinely change. If EF doesn't support this, then EF is pretty much a non-starter.

    Here's my test code. The database table is a made up one for testing EF.

    Module Module1
    
        Sub Main()
    
            Console.Out.WriteLine("Loading...")
            Dim p As New Paradev()
            Dim c = Aggregate r In p.CustomerOrders Into Count()
    
            Console.Out.WriteLine("Count is {0}", c)
    
            Dim first = p.CustomerOrders.First()
            first.Amount = 12345
            p.SaveChanges()
    
            Console.In.ReadLine()
        End Sub
    
    End Module
    
    Public Class CustomerOrder
        Public Property Name As String
        Public Property OrderDate As Date?
        Public Property Amount As Decimal?
    End Class
    
    Public Class Paradev
        Inherits DbContext
    
        Public Property CustomerOrders As DbSet(Of CustomerOrder)
    
        Protected Overrides Sub OnModelCreating(modelBuilder As System.Data.Entity.DbModelBuilder)
            modelBuilder.Entity(Of CustomerOrder).HasKey(Function(t) New With {t.Name, t.OrderDate, t.Amount})
            modelBuilder.Entity(Of CustomerOrder).Property(Function(t) t.Name).HasColumnName("NAME")
            modelBuilder.Entity(Of CustomerOrder).Property(Function(t) t.Amount).HasColumnName("AMT")
            modelBuilder.Entity(Of CustomerOrder).Property(Function(t) t.OrderDate).HasColumnName("DT")
            modelBuilder.Entity(Of CustomerOrder)().ToTable("CUSTOMER_ORDERS", "SSP01953")
        End Sub
    
    End Class

  • JohnAskew

    Why not have a proxy database that EF can rely on PKs?

    You could map from the proxy database to the 'real' database (via whatever PK vodoo you doo).

    Or perhaps have surrogate keys in addition to PKs-that-EF-can-rely-upon for your strange usage...

    'Routinely changing PKs'... I'm not sure I want to know why... sounds quite desperate.

  • spivonious

    @JohnAskew: Like I said, it's an existing database with other applications reading it, so updating the key structure isn't a real option.

    I was just surprised at this limitation of EF, since every database I've ever used lets you update the PK column. I wonder what the reasoning is behind this decision. Does EF not track original values like ADO.NET DataColumns do?

  • JohnAskew

    , spivonious wrote

    @JohnAskew: Like I said, it's an existing database with other applications reading it, so updating the key structure isn't a real option.

    I was just surprised at this limitation of EF, since every database I've ever used lets you update the PK column. I wonder what the reasoning is behind this decision. Does EF not track original values like ADO.NET DataColumns do?

    I would imagine PK values are immutable for EF tracking and navigation (as FK relations).

    I'd not want to deal with concurrency issues, meddling with PK values... perhaps that's one reason...

    ADO.NET DataColumns - how does it handle data access concurrency with transient PK values?

    Or is this PK value changing occurring offline?

    What an odd strategy, changing PK values... I'm curious why is this being done?

  • spivonious

    @JohnAskew: Here's an example from our system:

    PK = Master Analysis # (references other table), Piece Analysis #, and Effective Date

    The analysis numbers are just generated IDs, but the effective date is chosen by the user setting up the analysis. Let's say they enter a date of November 15th, but it was supposed to be October 15th. With EF, they would have to remove the analysis and re-add it with the correct date.

    Concurrency is not an issue because the row is locked in the database (another questionable choice, but it works for the limited cases where concurrency is an issue).

    Could we have designed the table with an "id" column as the primary key and the set the three columns as a unique key? Sure, but most times the effective date is not changing.

  • figuerres

    , spivonious wrote

    @JohnAskew: Here's an example from our system:

    PK = Master Analysis # (references other table), Piece Analysis #, and Effective Date

    The analysis numbers are just generated IDs, but the effective date is chosen by the user setting up the analysis. Let's say they enter a date of November 15th, but it was supposed to be October 15th. With EF, they would have to remove the analysis and re-add it with the correct date.

    Concurrency is not an issue because the row is locked in the database (another questionable choice, but it works for the limited cases where concurrency is an issue).

    Could we have designed the table with an "id" column as the primary key and the set the three columns as a unique key? Sure, but most times the effective date is not changing.

    while it may be a PIA to fix it I would think really hard about doing so....

    seems like it should not really be that hard to fix and might allow you to make the whole system better.

    changing a date should not make the whole thing break....  and a PK should just be an internal pointer not a data value the users ever need to alter.

  • JohnAskew

    , figuerres wrote

    *snip*

    while it may be a PIA to fix it I would think really hard about doing so....

    seems like it should not really be that hard to fix and might allow you to make the whole system better.

    changing a date should not make the whole thing break....  and a PK should just be an internal pointer not a data value the users ever need to alter.

    I agree that a PK should never be changed as a best practice.

    I will also suggest making the change for an "id" column as PK, and those three columns as an unique index. Those other apps that read from these three columns wouldn't have to change at all, correct? If true, then the change is not so hard.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.