PostgreSQL, Linq C# error 'could not determine data type of parameter $1'

Question

I am getting this error "could not determine data type of parameter $1" and my where clause where I am getting the error is like the following:

var result = from Table in model.Table 
                             where (filter.XId.HasValue ? Table.XId == filter.XId: true)

                             select new TableEntity
                             {
                                 ID = Table.XId
                             };

If my code was only like this 'Table.X == filter.X', it works ... How can I fix this?

and I am getting this problem only with PostgreSQL database ....

Answer 1

First about what that error usually means. When making parametrized queries to PostgreSQL, all parameters should be referenced in query itself. When you add more parameters than used in query, usually the error above appears.

It seems that when whatever EF provider for PosgreSQL you use converted your statement to SQL, it created more parameters than needed.

In general, it might be hard for EF providers to analyze and correctly parse statements like you used, so good practice is to use statements that are "closer" to SQL in certain sense. In your case equivalent query which is "closer" to SQL would be:

where (filter.XId == null || Table.XId == filter.XId)

If you want to generate different queries based on the value of filter, you can do something like this:

var query = (IQueryable<Table>) model.Table;
if (filter.XId != null) {
    query = query.Where(row => row.XId == filter.XId);
}
var result = query.Select(row => new TableEntity {
    Id = row.XId
});