Hallo,

i'm a newbie in LLBLGen Pro and i have to made a decision if it's used for or-mapping in a new project. We use .net 2.0 with C# and as database firebird.

the creation of the base code with LLBLGen Pro is very simple and fast. And after a few tries i've made my first selects. it seems good to me, to hide the creation of sql statements from the programmer, but isn't the any possibility to execute a selfmade sql statement and to get a result set? i know it's quick and dirty, but sometimes this could be a very comfortable way to do some things another question is: Is there a way to put a self made sql statement (as string) as a additional constraint in a RelationPredicateBucket or something like that.

And the last question is a concrete query:

i have 5 tables (t1 to t5)

t1 (- 1:n -) t2 (- m:1 -) t3

and also

t2 (- 1:n -) t4 (- m:1 -) t5

the intermediate table t2 is first to build a n:m relation between t1 and t3 but also it's used to build a connection from the objects t1-t3 to t5 via the intermediate table t4.

Now, i need all objects t1-t3 with the constraint that they have a connection to a specific member in t5.

select t1., t2. from t1, t2, t3 where t1.id = t2.t1id -- join constraint and t3.id = t2.t3id -- join constraint and exists (select * from t4, t5 where t2.id = t4.t2id -- join constraint and t5.id = t4.t5id -- join constraint and t5.value = 'param'); -- constraint to select

is such a constraint with FieldCompareSetPredicate or something else possible to create? and how looks the code like?

and is something like this possible: If for t1 exists a row in t3, fetch this and all other t3 which belong to this t1. (In this case the n:m relation between t1 and t3 should be interpreted as a 1:n releation)

sorry, if i ask a question which is maybe ask before in a variation, but it's all a little bit tricky in LLBLGen Pro and i've not so much time

For reply and maybe a few snippets of helpfull code i would be thankful

shennig

And sorry - i know my english is very ugly!

but isn't the any possibility to execute a selfmade sql statement and to get a result set?

For that, you can use a RetrivalQuery :

IRetrivalQuery selectQuery = new RetrivalQuery(connectionToUse, command);

Then execute this query with adapter with one of theses methods :

object o = ExecuteScalarQuery(selectQuery )
DataTable dt = ExecuteMultiRowDataTableRetrievalQuery(selectQuery , dataAdapterToUse,  fieldsPersistenceInfo)

Now, i need all objects t1-t3 with the constraint that they have a connection to a specific member in t5.

For this kind of queries you should use a RelationPredicateBucket, and add relations and predicate expressions to that objet. You can find a code sample in the doc in "Using generated code > Filtering and sorting > Advanced filter usage" :

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingVisitingAddressID, "VisitingAddress");
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingBillingAddressID, "BillingAddress");
bucket.PredicateExpression.Add((AddressFields.City.SetObjectAlias("VisitingAddress")=="Amsterdam") &
     (AddressFields.City.SetObjectAlias("BillingAddress")=="Rotterdam"));
EntityCollection customers = new EntityCollection(new CustomerEntityFactory());
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(customers, bucket);

and is something like this possible: If for t1 exists a row in t3, fetch this and all other t3 which belong to this t1. (In this case the n:m relation between t1 and t3 should be interpreted as a 1:n releation)

For doing this, you can use a "Via" PrefetchPath :

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.T1Entity); prefetchPath.Add(T1Entity.PrefetchPathT3CollectionViaT2);

adapter.FetchEntity(t1, prefetchPath );

then, you can access t3 collection with t1.T3CollectionViaT2

thanks for reply. but the answer to this

For this kind of queries you should use a RelationPredicateBucket, and add relations and predicate expressions to that objet. You can find a code sample in the doc in "Using generated code > Filtering and sorting > Advanced filter usage" :

Code:

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingVisitingAddressID, "VisitingAddress");
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingBillingAddressID, "BillingAddress");
bucket.PredicateExpression.Add((AddressFields.City.SetObjectAlias("VisitingAddress")=="Amsterdam") &
     (AddressFields.City.SetObjectAlias("BillingAddress")=="Rotterdam"));
EntityCollection customers = new EntityCollection(new CustomerEntityFactory());
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(customers, bucket);

leaves a question.

i have to generate a relation from t2 to t4 to t5 and on t5 i have to put a constraint like t5.value = 'param'. in the example above there is only a relation from t2 to t4 used. how could i put relations over multiple tables in a IRelationPredicateBucket and then put constraints on some of them? i've tried various possibilities like

IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(t1.Relations.t2EntityUsingt1id, "r1");
            bucket.Relations.Add(t2.Relations.t4EntityUsingt2id, "r2");
            bucket.Relations.Add(t4.Relations.t5EntityUsingt4id, "r3");

            bucket.PredicateExpression.Add((t5Fields.value.SetObjectAlias("r3") == 'param');

to construct this query

select t1.*, t2.* from t1, t2, t3
where t1.id = t2.t1id                                            -- join constraint
     and t3.id = t2.t3id                                             -- join constraint
     and exists (select * from t4, t5
                            where t2.id = t4.t2id                -- join constraint
                                and t5.id = t4.t5id              -- join constraint
                                and t5.value = 'param');         -- constraint to select

how could i construct this???????

Remark: Without aliasing it works!

But the next question is:

i want to load to every t1 (constraint on t5) all t3 which are also constraint on t5. so in the end i have a collection of t1 objects and in every t1 object is the set of t3 which fullfill the constraints with t5. is this possible?????

I find it hard to understand your query or the purpose behind it.

First start by: what do you want to retrieve? Are you going to use EntityCollections to be your fetch containers? Then you should have fields from one table in your select list, otherwise you will need to use a dynamic list or a typed list.

If you are going to use an entity collection to fetch all entities of type t1, and you want to filter on a field on table t5. Then you will need to add relation from t1 to t5, passing through intermediate tables

RelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(t1.Relations.t2EntityUsingt1id);
            bucket.Relations.Add(t2.Relations.t4EntityUsingt2id);
            bucket.Relations.Add(t4.Relations.t5EntityUsingt4id);

And then add the filter to the bucket

bucket.PredicateExpression.Add(t5Fields.SomeField == SomeValue);

No need for aliases as long as you don't use one table more than once in your join query.

Now as it appears to me that t3 has nothing to do with what is said so far.

But the next question is: i want to load to every t1 (constraint on t5) all t3 which are also constraint on t5. so in the end i have a collection of t1 objects and in every t1 object is the set of t3 which fullfill the constraints with t5. is this possible?????

You will have to use a preftechPath to t3, and pass to the prefetchPath the needed filter. This filter will be constructed in the same way we did for t1.

Now as it appears to me that t3 has nothing to do with what is said so far.

Quote: But the next question is: i want to load to every t1 (constraint on t5) all t3 which are also constraint on t5. so in the end i have a collection of t1 objects and in every t1 object is the set of t3 which fullfill the constraints with t5. is this possible?????

You will have to use a preftechPath to t3, and pass to the prefetchPath the needed filter. This filter will be constructed in the same way we did for t1.

for both tables t1 and t3 the constraint to t5 be used. only these t3 should be loaded which have a connection to the t1 via t2 and for this t2 must hold the constraint to t5. so implicit the constraint must hold for t1 and t3.

RelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(t1.Relations.t2EntityUsingt1id);
            bucket.Relations.Add(t2.Relations.t4EntityUsingt2id);
            bucket.Relations.Add(t4.Relations.t5EntityUsingt4id);
bucket.PredicateExpression.Add(t5Fields.SomeField == SomeValue);

is correct and now i must do something like this

IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.t1);
IPrefetchPathElement2 prePathEl = prefetchPath.Add(t1Entity.PrefetchPatht3Viat2);

            prePathEl.FilterRelations.Add(t3Entity.Relations.t2EntityUsingt3id);
            prePathEl.FilterRelations.Add(t2Entity.Relations.t4EntityUsingt2id);
            prePathEl.FilterRelations.Add(t4Entity.Relations.t5Usingt5id);
            prePathEl.Filter.Add((t5Fields.SomeField == SomeValue));

then i could use the adapter

DataAccessAdapter adapter = new DataAccessAdapter();
            EntityCollection t1Collection = new EntityCollection(new t1EntityFactory());
            adapter.FetchEntityCollection(t1Collection, bucket, prefetchPath);

This seems to work in a way. but it's very slow and the result is not quit correct. there are to many objects from t3 in every t1.

i hope i make it a little bit more clear. Is there a better way to do this selection????

This seems to work in a way. but it's very slow and the result is not quit correct. there are to many objects from t3 in every t1.

That's a good step.

If it's slow, I suggest you examine the generated query (ref: LLBLGen Pro manual ["Using the generated code -> Troubleshooting and debugging) And also read the Optimizing Prefetch Paths section in the LLBLGen Pro manual "Using the generated code -> Adapter -> Prefetch paths"