I'm used to using identity columns and having pk's generated for me. I'm wondering, is there any way to scope an identity column or otherwise compute it to be unique within a foreign key?
For example, using identity column a table might look like this:
| UserId | WidgetId | <-- Table Widget, WidgetId is identity column & pk
---------------------
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 4 |
| 2 | 5 |
| 2 | 6 |
What if I want to achieve something more like the following:
| UserId | WidgetId | <-- Table Widget, both UserId and WidgetId compose the pk
---------------------
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
I know EF allows for database-generated column values, like identity and computed. My question is, how might one achieve a computed column like this?
I realize it can be done in the application, by finding the highest WidgetId a user has and incrementing by one. But this doesn't feel like it's the right way.
I imagine it might also be possible to do this with a trigger, but adding a trigger to the db from the DbContext would place a dependency on the RDBMS, right?
Is there a better way, something that takes less of a dependency on which backend EF is using?
Update
To be more clear, in the model above, there is an identifying relationship between Widget and User on purpose. UserId is the primary key of the User table, and by having the FK to User as part of Widget's PK, this means that Widgets are never shared between Users. When a User is deleted, all of the Widgets get deleted along with it. Querying Widgets by WidgetId alone makes no sense; To query for a particular Widget, both the UserId and WidgetId parameters are necessary.
I'm exploring this because I recall reading in Evans DDD that non-root entity id's in an aggregate only need to be unique within the aggregate. The above is an example of this case:
ENTITIES other than the root have local identity, but that identity needs to be distinguishable only within the AGGREGATE, because no outside object can ever see it out of the context of the root ENTITY. (Domain-Driven Design, Evans)
Update 2
After Gorgan's answer, I have implemented something like the following in my widget factory class. Is this the correct approach to achieve the result desired above?
public class WidgetFactory : BaseFactory
{
private object _sync = new object();
internal WidgetFactory(IWriteEntities entityWriter) : base(entityWriter)
{
// my DbContext class implements the IWriteEntities interface
}
public Widget CreateOrUpdate(User user, int? widgetId, string prop1,
bool prop2, string prop3)
{
Widget widget = null;
if (!widgetId.HasValue)
{
// when widgetId is null do a create (construct & hydrate), then
EntityWriter.Insert(widget); // does DbEntityEntry.State = Added
}
else
{
// otherwise query the widget from EntityWriter to update it, then
EntityWriter.Update(widget); // does DbEntityEntry.State = Modified
}
// determine the appropriate WidgetId & save
lock (_sync)
{
widget.WidgetId = widgetId.HasValue ? widget.WidgetId
: EntityWriter.Widgets.Where(w => w.UserId == user.Id)
.Max(w => w.WidgetId) + 1;
EntityWriter.SaveChanges(); // does DbContext.SaveChanges()
}
return widget;
}
}
Widget
I suppose I should not have disguised this term. The actual entity is WorkPlace. I am building an app to keep track of where I work during the year, as I have to submit an itinerary with my municipal tax refund form every year. When done, I plan to publish it to the cloud so others can use it for free. But of course I want their WorkPlaces to be completely isolated from mine.
Users are automatically created for every visitor using anonymous identification / Request.AnonymousID (there will be a register feature later, but it is not necessary to try out a demo). The web app will have restful url's like /work-places/1, /work-places/2, etc. Because every request is guaranteed to have a user, the user id need not be identified in the url. I can identify users from Request.AnonymousID when nothing is present in User.Identity.Name.
If WorkPlaceId was an identity column, my controller actions would first need to check to make sure the user owns the workplace before displaying it. Otherwise I could hack the URL to see every WorkPlace that every user has set up in the system. By making the WorkPlaceId unique only for the user, I need not worry about it. The URL /work-places/1 will display entirely different data to 2 different users.
