How would i implement in code a query like :

SELECT CallId, Call FROM tblcalls WHERE CallId in (SELECT CallId from tblcallActivity where ActivityTypeId=A)

A= integer

Is the best way? How do i do this in code?

Using llblgen v2.5,dotnet2,adapter

I just got started with LLBLGen Pro (v2.0.0.0) last night, so I can't tell you the optimal solution, but here's how I'd go about it using the Adapter template:

1. Create a prefetch path to retrieve a Call entity when you retrieve the Activity entity.
2. Create a filter to retrieve only activities with the specified type ID.
3. Call the FetchEntityCollection method to get the Activity entity and read the Call property from it.

don't think your solution will work. I need to ensure that i have unique tblCalls records.

Calls can have many activities so it is possible to get dupliacte tblcall records if i use a join as you suggest.

Hope this makes sense.

The relationship between calls and activities is a one-to-many association i.e. one call points to multiple activities. This implies that one activity would have just one corresponding call record.

Unless you're searching for multiple activities, the approach would work. There is, however, a more elegant approach with which you can filter entities based on the attribute of a related entity, though I'm not sure of how to do that yet. Perhaps someone else on the forum could help out.

Anthony wrote:

How would i implement in code a query like :

SELECT CallId, Call FROM tblcalls WHERE CallId in (SELECT CallId from tblcallActivity where ActivityTypeId=A)

A= integer

Is the best way? How do i do this in code?

Using llblgen v2.5,dotnet2,adapter

I assume you want a collection of call entities and not a dynamic list (dataset)?

I also assume your SQL can be rewritten as:

SELECT DISTINCT
     CallId, Call
FROM 
     tblCalls c
     INNER JOIN tblCallActivity a ON a.CallId = c.CallId
WHERE
     a.ActivityTypeId = A

If the above assumptions are true, you want to do an EntityCollection Fetch of tblCalls. You will want to create a RelationPredicateBucket, with a relation from tblCalls to tblCallActivity and a predicate expression of "tblCallActivity.ActivityTypeId = A".

If you want a dymanic list/dataset, the steps are very similar, except you will need to create a field collection (callId and Call) and use adapter.FetchTypedList.

HTH,

Phil

I'm not sure if i am explaining my self well enough.

If a call has 2 activities with the same activitytype then i will get 2 call records returned which are duplicates.

How would i implement in code a query like :

QUERY #1 SELECT CallId, Call FROM tblcalls WHERE CallId in (SELECT CallId from tblcallActivity where ActivityTypeId=A)

A= integer

QUERY #2 SELECT DISTINCT CallId, Call FROM tblCalls c INNER JOIN tblCallActivity a ON a.CallId = c.CallId WHERE a.ActivityTypeId = A

Query 1 and 2 are not the same! Query 1 will always return unique records whereas query could return duplicates if a call has more then 1 activity with the same type.

I want to dupliacte the query of Query #1..any suggestions?

Anthony wrote:

I'm not sure if i am explaining my self well enough.

If a call has 2 activities with the same activitytype then i will get 2 call records returned which are duplicates.

If you are doing an entity fetch, it won't return duplicates. If you are doing a dynamic list fetch, you can specify that you don't want duplicates.

The query I posted uses the keyword "DISTINCT" to ensure no duplicates are returned.

Phil

thanks Phil...i didn't see the DISTINCT part.

Any suggestions how i do an enity fetch to achieve this?

Anthony wrote:

thanks Phil...i didn't see the DISTINCT part.

Any suggestions how i do an enity fetch to achieve this?

See my first post in this thread--if you get stuck, post the code that isn't working and I (or someone here) will be happy to help.

Phil

Anthony wrote:

How would i implement in code a query like :

SELECT CallId, Call FROM tblcalls WHERE CallId in (SELECT CallId from tblcallActivity where ActivityTypeId=A)

A= integer

Is the best way? How do i do this in code?

Using llblgen v2.5,dotnet2,adapter

You can do this type of filter using the **FieldCompareSetPredicate ** Take a look on the manual about the FieldCompareSetPredicate predicate.