I am using Npgsql to issue parameterized PostGIS queries on a Postgres database. The problem is that Npgsql casts all parameterized variables using a longhand notation, and PostGIS doesn't understand cast variables in some cases.
For example, suppose the original query starts with this:
ST_GeometryFromText('POLYGON((:x :y,...
Npgsql turns it into this:
ST_GeometryFromText('POLYGON((((1278594)::int4) ((1206979)::int4),...
That doesn't work. It would work if the casts could be left out, like this:
ST_GeometryFromText('POLYGON((1278594 1206979,...
There is apparently a UseCast attribute of a parameter, but it is not settable per NpgsqlParameter.cs.
Do I have any alternative besides dynamically constructing my queries?
Per Francisco Figueiredo Jr. and Josh Cooley, two key developers of Npgsql, there is currently no good way to force Npgsql not to cast parameterized values.
While it is possible to set the parameter to type DbType.Object, which may avoid casting, that has issues with ambiguous function calls and possibly inducing bugs.
For values that need to go inside WKT, I am using a workaround of string replacement on the query before parameters are processed.