EF6 Invalid Column Name Member_ID

c# entity-framework-6

Question

I've seen this error umpteen times and the cause is always due to a misconfigured Foreign Key setup. In this case though, I just can't see the issue.

This test:

    [TestMethod]
    [TestCategory("Integration")]
    public void DataModelMemberNoteBuilds() {

        _context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

        var sut = _context.MemberNotes.FirstOrDefault();

        if (sut == null) {
            Assert.Inconclusive("return null");
        }

        Assert.IsInstanceOfType(sut, typeof(Domain.Members.Note));

    }

Throws this exception:

System.Data.SqlClient.SqlException: Invalid column name 'Member_ID'.

This is the POCO I'm building the model for:

public class Note
{
    public int ID { get; set; }

    public int MemberID { get; set; }
    public DateTime Timestamp { get; set; }
    public int EnteredByEmployeeID { get;  set; }
    public string Subject { get; set; }
    public string Body { get; set; }
    public bool IsActiveHotnote { get; set; }

    public virtual Member Member { get; set; }
    public virtual Employees.Employee EnteredByEmployee { get; set; }

}

The table (not code-first generated):

CREATE TABLE [dbo].[MemberNotes](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DateCreated] [datetime2](7) NOT NULL,
    [rv] [timestamp] NOT NULL,
    [MemberID] [int] NOT NULL,
    [NoteTimestamp] [datetime2](7) NOT NULL,
    [NoteEnteredByEmployeeID] [int] NOT NULL,
    [NoteSubject] [nvarchar](255) NULL,
    [NoteBody] [nvarchar](2000) NOT NULL,
    [NoteIsActiveHotnote] [bit] NOT NULL,

    PRIMARY KEY CLUSTERED ([ID] ASC) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MemberNotes] ADD  DEFAULT (getdate()) FOR [DateCreated]
GO
ALTER TABLE [dbo].[MemberNotes] ADD  DEFAULT (getdate()) FOR [NoteTimestamp]
GO
ALTER TABLE [dbo].[MemberNotes] ADD  DEFAULT ((0)) FOR [NoteIsActiveHotnote]
GO
ALTER TABLE [dbo].[MemberNotes]  WITH CHECK ADD FOREIGN KEY([MemberID])
REFERENCES [dbo].[Members] ([MemberID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[MemberNotes]  WITH CHECK ADD FOREIGN KEY([NoteEnteredByEmployeeID])
REFERENCES [dbo].[Employees] ([EmployeeID])
ON UPDATE CASCADE
    -- <<<<<<<<<<<<<< NOTE ON DELETE NO ACTION <<<<<<<<<<<<<<<<<
GO

The model is being build like so:

        mb.Entity<Note>().ToTable("MemberNotes");
        mb.Entity<Note>().Property(x => x.Body).HasColumnName("NoteBody");
        mb.Entity<Note>().Property(x => x.EnteredByEmployeeID).HasColumnName("NoteEnteredByEmployeeID");
        mb.Entity<Note>().Property(x => x.IsActiveHotnote).HasColumnName("NoteIsActiveHotnote");
        mb.Entity<Note>().Property(x => x.Subject).HasColumnName("NoteSubject");
        mb.Entity<Note>().Property(x => x.Timestamp).HasColumnName("NoteTimestamp");
        mb.Entity<Note>()
            .HasRequired(x => x.Member)
            .WithMany(x => x.Notes)
            .HasForeignKey(x => x.MemberID);
        mb.Entity<Note>()
            .HasRequired(x => x.EnteredByEmployee)
            .WithMany(x => x.EnteredMemberNotes)
            .HasForeignKey(x => x.EnteredByEmployeeID);

The only two related entities are Member and Employee, which have ICollections as so:

// Member.cs
public virtual ICollection<Note> Notes { get; set; } 

// Employee.cs
public virtual ICollection<Members.Note> EnteredMemberNotes { get; set; }

EF Query ouput from the test (_context.MembersNotes.FirstOrDefault()):

SELECT TOP (1) 
    [c].[ID] AS [ID], 
    [c].[MemberID] AS [MemberID], 
    [c].[NoteTimestamp] AS [NoteTimestamp], 
    [c].[NoteEnteredByEmployeeID] AS [NoteEnteredByEmployeeID], 
    [c].[NoteSubject] AS [NoteSubject], 
    [c].[NoteBody] AS [NoteBody], 
    [c].[NoteIsActiveHotnote] AS [NoteIsActiveHotnote], 
    [c].[Member_ID] AS [Member_ID]
    FROM [dbo].[MemberNotes] AS [c]

Can anyone see where that Member_ID is coming from? I'm at a loss. Thanks

1
3
1/18/2017 9:04:55 PM

Accepted Answer

Found it.

My Member.cs class has a public read-only property that returns a List<Note> based on filtered content in ICollection<Note>

    public List<Note> Hotnotes {
        get
        {
            if (Notes == null) {
                return new List<Note>();
            }
            return Notes.Where(x => x.IsActiveHotnote).OrderByDescending(x => x.Timestamp).ToList();
        }
    }

I neglected to tell EF to ignore this property. It's reading the type and picking up the property as an additional relation/navigation property.

Putting this into the model builder fixes the problem:

mb.Entity<Member>().Ignore(x => x.Hotnotes);

Thanks for looking, hopefully this helps some other poor sod someday.

3
1/19/2017 12:34:21 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow