EF 4.1 & TPC inheritance

Question

Hello Team, firstly congratulations for your success about this framework.

We are developing a project in VS 2010 with EF 4.0. We start testing the first CTP and we decided to use it and continue developing in a normal way. Logically we had some problems with the CTPs versions but with every new version, some of these problems were fixed or implemented. When the RC version was released, we had none of the problems we had in the begining.

We are using basic TPC inheritance and works fine with the EF 4.1 RC. But now we are testing the application with the final release and the TPC inheritance is not working. We get the error 3013. The scenario is the following (the model was simplified with the most significant information):

We have three tables in a SQL Server 2008 Standard Edition:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/* Table Clients */
CREATE TABLE [dbo].[Clients](
	[ClientId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED 
(
	[ClientId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Clients] ADD CONSTRAINT [DF_Clients_ClientId] DEFAULT (newid()) FOR [ClientId]
GO

/* Table Cars */
CREATE TABLE [dbo].[Cars](
	[VehicleId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
	[ClientId] [uniqueidentifier] NOT NULL,
	[Doors] [int] NOT NULL,
 CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED 
(
	[VehicleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Cars] WITH CHECK ADD CONSTRAINT [FK_Cars_Clients] FOREIGN KEY([ClientId])
REFERENCES [dbo].[Clients] ([ClientId])
GO

ALTER TABLE [dbo].[Cars] CHECK CONSTRAINT [FK_Cars_Clients]
GO

ALTER TABLE [dbo].[Cars] ADD CONSTRAINT [DF_Cars_VehicleId] DEFAULT (newid()) FOR [VehicleId]
GO

/* Table Motos */
CREATE TABLE [dbo].[Motos](
	[VehicleId] [uniqueidentifier] NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
	[ClientId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Motos] PRIMARY KEY CLUSTERED 
(
	[VehicleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Motos] WITH CHECK ADD CONSTRAINT [FK_Motos_Clients] FOREIGN KEY([ClientId])
REFERENCES [dbo].[Clients] ([ClientId])
GO

ALTER TABLE [dbo].[Motos] CHECK CONSTRAINT [FK_Motos_Clients]
GO

And we have the code to access the database:

  public class GarageEntities : DbContext
  {
    public GarageEntities()
      : base("Data Source=localhost; Initial Catalog=Garage; User Id=garage; Password=garage; MultipleActiveResultSets=True; Application Name=Garage Application")
    {
    }

    public DbSet<Client> Clients { get; set; }
    public DbSet<Vehicle> Vehicles { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      base.OnModelCreating(modelBuilder);

      modelBuilder.Entity<Car>().Map(config => config.MapInheritedProperties());
      modelBuilder.Entity<Moto>().Map(config => config.MapInheritedProperties());
    }
  }

  [Table("Clients")]
  public class Client
  {
    [Key,
     Column("ClientId"),
     DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [Required(AllowEmptyStrings = false),
     MaxLength(50)]
    public string Name { get; set; }

    [InverseProperty("Client")]
    public virtual ICollection<Vehicle> Vehicles { get; set; }
  }

  public abstract class Vehicle
  {
    [Key,
     Column("VehicleId"),
     DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    [Required(AllowEmptyStrings = false),
     MaxLength(50)]
    public string Description { get; set; }

    public Guid ClientId { get; set; }

    [ForeignKey("ClientId")]
    public virtual Client Client { get; set; }
  }

  [Table("Cars")]
  public class Car : Vehicle
  {
  }

  [Table("Motos")]
  public class Moto : Vehicle
  {
    [Range(2, 6)]
    public int Doors { get; set; }
  }

When we create any query to any DbSet we get the error:

error 3013: Problem in Mapping Fragments starting at line 6: Missing table assignation: Foreign key restriction 'Client_Vehicles' of table Vehicle (ClientId) to the table Client (ClientId):; There is no assignation for the table Vehicle.

Any ideas? With the EF 4.1 RC works perfectly.

Thank you very much.

Regards,

Miguel Fernández Corral.

Answer 1

I think in Table per Concrete Class there is a table for each class, and each of those tables has a column for every property of that type,

While the EF runtime supports TPC, the designer doesn't, and using TPC in the EF forces you to avoid associations in your base type. Because of these issues we generally discourage the use of TPC with the Entity Framework.

So why not try to use TPT? Because I can't find anything wrong in your code.

Thanks

Tony

Answer 2

Hello Miguel and Tony,

 

Your issue has been explained by Diego, for your convenient, I quote his reply here:

"

What you are describing seems to match a an issue we found very late in EF 4.1 RTW cycle and we decided to postpone because our evaluation indicated that it had very low impact: while this unnecessary table is created for an abstract base type, the table does not participate in any mapping, therefore it shouldn't appear in any query produced by EF.

For case in which you are using Code First to generate the database, the table will be added to the database schema but never used. For cases in which you are using the Code First API to map to an existing database it shouldn't have an impact either, since no queries generated by EF will every fail because the table is not there.

If you are seeing other behavior or higher impact, please let us know.

By the way, I am not aware of a workaround tha just removes the table without changing the shape of your model.

Hope this helps,
Diego

"

Have a nice day,

---

Jackie Sun [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.

Answer 3

Hi Miguel and Jackie,

It might sounds surprising but your issue has nothing to do with the one that I brought up in this thread!  

You have been bitten by an issue that even has nothing to do with Code First, it's a limitation of EF in general when it comes to Polymorphic Associations with TPC mapping as I explained in my TPC article under the title "Polymorphic Associations with TPC is Problematic".

Let me clarify. When talking about polymorphic associations in TPC, we need to consider 2 different scenarios in terms of multiplicities. For example in your case, you won't be able to create a one-to-many association from Client to Vehicle because then both subclass tables would need a foreign key reference to the Client table and EF does not natively support this scenario. So this is not possible:

//This is NOT Possible!
public abstract class Vehicle
{ 
 public Guid Id { get; set; } 
 public string Description { get; set; }
 
 public virtual Client Client { get; set; }
}
 
public class Client
{ 
 public Guid Id { get; set; } 
 public string Name { get; set; }
 public Guid VehicleId { get; set; } 

 public virtual ICollection<Vehicle> Vehicles { get; set; }
}

 

That said, the only way to create a polymorphic association in a TPC mapping like this is to create a many-to-one association from Client to Vehicle which means the Clients table would need a single foreign key column which would have to refer both concrete subclass tables:

public abstract class Vehicle
{ 
 public Guid Id { get; set; } 
 public string Description { get; set; }
 
 public virtual ICollection<Client> Client { get; set; }
}
 
public class Client
{ 
 public Guid Id { get; set; } 
 public string Name { get; set; }
 public Guid VehicleId { get; set; } 

 public virtual Vehicle Vehicle { get; set; }
} 
 
public class Car : Vehicle { }
 
public class Moto : Vehicle
{ 
 public int Doors { get; set; }
}
 
public class Context : DbContext
{ 
 public DbSet<Client> Clients { get; set; }
 public DbSet<Vehicle> Vehicles { get; set; }

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
 modelBuilder.Entity<Car>().Map(m => 
 {
 m.MapInheritedProperties();
 m.ToTable("Cars");
 });

 modelBuilder.Entity<Moto>().Map(m => 
 {
 m.MapInheritedProperties();
 m.ToTable("Motos");
 });
 }
}

That’s why the title in my article reads as “Polymorphic Associations with TPC are Problematic” and not impossible.  

In a nutshell, a one-to-many association is not possible but many-to-one associations are. In other words, in TPC mapping you always have to keep the foreign key outside of the inheritance hierarchy or you have to change your strategy to TPH or TPT.

Hope this helps, 

Morteza

Answer 4

Hello Morteza, try it in EF 4.0 CTP5 and/or EF 4.1 RC, it works perfectly and the model is correct, because if my base class vehicle is not abstract that means that you can create Vehicles (then a table in the database must exists) also this is TPT and I don't want to do a join between Vehicles and Cars every time I query the cars table, and more, I don't allow to create Vehicles itself, only cars or motos.

On the other hand, my car is mine, then a vehicle has one owner, not several. I could map the datamodel without inheritance, but every client will have two navigation properties, one per cars and one per motos, if I can't do I'm trying (a simple inheritance in OOP), what is sense to make a framework that make SQL servers and OOP be closer?

See you,

Miguel.

Answer 5

Miguel,

No, we cannot have a one to many polymorphic association when using TPC mapping, this is a native limitation of EF so your object model as it stands won't work based on any build of the EF be it CTP5 or RC or RTW, this also has been confirmed by the EF team :)

But like I said this limitation does not exist in other inheritance mapping strategies and from what you described, your object model sounds like to be a good candidate for a TPT mapping which would not expose you to this problem.

Thanks,

Morteza

 

Answer 6

Hello Morteza, I am developing a project right now that works perfecty, I start with the CTP5 and that model works, then I updated to RC and stills works, but with the RTW stop working and I back to the RC to continue developing. Is a known issue, you can read it:

http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/30086074-6773-4b83-bed5-88a401fb2a4e

See you,

Miguel.