This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I am trying to figure out how Lightspeed determines how to build the join statement for a database like SqLite where foreign keys do not exist. I have an order table and an orderdetail table. I created an association and reverse association. I then did a query like this. var od = UnitOfWork.OrderDetails.Where(o => o.Order.OrderId = 15); I then looked at the sql created and there was a join statement that was correct inner join orders on orders.orderid = orderdetails.orderid I didn't have Lightspeed installed when I created the table so I know it wasn't looking over my shoulder when I created the code. How did LS figure it out, is it because OrderId exists in both tables? If so, what if I had a relationship but the column names were not the same, what would I need to do to ensure this would work? Thanks Joe Feser |
|
|
It works it out from the association. In your case, it goes something like this: The OrderDetails.Order association is associated with the OrderDetails.OrderId field. So we know that we are going to have to join on orderdetails.orderid: inner join ? on ? = orderdetails.orderid. The other end of the OrderDetails.Order association is the Order type, which maps to the orders table. So we know that we are going to have to join to the orders table: inner join orders on orders.? = orderdetails.orderid. Finally, associations always relate to the identity column (you cannot associate using an arbitrary unique column). In your case, I am assuming that you have mapped the Order type's identity column to orderid rather than the default Id. So this fills in the last blank: inner join orders on orders.orderid = orderdetails.orderid. So it's not about the column names being the same: that's a happy coincidence of your naming convention. E.g. if you had not mapped the Order identity column, the join would have been on orders.id = orderdetails.orderid. Hope this makes sense -- let us know if you have any further questions. |
|
|
Ivan, It does make sense. I will email you the actual case after I install v1.0.62 of SqLite if I run into issues. The part that is still not clear is the fact that I just declare an association, I didn't put any custom attributes other than the one reverse association that is required. If the OrderDetail table did not have OrderId, but had Oid for the column name, are you saying it would not have worked? Thank you for your time, Joe Feser |
|
|
Assuming the association is called Order, LightSpeed requires that the foreign key field in the entity be called OrderId. (The designer enforces this convention.) But the foreign key field in the entity is just a field -- and, like any field, can be mapped to a different column name by using the ColumnAttribute (Column Name in the designer). So you would enter Oid in the association's Column Name property, or add [Column("Oid")] to the _orderId field if writing the class by hand. And then it would work. So I sort of left out a step in how LightSpeed works out the join. We saw how when LightSpeed was working out which column to join on in the orders table (PK side), it did a mapping from the Id field to the orderid column. The same thing happens with the FK (OrderId) field on the OrderDetails side. In your example, LightSpeed used the default mapping, which is why I didn't mention it; but in general there could be a name mapping step on the FK side as well as the PK side. In any case, the only case where names have to match is the Order / OrderId convention between the EntityHolder and the FK field in the FKing entity. There is never a need for column names to match across tables, or for entity/property names to match across entities. |
|