none
Invalid Column Name 'CoilItm_Id1' Error. EF6 WPF

    Question

  • Connection from table 1 to table 2 work with no issues. When I try to get information from 'InfoCoils' on table 2 connected to table 3, I keep getting an error

    Invalid column name 'CoilItem_Id'. Invalid column name 'CoilItem_Id1'

    I have cross checked the column names with the database names and they are the same. Can't find a CoilItem_Id1 anywhere. I started getting this Error when I removed Two relationships from EF6 data table. I had two relationships, one for new entry and one that had the current entries. When I removed them to one simple relationship I started to get this Error. 

    TABLE 1:

    Imports System.ComponentModel
    Imports System.ComponentModel.DataAnnotations
    Imports System.ComponentModel.DataAnnotations.Schema
    Imports System.Data.Linq
    Imports System.Runtime.CompilerServices
    
    <Table("QY8K.CoilCollection")>
    Partial Public Class CoilCollection
        Implements System.ComponentModel.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged
    
        Public Event PropertyChanging As PropertyChangingEventHandler Implements INotifyPropertyChanging.PropertyChanging
        Public Event PropertyChanged As PropertyChangedEventHandler Implements INotifyPropertyChanged.PropertyChanged
        Public Sub NotifyPropertyChanged(<CallerMemberName()> Optional ByVal propertyName As String = Nothing)
            RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName))
        End Sub
    
        Public Sub New()
            MyBase.New
    
            Me._CoilItems = New EntitySet(Of CoilItem)(AddressOf Me.Attach_Collection, AddressOf Me.Detach_Collection)
        End Sub
    
    #Region "Database Properties"
        Private _Id As Integer
        Private _Name As String
        Private _Rank As Short?
    
        Public Property Id As Integer
            Get
                Return _Id
            End Get
            Set(value As Integer)
                _Id = value
            End Set
        End Property
    
        <StringLength(255)>
        Public Property Name As String
            Get
                Return _Name
            End Get
            Set(value As String)
                _Name = value
            End Set
        End Property
    
        Public Property Rank As Short?
            Get
                Return _Rank
            End Get
            Set(value As Short?)
                _Rank = value
            End Set
        End Property
    #End Region
    
    
        Private _CoilItems As EntitySet(Of CoilItem)
        <Global.System.Data.Linq.Mapping.AssociationAttribute(Name:="fkCoilItem", Storage:="_CoilItems", ThisKey:="Id", OtherKey:="CoilCollId")>
        Public Overridable Property CoilItems() As EntitySet(Of CoilItem)
            Get
                Return Me._CoilItems
            End Get
            Set
                Me._CoilItems.Assign(Value)
            End Set
        End Property
        Private Sub Attach_Collection(ByVal entity As CoilItem)
            entity.Collection = Me
        End Sub
        Private Sub Detach_Collection(ByVal entity As CoilItem)
            entity.Collection = Nothing
        End Sub
    
    End Class

    TABLE 2

    Imports System.ComponentModel
    Imports System.ComponentModel.DataAnnotations
    Imports System.ComponentModel.DataAnnotations.Schema
    Imports System.Data.Linq
    Imports System.Runtime.CompilerServices
    
    <Table("QY8K.CoilItem")>
    Partial Public Class CoilItem
        Implements System.ComponentModel.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged
    
        Public Event PropertyChanging As PropertyChangingEventHandler Implements INotifyPropertyChanging.PropertyChanging
        Public Event PropertyChanged As PropertyChangedEventHandler Implements INotifyPropertyChanged.PropertyChanged
        Public Sub NotifyPropertyChanged(<CallerMemberName()> Optional ByVal propertyName As String = Nothing)
            RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName))
        End Sub
    
        Public Sub New()
            MyBase.New
    
     ....
    
            Me._Collection = CType(Nothing, EntityRef(Of CoilCollection))
            Me._InfoCoils = New EntitySet(Of CoilInfo)(AddressOf Me.Attach_Coil, AddressOf Me.Detach_Coil)
    
        End Sub
    
    #Region "Database Properties"
        Private _Id As Integer
        Private _CoilCollId As Integer
    .....
    
        Public Property Id As Integer
            Get
                Return _Id
            End Get
            Set(value As Integer)
                _Id = value
            End Set
        End Property
    
        Public Property CoilCollId As Integer
            Get
                Return _CoilCollId
            End Get
            Set(value As Integer)
                _CoilCollId = value
            End Set
        End Property
    .....
    #End Region
    
    Private _Collection As EntityRef(Of CoilCollection)
    <Global.System.Data.Linq.Mapping.AssociationAttribute(Name:="fkCoilItem", Storage:="_Collection", ThisKey:="CoilCollId", OtherKey:="Id", IsForeignKey:=True)>
    Public Overridable Property Collection() As CoilCollection
        Get
            Return Me._Collection.Entity
        End Get
        Set
            Dim previousValue As CoilCollection = Me._Collection.Entity
    
            If ((Object.Equals(previousValue, Value) = False) OrElse (Me._Collection.HasLoadedOrAssignedValue = False)) Then
                If ((previousValue Is Nothing) = False) Then
                    Me._Collection.Entity = Nothing
                    previousValue.CoilItems.Remove(Me)
                End If
    
                Me._Collection.Entity = Value
    
                If ((Value Is Nothing) = False) Then
                    Value.CoilItems.Add(Me)
                    Me._CoilCollId = Value.Id
                Else
                    Me._CoilCollId = CType(Nothing, Integer)
                End If
            End If
        End Set
    End Property
        Private _InfoCoils As EntitySet(Of CoilInfo)
        <Global.System.Data.Linq.Mapping.AssociationAttribute(Name:="fkCoilInfo", Storage:="_InfoCoils", ThisKey:="Id", OtherKey:="CoilId")>
        Public Overridable Property InfoCoils() As EntitySet(Of CoilInfo)
            Get
                Return Me._InfoCoils
            End Get
            Set
                Me._InfoCoils.Assign(Value)
            End Set
        End Property
        Private Sub Attach_Coil(ByVal entity As CoilInfo)
            entity.Coil = Me
        End Sub
        Private Sub Detach_Coil(ByVal entity As CoilInfo)
            entity.Coil = Nothing
        End Sub
    End Class
    TABLE 3

    Imports System.ComponentModel
    Imports System.ComponentModel.DataAnnotations
    Imports System.ComponentModel.DataAnnotations.Schema
    Imports System.Data.Linq
    Imports System.Runtime.CompilerServices
    
    <Table("QY8K.CoilInfo")>
    Partial Public Class CoilInfo
        Implements System.ComponentModel.INotifyPropertyChanging, System.ComponentModel.INotifyPropertyChanged
    
        Public Event PropertyChanging As PropertyChangingEventHandler Implements INotifyPropertyChanging.PropertyChanging
        Public Event PropertyChanged As PropertyChangedEventHandler Implements INotifyPropertyChanged.PropertyChanged
        Public Sub NotifyPropertyChanged(<CallerMemberName()> Optional ByVal propertyName As String = Nothing)
            RaiseEvent PropertyChanged(Me, New PropertyChangedEventArgs(propertyName))
        End Sub
    
        Public Sub New()
            MyBase.New
    
            Me._Coil = CType(Nothing, EntityRef(Of CoilItem))
        End Sub
    
    #Region "Database Properties"
        Private _Id As Integer
        Private _CoilId As Integer
    
        Public Property Id As Integer
            Get
                Return _Id
            End Get
            Set(value As Integer)
                _Id = value
            End Set
        End Property
    
        Public Property CoilId As Integer
            Get
                Return _CoilId
            End Get
            Set(value As Integer)
                _CoilId = value
            End Set
        End Property
    
    ....
    
    #End Region
    
    
    
        Private _Coil As EntityRef(Of CoilItem)
        <Global.System.Data.Linq.Mapping.AssociationAttribute(Name:="fkCoilInfo", Storage:="_Coil", ThisKey:="CoilId", OtherKey:="Id", IsForeignKey:=True)>
        Public Overridable Property Coil() As CoilItem
            Get
                Return Me._Coil.Entity
            End Get
            Set
                Dim previousValue As CoilItem = Me._Coil.Entity
    
                If ((Object.Equals(previousValue, Value) = False) OrElse (Me._Coil.HasLoadedOrAssignedValue = False)) Then
                    If ((previousValue Is Nothing) = False) Then
                        Me._Coil.Entity = Nothing
                        previousValue.InfoCoils.Remove(Me)
                    End If
    
                    Me._Coil.Entity = Value
    
                    If ((Value Is Nothing) = False) Then
                        Value.InfoCoils.Add(Me)
                        Me._CoilId = Value.Id
                    Else
                        Me._CoilId = CType(Nothing, Integer)
                    End If
                End If
            End Set
        End Property
    
    End Class
    My Module:
    Imports System.Data.Entity
    
    Partial Public Class DBProduction
        Inherits DbContext
    
        Public Sub New()
            MyBase.New("name=CSProduction")
        End Sub
    
    
        Public Overridable Property CoilCollections As DbSet(Of CoilCollection)
        Public Overridable Property CoilItems As DbSet(Of CoilItem)
        Public Overridable Property CoilInfos As DbSet(Of CoilInfo)
    
    
        Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)
    ...
    
            modelBuilder.Entity(Of CoilCollection)() _
                .HasMany(Function(e) e.CoilItems) _
                .WithRequired(Function(e) e.Collection) _
                .HasForeignKey(Function(e) e.CoilCollId)
    
    ....
    
            modelBuilder.Entity(Of CoilItem)() _
                .HasMany(Function(e) e.InfoCoils) _
                .WithRequired(Function(e) e.Coil) _
                .HasForeignKey(Function(e) e.CoilId)
    
    ...
        End Sub
    End Class

    Yet when I try to get items for 'InfoCoils' I keep getting the Invalid column name 'CoilItem_Id'. Invalid column name 'CoilItem_Id1' error.

    There is no column called Id1 in table never has been.


    Wednesday, January 3, 2018 5:06 PM

All replies

  • Hi,

    If you have troubles about EF elationship, following links should help you.

    Configure One-to-Zero-or-One Relationship

    Configure One-to-Many Relationship in EF 6

    Configure Many-to-Many Relationship in Code-First:

    Entity Framework Relationships and Navigation Properties

    In addition, please consider which forum you're going to post in before posting. your question is more related EntityFrameWork, yu should ask question on ADO.NET Entity Framework and LINQ to Entities forum. You will get a more professional support from there, thank you for your understanding.

    Note: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Sincerely,

    Bob


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 4, 2018 9:19 AM
    Moderator
  • I think the problem is more with SQL background code not rebuilding, then entity framework.

    CREATE TABLE [QY8K].[CoilCollection] (
        [Id]   INT           IDENTITY (1, 1) NOT NULL,
        [Name] VARCHAR (255) NULL,
        [Rank] SMALLINT      DEFAULT ((0)) NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    )
    
    CREATE TABLE [QY8K].[CoilItem] (
        [Id]            INT           IDENTITY (1, 1) NOT NULL,
        [CoilCollId]    INT           NOT NULL,
        [KeyNo]         VARCHAR (50)  NULL,
        [Grade]         VARCHAR (10)  NULL,
        [Rank]          SMALLINT      NULL,
        [CustClrOne]    VARCHAR (255) NULL,
        [CustClrTwo]    VARCHAR (255) NULL,
        [IsDoubleColor] BIT           DEFAULT ((0)) NOT NULL,
        [IsSpecialty]   BIT           DEFAULT ((0)) NOT NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC),
        CONSTRAINT [fkCoilItem] FOREIGN KEY ([CoilCollId]) REFERENCES [QY8K].[CoilCollection] ([Id]) ON DELETE CASCADE
    );
    
    
    CREATE TABLE [QY8K].[CoilInfo] (
        [Id]              INT          IDENTITY (1, 1) NOT NULL,
        [CoilId]          INT          NOT NULL,
        [IsNew]           BIT          DEFAULT ((1)) NOT NULL,
        [CoilFlippedDate] DATE         NULL,
        [CoilAddCode]     VARCHAR (10) NULL,
        [CoilNo]          VARCHAR (50) NULL,
        [MatThick]        VARCHAR (50) NULL,
        [SqFtWght]        VARCHAR (50) NULL,
        [CoilWidth]       VARCHAR (50) NULL,
        [ODMeas]          VARCHAR (50) NULL,
        [IDMeas]          VARCHAR (50) NULL,
        [CoilLng]         VARCHAR (50) NULL,
        [CoilWght]        VARCHAR (50) NULL,
        [Layers]          VARCHAR (50) NULL,
        [MatCircum]       VARCHAR (50) NULL,
        [MatRad]          VARCHAR (50) NULL,
        [CoilSqFt]        VARCHAR (50) NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC),
        CONSTRAINT [fkCoilInfo] FOREIGN KEY ([CoilId]) REFERENCES [QY8K].[CoilItem] ([Id]) ON DELETE CASCADE
    );

    Thursday, January 4, 2018 5:01 PM
  • Hi,

    If you need any further help you will have to upload a reproducible sample that are required to reproduce the error on OneDrive and post the link to it here.

    Sincerely,

    Bob


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 17, 2018 11:51 AM
    Moderator