How to best update a composite key in entity framework

Question

0.00/5 (No votes)

See more:

Many of my classes have composite keys usually ID and DateValidFrom.

The DateValidFrom may change during update.

When I did my own updates before adopting Entity Framework, I tracked the original ID/Date combination and updated the row with them in the where clause.

What I have tried:

It is a very simple task to do the same again in EF, but it smacks of how I updated old school indexed records going all the way back to the mid Eighties.

Surely there is a more modern way with Entity Framework. How is this done best, particularly using the repository pattern?

Posted 18-Sep-20 1:02am

Ger Hayden

Updated 18-Sep-20 1:58am

v2

Add a Solution

Comments

[no name] 18-Sep-20 7:48am

Asking for a solution that is more modern than simple is difficult, particularly when one doesn't know what the "simple task" involved. An "update" could involve a delete and an insert; simple on the surface (maybe only). And while one is playing exclusively in the data context without saving, it makes no difference what one does.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gerry Schmitz · Accepted Answer · 2020-09-18T01:58:00

Solution 1

There's actually no reason to change the date; you simply have a "new" "effective date", and a new record; your queries just have to find the correct record for a given time span.

The expiry date of one record, is the effective date of the next record (for that id).

Posted 18-Sep-20 1:58am

Gerry Schmitz

Updated 18-Sep-20 1:59am

v3

Comments

Ger Hayden 18-Sep-20 9:44am

Thanks for the input Gerry. If a client came over the hill tomorrow and insisted on taking my system then that is what I would do to get it into production. However this is, and will probably always be no more than a training sandbox for me. So the "business case" is something like I have a new product to go on sale on 01/10/2020, but it gets deferred to 01/01/2021. In that scenario the user wants to change the fromdate on the price table to reflect this rather than ending the current entry to reflect that the product will commence sale on the new date.

I can do this by tracking the original key, but I am curious as to whether or not there is an Entity Framework specific approach to changing composite key.

The other thing to consider is while I dont have much in the way of foreign keys from MySql, I have them now as part of the migration I did recently to Sql Server (adding entity framework is part of this - and this is the first table). I will need to consider that too. Perhaps allowing key updates is not a good idea any more. But regardless of it being a pigheadded thing to do, I would still like to see how EF might go about it.

[no name] 18-Sep-20 13:04pm

I "used" to use composite keys "long ago"; something I might have picked up from James Martin ... E.F. Codd ... or IMS DB or whatever; performance, I think. Anyway, these days, I find they provide no advantage when I think seriously about my design. I have an "entity id" (usually an int), and all children reference only to their parent ID, and all other columns are values from "lookup tables".

Ger Hayden 25-Sep-20 1:16am

I've had a small outbreak of common sense. From here on, composite keys will only be editable prior to their initial commit. After that, they will have to be end dated and replaced by a new row.

[no name] 25-Sep-20 1:48am

Good plan. With effectively no "deletes" (by the user), your system is more robust. I think composite keys are more pre-SQL. However, they are very desirable in a "data warehouse" (for info retrieval). Operational vs informational efficiency.