SingleOrDefault returning null when entity exists

Question

I have the following entity class

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ApiForVivtrack3.Entities
{
    [Table("PropertyPair")]
    public partial class PropertyPair
    {
        [Key]
        public int PairID { get; set; }
        public int PropertyNameID { get; set; }
        public string Value { get; set; }
        public byte LinkType { get; set; }
        public int LinkID { get; set; }
        public Nullable<byte> BehaviourType { get; set; }
        public Nullable<byte> DataType { get; set; }
        public Nullable<byte> Source { get; set; }

        [ForeignKey("PropertyNameID")]
        public virtual PropertyName PropertyName { get; set; }
    }
}

And the following method to add or update

 internal static PropertyPair MakePair(ApiDbContext connect, string propertyName, string PropertyValue,
            JobLine job)
        {
            var propertyNameId = SiteCache.LookUpPropertyNameId(propertyName);
            var pair = connect.PropertyPairs.SingleOrDefault(x =>x.PropertyNameID == propertyNameId && x.LinkID == job.JobID);
            if (pair == null)
            {
                Log.Information($"Adding pair {propertyNameId}  for jobid {job.JobID}");
                pair = new PropertyPair
                {
                    PropertyNameID = propertyNameId,
                    LinkID = job.JobID,
                    LinkType = 1,
                    Value = PropertyValue,
                    Source = 3,
                    DataType = 0,
                    BehaviourType = 0
                };
                connect.PropertyPairs.Add(pair);
            }
            else
            {
                Log.Information($"Updating pair Id {pair.PairID} {pair.PropertyNameID} {pair.PropertyName}");
                pair.Value = PropertyValue;
            }

            connect.SaveChanges();
            return pair;
        }

My problem is that when the pair is already in the database MakePair tries to add it.

I am using 2.1.1 from Microsoft.AspNetCore.App SDK

Serlog reports

log: 0636997507330005125,   Adding pair 1116  for jobid 543405, 27-Jul-19 1:12:13 AM +10:00,    837 ms
log: 0636997507330002543,   SaveJob:Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'PropertyPair' when IDENTITY_INSERT is set to OFF.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple`2 parameters)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at ApiForVivtrack3.Feature_Job_Management.JobDataWriteHelper.MakePair(ApiDbContext connect, String propertyName, String PropertyValue, JobLine job) in d:\a\1\s\ApiForVivtrack3\Feature_Job_Management\JobDataWriteHelper.cs:line 102
   at ApiForVivtrack3.Feature_Job_Management.JobDataWrite.AddLineChangesWhereNeeded(ApiDbContext connect, LineItemRequestObject line, Head head, List`1 oldLineItemModifyInfos, Int32 lastJobId) in d:\a\1\s\ApiForVivtrack3\Feature_Job_Management\JobDataWrite.cs:line 380

However I am not trying to set PairID, which is an the identity column. The question is not a duplcate question of this question

Answer 1

Because a pair might have been added in the DbContext before SaveChanges() is called, you have to check whether the pair is in the database, or in the local context as follows.

var pair = connect.PropertyPairs.SingleOrDefault(x =>x.PropertyNameID == propertyNameId && x.LinkID == job.JobID);
if (pair == null)
    pair = connect.PropertyPairs.Local.SingleOrDefault(x =>x.PropertyNameID == propertyNameId  && x.LinkID == job.JobID);