none
EF5 Beta 1 'Invalid Column Name' Bug? Code First, Exisiting Database RRS feed

  • Question

  • Hi

    I may have found a bug in EF5 Beta 1 Code First Existing Database, either that or I'm trying to map to this existing database the wrong way.
    I have managed to reproduce this bug with a simplified existing database, the database has 2 tables details below;

    DomesticatedAnimals         
    AnimalId
    Name
    OwnersName

    Canines
    CanineId
    IsLeashTrained
    IsHouseTrained

    AnimalId and CanineId are the Primary Keys, there is a Foreign Key between CanineId and AnimalId effectively allowing a 0.1 to 1 relationship.

    In my DbContext I'm mapping DomesticatedAnimal to Pet and Canine to Dog (this is to simulate the mapping I'm doing on a poorly designed database) I'm also changing the names of the Primary Key columns respectively. See code..

    			modelBuilder.Entity<Pet>()
    				.Map(m => m.ToTable("DomesticatedAnimals"))
    				.Map<Dog>(m => m.ToTable("Canines"));
    
    			modelBuilder.Entity<Pet>().HasKey(p => p.PetId);
    			modelBuilder.Entity<Pet>().Property(p => p.PetId).HasColumnName("DomesticatedAnimalId");
    
    			modelBuilder.Entity<Dog>().HasKey(p => p.DogId);
    			modelBuilder.Entity<Dog>().Property(p => p.DogId).HasColumnName("CanineId");

    Upon executing a simple loop over each Dog in the database I get an exception as follows;
    System.Data.EntityCommandExecutionException was unhandled
      HResult=-2146232004
      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)...

    Upon looking at the SQL that gets executed for the context.Dogs DBSet I see an error in the SQL query as follows.

    {SELECT
    '0X0X' AS [C1],
    [Extent1].[DomesticatedAnimalId] AS [DomesticatedAnimalId],
    [Extent1].[Name] AS [Name],
    [Extent1].[OwnersName] AS [OwnersName],
    [Extent2].[CanineId] AS [CanineId],
    [Extent2].[IsLeashTrained] AS [IsLeashTrained],
    [Extent2].[IsHouseTrained] AS [IsHouseTrained]
    FROM  [dbo].[DomesticatedAnimals] AS [Extent1]
    INNER JOIN [dbo].[Canines] AS [Extent2] ON [Extent1].[DomesticatedAnimalId] = [Extent2].[DomesticatedAnimalId]}

    So the problem seems to be that the query generated is using the DomesticAnimalId column name on Extent2 instead of the CanineId column name.

    Hope this hasn't already been reported, I couldn't see it when searching. I have attached source code, bear in mind its Visual Studio 2011 targeting .NET 4.5

    https://skydrive.live.com/embed?cid=F752E788702B125C&resid=F752E788702B125C%21626&authkey=AFLURZS2n4xVba4


    • Edited by Ben-White Thursday, March 15, 2012 6:06 PM
    Thursday, March 15, 2012 6:02 PM

Answers

  • Hi The Duke2k,

    In the ADO.NET Team Blog, there's an article - "EF4.3.1 and EF5 Beta 1 Available on NuGet", at the end of this article, it said,

    "We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow.

    We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum."

    So, I think you can post the pre-released version question there.

    Best Regards


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

    Tuesday, March 20, 2012 8:27 AM
    Moderator

All replies

  • Update!

    I've also tried splitting the one entity of 2 tables and it makes the same SQL query with the same problem.

    Does anyone have any ideas how to work around this issue? Iit's preventing me moving forward with my project.

    Ben

    Friday, March 16, 2012 12:12 PM
  • Hi TheDuke2k,

    Welcome to MSDN Forum.

    I will test it and come back as soon as possible.

    Best Regards


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

    Monday, March 19, 2012 8:25 AM
    Moderator
  •  Thanks Allen

    Look forward to hearing back from you.

    Ben

    Monday, March 19, 2012 11:45 AM
  • Hi TheDuke2k,

    I have tested the code and I can repro the issue. But, I don't think it's a bug of EF5.0, because I create a same database and copy the code in EF4.3.1 and VS2010 .NET4.0, the exception is thrown too.

    The recommended way to define a model with table-per-type inheritance is by using the ADO.NET Entity Data Model Tools. For more information, see Walkthrough: Mapping Inheritance - Table-per-Type.

    Best Regards


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

    Tuesday, March 20, 2012 6:38 AM
    Moderator
  • Hi Allen

    I'm not really trying to implement TPT, I just have an entity that I want to hydrate from 2 different tables unfortunately the tables have different primary key column names.  I don't see how this behaviour can be by design especially while using the fluent mapping API and code first.  It would seem like a simple fix too as surely the SQL command generator just needs to pay attention to my specified mapping when joining the two tables together.

    I had hoped to use EF Migrations and I'm not sure if this is possible while using the model tools.  Is there somewhere I can report this as a bug or at least let the EF team know about this.

    Many Thanks

    Ben


    • Edited by Ben-White Tuesday, March 20, 2012 7:55 AM Clarification
    Tuesday, March 20, 2012 7:51 AM
  • Hi The Duke2k,

    In the ADO.NET Team Blog, there's an article - "EF4.3.1 and EF5 Beta 1 Available on NuGet", at the end of this article, it said,

    "We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow.

    We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum."

    So, I think you can post the pre-released version question there.

    Best Regards


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

    Tuesday, March 20, 2012 8:27 AM
    Moderator
  • Thanks Allen

    Will try and keep this thread updated with anything I learn.

    Tuesday, March 20, 2012 9:47 AM