Problem with LINQ to NHibernate and vb.net and more logical operator
Leo Alario
when I run a query on the domain model in VB.Net using at least two
logical operators (AND or OR)
I get the following error:
Unable to cast object of type 'NHibernate.Hql.Ast.HqlBitwiseOr' to
type 'NHibernate.Hql.Ast.HqlBooleanExpression'.
Simple query:
Dim res = From c In rep.Query (Of Context) ()
Where (c.Siglacontesto.Equals ("BARP") Or
c.Siglacontesto.Equals ("CVIT"))
Select c
Dim res = From c In rep.Query (Of Context) ()
Where (c.Siglacontesto.Equals ("BARP") And
c.Siglacontesto.Equals ("CVIT"))
Select c
the same query executed in c# works fine for me:
var query = from c in rep.Query <Contesto> ()
where c.Siglacontesto.Equals ("BARP") | |
c.Siglacontesto.Equals ("CVIT")
select c;
var query = from c in rep.Query <Contesto> ()
where c.Siglacontesto.Equals ("BARP") &&
c.Siglacontesto.Equals ("CVIT")
select c;
It seems a bug of the provider, how can I fix it?
Leo Alario
I do not know if it is correct but I worked in the following way:
in the file HqlGeneratorExpressionTreeVisitor.cs
I replaced the following statement:
//case ExpressionType.Or:
// return _hqlTreeBuilder.BitwiseOr(lhs, rhs);
with this one:
case ExpressionType.And:
return _hqlTreeBuilder.BooleanAnd(lhs.AsBooleanExpression(),
rhs.AsBooleanExpression());
and this:
//case ExpressionType.Or:
// return _hqlTreeBuilder.BitwiseOr(lhs, rhs);
with this one:
case ExpressionType.Or:
return _hqlTreeBuilder.BooleanOr(lhs.AsBooleanExpression(),
rhs.AsBooleanExpression());
The queries now works fine for me is in c # and in VB.Net.
Maximilian Raditya
Dim res = From c In rep.Query (Of Context) ()
Where (c.Siglacontesto.Equals ("BARP") Or
c.Siglacontesto.Equals ("CVIT"))
Select c
Dim res = From c In rep.Query (Of Context) ()
Where (c.Siglacontesto.Equals ("BARP") And
c.Siglacontesto.Equals ("CVIT"))
Select c
--
Regards,
Maximilian Haru Raditya
CSharper
Maybe that's the way it's currently working with NHibernate but for
Boolean operands, both operators should work in a query language
targeting SQL in the background.
The operator behaviour of SQL is neither exactly like the short
circuit && (or ||) nor the bitwise Boolean & (or |) operators. SQL
engines might do lazy evaluation like the short circuit operators but
the order of evaluation is chosen by the query optimizer. In contrast
to the && operator where the left hand side is guaranteed to be
evaluated first and the right hand side is only evaluated if required,
the query execution might do it the other way round depending on the
complexity and usable indexes available in the operands.
Maybe it would be better to not force the VB programmers to always add
this dumb "Else" word in the queries and allow the Boolean operator of
the C# programmer's choice.
On 5 Dez., 03:23, Maximilian Raditya <m4h...@gmail.com> wrote:
Maximilian Raditya
Maybe that's the way it's currently working with NHibernate but for
Boolean operands, both operators should work in a query language
targeting SQL in the background.
The operator behaviour of SQL is neither exactly like the short
circuit && (or ||) nor the bitwise Boolean & (or |) operators. SQL
engines might do lazy evaluation like the short circuit operators but
the order of evaluation is chosen by the query optimizer. In contrast
to the && operator where the left hand side is guaranteed to be
evaluated first and the right hand side is only evaluated if required,
the query execution might do it the other way round depending on the
complexity and usable indexes available in the operands.
Maybe it would be better to not force the VB programmers to always add
this dumb "Else" word in the queries and allow the Boolean operator of
the C# programmer's choice.
If you're curious to know why they design it that way, you should probably ask MS team directly :D.
CSharper
make a difference. The documentation link you have in there says it
works with two bits in 2008 R2 also but I tried with 2008 and it did
not work with bits there.
But as I wrote: an SQL AND or OR operator is no AndElse or OrElse
operator because the order of evaluation is not determined by the
operator but by the execution engine. And I don't know if the engine
really guarantees the execution of both paths with the bitwise | or &
operators shown in your links if both arguments are of bit type.
So for Boolean I would say the right thing to do is support both
variants of .NET operators all with using the logical operators in
SQL.
On 5 Dez., 10:19, Maximilian Raditya <m4h...@gmail.com> wrote:
David Schmitt
> Yes, but they are meant for integral data types where they actually
> make a difference. The documentation link you have in there says it
> works with two bits in 2008 R2 also but I tried with 2008 and it did
> not work with bits there.
> But as I wrote: an SQL AND or OR operator is no AndElse or OrElse
> operator because the order of evaluation is not determined by the
> operator but by the execution engine. And I don't know if the engine
> really guarantees the execution of both paths with the bitwise | or&
> operators shown in your links if both arguments are of bit type.
>
> So for Boolean I would say the right thing to do is support both
> variants of .NET operators all with using the logical operators in
> SQL.
As long as you're using only side-effect-free functions in your boolean
clauses (which is, like, everything in standard SQL), short-circuiting
does not change the outcome of the result and is therefore not relevant
to the discussion.
The differences between bitwise and boolean operators on the other side
are big, well-defined and mathematically sound. Nothing to discuss here
either.
The mapping from the boolean/bitwise concepts to And/AndElse in VB is
pretty much written in stone, since Microsoft made that decision and
masses of VB programmers learnt which is which.
So, if you cannot be convinced of the rightness of using the proper
operator for the operation you want, you are free to change NHibernate's
source to your liking, but do not expect agreement from the rest of the
world, or pity if you get a publication on http://thedailywtf.com/
Best Regards, David
>
>
> On 5 Dez., 10:19, Maximilian Raditya<m4h...@gmail.com> wrote:
>> On Mon, Dec 5, 2011 at 3:44 PM, CSharper<csharper2...@googlemail.com>wrote:
>>
>>> Maybe that's the way it's currently working with NHibernate but for
>>> Boolean operands, both operators should work in a query language
>>> targeting SQL in the background.
>>
>> Doesn't SQL have both operators: logical (http://msdn.microsoft.com/en-us/library/ms189773.aspx) and bitwise (http://msdn.microsoft.com/en-us/library/ms176122.aspx)?
>>
>>> The operator behaviour of SQL is neither exactly like the short
>>> circuit&& (or ||) nor the bitwise Boolean& (or |) operators. SQL
>>> engines might do lazy evaluation like the short circuit operators but
>>> the order of evaluation is chosen by the query optimizer. In contrast
>>> to the&& operator where the left hand side is guaranteed to be
Maximilian Raditya
...
But as I wrote: an SQL AND or OR operator is no AndElse or OrElse
operator because the order of evaluation is not determined by the
operator but by the execution engine. And I don't know if the engine
really guarantees the execution of both paths with the bitwise | or &
operators shown in your links if both arguments are of bit type.
...
--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
CSharper
operators in programming languages.
I just wanted to spot that there is no equivalent in SQL of the well-
defined short-circuit operators in C# where you can e.g. first check
if a value is not null and then evaluate some properties of the value
because the execution order is defined. If you have a subselect
returnung multiple results like ...
select *
from Table1
where ID = (select ID from TableWithMoreThanOneEntry) or 1=1
... you are most probably lucky because optimizer chooses to first
check the simple condition and returns successfully if the result of
the whole operator can be deduced. It's no matter if you have the
simple condition on the left or right hand side.
The following Linq query will throw an exception:
list1.Where(a => a.ID == listWithMoreThanOneEntry.Select(b =>
b.ID).Single() || true)
Only a select like ...
select *
from Table1
where ID = (select ID from TableWithMoreThanOneEntry) or 1=0
... will cause an exception because the execution engine is forced do
evaluate the complex condition.
But I must admit that there is a whole bunch of semantic differences
between C# Linq to objects and the way an SQL engine treats a query
that is at a first glance equivalent to the Linq query.
The whole point is: I don't say somebody must change this in NHib but
it's worth discussing about such semantic details and differences
because it strengthens the awareness that e.g. Linq to X is different
than Linq to Y. There are always trade-offs because of the diverse
target systems interpreting the Linq expression trees.
> world, or pity if you get a publication onhttp://thedailywtf.com/
Maximilian Raditya
The following Linq query will throw an exception:
list1.Where(a => a.ID == listWithMoreThanOneEntry.Select(b =>
b.ID).Single() || true)
Only a select like ...
select *
from Table1
where ID = (select ID from TableWithMoreThanOneEntry) or 1=0
... will cause an exception because the execution engine is forced do
evaluate the complex condition.
...
--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
CSharper
short circuit && and || operators in SQL.
Developers should be aware of the fact that there are subtle semantic
differences in Linq queries depending on the target system.
In SQL, only the first Select of the following sequence throws an
exception (at least if the query optimizer tries to avoid
"unnecessary" work):
create table table1(id int)
insert into table1 values (1)
create table tablewithmorethanoneentry(id int)
insert into tablewithmorethanoneentry values (1)
insert into tablewithmorethanoneentry values (2)
-- throws Exception
select *
from table1
where id = (select id from tablewithmorethanoneentry)
-- works although the problematic condition is on lhs
select *
from table1
where id = (select id from tablewithmorethanoneentry) or 1=1
-- works
select *
from table1
where 1=1 or id = (select id from tablewithmorethanoneentry)
In C#, the first two Selects (they seem to do the same as the SQL
queries above) throw exception because the operator has a defined
execution order for the operands:
class Program {
static void Main(string[] args) {
List<int> table1 = new List<int> {1};
List<int> tablewithmorethanoneentry = new List<int> {1,
2};
try {
var result = from i in table1
where i == tablewithmorethanoneentry.Single()
select i;
// throws Exception
result.ToList();
Console.WriteLine("OK");
} catch (Exception e) {
Console.WriteLine("Failed: " + e.ToString());
}
try {
var result = from i in table1
where i ==
tablewithmorethanoneentry.Single() || true
select i;
// throws because the problematic condition is on lhs
result.ToList();
Console.WriteLine("OK");
} catch (Exception e) {
Console.WriteLine("Failed: " + e.ToString());
}
try {
var result = from i in table1
where i ==
tablewithmorethanoneentry.Single() || true
select i;
// works because the problematic condition is on rhs
result.ToList();
Console.WriteLine("OK");
} catch (Exception e) {
Console.WriteLine("Failed: " + e.ToString());
}
Console.ReadLine();
}
}
On 6 Dez., 09:45, Maximilian Raditya <m4h...@gmail.com> wrote:
> On Tue, Dec 6, 2011 at 2:52 PM, CSharper <csharper2...@googlemail.com>wrote:
>
> > ...
> > The following Linq query will throw an exception:
> > list1.Where(a => a.ID == listWithMoreThanOneEntry.Select(b =>
> > b.ID).Single() || true)
>
> > Only a select like ...
> > select *
> > from Table1
> > where ID = (select ID from TableWithMoreThanOneEntry) or 1=0
> > ... will cause an exception because the execution engine is forced do
> > evaluate the complex condition.
> > ...
>
> I don't quite understand what you meant. I just try a similar query using
> Linq and it works just fine without exception. The semantic is preserved,
> including the RHS operand (1=0). The generated SQL includes everything
> being instructed.
>
CSharper
last select):
where true || i ==
tablewithmorethanoneentry.Single()
select i;
Maximilian Raditya
If C++/C#/VB has short-circuiting why can't SQL Server have it?
To truly answer this let's take a look at how both work with conditions. C++/C#/VB all have short circuiting defined in the language specifications to speed up code execution. Why bother evaluating N OR conditions when the first one is already true or M AND conditions when the first one is already false.We as developers have to be aware that SQL Server works differently. It is a cost based system. To get the optimal execution plan for our query the query processor has to evaluate every where condition and assign it a cost. These costs are then evaluated as a whole to form a threshold that must be lower than the defined threshold SQL Server has for a good plan. If the cost is lower than the defined threshold the plan is used, if not the whole process is repeated again with a different mix of condition costs. Cost here is either a scan or a seek or a merge join or a hash join etc... Because of this the short-circuiting as is available in C++/C#/VB simply isn't possible. You might think that forcing use of index on a column counts as short circuiting but it doesn't. It only forces the use of that index and with that shortens the list of possible execution plans. The system is still cost based.
As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.
It's just that there are no exactly equivalent operators like the C#short circuit && and || operators in SQL.Developers should be aware of the fact that there are subtle semanticdifferences in Linq queries depending on the target system.