Exception in LINQ for nullable strings in Oracle - blocks using WCF Data Services
Description
Environment
Activity
Alex Zaytsev September 8, 2014 at 11:31 AM
Close issues resolved in 3.3.3
TorvinK December 3, 2012 at 4:13 PM
OK, thanks, Oskar. I've reported
Oskar Berggren December 3, 2012 at 3:52 PM
Sounds like the problem and solution is completely different, so please report a new issue.
TorvinK December 3, 2012 at 3:48 PM
OK... looks like I was prematurely optimistic... This WCF DS query will fail:
http://.../Products()?$filter=substringof('23',Code) and substringof('2',Name)
The code WCF DS generates is horrible again. It looks something like this:
.Where(
p =>
((p.Code == null ? (bool?)null : p.Code.Contains("23"))
&&
(p.Name == null ? (bool?)null : p.Name.Contains("2"))) == null
?
false
:
((p.Code == null ? (bool?)null : p.Code.Contains("23"))
&&
(p.Name == null ? (bool?)null : p.Name.Contains("2"))).Value
)
This fails with `NHibernate.AssertionFailure: Cannot perform desired possible value set operation on expression of type: System.Nullable`1[System.Boolean]` because that && operator is executed against `bool?` operands.
The funny thing is && operator isn't even allowed in C# for `bool?` operands. NH apparently tries to follow the same semantics. However it is allowed in LINQ and that's exactly what WCF DS does.
Here is the test of somehow simplified LINQ expression (to keep code from getting too complicated) that fails exactly the same way as the query above:
[Test]
public void UsersWithStringContainsAndNotNullNameComplicated2()
{
var cond = ((Expression<Func<User, bool?>>)(x => x.Name.Contains("123")));
var expr = Expression.Lambda<Func<User, bool>>(
Expression.MakeBinary(
ExpressionType.Coalesce,
Expression.AndAlso(cond.Body, cond.Body),
Expression.Constant(false)
),
cond.Parameters
);
var query = db.Users
.Where(expr);
query.ToList();
}
Note that I had to construct LINQ manually. The query for this test looks something like this:
.Where(
x =>
(((bool?)x.Name.Contains("123")) && ((bool?)x.Name.Contains("123"))) ?? false
)
TorvinK December 3, 2012 at 2:10 PM
Thanks Oskar! I tested your fix and it works perfectly with WCF DS.
Imagine a WCF DS that returns an entity set of Products:
class Product
{
public virtual int ID { get; set; }
public virtual string Code { get; set; }
}
The 'Code' field is a nullable string (that's important). Now, let's try to query for some products:
http://.../Products()?$filter=substringof('123',Code)
WCF DS builds a horrible query for that:
session.Query<Product>()
.Where(p =>
(p.Code == null ? null : (bool?)p.Code.Contains("123")) == null
? false
: (p.Code == null ? null : (bool?)p.Code.Contains("123")).Value);
As far as I can tell, it tries to emulate sql-like 3-state Boolean logic with nulls for some reason. EF executes this LINQ just fine, but in NH you'll get 'ORA-00932: inconsistent datatypes: expected CHAR got NUMBER' for Oracle DB.
The generated SQL is the following:
SELECT ...
FROM t_product product0_
WHERE CASE
WHEN Cast(CASE
WHEN product0_."CODE" IS NULL THEN 0
ELSE
CASE
WHEN product0_."CODE" LIKE ( '%'||1||'%' ) THEN 1
ELSE 0
END
END AS NUMBER(1, 0)) IS NULL THEN
CASE
WHEN 2 = 1 THEN 1
ELSE 0
END
ELSE Cast(CASE
WHEN product0_."CODE" IS NULL THEN 3
ELSE
CASE
WHEN product0_."CODE" LIKE ( '%'||4||'%' ) THEN 1
ELSE 0
END
END AS NUMBER(1, 0))
END = 1
The problem occurs because of 0 and 3 type. In NH internals they have YesNoType type and for some reason are bound as 'Char' parameters for sql command with the value of DBNull. So, Oracle fails trying to match 'Char's with those '0's and '1's literals inside the query text.
This problem do not exist for SqlServer - my guess is that it ignores parameter type if it's value is DBNull, or somehow silently casts it to the proper type.