There is a functionality to fetch an entity based on a unique key (FetchEntityUsingUniqueConstraint) using a filter that can be created using the fields and values of a corresponding unique constraint (ConstructFilterForUCxxx).

Is there a functionality (that I can't find right now) that would be exactly the same, except it would rely on a unique index? If there is none ... why?

I have created unique indices in my database. Creating a unique key instead of the index would allow foreign key constraints "linking" to these unique constraints. From a database concept point of view this is not desirable.

But now I have to first create a filter (manually) and then (if the entity is not found) set all fields (including the unique index fields) again. So now I am actually considering to forget the correct db design and change the indices to keys.

magic wrote:

Is there a functionality (that I can't find right now) that would be exactly the same, except it would rely on a unique index? If there is none ... why?

Not supported: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=13320 http://llblgen.com/TinyForum/Messages.aspx?ThreadID=12754 http://llblgen.com/TinyForum/Messages.aspx?ThreadID=14594

magic wrote:

But now I have to first create a filter (manually) and then (if the entity is not found) set all fields (including the unique index fields) again. So now I am actually considering to forget the correct db design and change the indices to keys.

I don't really understand the consideration behind that criteria (UC = bad, UI = good)

daelmo wrote:

I don't really understand the consideration behind that criteria (UC = bad, UI = good)

thank you for the links.

About your question: From my understanding, an index is just for look up. Additionally to this an UK also enables other tables to set FKs pointing to the UK.

Now, you can say that I am the designer, so if I don't want a FK pointing to the UK, I just don't create it. But from a designer's point of view, I see no need for the UK (cause I don't want UKs pointing to it), so I don't create it.

I don't understand why there wouldn't be a lookUp functionality based on an unique index which essentially should be the same as the one based on an UK.

I also have another problem with the UK lookUp: Is there a way to make the fetch through the UK constraint case independent? Similar to what you suggested for a predicate comparison: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14804

I would like to identify the strings case independent, but store them in the case they were input by the user.

magic wrote:

I don't understand why there wouldn't be a lookUp functionality based on an unique index which essentially should be the same as the one based on an UK.

Because these aren't read as unique constraints. If you want unique constraint behavior, define a unique constraint. . Additionally, if you really want this, you can alter the driver code. In driver CatalogRetriever code, the unique constraints are retrieved. If you additionally retrieve the unique index meta-data and create DBUniqueConstraint objects as well, you'll get the meta-data in the catalog and everything else should work as normal.

magic wrote:

I also have another problem with the UK lookUp: Is there a way to make the fetch through the UK constraint case independent? Similar to what you suggested for a predicate comparison: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14804

I would like to identify the strings case independent, but store them in the case they were input by the user.

There's a way. You have to filter similar when filtering case-insentivie. Additional, it's recommended to set EntityFieldCore.CaseSensitiveStringHashCodes = true if the involved field participates in FKs or prefetchPaths:

// ensure that the PK/FK will be synchronized and merged correctly when 
// the casing at DB side differs
EntityFieldCore.CaseSensitiveStringHashCodes = true;

// make sure the entity is found, when the casing differs
FieldLikePredicate filter = new FieldLikePredicate(CocoFields.Name, null, "AAA");
filter.CaseSensitiveCollation = true;

// fetch the entity via UC
CocoEntity coco = new CocoEntity();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{                                           
    adapter.FetchEntityUsingUniqueConstraint(coco, new PredicateExpression(filter));
}

Obviously, it's recommended that this kind of casing situations would be managed DB-side when possible.

thank you for the hints daelmo. I will try to check where I can go from here ...