none
Entity Framework - Fluent API - Table per hierarchy - exception RRS feed

  • Question

  • An Order has zero or one OrderActionEntered, OrderActionCompleted and OrderActionProcessed.

     

    Actions are stored in one table using Table per Hierarchy(TPH)

     

     

    When I run the below code from console application, it throws exception :Invalid column name 'EnteredAction_ActionId'.\r\nInvalid column name 'ProcessedAction_ActionId

     

     

    EDISContext

     

    db = new EDISContext

    ();

     

    var

    ord1 = db.Orders.FirstOrDefault(o => o.OrderId == 1);

     

    OrderActionEntered ae1 = new OrderActionEntered

    ();

    ae1.ActionDoneBy=1;

    ae1.ActionDT =

    DateTime

    .Now;

    ae1.Order = ord1;

    db.OrderActions.Add(ae1);

    db.SaveChanges();

     

    //--Classes

    namespace

     

    ABCDE

    {

     

     

    public class

    Order

    {

     

    public int OrderId { get; set; }

    //PK

     

    public string Name { get; set

    ; }

     

    public OrderAction EnteredAction { get; set

    ; }

     

    public OrderAction ProcessedAction { get; set

    ; }

     

    public OrderAction CompletedAction { get; set

    ; }

    }

     

     

    public class

    OrderAction

    {

     

    public int ActionId { get; set

    ; }

     

    public DateTime ActionDT { get; set

    ; }

     

    public Order Order { get; set

    ; }

    }

     

    public class OrderActionCompleted :

    OrderAction

    {

    }

     

    public class OrderActionDoneBy :

    OrderAction

    {

     

    public int ActionDoneBy { get; set

    ; }

    }

     

    public class OrderActionEntered :

    OrderActionDoneBy

    {

    }

     

    public class OrderActionProcessed :

    OrderActionDoneBy

    {

     

    public bool IsDoneByStaff { get; set

    ; }

    }

     

    }

     

    //--Mapping

    namespace

     

    ABCDE

    {

     

    public class EDISContext:

    DbContext

    {

     

    public

    EDISContext()

    :

    base("EDISContext"

    )

    { }

     

     

    public DbSet<Order> Orders { get; set

    ; }

     

    public DbSet<OrderAction> OrderActions { get; set

    ; }

     

     

    protected override void OnModelCreating(DbModelBuilder

    modelBuilder)

    {

    modelBuilder.Entity<

    Order>().ToTable("Orders"

    );

    modelBuilder.Entity<

    OrderAction>().ToTable("OrderActions"

    );

     

    modelBuilder.Entity<

    OrderAction

    >().HasKey(k =>k.ActionId);

    modelBuilder.Entity<

    OrderAction>().Map<OrderActionEntered>(m => m.Requires("ActionType").HasValue("Entered"

    ))

    .Map<

    OrderActionCompleted>(m => m.Requires("ActionType").HasValue("Completed"

    ))

    .Map<

    OrderActionProcessed>(m => m.Requires("ActionType").HasValue("Processed"

    ))

    .Map<

    OrderActionDoneBy>(m => m.Requires("ActionType").HasValue("Doneby"

    ));

     

    modelBuilder.Entity<

    OrderAction>().HasRequired(a => a.Order).WithRequiredDependent(o=>o.EnteredAction).Map(m => m.MapKey("OrderId"

    ));

    modelBuilder.Entity<

    OrderAction>().HasRequired(a => a.Order).WithRequiredDependent(o => o.ProcessedAction).Map(m => m.MapKey("OrderId"

    ));

    modelBuilder.Entity<

    OrderAction>().HasRequired(a => a.Order).WithRequiredDependent(o => o.CompletedAction).Map(m => m.MapKey("OrderId"

    ));

    }

    }

    }

    Saturday, November 26, 2011 4:52 AM

All replies

  • Hi prasadKR,

    Welcome!

    Based on my test, the code will create "EnteredAction_ActionId" and "PrcessedAction_ActionId" to map OrderActions, you can easily watch the database structure in SQL Server.  Database uses foreign key to keep a relationship, it couldn't recognise the distinct field. Here is my test code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    
    namespace ConsoleApplication15
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var context= new EDISContext() )
                { 
                    EDISContext db = new EDISContext();
                    var order = new Order() {  Name="test"};
                    //OrderActionEntered ae1 = new OrderActionEntered();
                    OrderActionProcessed ae1 = new OrderActionProcessed();
                    ae1.ActionDoneBy = 1;
                    ae1.ActionDT = DateTime.Now;
                    ae1.IsDoneByStaff = false;
                    ae1.Order = order;
                    db.OrderActions.Add(ae1);
                    db.SaveChanges();
                }
            }
        }
        public class Order
        {
            public int OrderId { get; set; }
            //PK
            public string Name
            {
                get;
                set;
            }
        public OrderAction EnteredAction
            {
                get;
                set;
            }
        public OrderAction ProcessedAction
        {
            get;
            set;
        }
        public OrderAction CompletedAction
        {
            get;
            set;
        }
        }
        public class OrderAction
        {
            public int ActionId { get; set ; } 
            public DateTime ActionDT { get; set ; } 
            public Order Order { get; set ; } 
        }
    public class OrderActionCompleted : OrderAction
    {
     
    }
    public class OrderActionDoneBy : OrderAction
    {
    public int ActionDoneBy { get; set ; } 
    }
    public class OrderActionEntered : OrderActionDoneBy
    {
    }
     public class OrderActionProcessed : OrderActionDoneBy
    {
    public bool IsDoneByStaff { get; set ; } 
    }
     public class EDISContext : DbContext
     {
         public DbSet<Order> Orders { get; set; }
         public DbSet<OrderAction> OrderActions { get; set; }
         protected override void OnModelCreating(DbModelBuilder modelBuilder)
         {
             modelBuilder.Entity<Order>().ToTable("Orders");
             modelBuilder.Entity<OrderAction>().ToTable("OrderActions");
             modelBuilder.Entity<OrderAction>().HasKey(k => k.ActionId);
             modelBuilder.Entity<OrderAction>().Map<OrderActionEntered>
                 (m => m.Requires("ActionType").HasValue("Entered"))
                 .Map<OrderActionCompleted>(m => m.Requires("ActionType").HasValue("Completed"))
                 .Map<OrderActionProcessed>(m => m.Requires("ActionType").HasValue("Processed"))
                 .Map<OrderActionDoneBy>(m => m.Requires("ActionType").HasValue("Doneby"));
             modelBuilder.Entity<OrderAction>().HasRequired(a => a.Order)
                 .WithOptional(o => o.EnteredAction).Map(m => m.MapKey("OrderId"));
             modelBuilder.Entity<OrderAction>().HasRequired(a => a.Order)
                 .WithOptional(o => o.ProcessedAction).Map(m => m.MapKey("OrderId"));
             modelBuilder.Entity<OrderAction>().HasRequired(a => a.Order)
                  .WithOptional(o => o.CompletedAction).Map(m => m.MapKey("OrderId"));
         }
     }
    
    }
    
    

    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.

    Monday, November 28, 2011 7:30 AM
    Moderator
  • But that is not what is needed here.

    Orders table is related to Actions table, with OrderId stored in Action table as a foreign key.

    Order has zero to one ProcessedAction or EnteredAction etc..

     

    I tried Table per type also, but no luck there also. I want to have a relationship to a child/derived class, but unable to find a way to do it with Entity framework. please look at a more simplified version of this issue on another thread: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/69d9145a-f7ec-4845-b01a-26e57e9ba16e

     

    Monday, November 28, 2011 3:45 PM
  • any one done similar thing?
    Tuesday, November 29, 2011 9:02 PM