Adding Extra Column to join table

Question

I currently have an employee model

public string FirstName { get; set; }
public string LastName { get; set; }
public virtual ICollection<StateLicenseType> Licenses { get; set; }

and a License Type Model

public class StateLicenseType
{
    public int StateLicenseTypeId { get; set; }
    public string State { get; set; }
    public string LicenseName { get; set; }
    public virtual Employee Employee { get; set; }
}

This relationship can be one to many, but I also need to add some information to the license when saved. I need to be able to store the employees unique license number and have not been able to find out how to do this while searching around. Is there a way to have Entity Framework add a column to a join table and then even if I have to, update it myself?

Is there a better/different way to model this relationship with EF?

In an old DB the table was created like this,

CREATE TABLE `nmlsstatelicenses` ( `peopleid` int(11) DEFAULT NULL,  `statelicensetypeid` int(11) DEFAULT NULL, `licensenumber` varchar(25) DEFAULT NULL)

renakre · Accepted Answer · 2015-03-31 19:10:22Z

2

You need to create a third entity which will be a linking entity (like a linking table in many-to-many relationships in database. Here is an example: many-to-many relationships with additional information.

So you would have the following entities in your model:

public Employee 
{
    public string EmployeeId { get;set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public virtual ICollection<LicenseRegistration> RegisteredLicenses { get; set; }
}
public LicenseType
{
   public int StateLicenseTypeId { get; set; }
   public string State { get; set; }
   public string LicenseName { get; set; } 
   public virtual ICollection<LicenseRegistration> RegisteredLicenses { get; set; }
}
public LicenseRegistration
{
   //properties for the additional information go here
   /////////////////////////////////////////////////////

   public int EmployeeId {get;set;}
   [ForeignKey("EmployeeId")]
   public Employee Employee {get;set;}

   public int LicenseTypeId {get;set;}
   [ForeignKey("LicenseTypeId")]
   public LicenseType {get;set;}
}

Then, in your DBContext file, you will need to define 1-to-many relationship between Employee and LicenseRegistration, and between LicenseType and LicenseRegistration.

Hope this helps!

UPDATE Here is how you would set up the relationships:

modelbuilder.Entity<LicenseRegistration>()
            .HasRequired(lr => lr.LicenseType)
            .WithMany(lt => lt.RegisteredLicenses)
            .HasForeignKey(lr => lr.LicenseTypeId);

modelbuilder.Entity<LicenseRegistration>()
            .HasRequired(lr => lr.Employee)
            .WithMany(e => e.RegisteredLicenses)
            .HasForeignKey(lr => lr.EmployeeId);

edited Mar 31, 2015 at 19:10

answered Mar 31, 2015 at 4:21

renakre

8,3318 gold badges55 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Echofiend Over a year ago

This looks like its going to work for me. I had considered this but couldnt get it quite right, in how the three classes should be related. Thanks.

renakre Over a year ago

I added the code to configure the relationships in the dbcontext class.

Steve Smith Over a year ago

How would you add records to it? For example, you can no longer write "employee.Licenses.Add(new StateLicenseType())".

Collectives™ on Stack Overflow

Adding Extra Column to join table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related