CODEWITHSUNDEEP
c#linqpostgresql
LFB
LFB

Reputation: 175

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

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 ....

Upvotes: 5

Views: 1592

Answers (2)

Gleb S
Gleb S

Reputation: 172

As it is the first SO answer in Google - Marked answer didn't help me in similar case (EF6 + Postgre), so I had to use .AsEnumerable();

Yeah, it could be bad for perfomance but it fitted well in my case.

So this one would work:

var query = model.Table
                    .AsEnumerable()
                    .Where (p => filter.XId.HasValue ? p.XId == filter.XId: true);

Upvotes: 0

Evk
Evk

Reputation: 101623

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
});

Upvotes: 4

Related Questions