Best Code First approach to central ID table

Question

We are using Entity Framework Code First for this project's database.

Our requirements call for a central 'Resource' table, with a single column of ResourceId (uniqueidentifier NOT NULL DEFAULT (newsequentialid())). Various tables would use this table for their ID.

Profile - ProfileId (uniqueidentifier NOT NULL) Organization - OrganizationId (uniqueidentifier NOT NULL) Document = DocumentId (uniqueidentifier NOT NULL)

So, if I create a new Profile record, I would create a new Resource record, and use that sequentially created Guid as the ID for the new Profile record.

The reason for this is to prevent an Id from Profile ever being present as an Id for Organization. (I know that this is most likely improbable, but not impossible.)

Right now we define this with relationships like this:

public class Resource : BaseEntity
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid ResourceId { get; set; }
    public virtual Profile Profile_ProfileId { get; set; }
    //...
}

public class Profile : BaseEntity, IAuditableEntity
{
    [Key]     
    public Guid ProfileId { get; set; }
    public virtual Resource Resource { get; set; }
    //...
}

public class ProfileMapping : EntityTypeConfiguration<Profile>
{
    public ProfileMapping()
    {
        //Primary key 
        HasKey(t => t.ProfileId);

        //Constraints 
        Property(t => t.ProfileId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
        //...

        ToTable("Profile");

        //Create Relation
        HasRequired(t => t.Resource).WithOptional(t => t.Profile_ProfileId);
    }
}

Then, when we create a new Profile we do this (db being an instance of our DBContext):

var res = new Resource();
db.Resource.Add(res);

var newProfile = new Profile{
    ProfileId = res.ResourceId,
    IsActive = true
};

db.Profile.Add(newProfile);

However, I am wondering, could we define our classes/models to inherit from Resource and get better results?
Have any of you worked with a database structure like this?

If this is only for preventing duplicate primary key values this is an **absolutely useless** overkill. GUIDs are [extremely](http://stackoverflow.com/q/184869/861716) [unlikely](http://stackoverflow.com/q/3138395/861716) to be duplicated. It'll be a constant burden without *any* benefit. — Gert Arnold, Oct 16 '15 at 20:36

Yacoub Massad · Accepted Answer · 2015-10-19T19:52:32.323

Actually, since the GUIDs for ProfileId and OrganizationId are generated on the same database server, you have a 100% guarantee that they are unique. I am assuming that you will let the database server generate the GUIDs.

GUIDs might have a chance (a very small chance) to collide if they are generated on different machines.

Anyway, here is a direct answer to your question:

You can do something like this:

public class Resource
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid ResourceId { get; set; }        
}

public class Profile
{
    [Key]
    [ForeignKey("Resource")]
    public Guid ProfileId { get; set; }

    public Resource Resource { get; set; }

    public string Name { get; set; }

    public Profile()
    {
        Resource = new Resource();
    }
}

Note how the Profile entity is creating a Resource entity in its constructor. Also note that the primary key for Profile is also a foreign key.

UPDATE:

Here is another solution that I think is better, and that will work also in the case where you want to access the Profile entity from the Resource entity:

I added a Profile property to the Resource entity:

public class Resource
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid ResourceId { get; set; }

    public virtual Profile Profile { get; set; }
}

Here is the profile entity:

public class Profile
{
    [Key, ForeignKey("Resource"), DatabaseGenerated(DatabaseGeneratedOption.None)]
    public Guid ProfileId { get; set; }

    public Resource Resource { get; set; }

    public string Name { get; set; }
}

Notice that I no longer create a Resource object in the constructor.

Instead, I create it whenever the entity is saved by overriding the SaveChanges method on the DbContext like this:

public class MyContext : DbContext
{
    public DbSet<Resource> Resources { get; set; }
    public DbSet<Profile> Profiles { get; set; }

    public override int SaveChanges()
    {

        foreach (var profile in ChangeTracker.Entries<Profile>()
            .Where(x => x.State == EntityState.Added))
        {
            profile.Entity.Resource = new Resource();
        }

        //Here you also need to do the same thing for other Entities that need a row in the Resources table (e.g. Organizations)

        return base.SaveChanges();
    }
}

So, with that, I don't need to handle creating the Resource in the rest of my code? I knew there had to be a way. — M Kenyon II, Oct 16 '15 at 20:01
Yes. Since each `Profile` instance will create a `Resource` object for itself when it is constructed. — Yacoub Massad, Oct 16 '15 at 20:09
One follow up question. If we want to Navigate from Resource to Profile in code, for instance: thisResource.Profile.Name; What's the best way in this scenario to achieve this? — M Kenyon II, Oct 19 '15 at 17:59
@MKenyonII, do you have the Resources table just for Profiles? What about Organizations and other entity types? Do they also have a row in the Resources table? — Yacoub Massad, Oct 19 '15 at 18:02
@MKenyonII, in this case, how would the Resource object know that it corresponds to a Profile row and not an Organization row for example? — Yacoub Massad, Oct 19 '15 at 18:34
What we have been doing is (from my first example in the post) `if (thisResource.Profile_ProfileId == null)...` — M Kenyon II, Oct 19 '15 at 18:36

Best Code First approach to central ID table

1 Answers1