Good day all,

I am currently busy writing an email management program for our team in the office.

The issue that I am currently facing is inserting into a join table.

The setup is as follows:

• I have a table called EmailDetails (This is basically all information related to an email)
• A table called EmailUser (This is a table containing all the users that would be using the app, and also the users that would be a recipient or CC)
• Lastly, a table called Email_EmailUser (The join table, seeing that EmailUser & EmailDetails have many-to-many relationship)
• This is a Database-first approach

{
    [Table("EmailDetails")]
    public class EmailDetails
    {

        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Key]
        public long ID { set; get; }
        public string EmailHeading { set; get; }
        public string EmailBody { set; get; }
        public string EmailAttachment { set; get; }
        public string EmailAttachmentType { set; get; }
        public string EmailAttachmentPath { set; get; }
  
        public virtual ICollection<Email_EmailUser> Email_EmailUser { set; get; }

        public EmailDetails()
        {
            this.Email_EmailUser = new HashSet<Email_EmailUser>();
        }

        public EmailDetails(long ID, string emailHeading, string emailBody, string emailAttachment, string emailAttachmentType, string emailAttachmentPath)
        {

            this.Email_EmailUser = new HashSet<Email_EmailUser>();
            this.ID = ID;
            this.EmailHeading = emailHeading;
            this.EmailBody = emailBody;
            this.EmailAttachment = emailAttachment;
            this.EmailAttachmentType = emailAttachmentType;
            this.EmailAttachmentPath = emailAttachmentPath;
        }
    } 
}

{
    [Table("EmailUsers")]
    public class EmailUser : DbContext
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Key]
        public long ID { set; get; }
        public string UserName { set; get; }
        public string UserEmail { set; get; }
        public int isActive { set; get; }

        public EmailUser()
        {
            this.Email_EmailUser = new HashSet<Email_EmailUser>();
        }


        public EmailUser(long id, string name, string email)
        {
            this.Email_EmailUser = new HashSet<Email_EmailUser>();
            this.ID = id;
            this.UserName = name;
            this.UserEmail = email;
            this.isActive = 1;
        }
   
        public virtual ICollection<Email_EmailUser> Email_EmailUser { set; get; }
    }
}

{
    [Table("Email_EmailUser")]
    public class Email_EmailUser
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Key]
        public long ID { set; get; }
        public long EmailID { set; get; }
        public long EmailUserID { set; get;  } 
        public int IsCC { set; get; }

        public EmailUser EmailUser { set; get; }
        public EmailDetails EmailDetails { set; get; }

        public Email_EmailUser()
        {

        }

    }
}

In my controller I am trying to create a new email which a specific user will have access too. I.e., a new email needs to be created, a record then needs to be inserted into Email_EmailDetails with the new email id and also the id of the current user.

My controller:

            long newEmailID = GetNewEmailID();

            try
            {
                using (DatabaseContext db = new DatabaseContext())
                {
                    EmailDetails newEmail = new EmailDetails(newEmailID, emailHeading, emailBody, emailAttachment, emailAttachmentType, emailAttachmentPath);
                    db.EmailDetails.Add(newEmail);
                    db.SaveChanges();

                    Email_EmailUser emailEmailUser = new Email_EmailUser();

                    emailEmailUser.ID = newEmail_EmailUserID;
                    emailEmailUser.EmailID = newEmail.ID;
                    emailEmailUser.EmailUserID = user.ID;
                    emailEmailUser.IsCC = 1;
                    
                    db.Email_EmailUser.Add(emailEmailUser);
                    db.SaveChanges();
                }
                return true;
                //return email_EmailUser;
            }

When running this code I get the following error:

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Invalid column name 'EmailDetails_ID'.

I am assuming this issue has something to with how the model is setup, however, I've tried multiple suggestions with no luck.

I am still new to C# & EF, so any advice would be appreciated.

CodePudding user response：

If you didn't need to include the IsCC property, you normally configure this in the OnModelCreating override in your instance of DbContext (depending on which version of EF you're using - EFCore 5 should support this properly now):

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<EmailDetails>()
    .HasMany(e => e.EmailUsers)
    .WithMany(u => u.EmailDetails)
    .Map(m =>
      {
        m.MapLeftKey("EmailID");
        m.MapRightKey("EmailUserID");
        m.ToTable("Email_EmailUser");
      });
}

For a database I'm running something similar on the join tables don't have a unique ID, I'm just using a compound ID of the two IDs.

However your setup should work for the additional fields - i.e. you can't navigate from an Email to a User directly, but have to go through the join table to get to them.

CodePudding user response：

After a long and tedious search, I came up with something that worked for me. For some reason, it seems that EF did map the keys correctly.

I ended up using a simple data annotation in my class representing my join table and problem solved.

My join now looks like this:

{
    [Table("Email_EmailUser")]
    public class Email_EmailUser
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Key]
        public long ID { set; get; }
        [ForeignKey("EmailDetails")]
        public long EmailID { set; get; }
        [ForeignKey("EmailUser")]
        public long EmailUserID { set; get;  } 
        public int IsCC { set; get; }
        public virtual EmailUser EmailUser { set; get; }
        public virtual EmailDetails EmailDetails { set; get; }

        public Email_EmailUser()
        {

        }

    }
}

Thanks for all your comments and support. I do appreciate all the effort!