Npgsql is Redshift-compatible, but EntityFramework6.Npgsql is not

[diegoful]

Since Npgsql supports Redshift (see Server Compatibility Mode connection string parameter accepted values, which include Redshift), then EntityFramework6.Npgsql should support Redshift too. However, this is not the case.

To reproduce, connect to a Redshift cluster and create a table with a varchar column. Then try to query it through EF using a string literal to match on that column. For example:

var data = myContext.MyTable.Where(c => c.MyVarcharColumn == "foo").ToArray();

Expected: get no results or some results. Actual: exception is thrown (42704: type "e" does not exist) because generated SQL statement has a syntax error. Notice that "E" before opening single quote in string literal in generated SQL below:

SELECT "Extent1"."MyVarcharColumn" FROM "dbo"."MyTable" WHERE E'foo' = "Extent1"."MyCarcharColumn"

[Emill]

This is done here: https://github.com/npgsql/EntityFramework6.Npgsql/blob/v6.4.3/EF6.PG/SqlGenerators/VisitedExpression.cs#L183.

The E' syntax was introduced a long time ago, but since Redshift is based on an ancient PostgreSQL version, it doesn't support all features.

You could workaround that by putting "foo" in a local variable, and refer to that in your linq query. That will make EF use a parameter instead of a literal.

[diegoful]

The Npgsql project made code changes to support Redshift. It did not wait for Redshift to eventually become compatible with it. Why can't this project do the same?

[Emill]

If I remember correctly, the reason for the E' syntax is that it it's consistent how it works. Only using ' and ' had the issue that it was configurable (or maybe different between pg versions) how the escaping was done.

[roji]

Why can't this project do the same?

Unlike Npgsql, the EF6 provider is basically archived - it's no longer actively maintained. I personally have no idea what removing the E might break.