none
Code First, Compound Keys and Inheritance RRS feed

  • Question

  • Is it possible to create a compound key in EF code first on a class, using two properites - one of which is inherited from a base class.

     

    For example, if using the classes below, is it possible to use SuperAssociate.ID and SuperAssociate.SoftwareID as a compound primary key?

     

    Public Class Associate

        Public Overridable Property ID As Integer Implements

        Public Overridable Property Address As PostalAddress

        Public Overridable Property Fax As String Implements

        Public Overridable Property Name As CompanyName

        Public Overridable Property Owner As HumanName

        Public Overridable Property Telephone As String

        Public Overridable Property Cancelled As Date?

        Public Overridable Property Registered As Date

        Public Overridable Property Email As String

    End Class

     

    Public Class SuperAssociate
        Inherits Associate

        Public Property SoftwareID As Integer

        Public Property Software As MembershipSoftware

    End Class

     

    I have tried with little success so far. I've tried the following fluent api combinations...

      modelBuilder.Entity(Of Associate).ToTable("Associates")
      modelBuilder.Entity(Of Associate).HasKey(Function(t) t.ID)
      modelBuilder.Entity(Of Associate).Property(Function(t) t.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
      modelBuilder.Entity(Of SuperAssociate).HasKey(Function(t) t.SoftwareID)
      modelBuilder.Entity(Of SuperAssociate).ToTable("Associates")

    and this...

      modelBuilder.Entity(Of Associate).ToTable("Associates")
      modelBuilder.Entity(Of Associate).HasKey(Function(t) t.ID)
      modelBuilder.Entity(Of Associate).Property(Function(t) t.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
      modelBuilder.Entity(Of SuperAssociate).HasKey(Function(t) New With {t.SoftwareID, t.ID})  'composite key (id + software)
      modelBuilder.Entity(Of SuperAssociate).ToTable("Associates")

    and this...

      modelBuilder.Entity(Of Associate).ToTable("Associates")
      modelBuilder.Entity(Of Associate).Property(Function(t) t.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
      modelBuilder.Entity(Of SuperAssociate).HasKey(Function(t) New With {t.SoftwareID, t.ID})  'composite key (id + software)
      modelBuilder.Entity(Of SuperAssociate).ToTable("Associates")

     

    None of the above work. Either get an mapping error saying property ID doesnt exist on entity SuperAssociate, or if the db creation is successfull, it seems to ignore the fluent api commands and creates the table with a primary key consising of only the ID column.

     

    Please help!


    ding_jimmy
    • Edited by ding_jimmy Tuesday, December 6, 2011 1:12 PM
    Tuesday, December 6, 2011 1:02 PM

Answers

  • Hi ding_jimmy,

    Welcome!

    Based on my testing, It seems it is impossible to use Composite Primary key in the inheritance scenario. Thanks for understanding.

    I think we should put the basic properties in Base class, If we move "SoftWareId" to Association class, everything is OK.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations;
    
    namespace ConsoleApplication22
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db= new Context())
                {
    
                    db.Database.CreateIfNotExists();
                }
            }
        }
        public class Associate
        {
            
            public int Id { get; set; }
            public int SoftwareId { get; set; }
            public string Name { get; set; }
        }
        public class SuperAssociate:Associate
        {      
            public string  Address { get; set; }
        }
        public class Context:DbContext
        {
            public DbSet<Associate> Associates { get; set; }
            public DbSet<SuperAssociate> SuperAssociates { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Associate>().ToTable("Associate");
                modelBuilder.Entity<Associate>().HasKey(l => new { l.Id, l.SoftwareId});
                modelBuilder.Entity<SuperAssociate>().Map(m =>
                {
                    m.MapInheritedProperties();
                    m.ToTable("SuperAssociate");
                });
    
            }
        }
    }
    
    

    For inheritance in Code Frist: TPH, TPT, TPC, you can refer Fluent API here: http://blogs.msdn.com/b/adonet/archive/2010/12/06/ef-feature-ctp5-fluent-api-samples.aspx

    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.

    • Marked as answer by ding_jimmy Wednesday, December 7, 2011 11:46 AM
    Wednesday, December 7, 2011 9:13 AM
    Moderator

All replies

  • Hi ding_jimmy,

    Welcome!

    Based on my testing, It seems it is impossible to use Composite Primary key in the inheritance scenario. Thanks for understanding.

    I think we should put the basic properties in Base class, If we move "SoftWareId" to Association class, everything is OK.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations;
    
    namespace ConsoleApplication22
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db= new Context())
                {
    
                    db.Database.CreateIfNotExists();
                }
            }
        }
        public class Associate
        {
            
            public int Id { get; set; }
            public int SoftwareId { get; set; }
            public string Name { get; set; }
        }
        public class SuperAssociate:Associate
        {      
            public string  Address { get; set; }
        }
        public class Context:DbContext
        {
            public DbSet<Associate> Associates { get; set; }
            public DbSet<SuperAssociate> SuperAssociates { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Associate>().ToTable("Associate");
                modelBuilder.Entity<Associate>().HasKey(l => new { l.Id, l.SoftwareId});
                modelBuilder.Entity<SuperAssociate>().Map(m =>
                {
                    m.MapInheritedProperties();
                    m.ToTable("SuperAssociate");
                });
    
            }
        }
    }
    
    

    For inheritance in Code Frist: TPH, TPT, TPC, you can refer Fluent API here: http://blogs.msdn.com/b/adonet/archive/2010/12/06/ef-feature-ctp5-fluent-api-samples.aspx

    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.

    • Marked as answer by ding_jimmy Wednesday, December 7, 2011 11:46 AM
    Wednesday, December 7, 2011 9:13 AM
    Moderator
  • Hi Alan

     

    Dam. I was thinking the same, but was hoping I had missed somthing obvious. 

     

    Thank you for your help!


    ding_jimmy
    • Edited by ding_jimmy Wednesday, December 7, 2011 11:50 AM Poor use of english!
    Wednesday, December 7, 2011 11:48 AM