LLBLGen Pro V: 3.1 Final Runtime Library 3.1.0.0 .NET Version: 4.0 Template Group: Adapter Oracle V: 10g Oracle Clieng V: 11.1 Inheritance Hierarchies: None OPD Oracle.DataAccess.dll V: 2.111.7.20

Hello,

Hoping someone can help me with this. I am just learning LLBLGen Pro, and am trying to build a where filter and ran across something that I am just not sure how to do.

I am trying to build a RelationPredicateBucket which would be the where clause of the following Sql query:

select *
from some_view1
where 
  some_view1.last_name = 'some last name'
  and some_view1.first_name = 'some first name'
  and some_view1.id_number = 1234
  and exists(
    select count(1) 
    from some_table2
    where 
      some_table2.license_number = 'some license number'
      and some_table2.certification_date between some_date and some_later_date
      and some_table2.specialty_code = 'some value'
);

In the code above there is a view (some_view1) and a table (some_table2). The view and the table are related by the license number. I need to get all entities from the view where there exists any items that match the filter in the table in the line query.

Would appreciate any assistance.

Thanks, Shaun

Since you will be working with views and entities together, you could do this:

1. In LLBLGen Designer, map your view as an entity. Just right-click the view in the Catalog explorer and click 'Reverser-engineer to entity definitions'. Now you have a new entity that targets your view.

2. Now you have two entities: Table1Entity and Table2Entity. You can now add a custom (model-only) relation between them.

3. Generate code from LLBLGen Designer. Now you have your two entities: Table1Entity and Table2Entity ready to use in the code.

4. Now build your query. Here is some approximate code. Since now you have both objects related, you don't need the EXISTS predicate.

EntityCollection<Table1Entity> results = new EntityCollection<Table1Entity>();
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(Table1Entity.Relations.Table2EntityUsingLicenseNumber());
bucket.PredicateExpression.Add(Table1Fields.FirstName == "some first name");
bucket.PredicateExpression.Add(Table1Fields.IdNumber = 1234;
bucket.PredicateExpression.Add(Table2Fields.CertificationDate <= someStartDate & Table2Fields.CertificationDate >= someEndDate);
...

using (var adapter = new DataAccessAdapter())
{
     adapter.FetchEntityCollection(results, bucket);
}

If you want, for some reason, use the subquery (exists), use a FieldCompareSetPredicate.

Hope helpful