search

npgsql / EntityFramework6.Npgsql

Entity Framework 6 provider for PostgreSQL
PostgreSQL License
66 stars 54 forks source link

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

Open diegoful opened 3 years ago

diegoful commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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.