Can I update a record using EF Core without using the primary ID?-CodePudding

I want to update a row but I don't have the primary key to use to make the update. Can I use another column (SomeOtherId) value as a key to update the row? How can I do this? Do I need a composite key? I can make the column unique and index it.

Here is how I want to update the row

var transfer = new Transfer { SomeOtherId = "12345ABCDE", TransferStatus = TransferStatus.Complete};
_context.Attach(transfer);
_context.Entry(transfer).State = EntityState.Modified;
_context.SaveChangesAsync()

CodePudding user response：

Yes, you can. Provided you have enough information to uniquely identify a row, you can select that entity using those details. However, if you cannot write a Linq query that can return a row using Single() then you don't have enough information to uniquely identify a row and your question is impossible. A unique constraint on one or more fields helps ensure you can find a specific row without risk of multiple matches, but it isn't required, without it just adds the risk that your query might still return 2 or more rows.

As an example say we have a Person record that has:

PersonId
FirstName
LastName
DateOfBirth
...

We may have an import process that updates values about Person but does not have our internal PK, so it needs to pass enough information to uniquely identify a Person. As an example we assume that the combination of FirstName, LastName, and DateOfBirth is "unique". This means that the database should have a unique constraint on these fields.

 var person = context.Persons
    .Where(x => x.FirstName == firstName
        && x.LastName == lastName
        && x.DateOfBirth == dateOfBirth)
    .SingleOrDefault();

If we cannot have that constraint but want to consider it "unique enough" for most cases, then we should handle the situation that more than one might be returned:

 var persons = context.Persons
    .Where(x => x.FirstName == firstName
        && x.LastName == lastName
        && x.DateOfBirth == dateOfBirth)
    .ToList();

if (persons.Count > 1) 
{ // combination was not unique enough, what do you want to do in this case?
}
else if (persons.Count == 1)
{ // Good to go with persons[0]
}
else 
{ // Not found.
}

a sloppy solution to the above problem might be:

 var person = context.Persons
    .Where(x => x.FirstName == firstName
        && x.LastName == lastName
        && x.DateOfBirth == dateOfBirth)
    .FirstOrDefault();

Where this would just take the first match when more than one were found. This can be "ok" in situations where you really don't care what match you use, otherwise the First* methods should always be used in conjunction with an OrderBy* clause to ensure their selection is predictable. I.e. .OrderByDescending(x => x.PersonId) to ensure the newest record is always selected rather than an arbitrary one (assuming an Identity column). Without an OrderBy* clause it can appear to select a meaningful result like the oldest record (lowest ID) but with larger data sets the picked item can be influenced by data paging leading to inconsistent picks.

Generally in the above scenario when matching data from an external source where we don't have the PK it is a good idea to also store something like an "External Key" as an indexed unique constraint even if we don't intend to use it as our PK. This helps expedite finding rows. Either way, the only way we can be sure to find just 1 (or no) row is to have a unique constraint on alternative fields to the PK.

CodePudding user response：

first, you should find existed entity without an id and update it with the below code

var transfer = new Transfer { SomeOtherId = "12345ABCDE", TransferStatus = TransferStatus.Complete};
var found=_context.transfers.where(a=>a.SomeOtherId==transfer.SomeOtherId && a.TransferStatus==transfer.TransferStatus).fristOrDefualt();

 _context.Entry(found).CurrentValues.SetValues(transfer )
_context.SaveChangesAsync()