One crucial fact @slauma gave in comments. So look at the answer AND comments!
I'm trying to use components, that are actually used with NHibernate, with the EF6 now. The problem is, I have some TPT inheritances with primary keys of different names. The database and POCO classes are given and I can't change either of them so both CodeFirst and the EF designer are out of question.
Is there a way to just map an existing Db to existing POCO classes, like you do with these .hbm.xml mapping files in NHibernate?
UPDATE:
The actual problem I encountered is first of all the TPT mapping of several classes, whereby these classes have differently named primary keys, which seems not to be supported by code first.
So like:
public class Record
{
public virtual int Ndx { get; set; } // table column 'ndx'
public virtual DateTime CreatedAt { get; set; } // table column 'created'
// ... further properties
}
public class Patient : Record
{
public virtual int RecordNdx {get; set;} // table column 'record_ndx) with FK => records.ndx
// ... further properties
}
And as already said, changing property or column names is not an option.
Update II:
This is my registration code:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Record>()
.ToTable("record_descriptors", "schema");
modelBuilder.Entity<Record>()
.HasKey<int>(e => e.ndx);
modelBuilder.Entity<Record>()
.Property(e => e.read_flag)
.IsFixedLength()
.IsUnicode(false);
modelBuilder.Entity<Record>()
.Property(e => e.row_version)
.IsFixedLength();
modelBuilder.Entity<Record>()
.Property(e => e.update_info)
.IsUnicode(false);
modelBuilder.Entity<Patient>()
.ToTable("patienten", "schema");
modelBuilder.Entity<Patient>()
.Property(e => e.mpi)
.IsUnicode(false);
modelBuilder.Entity<Patient>()
.Property(e => e.ndx)
.HasColumnName("record_ndx");
modelBuilder.Entity<Patient>()
.Ignore(r => r.RecordNdx);
}
UPDATE III
For testing I use:
db.patients.First(p => p.Ndx == 6040);
And this produces following SQL (more extensive because of real record & patient classes):
SELECT
[Limit1].[C1] AS [C1],
[Limit1].[ndx] AS [ndx],
[Limit1].[owner_user_object_ndx] AS [owner_user_object_ndx],
[Limit1].[creator_department_user_object_ndx] AS [creator_department_user_object_ndx],
[Limit1].[creator_user_user_object_ndx] AS [creator_user_user_object_ndx],
[Limit1].[created] AS [created],
[Limit1].[read_flag] AS [read_flag],
[Limit1].[last_update] AS [last_update],
[Limit1].[last_update_user] AS [last_update_user],
[Limit1].[last_update_department] AS [last_update_department],
[Limit1].[freitext] AS [freitext],
[Limit1].[row_version] AS [row_version],
[Limit1].[update_info] AS [update_info],
[Limit1].[mpi] AS [mpi]
FROM ( SELECT TOP (1)
[Extent1].[ndx] AS [ndx],
[Extent1].[mpi] AS [mpi],
[Extent2].[owner_user_object_ndx] AS [owner_user_object_ndx],
[Extent2].[creator_department_user_object_ndx] AS [creator_department_user_object_ndx],
[Extent2].[creator_user_user_object_ndx] AS [creator_user_user_object_ndx],
[Extent2].[created] AS [created],
[Extent2].[read_flag] AS [read_flag],
[Extent2].[last_update] AS [last_update],
[Extent2].[last_update_user] AS [last_update_user],
[Extent2].[last_update_department] AS [last_update_department],
[Extent2].[freitext] AS [freitext],
[Extent2].[row_version] AS [row_version],
[Extent2].[update_info] AS [update_info],
'0X0X' AS [C1]
FROM [schema].[patienten] AS [Extent1]
INNER JOIN [schema].[record_descriptors] AS [Extent2] ON [Extent1].[ndx] = [Extent2].[ndx]
WHERE 6040 = [Extent1].[ndx]
) AS [Limit1]
which is wrong, as it selects [ndx] from [patienten] (must be record_ndx) and also tries to join over [ndx]
The remark about closing this work item at CodePlex claims that since EF 6 defining different key column names of parent and child entities in a TPT mapping works with Code-First. If that is true the following Code-First mapping should allow to map your model and database:
modelBuilder.Entity<Record>()
.ToTable("YourRecordTableName");
modelBuilder.Entity<Record>()
.HasKey(r => r.Ndx);
modelBuilder.Entity<Record>()
.Property(r => r.Ndx)
.HasColumnName("ndx"); // probably redundant because case doesn't matter
modelBuilder.Entity<Record>()
.Property(r => r.CreatedAt)
.HasColumnName("created");
modelBuilder.Entity<Patient>()
.ToTable("YourPatientTableName");
modelBuilder.Entity<Patient>()
.Property(r => r.Ndx) // Yes, no typo: It must be Ndx, NOT RecordNdx !
.HasColumnName("record_ndx");
modelBuilder.Entity<Patient>()
.Ignore(r => r.RecordNdx);
The last mapping (ignoring the RecordNdx property) is important. It means that your key property will be Patient.Ndx. I don't think that you can make any property in a derived class a key property. The key property must always be in the base class of the inheritance hierarchy. However this property can be mapped twice (or generally once per entity in a TPT inheritance chain) to different column names per table - since EF 6.
Getting rid of the RecordNdx property completely would be the cleanest solution. But since you said you can't touch your properties it would at least make sense to couple the value of the RecordNdx to the Ndx property (if you can change the property getter and setter):
public virtual int RecordNdx
{
get { return Ndx; }
set { Ndx = value; }
}
Edit
I just tested the Code-First mapping above with EF 6.1 and it works indeed! Primary key column in Record table is ndx and in Patient table it is record_ndx. Between those EF creates the one-to-one relationship that is required for TPT mapping.
Edit 2
That's the complete test program I have used (current EF 6.1 Nuget package, .NET 4.5, VS 2012, SQL Server 2012 Express):
using System;
using System.Data.Entity;
namespace EFTPT6
{
public class Record
{
public virtual int Ndx { get; set; }
public virtual DateTime CreatedAt { get; set; }
}
public class Patient : Record
{
public virtual int RecordNdx { get; set; }
public virtual string Name { get; set; }
}
public class MyContext : DbContext
{
public DbSet<Record> Records { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Record>()
.ToTable("Records");
modelBuilder.Entity<Record>()
.HasKey(r => r.Ndx);
modelBuilder.Entity<Record>()
.Property(r => r.Ndx)
.HasColumnName("ndx");
modelBuilder.Entity<Record>()
.Property(r => r.CreatedAt)
.HasColumnName("created");
modelBuilder.Entity<Patient>()
.ToTable("Patients");
modelBuilder.Entity<Patient>()
.Property(r => r.Ndx)
.HasColumnName("record_ndx");
modelBuilder.Entity<Patient>()
.Ignore(p => p.RecordNdx);
}
}
class Program
{
static void Main(string[] args)
{
Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());
using (var ctx = new MyContext())
{
ctx.Database.Initialize(true);
string sql = ctx.Records.ToString();
}
}
}
}
The string sql at the end of the program is:
SELECT
CASE WHEN ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)))
THEN '0X'
ELSE '0X0X'
END AS [C1],
[Extent1].[ndx] AS [ndx],
[Extent1].[created] AS [created],
CASE WHEN ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL)))
THEN CAST(NULL AS varchar(1))
ELSE [Project1].[Name]
END AS [C2]
FROM [dbo].[Records] AS [Extent1]
LEFT OUTER JOIN (SELECT
[Extent2].[record_ndx] AS [record_ndx],
[Extent2].[Name] AS [Name],
cast(1 as bit) AS [C1]
FROM [dbo].[Patients] AS [Extent2] ) AS [Project1]
ON [Extent1].[ndx] = [Project1].[record_ndx]
It looks that the mapping is respected, i.e. the Records and Patients tables are joined by the ndx and record_ndx column.
Edit 3
It's important that the context class does not contain a set for the derived entity, i.e. no public DbSet<Patient> Patients { get; set; }. If it does the mapping modelBuilder.Entity<Patient>().Property(r => r.Ndx).HasColumnName("record_ndx"); is ignored and EF expects that the primary key name in Patient is ndx and not record_ndx. For example the last line in the SQL above becomes ON [Extent1].[ndx] = [Project1].[ndx].