none
Possible bug in TPH Code First mapping RRS feed

  • Question

  • I have two tables

     

    CREATE TABLE Program (
    BasicId INT NOT NULL IDENTITY(1,1),
    Name VARCHAR(50) NOT NULL
    )
    
    CREATE TABLE Layer (
    LayerId INT NOT NULL IDENTITY(1,1),
    BasicId INT NOT NULL,
    Name VARCHAR(50) NOT NULL,
    IsQuote BIT NOT NULL DEFAULT(0)
    )
    

     

    These tables map to the following POCO classes

     

    public class Program
    {
      public int BasicId {get;set;}
      public string Name {get;set;}
      public virtual IList<RealLayer> RealLayers {get;set;}
      public virtual IList<QuotedLayer> QuotedLayers {get;set;}
    }
    
    public Layer
    {
      public int LayerId {get;set;}
      public string Name {get;set;}
    }
    
    public RealLayer : Layer
    {
      public int BasicId {get;set;}
      public virtual Program Program {get;set;}
    }
    
    public QuotedLayer : Layer
    {
      public int BasicId {get;set;}
      public virtual Program Program {get;set;}
    }
    

     


    These classes are mapped using the following Fluent API (For brevity the addition of the Configuration classes to the modelBuilder is not shown).

     

        public class ProgramConfiguration : EntityTypeConfiguration<Program>
        {
            public ProgramConfiguration()
            {
                HasKey(p => p.BasicId);<br/>            HasMany(p => p.RealLayers).WithRequired(l => l.Program).HasForeignKey(p => p.BasicId);
                HasMany(p => p.QuotedLayers).WithRequired(l => l.Program).HasForeignKey(p => p.BasicId);
            }
        }
     
        public class LayerConfiguration : EntityTypeConfiguration<Layer>
        {
            public LayerConfiguration()
            {
                Map<RealLayer>(m => m.Requires("IsQuote").HasValue(false));
                Map<QuotedLayer>(m => m.Requires("IsQuote").HasValue(true));
            }
        }
    
        public class RealLayerConfiguration : EntityTypeConfiguration<RealLayer>
        {
            public RealLayerConfiguration()
            {
                HasRequired(l => l.Program).WithMany().HasForeignKey(l => l.BasicId);
            }
        }
    
        public class QuotedLayerConfiguration : EntityTypeConfiguration<QuotedLayer>
        {
            public QuotedLayerConfiguration()
            {
                HasRequired(l => l.Program).WithMany().HasForeignKey(l => l.BasicId);
            }
        }
    

    This is where the bug comes in. Which ever IList<> property is declared FIRST in the Program class will load successfully. The second one declared returns the following error "Invalid column name 'BasicId1'. Invalid column name 'BasicId1'." And a SQL trace shows the following SQL being executed for the second list property.

    exec sp_executesql N'SELECT 
    ''0X0X'' AS [C1], 
    [Extent1].[LayerId] AS [LayerId], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[BasicId1] AS [BasicId1]
    FROM [dbo].[Layer] AS [Extent1]
    WHERE ([Extent1].[IsQuote] = 1) AND ([Extent1].[BasicId1] = @EntityKeyValue1)',N'@EntityKeyValue1 int',@EntityKeyValue1=176432
    


    If this isn't a bug and I have the configuration wrong, please feel free to point that out. I've been through several permutations of configurations and this is the one that has gotten me the closest. Ideally the BasicId and Program properties would reside on the Layer class not the derived classes but housing the properties there returned a different error.

    Thanks for any and all assistance.

    Rob Epstein

     

    Friday, November 11, 2011 3:01 PM

All replies

  • Hi Rob,

    The two relation couldn't create on one FK, as the same name with "BisicId" Code Frist append "1" to distinguish them.  You just need to create on relation between the two entities and let "IsQuote" to distinguish the two type, the logic should put in your code instead of database.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, November 16, 2011 7:19 AM
    Moderator
  • Alan,

    I'm sorry for not understanding but would you mind terribly providing a revised class definition/configuration that will accomplish what you stated. The schema can't change. The Program table relates to the Layer table in a one-to-many way on BasicId (PK on Program, FK on Layer). This is standard normalization stuff and I would hope that EF could handle this. The configuration as coded already differentiates on "IsQuote". I had also tried making BasicId and Program properties on Layer as opposed to RealLayer and QuotedLayer but ran into a different issue which is why those properties now reside on the derived classes, as well as the HasRequired mapping.

    Thanks for your time,

    Rob

    Wednesday, November 16, 2011 12:15 PM
  • Alan,

    I was really hopeful regarding your answer to my problem. Could you please spare a minute or two to better explain your answer. My apologies but I think it's the translation to English that has confused me. Mind posting a quick code snippet as an example regarding your solution?

    Thanks again for your time,
    Rob 

    Sunday, November 20, 2011 1:44 AM
  • You've probably worked this out by now, but in case anyone else comes across this - you might want to try declaring the BasicId as a property on your base class (Layer) instead of the 2 derived classes.

    I had a similar error message, but mine was caused by declaring the property I was using as the 'discriminator' to differentiate between the subtypes on my abstract base class.  

    Friday, December 9, 2011 2:18 AM
  • Unfortunately this appears to be as designed behavior. I have not found a solution and I had already tried placing BasicId on the base class to no avail. My "solution" ended up being adding an IList<Layer> AllLayers property then changing RealLayers and QuotedLayers to be readonly, non-mapped properties that return filtered results from AllLayers as appropriate. Kind of sucks but it is what it is.
    Sunday, December 11, 2011 1:56 PM