LLBLGen load a distinct item by fields that are not primary key

Question

I have been trying to load single items from with LLBLGen by fields in the table that are not primary keys.

I can only work out how to filer on primary keys on FetchEntity.

To filter on non primary keys I am having to getthe collection and use linq to get the first. It fells like a smell, I was wondering if there was a better way?

 public BinLocationEntity GetDefaultBinLocation(string firstName, string lastName)
    {


        var persons = new EntityCollection<PersonEntity>();
        var filter = new RelationPredicateBucket();
        filter.PredicateExpression.Add(PersonFields.FirstName == firstName);
        filter.PredicateExpression.Add(PersonFields.LastName== lastName);

        using (var adapter = this.DataAccessAdapter)
        {
            adapter.FetchEntityCollection(persons , filter);
        }
        return persons .First();
    }

I know the demo code would be bad in real world, Its just there as an example.

score 1 · Answer 1 · answered Mar 09 '20 at 01:46

You can also fetch by unique constraint:

https://www.llblgen.com/Documentation/5.6/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/gencode_usingentityclasses_instantiating.htm#using-a-unique-constraints-value

It does not make a lot of sense to (directly) fetch a single entity using fields that are not primary keys and are not unique constraints. The generated code has no way to know that your query should logically result in a single entity being returned.

Using Linq .First() in these cases is completely appropriate and not at all a code/design smell.

score 0 · Answer 2 · answered May 20 '20 at 17:55

The only thing that i'd add to your answer is that if you truly do expect that your query is going to return just a single result, that you change your FetchEntityCollection() call to:

adapter.FetchEntityCollection(persons , filter, 1);

to specifically limit the results to 0 or 1 rows. It might well be that the first result is the one you want, but without any top limit supplied it could be that this query is returning thousands of rows or more which can incur a huge performance hit.

LLBLGen load a distinct item by fields that are not primary key

2 Answers2