I'm working with an existing database and using EF 4.3 Code First. I have an entity hierarchy that looks like this, where Note is the base class:
Note
- CompanyNote
- OrderNote
- etc
I'm using TPH with a discriminator column having the following mapping:
Map<CompanyNote>(t => t.Requires("type").HasValue("company"));
Map<OrderNote>(t => t.Requires("type").HasValue("order"));
The database type of type
is char(18)
. EF generates sql as if its nchar
:
SELECT /* columns */
FROM [dbo].[notes] AS [Extent1]
WHERE [Extent1].[type] = N'company'
That N
is a problem because this table has many thousands of rows and it prevents SQL from using an index. I need to query to look this way:
SELECT /* columns */
FROM [dbo].[notes] AS [Extent1]
WHERE [Extent1].[type] = 'company'
Here's what I've tried so far:
- Adding a
Type
property and mapping it withProperty(t => t.Type).IsFixedLength().HasMaxLength(18).IsUnicode(false);
- Adding column configuration to the inheritance mapping with
Map<CompanyNote>(t => t.Requires("type").HasValue("company").IsFixedLength().HasMaxLength(18).IsUnicode(false));
Neither changes made a difference. Unfortunately, I can't change the database column type to nchar
.
How can I tell Entity Framework that the discriminator column is of type char
?
Update: Here's a complete example
[TestFixture]
public class TphMappingFixture
{
[Test]
public void CompanyNotesQueryShouldNotHaveUnicodeDiscriminator()
{
string sql;
using (TphTestContext context = new TphTestContext())
{
sql = context.CompanyNotes.ToString();
}
Console.WriteLine(sql);
/* sql is:
SELECT
'0X0X' AS [C1],
[Extent1].[id] AS [id],
[Extent1].[text] AS [text]
FROM [dbo].[notes] AS [Extent1]
WHERE [Extent1].[type] = N'company'
*/
Assert.That(!sql.Contains("N'company'"));
}
}
public abstract class TphTestNote
{
public int Id { get; set; }
public string Text { get; set; }
}
public class TphTestCompanyNote : TphTestNote
{
}
public class TphTestOrderNote : TphTestNote
{
}
public class TphTestNoteMap : EntityTypeConfiguration<TphTestNote>
{
public TphTestNoteMap()
{
HasKey(t => t.Id);
Property(t => t.Text)
.HasMaxLength(254)
.IsUnicode(false);
ToTable("notes");
Property(t => t.Id).HasColumnName("id");
Property(t => t.Text).HasColumnName("text");
Map<TphTestCompanyNote>(t => t.Requires("type").HasValue("company").IsUnicode(false));
Map<TphTestOrderNote>(t => t.Requires("type").HasValue("order").IsUnicode(false));
}
}
public class TphTestContext : DbContext
{
static TphTestContext()
{
Database.SetInitializer<TphTestContext>(null);
}
public DbSet<TphTestCompanyNote> CompanyNotes { get; set; }
public DbSet<TphTestOrderNote> OrderNotes { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new TphTestNoteMap());
}
}