In ASP.NET Core-6 Web API using Entity Framework, I have this code:

public async Task<Response<NotificationCredentialListDto>> CreateNotificationCredentialAsync(CreateNotificationCredentialDto requestDto)
{
    var userName = _currentUserService.UserName;
    var response = new Response<NotificationCredentialListDto>();
    using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        if (userName != null)
        {
            try
            {
                var notification = _mapper.Map<NotificationCredential>(requestDto);
                var existingNotification = _dbContext.NotificationCredentials.LastOrDefault(e => e.MerchantId == notification.MerchantId && e.IsModified == false);
                if (existingNotification != null)
                {
                    existingNotification.IsModified = true;
                    _unitOfWork.MerchantNotificationCredentials.Update(notification);
                    await _unitOfWork.Save();
                }
                requestDto.IsModified = false;
                notification.IsModified = requestDto.IsModified;
                notification.UserName = requestDto.UserName;
                requestDto.BasicAuth = encoded;
                notification.BasicAuth = requestDto.BasicAuth;
                notification.CreatedBy = _currentUserService.UserName;

                await _unitOfWork.MerchantNotificationCredentials.InsertAsync(notification);
                await _unitOfWork.Save();
                return response;
            }
            catch (Exception ex)
            {
                _logger.Error("An error occured: "   ex);
                transaction.Dispose();
                return response;
            }
        }
        _logger.Error("Registration failed");
        transaction.Dispose();
        response.StatusCode = (int)HttpStatusCode.BadRequest;
        response.Successful = false;
        response.Message = "Registration failed. Please try again";
        return response;
    }
}

What I want to achieve is that if record exist, it should update the last record as:

IsModified = true

and also insert a new one.

But I git this error:

An error occured: System.InvalidOperationException: Queries performing 'LastOrDefault' operation must have a deterministic sort order. Rewrite the query to apply an 'OrderBy' operation on the sequence before calling 'LastOrDefault'.

How do I resolve this?

Thanks

CodePudding user response：

Like @Rafalon mentioned, you need to sort the collection using OrderBy() before using LastOrDefault().

var existingNotification = _dbContext.NotificationCredentials.OrderBy(e => e.CreatedAt).LastOrDefault(e => e.MerchantId == notification.MerchantId && e.IsModified == false);

But be careful because multiple records could have been added before CreateNotificationCredentialAsync is called. Maybe you should search for all records where MerchantId = notification.MerchantId and e.IsModified = false:

var existingNotifications = _dbContext.NotificationCredentials.Where(e => e.MerchantId == notification.MerchantId && e.IsModified == false).ToList();

and update IsModified property to all of them.

CodePudding user response：

First, I think your code does not work correctly because after checking the condition existingNotification != null and updating the entity MerchantNotificationCredentials you are inserting it in next lines.

Second, I suggest change code to this:

public async Task<Response<NotificationCredentialListDto>> CreateNotificationCredentialAsync(CreateNotificationCredentialDto requestDto)
{
    var userName = _currentUserService.UserName;
    var response = new Response<NotificationCredentialListDto>();
    using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        if (userName != null)
        {
            try
            {
                var notification = _mapper.Map<NotificationCredential>(requestDto);
                var existingNotification = await _dbContext.NotificationCredentials.Where(e => e.MerchantId == notification.MerchantId && e.IsModified == false).OrderByDescending(x=>x.CreatedAt).FirstOrDefaultAsync();
               
                if (existingNotification != null)
                {
                    existingNotification.IsModified = true;
                    _unitOfWork.MerchantNotificationCredentials.Update(notification);
                    await _unitOfWork.Save();
                    return response;
                }
               
                requestDto.IsModified = false;
                notification.IsModified = requestDto.IsModified;
                notification.UserName = requestDto.UserName;
                requestDto.BasicAuth = encoded;
                notification.BasicAuth = requestDto.BasicAuth;
                notification.CreatedBy = _currentUserService.UserName;

                await _unitOfWork.MerchantNotificationCredentials.InsertAsync(notification);
                await _unitOfWork.Save();
          
                return response;
            }
            catch (Exception ex)
            {
                _logger.Error("An error occured: "   ex);
                transaction.Dispose();
                return response;
            }
        }
        _logger.Error("Registration failed");
        transaction.Dispose();
        response.StatusCode = (int)HttpStatusCode.BadRequest;
        response.Successful = false;
        response.Message = "Registration failed. Please try again";
        return response;
    }
}