none
EF: TPH implementation with Fluent mapping throws Invalid Column Name exception

    Question

  • Here is my implementation.

        public partial class Person
        {
        	#region Constructors
        
            public Person()
            {            
                PersonExpirableCredentials = new List<personexpirablecredential>();
            }
        	
        	#endregion Constructors
        
        	#region Properties
        	
        	/// <summary>
            /// Gets or sets the person id.
            /// </summary>
        	/// <value>
            /// The person id.
        	/// </value> 
            public int PersonID 
        	{ 
        		get; 
        		protected set; 
        	}
    
            public virtual ICollection<personexpirablecredential> PersonExpirableCredentials
            {
                get;
                set;
            }        
        
        	#endregion
        }
    	
        public abstract class PersonCredential
        {
        	#region Constructors
        
            public PersonCredential()
            {
            }
        	
        	#endregion
        
        	#region Properties
        	
        	/// <summary>
            /// Gets or sets the person id.
            /// </summary>
        	/// <value>
            /// The person id.
        	/// </value> 
            public int PersonID 
        	{ 
        		get; 
        		protected set; 
        	}
        		
        	/// <summary>
            /// Gets or sets the person credential id.
            /// </summary>
        	/// <value>
            /// The person credential id.
        	/// </value> 
            public int PersonCredentialID 
        	{ 
        		get; 
        		protected set; 
        	}      
    
            /// <summary>
            /// Gets or sets the when effective.
            /// </summary>
        	/// <value>
            /// The when effective.
        	/// </value> 
            public System.DateTime WhenEffective 
        	{ 
        		get; 
        		set; 
        	}
        		
        	/// <summary>
            /// Gets or sets the when expire.
            /// </summary>
        	/// <value>
            /// The when expire.
        	/// </value> 
            public Nullable<system.datetime> WhenExpire 
        	{ 
        		get; 
        		set; 
        	}
    	
        	/// <summary>
            /// Gets or sets the person.
            /// </summary>
        	/// <value>
            /// The person.
        	/// </value> 
            public virtual Person Person
            {
                get;
                set;
            }
        }
    	
        public partial class PersonExpirableCredential : PersonCredential
        {
            #region Constructors
    
            public PersonExpirableCredential() :
                base()
            {
            }
    
            #endregion
    
            #region Properties
    
            /// <summary>
            /// Gets or sets the when completed.
            /// </summary>
            /// <value>
            /// The when completed.
            /// </value>
            public DateTime? WhenCompleted
            {
                get;
                set;
            }
    
            /// <summary>
            /// Gets or sets the credential number.
            /// </summary>
            /// <value>
            /// The number.
            /// </value>
            public string CredentialNumber
            {
                get;
                set;
            }
    
            #endregion
        }</system.datetime></personexpirablecredential></personexpirablecredential>

    Below are the fluent mappings

    internal partial class PersonMapping : EntityTypeConfiguration<person>
        {
        	#region Constructors
        
            public PersonMapping()
            {
                this.HasKey(t => t.PersonID);		
                this.ToTable("Person");
    		}
        
        	#endregion
        }
    	
    	internal partial class PersonCredentialMapping : EntityTypeConfiguration<personcredential>
        {
        	#region Constructors
        
            public PersonCredentialMapping()
            {
                this.HasKey(t => new { t.PersonCredentialID }); 
    			this.ToTable("PersonCredential");
    			this.HasRequired(t => t.Person).WithMany().HasForeignKey(d => d.PersonID);			
            }
        
        	#endregion
        }
    	
    	internal partial class PersonExpirableCredentialMapping : EntityTypeConfiguration<personexpirablecredential>
        {
            #region Constructors
    
            public PersonExpirableCredentialMapping()
            {
                this.Map(m =>
                {
                   m.Requires("CredentialCategoryCode").HasValue("Expirable");
                });
                this.ToTable("PersonCredential");
            }
    
            #endregion
        }</personexpirablecredential></personcredential></person>

    Now in my data access layer, when i retrieve the person and try to do access "Person.PersonExpirableCredentials". It throws an error with Invalid Column Name "Person_PersonID". Below is the SQL query, it generates.

    exec sp_executesql N'SELECT 
    [Extent1].[PersonID] AS [PersonID], 
    ''1X0X'' AS [C1], 
    [Extent1].[PersonCredentialID] AS [PersonCredentialID], 
    [Extent1].[WhenEffective] AS [WhenEffective], 
    [Extent1].[WhenExpire] AS [WhenExpire],  
    [Extent1].[WhenCompleted] AS [WhenCompleted], 
    [Extent1].[CredentialNumber] AS [CredentialNumber], 
    [Extent1].[Person_PersonID] AS [Person_PersonID]
    FROM [dbo].[PersonCredential] AS [Extent1]
    WHERE ([Extent1].[Person_PersonID] IS NOT NULL) AND ([Extent1].[Person_PersonID] = @EntityKeyValue1) AND ([Extent1].[CredentialCategoryCode] = ''Expirable'')',N'@EntityKeyValue1 int',@EntityKeyValue1=3

    For some reason, EF is not able to identify the relationship between the Person Class and the subclass PersonExpirableCredentials. Help Please.

    Thanks


    • Edited by HighTech Friday, February 10, 2012 5:00 PM
    Friday, February 10, 2012 4:59 PM

All replies

  • Hi HighTech,<o:p></o:p>

    Welcome to MSDN Forum.<o:p></o:p>

    I've test the code you post, and add a Person record into the database, the object's PersonExpirableCredential list property has one member. In the Main method, I try to get the person and access the Person.PersonExpirableCredentials property, everything works well. Here's the code below.<o:p></o:p>

    namespace TPHInvalidColumnName
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (myContext context = new myContext())
                {
                    //Person p = new Person();
                    //PersonExpirableCredential pec = new PersonExpirableCredential();
                    //pec.WhenCompleted = null;
                    //pec.CredentialNumber = "123";
                    //pec.WhenEffective = DateTime.Now;
                    //pec.WhenExpire = DateTime.Now.AddDays(5);
                    //p.PersonExpirableCredentials.Add(pec);
                    //context.persons.Add(p);
                    //context.SaveChanges();
    
                    Person person = (from p in context.persons select p).First();
                    Console.WriteLine(person.PersonExpirableCredentials.Count());
                    Console.Read();
                }
    
                
            }
        }
    
    
        public partial class Person
        {
            #region Constructors
    
            public Person()
            {
                PersonExpirableCredentials = new List<PersonExpirableCredential>();
            }
    
            #endregion Constructors
    
            #region Properties
    
            /// <summary>
            /// Gets or sets the person id.
            /// </summary>
            /// <value>
            /// The person id.
            /// </value> 
            public int PersonID
            {
                get;
                protected set;
            }
    
            public virtual ICollection<PersonExpirableCredential> PersonExpirableCredentials
            {
                get;
                set;
            }
    
            #endregion
        }
    
        public abstract class PersonCredential
        {
            #region Constructors
    
            public PersonCredential()
            {
            }
    
            #endregion
    
            #region Properties
    
            /// <summary>
            /// Gets or sets the person id.
            /// </summary>
            /// <value>
            /// The person id.
            /// </value> 
            public int PersonID
            {
                get;
                protected set;
            }
    
            /// <summary>
            /// Gets or sets the person credential id.
            /// </summary>
            /// <value>
            /// The person credential id.
            /// </value> 
            public int PersonCredentialID
            {
                get;
                protected set;
            }
    
            /// <summary>
            /// Gets or sets the when effective.
            /// </summary>
            /// <value>
            /// The when effective.
            /// </value> 
            public System.DateTime WhenEffective
            {
                get;
                set;
            }
    
            /// <summary>
            /// Gets or sets the when expire.
            /// </summary>
            /// <value>
            /// The when expire.
            /// </value> 
            public Nullable<DateTime> WhenExpire
            {
                get;
                set;
            }
    
            /// <summary>
            /// Gets or sets the person.
            /// </summary>
            /// <value>
            /// The person.
            /// </value> 
            public virtual Person Person
            {
                get;
                set;
            }
    #endregion
        }
    
        public partial class PersonExpirableCredential : PersonCredential
        {
            #region Constructors
    
            public PersonExpirableCredential() :
                base()
            {
            }
    
            #endregion
    
            #region Properties
    
            /// <summary>
            /// Gets or sets the when completed.
            /// </summary>
            /// <value>
            /// The when completed.
            /// </value>
            public DateTime? WhenCompleted
            {
                get;
                set;
            }
    
            /// <summary>
            /// Gets or sets the credential number.
            /// </summary>
            /// <value>
            /// The number.
            /// </value>
            public string CredentialNumber
            {
                get;
                set;
            }
            #endregion
        }
    
    
        internal partial class PersonMapping : EntityTypeConfiguration<Person>
        {
        	#region Constructors
        
            public PersonMapping()
            {
                this.HasKey(t => t.PersonID);		
                this.ToTable("Person");
    		}
        
        	#endregion
        }
    	
    	internal partial class PersonCredentialMapping : EntityTypeConfiguration<PersonCredential>
        {
        	#region Constructors
        
            public PersonCredentialMapping()
            {
                this.HasKey(t => new { t.PersonCredentialID }); 
    			this.ToTable("PersonCredential");
    			this.HasRequired(t => t.Person).WithMany().HasForeignKey(d => d.PersonID);			
            }
        
        	#endregion
        }
    	
    	internal partial class PersonExpirableCredentialMapping : EntityTypeConfiguration<PersonExpirableCredential>
        {
            #region Constructors
    
            public PersonExpirableCredentialMapping()
            {
                this.Map(m =>
                {
                   m.Requires("CredentialCategoryCode").HasValue("Expirable");
                });
                this.ToTable("PersonCredential");
            }
    
            #endregion
        }
    
        public class myContext : DbContext
        {
            public DbSet<Person> persons { get; set; }
            public DbSet<PersonCredential> personCredentials { get; set; }
            public DbSet<PersonExpirableCredential> personExpirableCredentials { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Configurations.Add(new PersonMapping());
                modelBuilder.Configurations.Add(new PersonCredentialMapping());
                modelBuilder.Configurations.Add(new PersonExpirableCredentialMapping());
            }
        }
    
    }

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, February 13, 2012 3:49 AM
  • I copied the above code and got the following error when executing the first part of Main method for saving the data.

    on  context.SaveChanges();

    An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

    Inner Exception: {"Invalid column name 'Person_PersonID'."}

    Bipin

    Monday, February 13, 2012 6:50 AM
  • I commented the save code and execute the read ones.

    Got exception in line Console.WriteLine(person.PersonExpirableCredentials.Count());

    {"An error occurred while executing the command definition. See the inner exception for details."}

    Inner Exception : {"Invalid column name 'Person_PersonID'.\r\nInvalid column name 'Person_PersonID'.\r\nInvalid column name 'Person_PersonID'."}

    Detail:

    System.Data.EntityCommandExecutionException was unhandled
      Message=An error occurred while executing the command definition. See the inner exception for details.
      Source=System.Data.Entity
      StackTrace:
           at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
           at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
           at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
           at System.Data.Objects.ObjectQuery`1.Execute(MergeOption mergeOption)
           at System.Data.Objects.DataClasses.EntityCollection`1.Load(List`1 collection, MergeOption mergeOption)
           at System.Data.Objects.DataClasses.EntityCollection`1.Load(MergeOption mergeOption)
           at System.Data.Objects.DataClasses.RelatedEnd.Load()
           at System.Data.Objects.DataClasses.RelatedEnd.DeferredLoad()
           at System.Data.Objects.Internal.LazyLoadBehavior.LoadProperty[TItem](TItem propertyValue, String relationshipName, String targetRoleName, Boolean mustBeNull, Object wrapperObject)
           at System.Data.Objects.Internal.LazyLoadBehavior.<>c__DisplayClass7`2.<GetInterceptorDelegate>b__1(TProxy proxy, TItem item)
           at System.Data.Entity.DynamicProxies.Person_3C423F5135068C6E2C0DB249D46D113BB2B358A0BA10601C2B3981C133F0366C.get_PersonExpirableCredentials()
           at ConsoleApplication.Program.Main(String[] args) in D:\IT\NET\Prototype\ConsoleApplication\Program.cs:line 26
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: System.Data.SqlClient.SqlException
           Message=Invalid column name 'Person_PersonID'.
    Invalid column name 'Person_PersonID'.
    Invalid column name 'Person_PersonID'.
           Source=.Net SqlClient Data Provider
           ErrorCode=-2146232060
           Class=16
           LineNumber=11
           Number=207
           Procedure=""
           Server=(local)
           State=1
           StackTrace:
                at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
                at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
                at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
                at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
                at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
                at System.Data.SqlClient.SqlDataReader.get_MetaData()
                at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
                at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
                at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
                at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
                at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
                at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
                at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
                at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
           InnerException:


    Bipin

    Monday, February 13, 2012 7:04 AM
  • Hi HighTech,

    I'm not sure why the error was thrown, it works well in my computer. Here's the result after run the project.

    I have upload the demo project to skydrive and here's the link.

    https://skydrive.live.com/redir.aspx?cid=762328a3e843f261&resid=762328A3E843F261!105&parid=762328A3E843F261!104&authkey=!ACe1w0XJ221otDQ

    Please check it.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 15, 2012 3:30 AM
  • Hi Allen,

    Thanks for looking at it. I downloaded the project and added the connection string to the database as below. I am still getting the same Invalid Column error.

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <configSections>
        <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.3.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      </configSections>
      <connectionStrings>
        <add name ="myContext" connectionString="data source=localhost;initial catalog=Concept;integrated security=true;" providerName="System.Data.SqlClient"/>
      </connectionStrings>
    </configuration>

    The database model looks like this


    Bipin

    Wednesday, February 15, 2012 8:42 PM
  • Hi HighTech,

    You didn't modify anything except connection string? When I run the code and the generated database is not as same as yours. Below is the database diagram.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, February 16, 2012 3:17 AM
  • Hi Allen,

    I should have stated my question much clearer. I had my database table setup and wrote classes and mappings later.

    The extra Person_PersonID is the actual problem which I am trying to get away with. The PersonID and Person_PersonID refer to the same value.

    In a big picture, I have 10 classes that inherits from PersonCredentials. With the extra column approach, I have to have 10 columns for each subclass refering back to Person. And everytime i add another subclass, i need to add extra ID column in database. This doesnot make sense.

    Isnt there a way to not have extra column for each inherited class reference? When I do the same thing with EDMX mappings, it works fine. It does not need any extra column. But Fluent mapping looks for extra column.


    Bipin

    Thursday, February 16, 2012 2:55 PM
  • Hi HighTech,

    I'm not sure what's the meaning. If you use TPH, every subclass will derive all the properties of the super class. I'm not sure why you said it doesn't make sense. Based on the issue, you have exist database and you can do the work with EDMX mapping, I suggest you to use database first and use the designer to realize TPH, it seems more easy.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, February 17, 2012 7:39 AM
  • Hi Allen,

    Yes. Subclass derives all the properties of the super class. so why do i need that extra ID column? Subclass already derives the PersonID property from the superclass. I should be able to map the subclass to the PersonID of the superclass.


    HighTech

    Friday, February 17, 2012 2:18 PM
  • Hi HighTech,

    In the code, you create subclasses derive from superclass, but when you save it into database, it will insert into superclass table, the superclass has this column, so you have to assign the value to it.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, February 20, 2012 7:20 AM
  • I didnt get what you mean by that.

    In this case, superclass has PersonID and subclass inherits it. So subclass will write to PersonID column. It should not need another column "Person_PersonID". It is duplicated data as well as violates the data integrity.

    If I have 10 subclasses, I need 11 PersonIDs [ex. PersonID, Person_PersonID1, Person_PersonID2, ..., Person_PersonID10 ]column in the table. For each subclass data, 9 of PersonID columns would be null.

    If I have 20 or 100 subclasses, imagine the table will be huge with that many repeated columns.

    This will even worse if table has composite keys. Then you will have to create that many duplicated composite columns for each subclass.

    Superclass and subclass shares the key property/column so they should be able to point and write to the same property/column.


    HighTech

    Friday, February 24, 2012 4:22 PM