Define alternative key selector

[TimSirmovics]

We have tables which use an auto generated identity column as the PK, and one or more fields as another candidate key that the client knows about.

e.g.

public class SomeEntity
{
    [Key]
    public int SomeEntityId { get; set; }

    public string ClientKey1 { get; set; }

    public string ClientKey2 { get; set; }
}

In this case the key used by the client is a combination of ClientKey1 and ClientKey2, but for consistency we have an auto generated identity as the actual PK.

Could a feature be added to the ExtentBuilder to specify which properties should be used for matching?

[jtheisen]

If you have to specify them, that means there's no nav prop that has a foreign key that references them, correct?

If I understood this correctly, how is this supposed to work? You load/reconcile one entity tree that is connected only by nav props. If not all required nav props are there, the tree is no longer connected. How do you retrieve/provide the parts that are disconnected from the root?

[TimSirmovics]

In my example I was just talking about one entity, but I'll try and give a more concrete example with navigation properties.

public class Parent
{
    [Key]
    public int ParentId { get; set; }

    public string ClientKey1 { get; set; }

    public string ClientKey2 { get; set; }

    public ICollection<Child> Children { get; set; }
}

public class Child
{
    [Key]
    public int ChildId { get; set; }

    public int ParentId { get; set; }

    public string ClientKey { get; set; }
}

So from the client we get a model like this:
var fromClient = new Parent { ClientKey1 = "A", ClientKey2 = "B", Children = new List<Child> { new Child { ClientKey = "AA" }, new Child { ClientKey = "BB" } }

When we pass the parent to EF, it inserts the parent and both children and generates the ID property for each.

Now we get an update from the client:
var fromClient = new Parent { ClientKey1 = "A", ClientKey2 = "B", Children = new List<Child> { new Child { ClientKey = "AA" }, new Child { ClientKey = "BB" }, new Child { ClientKey = "CC" } }

In current code we manually update the entity.
This is what I would like to replace with reconciler.
However reconciler has no alternative for matching records on anything other than the primary key, so it considers this to be a completely new tree, and inserts it again.

[jtheisen]

Ok, I see.

First of all, note that Reconciler is currently assuming that the (primary) keys are set properly before you commit them to the database in those cases where it's relevant to relationships. In particular there's the Key Consistency Requirement (see the main readme). It will work in many cases when the database sets the keys, but I haven't thought that through properly because I myself, in my own apps, am generating the keys as guids on the application side, rather than as auto-increments on the database side. I'm not saying don't do it, but you may face further subtle issues I didn't think of.

To your actual feature request, I think at least in some cases Reconciler could indeed consider other keys for matching, but if that information is to be taken from EF's model, it would be a bit more involved to implement than what is currently there: both flavors of EF offer services to match entities on primary keys, but none (I believe) offer services to match them on other keys. So I'd have to write that matching code myself, which I didn't have to do with the primary keys.

It would be a lot easier to implement when you have to specify the additional keys in the extent definition, which is what you're thinking of, correct?

Still, I see some subtly there. For example, key matching is subject to collations (for strings) and I'm glad I can just delegate such issues to EF, if only for primary keys.

[TimSirmovics]

It would be a lot easier to implement when you have to specify the additional keys in the extent definition, which is what you're thinking of, correct?

Correct.

Still, I see some subtly there. For example, key matching is subject to collations (for strings) and I'm glad I can just delegate such issues to EF, if only for primary keys.

Not sure on the implementation details, but I would have thought the matching would occur in a LINQ to Entities statement which would be translated down to SQL and executed on the server with SQL handling the collation? I haven't looked at the code myself.

Is this something you would like me to have a look at and submit a PR or would you prefer to work on it yourself?

[jtheisen]

No, it first loads the collection as it exists in the database already and then, after having loaded the existing entities, goes through them and sees if some of them correspond to the ones in the template.

The approach you were suspecting may also be possible, I would have to think about that a bit more, but I suspect it may lead to more requests. In any case, it's different from the current one, and I really like the simplicity of the current one.

Before you go into the trouble of doing a proper PR, it may be less work for you to spitball around more concrete implementation ideas. I'm happy to entertain them, even though I don't yet see how anything I would want to accept in that direction could look like.

PS: Out of curiosity, are you using EF6 or EFCore?

[TimSirmovics]

I will have to take a look at the code when I have time to understand how it works currently before I can come up with an implementation idea.

We are using a mixture of EF6 and EFCore at the moment.

[TimSirmovics]

@jtheisen

When configuring an entity for a table which has a Unique Key defined, this can be specified as an alternative key in the IEntityTypeConfiguration<TEntity>.Configure(EntityTypeBuilder<Order> builder) method with the line builder.HasAlternateKey(@"AltKey");

This is what the Unique Key setup looks like in SSMS:

In your EntityKey class the key is found by calling db.Model.FindEntityType(typeof(E)).FindPrimaryKey();, but there is also the opportunity to call db.Model.FindEntityType(typeof(E)).FindKey(property); which will find any alternative keys by specifying the properties.
You can see all of the available keys with db.Model.FindEntityType(typeof(E)).GetKeys();.

Do you think this approach could be used to pass in the properties to reconciler and have it find the appropriate alternative key?