Having trouble where a save changes call is timing out after basic add statement

.net-core asp.net-core asp.net-core-webapi c# entity-framework-core

Question

Having trouble with a SaveChanges call that is timing out after I have performed some basic add operations the last being adding a record to a bridging table. I am unsure why this is happening. Could anyone provide some tips and advice?

I am using ASP.NET Core with EF Core as an ORM for a MSSQL Server database.

Here is the code in question.

Edit: Made changes to code based on suggestions in comments.

Edit 2: Appologies I forgot to mention I am using .NET Core 2.2 and EF Core 2.2.6

public AddToQueueResponse AddToQueue(AddToQueueRequest request)
{
    AddToQueueResponse response = new AddToQueueResponse();

    response = (AddToQueueResponse)request.CheckValidation(response);

    if (response.Status == HttpStatusCode.BadRequest)
        return response;

    var nickname = new Nicknames()
    {
        Sid = request.SID,
        NickName = request.Nickname
    };

    var item = new Queueitem()
    {
        Description = request.Description,
        TimeAdded = DateTime.Now,
        TopicId = request.TopicID
    };

    if (request.CheckInID.HasValue)
    {
        var checkIn = _checkInDataLayer.GetCheckIn(request.CheckInID.Value);

        if (checkIn == null)
        {
            response.Status = HttpStatusCode.NotFound;
            response.StatusMessages.Add(new StatusMessage(HttpStatusCode.NotFound, "Check in not in database."));
            return response;
        }
    }

    using (IDbContextTransaction trans = _queueDataLayer.GetTransaction())
    {
        try
        {
            if (!request.StudentID.HasValue)
            {
                _studentDataLayer.AddStudentNickname(nickname);
                _studentDataLayer.Save();

                if (nickname.StudentId == 0)
                {
                    throw new Exception("Unable to add student nickname");
                }
                request.StudentID = nickname.StudentId;
            }

            item.StudentId = request.StudentID.Value;

            _queueDataLayer.AddToQueue(item);
            _queueDataLayer.Save();

            if (request.CheckInID.HasValue)
            {
                Checkinqueueitem checkinqueueitem = new Checkinqueueitem()
                {
                    CheckInId = request.CheckInID.Value,
                    QueueItemId = item.ItemId
                };

                _checkInDataLayer.AddCheckinQueueItem(checkinqueueitem);
                _checkInDataLayer.Save();
            }

            response.ItemId = item.ItemId;
            response.Status = HttpStatusCode.OK;

            trans.Commit();
        }
        catch (Exception ex)
        {
            trans.Rollback();
            s_logger.Error(ex, "Unable to add queue item");
            response.Status = HttpStatusCode.InternalServerError;
            response.StatusMessages.Add(new StatusMessage(HttpStatusCode.InternalServerError, "Unable to add queue item"));
        }
    }
    return response;
}
1
0
12/27/2019 4:18:19 AM

Accepted Answer

Based @bablelias's comment on their answer. I used the built in .NET Core dependency injection so that one instance was used throughout the program. This solved the issue not 100% sure why but I think it is because I know longer have multiple connections interacting with the database at once as there is now one context. Thanks everyone for your assistance.

0
12/31/2019 10:31:26 AM

Popular Answer

I think the problem is that you have two layers (queue and checkin) which, I suppose, has their own dbcontext, constructed separately and thus having different scopes. Writes from first dbcontext lock data for transaction (in second layers). The whole point is that you need to get both contexts work with same transaction. Whether use same context for both levels or share transaction between them (https://docs.microsoft.com/en-us/ef/core/saving/transactions#cross-context-transaction-relational-databases-only). Probably you can also try to change isolation level, but that will be worse imo.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow