Hi, users of the NHibernate
There is an
SQL Server 2005 legacy database and application using it. I'm trying to migrate existing data-access layer to NHibernate. I'm using
NHibernate v3.0 with my mappings declared via
Fluent NH. Having a couple of POCOs with mappings declared, I tried to run a query using
LINQ-to-NH,
Code:
var orders = session.Query<Order>().Where(o => o.Reships.Any(r => r.Reason.IsBillable));
and get a weird error.
NHibernate.Exceptions.GenericADOException : could not execute query
System.Data.SqlClient.SqlException : Incorrect syntax near ','.
Details are below.
Database structure Actually database has a large amount of tables, but only three are relevant in current discussion.
Table "Order"
Code:
table Order
(
OrderID bigint PK not null,
ClientOrderID varchar(255) null,
...
Status, ShippingInfo, PaymentInfo, and couple of other columns, we will not look at them as they are irrelevant in current discussion
...
}
)
Table "Order_reshipment" have composite primary key which composed of both old and new order IDs.
Code:
table Order_reshipment
(
SourceOrderID bigint not null PK,
TargetOrderID bigint not null PK,
ReshipReasonID int null FK references ReshipReason(ReasonID),
)
Table "Reship_reason"
Code:
table Reship_reason
(
ReasonID int not null PK,
Name varchar(512) not null,
IsReshipBillable bit not null
)
Domain Problem Explained Few words about what are those table for.
User of a system can place an order. When order is placed for the first time, row in table Order is inserted and that is it. If placed order has not been shipped to the customer after all, user may ask for the reship. When reship is fulfilled, new row is inserted in table Order. Also row in table Order_reshipment is inserted to bind the new order and the old one. Order reshipment refers to reship reason via ReshipReasonID foreign key. Reship reason is specified by user.
POCO and mappings Entity
"Order" and mapping.
Code:
public class Order
{
public Order()
{}
public virtual long OrderID { get; private set; }
...
public virtual IList<Reship> Reships { get; private set; }
}
public class OrderMap : ClassMap<Order>
{
public OrderMap()
{
Table("`Order`");
Id(o => o.OrderID);
HasMany(o => o.Reships).KeyColumn("TargetOrderID").Inverse().Cascade.All();
}
}
Entity
"Reship" and mapping
Code:
public class Reship
{
public virtual ReshipIdentifier ID { get; set; }
public virtual ReshipReason Reason { get; set; }
}
Here we have a composite key mapped as a separate class, having key columns mapped as entity references to parent order (old one) and child order(new one).
Code:
[Serializable]
public class ReshipIdentifier
{
public Order ChildOrder { get; private set; }
public Order ParentOrder { get; private set; }
}
Mapping is as follows:
Code:
public class ReshipMap : ClassMap<Reship>
{
public ReshipMap()
{
Table("Order_resubmitted");
CompositeId(r => r.ID)
.KeyReference(rid => rid.ChildOrder, k => k.ForeignKey("FK_Order_resubmitted_TargetOrder_OrderID"), "TargetOrderID")
.KeyReference(rid => rid.ParentOrder, k => k.ForeignKey("FK_Order_resubmitted_SourceOrder_OrderID"), "SourceOrderID");
References(r => r.Reason).ForeignKey("FK_Order_resubmitted_Reship_reason_ReasonID");
}
}
Entity
"ReshipReason" and mapping
Code:
public class ReshipReason
{
public virtual int ReasonID { get; private set; }
public virtual string Name { get; set; }
public virtual bool IsBillable { get; set; }
}
public class ReshipReasonMap : ClassMap<ReshipReason>
{
public ReshipReasonMap()
{
Id(r => r.ReasonID).Column("ReasonID");
Map(r => r.Name).Not.Nullable().Column("ReasonName");
Map(r => r.IsBillable).Not.Nullable().Column("IsReshipBillable");
}
}
Query and Fail So, having all this code, I'm trying to get orders which were created as a result of reship operation, and were reshipped with billable reason. I'm using LINQ, which have out of box support in version 3.0 of the NHibernate.
Code:
var orders = session.Query<Order>().Where(o => o.Reships.Any(r => r.Reason.IsBillable));
Very simple query, isn't it? However it does not work at all. Let's have a look on SQL generated by the query.
Code:
select
order0_.OrderID as OrderID4_,
order0_.ExternalOrderID as External2_4_,
...
from [Order] order0_
where exists(
select
(reships1_.TargetOrderID,
reships1_.SourceOrderID)
from
Order_resubmitted reships1_,
Reship_reason reshipreas2_
where
order0_.OrderID=reships1_.TargetOrderID
and reships1_.ReasonID=reshipreas2_.ReasonID
and reshipreas2_.IsReshipBillable=1
)
If you run it you will get and error with message:
"Incorrect syntax near ','." The problem is the brackets in these lines of code.Code:
...
select
(reships1_.TargetOrderID,
reships1_.SourceOrderID)
from
...
NHibernate selects the key of Reship entity (Order_reshipment table) in the subquery in where clause. The key is composite, thus two columns are selected. But those columns are embraced in brackets. This is actually the error reason. With brackets removed, query is executed successfully.
I cannot realize why NHibernate composes SQL statements in that way. Very strange behavior. Can anyone help me to figure out what is the reason? Maybe something wrong with my code, or maybe this is as designed, or may there is a bug in NH?
Waiting for your help.
Login
FAQ
Search