Devart Forums

Posted: **Tue 10 Aug 2010 08:26**

We are start move our product from dotConnect for PostgreSQL 4.65 to 4.95.152. And this LINQ to SQL is worked in 4.65, but throw ArgumentException in 4.95.152:

Code: Select all

var query = from tr in context.Table
where tr.Status == (nullstatus ? null : new int?(-1)) || statuses.Contains(tr.Status) == statusesIn
select tr;

Variables nullstatus and statusesIn - is boolean variables. They are become true or false with some conditions. statuses - is a int?[] array.
Exception is throw when statuses is an empty array (not null, but int?[0]). If I remove first part of condition and rewrite LINQ like this:

Code: Select all

var query = from tr in context.Table 
where statuses.Contains(tr.Status) == statusesIn 
select tr;

the query will execute in any cases.

Please, look at this point and tell me if any fixes is available...

Posted: **Wed 11 Aug 2010 11:12**

And one more question.
dotConnect translate condition "tr.Status == (nullstatus ? null : new int?(-1))" to query with CASE operator. Can I rewrite this condition for avoid CASE operator in result query?

Simple example:

Code: Select all

bool condition = true;
var query = from table in context.Table
where condition == true ? table.Id == 1 : true
select table;

What I want to see in SQL query:
1) If "condition" == true, then "SELECT * FROM Table WHERE Table.Id = 1".
2) If "condition" == false, then "SELECT * FROM Table WHERE TRUE".

Can you give me some tricks or links to info?

Posted: **Wed 11 Aug 2010 14:47**

Thank you for your report, we've reproduced the issue with the empty array. We will investigate it and inform you about the results.

As for the CASE operator, this is the common Linq runtime behaviour. The SQL statement is generated in a universal way, i.e., the statement is independent of the variable values available in the query, and these values are binded to parameters. Hence, two different results of the "condition ? table.Id == 1 : true" statement should be separated using the CASE operator. The possible way to change this behaviour is to separate these cases outside the query:

Code: Select all

bool condition = true;
var query = condition 
  ? 
  from table in dc.Tests
    where table.Status == 1
    select table
  :
  from table in dc.Tests
    select table;

Posted: **Wed 11 Aug 2010 15:03**

Thnx for reply. We will wait your fixes or any other results.

Posted: **Thu 12 Aug 2010 08:39**

One more issue with ?: operator.
Test code:

Code: Select all

long? condition = 1;
var query = from table in context.Table
where condition == null ? true : table.Id == condition.Value;

This code is also throw exception. If I rewrite it to this:

Code: Select all

long? condition = 1;
long conditionValue = condition.Value;
var query = from table in context.Table
where condition == null ? true : table.Id == conditionValue;

then all be alright.

Posted: **Thu 12 Aug 2010 12:44**

Could you please specify the scripts needed to create the table from the latest issue? Also, please specify the exception message and stack trace. We couldn't reproduce the problem with a simple table with the only integer field.

We've fixed the problem with the empty array, the fix will be available in the nearest build.

Posted: **Thu 12 Aug 2010 13:58**

Sorry for not full info. To reproduce issue you need to set condition variable to null. Like this:

Code: Select all

long? condition = null; 
var query = from table in context.Table 
where condition == null ? true : table.Id == condition.Value;

And exception is not throw if I change code to this:

Code: Select all

long? condition = null; 
long conditionValue = condition == null ? -1 : conditionValue;
var query = from table in context.Table 
where condition == null ? true : table.Id == conditionValue;

Exception is System.Reflection.TargetException with message Non-static method requires a target.

StackTrace:
в System.Reflection.RuntimeMethodInfo.CheckConsistency(Object target)
в System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
в System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
в System.Reflection.RuntimePropertyInfo.GetValue(Object obj, Object[] index)
в Devart.Data.Linq.Provider.Query.ak.b.a(Expression A_0)
в Devart.Data.Linq.v.a(BinaryExpression A_0)
в Devart.Data.Linq.v.b(Expression A_0)
в Devart.Data.Linq.v.a(ConditionalExpression A_0)
в Devart.Data.Linq.v.b(Expression A_0)
в Devart.Data.Linq.v.a(LambdaExpression A_0)
в Devart.Data.Linq.v.b(Expression A_0)
в Devart.Data.Linq.v.a(UnaryExpression A_0)
в Devart.Data.Linq.v.b(Expression A_0)
в Devart.Data.Linq.v.b(ReadOnlyCollection`1 A_0)
в Devart.Data.Linq.v.a(MethodCallExpression A_0)
в Devart.Data.Linq.v.b(Expression A_0)
в Devart.Data.Linq.v.b(ReadOnlyCollection`1 A_0)
в Devart.Data.Linq.v.a(MethodCallExpression A_0)
в Devart.Data.Linq.v.b(Expression A_0)
в Devart.Data.Linq.v.b(ReadOnlyCollection`1 A_0)
в Devart.Data.Linq.v.a(MethodCallExpression A_0)
в Devart.Data.Linq.v.b(Expression A_0)
в Devart.Data.Linq.Provider.Query.av.a(Expression A_0)
в Devart.Data.Linq.Provider.DataProvider.GetCompiledQueryFromCacheWithLock(Expression query, CompiledQueryCache& queryCache, a& hashKey)
в Devart.Data.Linq.Provider.DataProvider.BuildQuery(Expression query)
в Devart.Data.Linq.Provider.DataProvider.Devart.Data.Linq.Provider.IProvider.Compile(Expression query)
в Devart.Data.Linq.DataQuery`1.i()
в System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
в System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
в TestLinqCahing.Window1..ctor() в E:\Project\TestLinqCahing\TestLinqCahing\Window1.xaml.cs:строка 39

Posted: **Fri 13 Aug 2010 13:05**

Thank you for your report, we've reproduced the problem. We will inform you when it is fixed.

Posted: **Mon 16 Aug 2010 16:57**

We've investigated the situation, this is the expected behaviour. When the null value is binded to the condition variable, the LINQ translator tries to compare condition and table.Id, and hence casts null to integer. This is not allowed, hence the exception is thrown (we've changed its message to 'Nullable object must have a value'). To resolve the problem, please separate the cases when 'condition' is and is not null outside the query.

Posted: **Tue 17 Aug 2010 12:58**

Oh. I see. But for me - this is not expected behavior. Why translator cannot translate "table.Id == condition.Value" to:
"public.Table."Id" is NULL"? - when condition == null
"public.Table."Id" = 1"? - when condition == 1?
Logically query public.Table."Id" is NULL is not correct, because column table.Id - is not nullable type, but for DataBase server this query is correct and return no results.

And when we see full example this will looks like:
LINQ:

Code: Select all

long? condition = null; 
var query = from table in context.Table 
where condition == null ? true : table.Id == condition.Value;

Will be translated to:

Code: Select all

SELECT t1."Id" FROM public.Table t1
WHERE 
    (CASE 
        WHEN :p0 THEN :p1
        ELSE t1."Id" IS NULL
     END)
-------
p0 = true
p1 = true

And this LINQ:

Code: Select all

long? condition = 1; 
var query = from table in context.Table 
where condition == null ? true : table.Id == condition.Value;

Will be translated to:

Code: Select all

SELECT t1."Id" FROM public.Table t1
WHERE 
    (CASE 
        WHEN :p0 THEN :p1
        ELSE t1."Id" = :p2
     END)
-------
p0 = false
p1 = true
p2 = 1

For simple understanding I can write this query:

Code: Select all

long? condition = null;
var query = from table in context.Table 
where table.Id == condition
select table;

And this query will execute as expected and no exceptions will throw. Why so limited behavior for ?: operator?

Posted: **Wed 18 Aug 2010 14:31**

When processing the Linq query, the translator builds the expression tree. For the query specified, at some node you will have the comparison 'Id == null', which is incorrect, as null cannot be cast to integer. The exception is thrown at this point, i.e., before the query is sent to the database.

Feel free to contact us if something is unclear.

Posted: **Wed 18 Aug 2010 14:53**

Thnx for reply. I'm compare expression trees and found solution!
This query will execute as expected without any exceptions:

Code: Select all

long? condition = null; 
var query = from table in context.Table 
where condition == null ? true : table.Id == condition;

I'm just remove call condition.Value that cause exception if condition value is null! This action force translator to wrap table.Id with Convert function for compare not nullable type with nullable type. And this will properly parsed by translator.

Thank you again

Posted: **Thu 19 Aug 2010 15:07**

Glad to see that the problem was resolved.

Posted: **Fri 24 Sep 2010 16:55**

We have fixed the 'Value cannot be null' issue. The fix is available in the new 4.95.170 build of dotConnect for PostgreSQL.

The build can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area (provided that you have an active subscription):
http://secure.devart.com/

The detailed information about the fixes and improvements implemented in dotConnect for PostgreSQL 4.95.170 is available at
http://www.devart.com/forums/viewtopic.php?t=19070

Devart Forums

ArgumentException with ?: operator

ArgumentException with ?: operator