mvc entity framework model: a primary key and 2 more keys issue

Question

So I have the following models

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
 }

public class Appointment
{
    public int Id { get; set; }
    public string Name{ get; set; }
}

and I want them assigned to a another table with a separate primary key like so

public class UserAppointment
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public int AppointmentId { get; set; }

    public virtual Appointment Appointment {get;set;}
    public virtual User User {get;set;}
 }

What I wanted is the Id be a key (unique, auto generate, etc) while at the same time will force the UserId and AppointmentId to have a unique combination as well.

eg
this is what i want
  Id         UserId          AppointmentId
  1            1                  2
  2            1                  3

and not this
  Id         UserId          AppointmentId
  1            1                  2
  2            1                  2
  3            1                  2

At the moment I did some modelbuilder statements on my context to configure my keys

modelbuilder.Entity<UserApointment>()
  .HasRequired(e => e.User)
  .HasMany(u => u.Appointment)
  .HasForeignKey(e => e.UserId);

modelbuilder.Entity<UserApointment>()
  .HasRequired(e => e.Appointment)
  .HasMany(u => u.Users)
  .HasForeignKey(e => e.AppointmentId);

But it will still let me insert records with similar user and appointment ids.

Any advice on how to deal with this is very much appreciated! Thanks!!

Why do you want to maintain `Id` column for `UserApointment` when you can use a composite PK. — Eranga, Apr 26 '12 at 14:20
ill be using it on another table which will have a one to many relationship with the UserAppointment. i feel like it makes it easier to query? — gdubs, Apr 26 '12 at 14:31

Icarus · Accepted Answer · 2012-04-26T15:17:46.853

1

You can tackle this at the database level by simply creating a unique constraint on the UserAppointment table.

ALTER TABLE UserAppointments
ADD CONSTRAINT uc_UserAppointment UNIQUE (UserID,AppointmentID)

On Entity Framework (which I am not familiar with) seems to be done like this:

modelbuilder.Entity<UserApointment>().HasKey(x=> new { x.UserId,x.AppointmentId});

Read more here., specially the remark:

If the primary key is made up of multiple properties then specify an anonymous type including the properties. For example, in C# t => new { t.Id1, t.Id2 }

UPDATE

Probably a Unique Index serves your purposes better:

CREATE UNIQUE INDEX IX_UserAppointment
ON UserAppointment (UserId, AppointmentId)

This unique index will ensure that no duplicate combination of UserId and AppointmentId will ever exist in the table and will also speed up your queries.

edited Apr 26 '12 at 15:17

answered Apr 26 '12 at 14:24

Icarus

63,293
14
100
115

i saw that first option on some scripts, didn't really fully understood it. let me try the 2nd one first real quick. Thanks! – gdubs Apr 26 '12 at 14:30
the second one worked! im pretty sure the 1st would too. which one would you recommend? anyway, thanks! EDIT:ok hold up. the second one had an issue, the Id lost it's primary key status. and it would not generate a value. womp. maybe, ill use the 1st one – gdubs Apr 26 '12 at 14:41
I am glad it worked. I would add the constraint to the database. This will take care of maintaining data integrity from any application and not just yours. – Icarus Apr 26 '12 at 14:42
@gdubs I see your edit. You don't really need the Id column in the UserAppointment table. Your primary key is really a composite key of UserId and AppointmentId. I would get rid of it. From a database perspective, at least, I don't see the point on having this Id column at all. Also, instead of creating a constraint, you may be better served by creating a Unique Index using UserId and AppointmentId. This way, your searches will be faster. I will update my answer showing the syntax to create a Unique index using these 2 columns – Icarus Apr 26 '12 at 14:47
sounds good. so i was wondering tho, since im using that userappointments to map user and appointments. the reason why i needed that id on the userappointments is that im mapping it with another table. does that make sense? like id have userappointments and doctor or something like that. ill be putting it in another table to map it since it's going to be a many to many between the two. – gdubs Apr 26 '12 at 14:51
"...does that make sense?" In that case, yes, it makes sense. I recommend that you still create the index instead of the constraint since you are very likely to query this table by either userid, appointmentid or both. – Icarus Apr 26 '12 at 14:54
@gdubs I wonder (as I said, I am not an expert on EF), what happens if you do this: `modelbuilder.Entity().HasKey(x=> new { x.Id,x.UserId,x.AppointmentId});`? Does it still fail to generate the Id automatically? – Icarus Apr 26 '12 at 14:55
That's awesome boss! Thank yoU!. so quick question tho. i will still have the Id field on that table correct? Thanks so much UPDATE: Yes that did not work. that's what i did on the 2nd attempt. Id is still not auto generated and would have a value of 0. – gdubs Apr 26 '12 at 14:56
1

@gdubs yes, you'd still have the Id column on that table since you are linking it to other tables and even though you could potentially link it using both columns (userid,appointmentid) I would prefer using a single column (id). – Icarus Apr 26 '12 at 15:00
ok so i tried creating the index it shows like this on the sql management studio "IX_UserAppointments(Non-Unique, Non-Clustered)" . But it would still let me insert same user same appointment. it will have a unique Id tho. Do I include the unique constraint? cuz I didn't do that. lol. sorry im dumb with sql servers. – gdubs Apr 26 '12 at 15:12
1

If it shows "Non-Unique" in SSMS then you did not create a Unique Index and I can see why: I forgot to put "UNIQUE" on my answer (sorry about that, I just updated my answer once again). Drop the index you created and recreate it using `CREATE UNIQUE INDEX IX_UserAppointment ON UserAppointment (UserId, AppointmentId)`. In order to drop the index, just select it in SSMS, right-click and pick "delete". – Icarus Apr 26 '12 at 15:20

mvc entity framework model: a primary key and 2 more keys issue

1 Answers1