3

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 with Property(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());
    }
}
jrummell
  • 42,637
  • 17
  • 112
  • 171
  • I copied and pasted your model. I get exactly the same query but with `WHERE [Extent1].[type] = 'company'`. What version of EF do you use exactly? And what database and database version? BTW: If you hadn't your DB initializer set to `null` you would get an exception due to a bug in EF 4.3(.1?): http://stackoverflow.com/a/10020023/270591 – Slauma Aug 09 '12 at 17:27
  • That's strange. I have EF 4.3.1.0, and SQL Server 2005. There is no connection string for the context in my model, and it never actually hits the database, so I'm not sure that it matters. – jrummell Aug 09 '12 at 18:11
  • If you have no connection string (and no context constructor parameter) supplied EF uses the `System.Data.SqlClient` provider (called the ".NET Framework Data Provider for SQL Server") and tries to connect to a `.\SQLEXPRESS` DB instance. The provider works for SQL Server 2005 and later and should generate the same SQL (in my understanding). If you had used another provider it would matter (even without hitting the DB) because another provider can generate other SQL. Are you using .NET 4.0? Or 4.5? – Slauma Aug 09 '12 at 18:28
  • .NET 4.0. I didn't realize it was connecting to `.\SQLEXPRESS` on my local machine (I confirmed by stopping the service and getting a ProviderIncompatibleException). That version of SQL Server is 2008. – jrummell Aug 09 '12 at 18:36
  • @Slauma, I'm not sure why we're seeing different results with the same code, and same version of EF, but I found something else that works. See my answer. – jrummell Aug 09 '12 at 19:09
  • I have tested on a different machine with no DB installed at all, but I get the same query without Unicode, just `... = 'company'`, strange thing... – Slauma Aug 09 '12 at 19:28
  • Maybe a .NET security update? The runtime version of my referenced System.Data.Entity assembly is v4.0.30319. I do have .NET 4.5 installed but my project targets 4.0. – jrummell Aug 09 '12 at 19:36
  • I use *exactly* the same version. Also this version for `System.Data` assembly where the SqlClient lives in. – Slauma Aug 09 '12 at 19:44

2 Answers2

3

I'm still not sure why I'm seeing different results than @Slauma, but I finally found something that works for me. I explicitly set the column type to char in the inheritance mapping.

Map<TphTestCompanyNote>(t => t.Requires("type")
                              .HasValue("company")
                              .HasColumnType("char"));

Map<TphTestOrderNote>(t => t.Requires("type")
                            .HasValue("order")
                            .HasColumnType("char"));

And the resulting SQL:

SELECT 
'0X0X' AS [C1], 
[Extent1].[id] AS [id], 
[Extent1].[text] AS [text]
FROM [dbo].[notes] AS [Extent1]
WHERE [Extent1].[type] = 'company'
jrummell
  • 42,637
  • 17
  • 112
  • 171
  • I had tested that as well, but because it worked for me the same way as your original code I thought it wouldn't matter and make no difference. Really strange, why this works for your but not the original mapping. I have no idea what the reason could be. – Slauma Aug 09 '12 at 19:24
1

I can't reproduce the use of a Unicode string in the SQL query. Test application (Console app with EF 4.3.1):

using System;
using System.Data.Entity;
using System.Linq;

namespace EF43TPH
{
    public abstract class Note
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class CompanyNote : Note
    {
        public string ExtendedName { get; set; }
    }

    public class OrderNote : Note
    {
        public string AnotherExtendedName { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<Note> Notes { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CompanyNote>()
                .Map<CompanyNote>(t => t.Requires("type").HasValue("company"));

            modelBuilder.Entity<OrderNote>()
                .Map<OrderNote>(t => t.Requires("type").HasValue("order"));
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());

            using (var ctx = new MyContext())
            {
                try
                {
                    var query = ctx.Notes.OfType<CompanyNote>();
                    var queryString = query.ToString();
                }
                catch (Exception e)
                {
                    throw;
                }
            }
        }
    }
}

The SQL query I get in queryString is:

SELECT 
'0X0X' AS [C1], 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[ExtendedName] AS [ExtendedName]
FROM [dbo].[Notes] AS [Extent1]
WHERE [Extent1].[type] = 'company'

What is different between this test and your code?

Slauma
  • 175,098
  • 59
  • 401
  • 420
  • One difference is that I'm querying with `ctx.Set()` instead of `ctx.Set().OfType()`. I'll put together a minimal but complete example and update my question. – jrummell Aug 09 '12 at 16:53
  • 1
    @jrummell: I get the same SQL if I change my LINQ query to your query. – Slauma Aug 09 '12 at 17:28