Query ignores OfType and breaks citext comparison

[Dennis-Petrov]

Hi everyone. I've got a types hierarchy in code:

public abstract class ImTask
{
    public int Id { get; set; }
}

public abstract class DocflowTask : ImTask
{
    public string BoxId { get;set; }
}

There are several non-abstract descendants both from ImTask and DocflowTask. They are all mapped using TPH pattern to the same table. Note, that BoxId is mapped to citext-typed column.

Now I want to get entities filtered by BoxId, and I need case-insensitive search (this is important).
Here's the query:

    var tasks = dbContext.Set<ImTask>()
        .OfType<DocflowTask>()
        .Where(_ => _.BoxId == "some value")
        .ToList();

I'm getting none. Detailed analysis shows, that generated SQL contains WHERE clause with this CASE:

CASE
    WHEN ("Extent1"."TaskType" = E'ACCEPT_TITLES') THEN ("Extent1"."Docflow_BoxId") 
    WHEN ("Extent1"."TaskType" = E'CERTIFICATE_INFO') THEN (CAST (NULL AS text)) 
    // a number of WHEN below
END  = 'some value'

TaskType is a discriminator column.
Here I see two problems:

1. Query above uses OfType<DocflowTask>. Type with CERTIFICATE_INFO discriminator value isn't a DocflowTask. It's an ImTask. Why it mentioned in generated SQL?
2. Since CERTIFICATE_INFO isn't of type DocflowTask, it hasn't BoxId property. So, instead of Docflow_BoxId column, NULL should be returned. It's OK, but there is cast to text, not citext. As a result, CASE returns text instead of citext, and values are being compared case-sensitive.

Of course, when text casing in DB and .Where() match, query behaves as expected.

I think, this is wrong behavior, because OfType is ignored, and citext comparison is broken.

[Emill]

Hi. For your first question, it's outside of Npgsql EF6's scope. That part is made by the core Entity Framework 6 (assuming the Npgsql SQL generator is correct). Could you please set a breakpoint here: https://github.com/npgsql/EntityFramework6.Npgsql/blob/dev/src/EntityFramework6.Npgsql/NpgsqlServices.cs#L96 and print the commandTree.ToString()?

For your second question, it's quite hard to be citext-compatible, since EF has so bad support for data types (such as citext) except the built-in ones. In this case, the command tree that the EF core generates contains a cast-operation of NULL to String, which is generated as CAST (NULL as text). I'm not sure how we could workaround this.

[Dennis-Petrov]

@Emill, thanks for the answer.

That part is made by the core Entity Framework 6

That's sad. At least, this is not optimal. OfType<T> clearly says: "I want only T or its descendants". :(
I'll either try to set breakpoint later, or look at SQL text, generated by MS for SQL Server.

I'm not sure how we could workaround this

Well, I see your arguments. Just a suggestion: what if Npgsql will look at column type? I mean, technically, we can set column type explicitly using entity configuration:

        Property(_ => _.BoxId)
            .IsRequired()
            .HasColumnName("Docflow_BoxId")
            .HasColumnType("citext");

So, when Npgsql needs to map String from Entity SQL to PostgreSQL type, it can look into property configuration, peek citext from there, and generate CAST (NULL as citext). Actually, I've tried to replace text to citext in generated SQL - the query works as expected in this case.

Of course, if column type isn't set explicitly, Npgsql must use its default mapping.

[Emill]

I don't think the column info in the CAST (NULL as text) command tree node is included. What we would have to do in this case is to maybe perform some kind of traversation of all CASE nodes and inspect their THEN data types. If at least one is a column with a citext type, we could cast all other THENs to citext as well.