CODEWITHSUNDEEP
c#entity-framework-4.1
Evgeni
Evgeni

Reputation: 3343

EF 4.1 - Insert entity if doesn't exist, use existing one otherwise - how?

Here's a scenario: There are users, users have skills. If a user specifies new skill, it needs to be added, however if user specifies existing skill - it should link to the existing one. So, setup is pretty basic - 3 tables (users, skills, userSkillsLink). It's a web app, but I don't want to add every skills there's in a drop down - so I won't know if skill exists right away. I could check if skill already exists using select, but in web app that's not the best choice - not thread safe. I can keep a track of which skills already exist in a separate cache, but this problem should be.. very common, so I'm thinking maybe I'm over-complicating, and there's a known solution already.

Thanks !

Upvotes: 2

Views: 759

Answers (2)

Merlyn Morgan-Graham
Merlyn Morgan-Graham

Reputation: 59101

I think the proper way to solve this with transactional semantics is:

  • Make the potentially shared value the primary key of its table so it is guaranteed to be unique
  • Query to populate the dropdown and store the results in a local cache
  • If the record exists in the cache, reuse it
  • If the record doesn't exist, create it

Then you handle concurrency by letting the transaction throw an exception. In your exception handler you can re-query and use the existing value.

If your transaction dies again (if the record gets deleted), just give an appropriate error message to the user, and tell them to try again.

You can't really do this any other way, since you can't query and commit atomically besides just wrapping it in a transaction, even in a stored procedure.

Upvotes: 1

Fyodor Soikin
Fyodor Soikin

Reputation: 80714

I would create a stored procedure that would take name of the skill as argument, perform the check and insert new record if needed, then return the resulting record (either existing or new).

Then I can map that procedure to a method on DbContext and make that method return an entity.

Then I can just call it: var skill = database.GetSkillCreateIfNone( skillName )

Upvotes: 2

Related Questions