Hi,

Been trying to figure out how to do this in LLBLGen. I would like to return a single UsersEntity:

SELECT * FROM users WHERE username = 'blah' AND password = 'deedah'

This will return 1 record. There is a unique constraint in the db on username, but no constraint defined on password, thus FetchUsingUniqueConstraint won't do it for me. I tried creating a PredicateBucket:

IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(
    PredicateFactory.CompareValue(UsersFieldIndex.UserName, ComparisonOperator.Equal, userName));
filter.PredicateExpression.AddWithAnd(
    PredicateFactory.CompareValue(UsersFieldIndex.Password, ComparisonOperator.Equal, password));

but then discovered that a bucket can't be passed into adapter.FetchEntity, only a PrefetchPath can. Since it's only querying one table, there's no appropriate PrefetchPath (that I could see).

I know I can do FetchEntityCollection or make a dynamic list, but it seems like there must be a straightforward way of querying one table using multiple predicates to return a single typed entity.

Thx, Jack

FetchEntity is for fetching an entity's data into the entity instance you pass in, which has the PK values set. So if you want to fetch the customer "CHOPS", you can do:

CustomerEntity c = new CustomerEntity("CHOPS"); // sets PK adapter.FetchEntity(c);

However if you want to fetch an entity using a custom filter, you should use FetchNewEntity, which fetches the entity data matching the filter into a new entity instance using the factory you pass in. So in your case:

IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(
    PredicateFactory.CompareValue(UsersFieldIndex.UserName, ComparisonOperator.Equal, userName));
filter.PredicateExpression.AddWithAnd(
    PredicateFactory.CompareValue(UsersFieldIndex.Password, ComparisonOperator.Equal, password));
UsersEntity user = (UsersEntity)adapter.FetchNewEntity(new UsersEntityFactory(), filter);

... thx. Glad there was an easy way to do it. Spent some hairpulling time over that, seemed like for sure there would be.

• Jack

Jackk100 wrote:

... thx. Glad there was an easy way to do it. Spent some hairpulling time over that, seemed like for sure there would be. - Jack

Please consult the reference manual as well, which can give you proper insight in which methods there are and more info on each method.

Franz, I recently had this problem, I was working with Microsoft CRM (ugh, what a poorly designed database!!). There was no constraint defined, but I knew I would get one record. It occurred to me if I could do the equivalent of SELECT FIRST (or TOP 1) that would be fine.

Is there a way to do those queries, other than what you've presented here? Or is this the best way?

You could simply formulate the filter on the field and call adapter.FetchEntityUsingUniqueConstraint(). That method expects a filter which will result in a single entity.

Is there a way do this with Self-Servicing?

jcsutton wrote:

Is there a way do this with Self-Servicing?

No, you then need the collection trick with a filter, GetMulti and limit set to 1, and grab the first instance.