Hi - I've got 3 tables I'm working with Person - PersonRoles - Roles - and would like to get all the people from a particular role.

You can see how they are related probably. I need to get all the people in a particular role - I will pass in a Role name.

Do I need to do 2 queries and iterate through them. Normally, I would do a SQL statement to do this - how can I do this with the LLBL Entities?

Thanks,

Jay

So you are looking for something like

SELECT * FROM Person WHERE PersonId in (SELECT PersonId FROM PersonRole WHERE RoleId IN (SELECT RoleId FROM Role WHERE RoleName = 'MyRoleName'))

Like this (code from memory BTW...)

//a collection to hold the people
var people = new EntityCollection<PersonEntity>();
//an adapter
var adapter = new DataAccessAdapter();

// something to hold the relations (effictivly the joins to the other tables) and the filter value
var bucket = new RelationPredicateBucket();
//add the relations
bucket.Relations.Add(PersonEntity.Relations.PersonRoleUsingPersonId);
bucket.Relations.Add(PersonRoleEntity.Relations.RoleUsingRoleId);
//and the filter
bucket.PredicateExpression.Add(RoleFields.Name == myRoleName)

//get the data (you'll need to check the exact overload...!)
adapter.FetchEntityCollection(people,bucket);

Hopefully this should get you started.

Matt

MTrinder wrote:

So you are looking for something like

SELECT * FROM Person WHERE PersonId in (SELECT PersonId FROM PersonRole WHERE RoleId IN (SELECT RoleId FROM Role WHERE RoleName = 'MyRoleName'))

Like this (code from memory BTW...)

//a collection to hold the people
var people = new EntityCollection<PersonEntity>();
//an adapter
var adapter = new DataAccessAdapter();

// something to hold the relations (effictivly the joins to the other tables) and the filter value
var bucket = new RelationPredicateBucket();
//add the relations
bucket.Relations.Add(PersonEntity.Relations.PersonRoleUsingPersonId);
bucket.Relations.Add(PersonRoleEntity.Relations.RoleUsingRoleId);
//and the filter
bucket.PredicateExpression.Add(RoleFields.Name == myRoleName)

//get the data (you'll need to check the exact overload...!)
adapter.FetchEntityCollection(people,bucket);

Hopefully this should get you started.

Matt

Thanks, Matt..

The syntax looks a little different than what the code base I'm using uses..

EntityCollection<T> isn't referenced, but EntityCollectionBase<T> is.. but doesn't work in this example.

Here is an exmple of a typical query in this code base - this example below is clear, but it just queries 1 entity with some expressions as filters - I'm having an issue with the relations syntax;

 PersonCollection inactiveStudents = new PersonCollection();
 IPredicateExpression xFilter = new PredicateExpression(PersonFields.CustomerGuid == cust.Guid);
 xFilter.AddWithAnd(PersonFields.IsStudent == true);
 inactiveStudents.GetMulti(xFilter);

I'm not sure if it's real differences between your example and this - or just syntax preference going on here.

Thanks,

Jay

The difference is that you are using Selfservicing rather than Adapter - sorry about that, we tend to give most of our examples in Adapter format as it is more commonly used.

Your example is nearly there - all you are missing is adding the relations to the query to join to the related tables, and the filter on the Role Name. Have a look at the documentation at http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/SelfServicing/Filtering%20and%20Sorting/gencode_filteringadvanced.htm#Multientityfilters

You need to create a RelationCollection, add the relations you need and pass this to the call to .GetMulti, as well as adding the filter on Role.Name to your predicate expression.

Matt

MTrinder wrote:

The difference is that you are using Selfservicing rather than Adapter - sorry about that, we tend to give most of our examples in Adapter format as it is more commonly used.

Your example is nearly there - all you are missing is adding the relations to the query to join to the related tables, and the filter on the Role Name. Have a look at the documentation at http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/SelfServicing/Filtering%20and%20Sorting/gencode_filteringadvanced.htm#Multientityfilters

You need to create a RelationCollection, add the relations you need and pass this to the call to .GetMulti, as well as adding the filter on Role.Name to your predicate expression.

Matt

Thanks - great examples.

Jay