Wrong column name weird issue in Entity Framework

Question

I have been facing this problem some time, and to be honest I am myself confused with it so please excuse me if i don't succeed explaining it as I should.

I am trying to insert some data into a Table called CommunicationAttachment which is related as One to Many relationship with Communication; every communication could have many attachments.

The thing is that I get:

UpdateException: Invalid Column Name: "Communication_CommunicationId

when I try to insert list of attachments.

And please note that I am using the repository pattern but I even tried the normal way and the issue wasn't fixed.

I tried tracing the transaction that happens on the database and I figured out that it sends Communication_CommunicationId with the Insert statement, yet there is no such column. I am pretty sure I didn't send such a column.

Here is my code (this is happening when adding new Communication); first of all I call CasefileAttachments to make copies from them, and Communications are related to CaseFiles:

public List<CorrespondenceAttachment> GetCaseFileAttachments(List<Guid> CorrespondenceAttachmentIds) 
{
    List<CorrespondenceAttachment> originalAttachments = new List<CorrespondenceAttachment>();

    foreach (var item in CorrespondenceAttachmentIds)
    {
        var attachment = QueryData.Query<CorrespondenceAttachment>().Where(att => att.CorrespondenceAttachmentID == item).FirstOrDefault();
        originalAttachments.Add(attachment);
    }

    return originalAttachments;
}

Then I copy the CaseFileAttachments and create new objects of CommunicationAttachments :

public List<CommunicationAttachment> CopyCaseFileAttachmentsToCommunication(List<CorrespondenceAttachment> originalAttachments,Guid communicationId)
{
    var communicationAttachments = new List<CommunicationAttachment>();

    if (originalAttachments.Any())
    {
        foreach (var attachmentRef in originalAttachments)
        {
            var CommunicationAttachmentId = Guid.NewGuid();

            communicationAttachments.Add(new CommunicationAttachment()
            {
                CommunicationAttachmentId = CommunicationAttachmentId,
                DmsFileId = CommunicationAttachmentId,
                CommunicationId = communicationId,
                AttachmentTitle = attachmentRef.AttachmentTitle,
                MimeType = attachmentRef.MimeType,
                NewVersionID = null,
                UploadDate = DateTime.Now,
                Size = attachmentRef.Size,
                Version = "0001",
                AttachmentsGroupId = attachmentRef.AttachmentsGroupId,
                DocumentId = attachmentRef.DocumentId,
                RelativePath = attachmentRef.RelativePath,
                Extension = attachmentRef.Extension,
                AttachmentSubject = attachmentRef?.AttachmentSubject,
                ExternalContactID = attachmentRef?.ExternalContactID,
                AttachmentNumber = string.IsNullOrEmpty(attachmentRef?.AttachmentNumber) ? null : attachmentRef.AttachmentNumber,
                TemplatedmsId = attachmentRef.TemplatedmsId,
                State = eSense.Framework.Data.ObjectState.Added,
            });
        }
    }

    return communicationAttachments;
}

and the methods above are called something like this way:

public void AddNewCommunication(CommunicationDto communicationDto)
{
    var communication = communicationDto

   if (communicationDto.CommunicationAttachmentIdList.Any())
   {
       caseFileAttachments = GetCaseFileAttachments(communicationDto.CommunicationAttachmentIdList);

       if (caseFileAttachments.Any())
       {
           commAttachments = CopyCaseFileAttachmentsToCommunication(caseFileAttachments, communication.CommunicationId);
       }
   }

   communication.Attachments = commAttachments;

   Save(communication)
}

So what could be the problem that I get a wrong column name?

Here is the relation between Communication and CommunicationAttachment

Note I added only the Important fields so don't bother if the declaring does not match the entity

Communication Entity:

public class Communication : BaseEntity
{
    public Communication()
    {
        Attachments = new HashSet<CommunicationAttachment>();
    }

    [Key]
    public Guid CommunicationId { get; set; }

    public string Subject { get; set; }

    public string CommunicationNumber { get; set; }

    public virtual ICollection<CommunicationAttachment> Attachments { get; set; }

    public DateTime DateCreated { get; set; }

    public Guid? PreviousCommunicationId { get; set; }
    [ForeignKey("PreviousCommunicationId")]
    public virtual Communication PreviousCommunication { get; set; }

}

CommunicationAttachment Entity:

public class CommunicationAttachment : AttachmentBaseWithDelegation<Guid>
{
    public override Guid PrimaryId
    {
        get
        {
            return this.CommunicationAttachmentId;
        }
    }

    public CommunicationAttachment()
    {
    }

    [Key]
    public Guid CommunicationAttachmentId { get; set; }

    private string _attachmentNumber;

    public string AttachmentNumber { get; set; }

    [ForeignKey("NewVersionID")]
    public virtual CommunicationAttachment CaseFileAttachmentNewerVersion { get; set; }

    public Guid CommunicationId { get; set; }
    [ForeignKey("CommunicationId")]
    public virtual Communication Communication { get; set; }


}

Sorry if you found it hard to understand my question I myself is confused!

Thanks in advance.

Answer 1

This is typically a case where a relationship between entities is not set up correctly. It would appear that EF should be resolving this relationship by convention if Communication's PK is "CommunicationId".

I notice that you've commented out a line to set the CommunicationId on the new entity:

//CommunicationId = communicationId,

What fields are in the CommunicationAttachment? is there a CommunicationId? Is there a Communication navigation property? What configuration settings are you are using?

For example, with fluent configuration I would have something like:

(CommunicationEntityConfiguration)

If CommunicationAttachment has a navigation property back to Communication and a FK field called CommunicationId...

HasMany(x => x.CommunicationAttachments)
   .WithRequired(x => x.Communication)
   .HasForeignKey(x => x.CommunicationId);

If the attachment entity has a navigation property without a mapped FK in the entity...

HasMany(x => x.CommunicationAttachments)
   .WithRequired(x => x.Communication)
   .Map(x => x.MapKey("CommunicationId"));

If the attachment entity does not have a navigation property, but has a FK in the entity...

HasMany(x => x.CommunicationAttachments)
   .WithRequired()
   .HasForeignKey(x => x.CommunicationId);

Or lastly if the attachment entity does not have a navigation property nor a mapped FK...

If the attachment entity does not have a navigation property, but has a FK in the entity...

HasMany(x => x.CommunicationAttachments)
   .WithRequired()
   .Map(x => x.MapKey("CommunicationId"));

I am a big fan of explicit mapping over convention as it is very clear as to what maps to what, and how, in order to resolve potential mapping conflicts. If the rest of the similar relations seem to be working and just this one is playing up, I'd be looking for possible typos in the field names. With a mapped collection like above, setting a Communcation.CommunicationAttachments.Add(attachment) should be setting the FK / related entity on the attachment without having to explicitly set the FK or related entity manually.

One additional note:

From your example I see you are setting Primary Keys manually client-side using Guid.NewGuid(). It is generally better to allow the database to manage PK generation and let EF manage FK assignment to ensure that related entities get the FKs to newly inserted rows automatically. Rather than SQL's NewId() or using Guid.NewGuid(), it is advisable to use sequential UUIDs. In SQL Server this is NewSequentialId(). For client-side setting, you can reproduce the sequential UUID pattern either with a system DLL call to get the ID, or a simple re-hash of the Guid bytes. see: Is there a .NET equalent to SQL Servers newsequentialid()

The GUIDs still carry the same uniqueness, the bytes are simply arranged to be more sequential and practical for database indexing to reduce page fragmentation. The downside is that IDs are more predictable. Depending on your database engine you might want to customize the algorithm based on whether the database is optimized for indexing on the lower-order or high-order bytes.

When using GUIDs for database, sequential or otherwise, you should ensure you have a scheduled index maintenance job on the database. With sequential IDs this job will run faster and keep the index tables more compact.