Hi,

Does anyone know how to convert the below SQL statement in self servicing?

SQL: SELECT DISTINCT u.* FROM dbo.UserTable u INNER JOIN dbo.Role creator ON u.CreatedById=creator.RoleId LEFT JOIN dbo.UserTable ownedby ON u.OwnerId=ownedby.RoleId WHERE ownedby.Name LIKE '%test%'

Thanks,

Hi there,

I will assume that the relationships between entities are already mapped in LLBLGen Designer. A will also assume you are using v3.x. Next time please post these and other relevant information (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

The only thing tricky in your query is that you are filtering on the same entity type but it comes from the result of another relation, that's why you need to alias them. When you do these kind of things you must pass a relationCollection into your GetMulti call and for those relations that could end up with ambiguity you must add an alias for one of them. You also have to specify the alias when you construct the filter (WHERE clause). Read this for more info....

An approximate query would be:

var relations = new RelationCollection();
relations.Add(UserEntity.Relations.RoleEntityUsingCreatedById);
relations.Add(UserEntity.Relations.UserEntityUsingOwnedById, "OwnedBy", JoinHint.Left);

var filter = new PredicateExpression();
filter.Add(UserFields.Name.SetObjectAlias("OwnedBy") % "%test%");

var users = new UserCollection();
users.GetMulti(filter, relations);

Hi daelmo,

Thanks I actually got it. Yes you're correct I'm using the latest version and the only difference from our code is that I used JoinHint.Right for "OwnedBy".

Again thank you so much, really appreciate your help.

Thanks,