Arbitrary SELECT statement in EXISTS

Posts   
 
    
timur
User
Posts: 4
Joined: 09-Jul-2007
# Posted on: 09-Jul-2007 16:34:15   

Hi,

We are currently evaluating LLBLGen. The issue we are struck in now is: 1) We have the following SELECT statement

select
l.id, 
l.user_label_a, 
lay.id, 
lay.name
from
log_topological_link l,
log_layer lay
where exists 
(select 1
from
csh_tl_path clp
where clp.id = l.id
and clp.a_centre_id = 11231011)
and lay.id = l.layer_id

2) For the purpose we composed the following code

PredicateExpression pe = new PredicateExpression();
FieldCompareSetPredicate fcsp = new FieldCompareSetPredicate(null, null, CshTlPathFields.Id, null, SetOperator.Exist,
(CshTlPathFields.Id == LogTopologicalLinkFields.Id) & (CshTlPathFields.ACentreId == 11231011), false);
pe.Add(fcsp);

The question now is: Whether it is possible to put arbitrary SELECT statement (not the default one generated by previous code) to the EXISTS clause? If yes, what should we do?

Thank you in advance.

Jessynoo avatar
Jessynoo
Support Team
Posts: 296
Joined: 19-Aug-2004
# Posted on: 09-Jul-2007 16:57:36   

Hi,

I don't think you'll find easy to inject raw sql in the sub query although there are a few entry points for such manual insertion.

Instead, I reckon that you should be fine using the default engine if you finely tune the parameters -> seems to me that your problem is related to aliasing:

look for the largest overload and build your filter predicates prior to using them in the compareset constructor so that you can change the aliases associated with the predicate fields and get them in sync with the main query

Anyway, can you provide more information on what's not working?

Can you copy paste the full llblgen code? the correponding query? the exception stack if any?

timur
User
Posts: 4
Joined: 09-Jul-2007
# Posted on: 09-Jul-2007 17:10:09   

Sure, here is the code:

DataAccessAdapter adapter = new DataAccessAdapter();
LogLayerEntity entity = new LogLayerEntity();
entity.Id = 11227821;
if (adapter.FetchEntity(entity))
Console.Write("{0}, {1}\n", entity.Id, entity.Name);
TllayerTestTypedList tl = new TllayerTestTypedList();
PredicateExpression pe = new PredicateExpression();
FieldCompareSetPredicate fcsp = new FieldCompareSetPredicate(null, null, CshTlPathFields.Id, null, SetOperator.Exist,
(CshTlPathFields.Id == LogTopologicalLinkFields.Id) & (CshTlPathFields.ACentreId == 11231011), false);
pe.Add(fcsp);
adapter.FetchTypedList(tl, pe);
for (int n = 0; n < tl.Rows.Count; n++)
{
Console.Write("\n";
for (int i = 0; i < tl.Rows[n].ItemArray.Length; i++)
Console.Write("{0}: {1} - {2}\n", n, tl.Columns[i].ColumnName, tl.Rows[n].ItemArray[i]);
}

But let's not concentrate on the previous C# code. I would appreciate if you could translate the following SQL code to LLBLGen equivalent:

select
l.id,
l.user_label_a,
lay.id,
lay.name
from
log_topological_link l,
log_layer lay
where exists
(select 1
from
csh_tl_path clp
where clp.id = l.id
and clp.a_centre_id = 11231011)
and lay.id = l.layer_id
jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 09-Jul-2007 18:30:27   

re-written query

select l.id, l.user_label_a, lay.id, lay.name
from log_topological_link l inner join  log_layer lay on lay.id = l.layer_id
inner join csh_tl_path clp on clp.id = l.id and clp.a_centre_id = 11231011

llbl

IEntityFields2 fields = new ResultSet(4);
fields[0] LogTopologicalLinkFields.Id;
fields[1] LogTopologicalLinkFields.UserLabelA;
fields[2] LogLayerFields.Id;
fields[3] LogLayerFields.Name;

IRelationCollection relations = new RelationCollection();
relations.Add(LogTopologicalLinkEntity.Relations.LogLayerOnId);
relations.Add(LogTopologicalLinkEntity.Relations.CshTlPathOnId);
realtions[1].CustomFilter = new PredicateExpression(CshTlPathFields.ACentreId == 11231011);

using (IDataAccessAdapter adapter = new DataAccessAdatper())
{
   adapter.FetchTypedList(...);
}

timur
User
Posts: 4
Joined: 09-Jul-2007
# Posted on: 10-Jul-2007 11:55:04   

Thanks. But still what about original query?

jmeckley
User
Posts: 403
Joined: 05-Jul-2006
# Posted on: 10-Jul-2007 14:13:14   

the rewrittne query should produce the same results as the original query.