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
|
SQL similar to the following gives me the result I want when I run it in an SQL client, but I'm having difficulty representing it using Query class. Can you point me in right direction. I am using Oracle database. Thanks select * from table1 t1, table2 t2 |
|
|
Hi Gail, In general trying to reverse engineer SQL statements back into a query object can be problematic, in particular the use of comparing two subqueries as criteria is not something LightSpeed allows you to express through our QueryExpression syntax, instead you would need to join those two subqueries in to the query and then compare the resulting selection from each query to each other as part of the query criteria so this would lead to quite a different looking SQL statement albeit with the same result. A better way of handling this if you are only looking to fetch out the results for a single entity (e.g. Table1) would be to use the FindBySql Another alternative would be to wrap this in a stored procedure if that is a possibility for you.
|
|
|
Hi Jeremy, Thanks for response and outlining the options. Could you give a little more detail or simple example to clarify from your first paragraph how to structure Query expression "to join those two subqueries in to the query and then compare the resulting selection from each query to each other as part of the query criteria". Gail |
|
|
Hi Gail, You can do this by creating additional query object instances and then joining these against the original query. e.g. var query1 = new Query(typeof(Entity1));
query1.Join = Join.Inner(typeof(Entity1), query2, "Entity1Property", "Entity2Property"); You can then express criteria against those joined tables by adding table aliases to the original query and then expressing the query required. e.g. query1.Mappings.Add(typeof(Entity1), "t0");
query1.QueryExpression = Entity.Attribute("Entity1Property") == Entity.Attribute(typeof(Entity2), "t1", "Entity2Property");
|
|