EF adding columns to bridge table and accessing data

Question

I have a many-to-many relationship between two tables. EF created (model first) created the bridge table for me with two columns that has links the two keys together. All good and fine. But I want to add a few columns to the bridge table for holding additional data that's relevant to a particular relationship.

If I do an alter table add <column name> on the bridge table, then how can I access the data using EF? Is it possible? I guess I can always do a straight SQL query..but that's so 90's.

Answer 1

You can use the default bridge table but there is an easier way. Here is a sample of the bridge table with extra fields. In this example, we have two entity table and one bridge table

public class Student
 {
    public int id { get; set; }
    public string FullName { get; set; }
    public IList<CourseStudent> CourseStudents { get; set; }
 }
public class Course
 {
    public int id { get; set; }
    public string CourseName { get; set; }
    public IList<CourseStudent> CourseStudents { get; set; }
 }

  //bridge Table
    public class CourseStudent
{
    public Student Student { get; set; }
    [Key, Column(Order = 0)]
    public int Studentid { get; set; }

    public Course Course { get; set; }
    [Key, Column(Order = 1)]
    public int Courseid { get; set; }

    //You can add foreign keys like this
    //public Yourclass Yourclass{ get; set; }
    //[key, Column(Order = )]
    //public Yourclasstype(int,string or etc.) Yourclassid{ get; set; }

    //Other data fields
    public DateTime RegisterDate { get; set; }
}

Now Add this to your DBcontext

 public class Yourdbcontextname: DbContext
{
    public BridgeDB() : base("name=EFbridge")
    {
    }
    public DbSet<Student> Students { get; set; }
    public DbSet<Course> Courses { get; set; }
    public DbSet<CourseStudent> CourseStudents { get; set; }
}

Answer 2

Here is an example how can you do that:

The default brdige table name is

dbo.Table1NameTable2Name but you can customize it.

you can also use automapper to create a generic soultion.

Example:

public class Account
{
    public int AccountId { get; set; }
    public virtual List<Operation> Operations { get; set; }
}

public class Operation
{
    public Int32 OperationId { get; set; }
    public virtual List<Account> Accounts { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<Operation> Operations { get; set; }
    public DbSet<Account> Accounts { get; set; }
    public MyDbContext()
        : base("name=cs")
    {
    }
}

public class OperationAccounts
{
    public int AccountId { get; set; }
    public int OperationId { get; set; }
    public string ExtraInfo { get; set; }
}

public static ICollection<OperationAccounts> GetOperationAccounts(string connectionString = @"Data Source=.\;Initial Catalog=TestDb;Integrated Security=true")
{
    ICollection<OperationAccounts> dict = new List<OperationAccounts>();
    var sqlBuilder = new SqlConnectionStringBuilder(connectionString);
    using (var con = new SqlConnection(connectionString))
    {
        con.Open();
        var cmd = con.CreateCommand();
        cmd.CommandText = "SELECT * FROM OperationAccounts";

        using (var rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
        {
            while (rdr.Read())
            {
                var accountId = rdr.GetInt32(0);
                var opertationId = rdr.GetInt32(1);
                var extraColumn = rdr.IsDBNull(2)? string.Empty : rdr.GetString(2);

                dict.Add(new OperationAccounts() { AccountId = accountId, OperationId = opertationId, ExtraInfo = extraColumn });
            }
        }
    }
    return dict;
}

public static void SetOperationAccounts(ICollection<OperationAccounts> operationAccounts, string connectionString = "name=cs")
{
    // Your homework same as GetOperationAccounts
}

static void Main(string[] args)
{
    Database.SetInitializer<MyDbContext>(new CreateDatabaseIfNotExists<MyDbContext>());
    using (var dbContext = new MyDbContext())
    {
        dbContext.Database.ExecuteSqlCommand(@"ALTER TABLE OperationAccounts ADD ExtraInfo VARCHAR(20) NULL; ");
        var account = new Account();
        var operation = new Operation();

        account.Operations = new List<Operation> { operation };
        operation.Accounts = new List<Account> { account };

        dbContext.Accounts.Add(account);
        dbContext.SaveChanges();

        var oas = GetOperationAccounts();
        foreach (var oa in oas)
        {
            oa.ToString();
        }
    }
}

Answer 3

Setup your bridge tables manually:

public class User
{
    // User stuff

    // Bridge table
    public virtual ICollection<UserFile> Files { get; set; }
}

public class File
{
    // Other File stuff ....

    // Bridge table
    public virtual ICollection<UserFile> Users { get; set; }
}

// Bridge table
public class UserFile
{
    public User User { get; set; }
    public File File { get; set; }

    public DateTime CreatedDate { get; set; }
    // Other metadata here.
}

You may need to setup the relationships in your context override OnModelCreating()

 modelBuilder.Entity<UserFile>()
    .HasRequired(i => i.User)
    .WithMany(i => i.Files)
    .WillCascadeOnDelete(true);