locked
Update Record Non-primary Key Lookup RRS feed

  • Question

  • User1122355199 posted

    Hello everyone and thanks for the help in advance.  Learning to use EF Core with a Core 3.1 controller that accepts posts from an external web api (Twilio).  The controller accepts a message id that is sent as a string along with a few other fields of data used to update an existing record in a SQL Server database.  Where I am running into problems is that the returned message id is not the primary key of the database table.  So EF requires a lookup before saving routine, however when I utilize the find method, the subsequent update requires I perform the initial lookup using the primary key rather than the message id.  So do I have to do two lookups before updating?  

    Tuesday, June 2, 2020 7:13 PM

Answers

  • User1120430333 posted

    I'm trying to debug this now, but not sure if I'm on the right track to solve the problem.

    Your program threw an unhandled exception. The Web server swallowed it and returned a HTTP 500 error.

    It could help you debug quicker.

    https://stackify.com/csharp-catch-all-exceptions/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 10, 2020 1:47 AM
  • User1120430333 posted

    You should configure Dbcontext based on your needs.

    https://docs.microsoft.com/en-us/ef/core/miscellaneous/configuring-dbcontext

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 11, 2020 6:01 PM

All replies

  • User475983607 posted

    kmcnet

    So EF requires a lookup before saving routine, however when I utilize the find method, the subsequent update requires I perform the initial lookup using the primary key rather than the message id.  So do I have to do two lookups before updating?  

    Is the messageId unique?  If so,  fix the design and make the messageId the primary key.   That is, if you want to use Find().   If the messageId is not unique then you need another piece of information to get the correct record.

    Tuesday, June 2, 2020 7:40 PM
  • User1122355199 posted

    Not quite so simple.  The sending application that logs the message into the database.  The application then calls the api which retrieves the message id.  So the message id isn't present when the record is stored in the database, so I don't think this could be the primary key.

    Tuesday, June 2, 2020 8:33 PM
  • User475983607 posted

    Not quite so simple.  The sending application that logs the message into the database.  The application then calls the api which retrieves the message id.  So the message id isn't present when the record is stored in the database, so I don't think this could be the primary key.

    It sounds like a records is created then later the same record is updated with a messageId.  I'm not sure what problem you are trying to solve. If the messageId is unique then you can simply query by messageId once the message Id is available. 

    Is the problem you are having difficulty finding the record after receiving the messageId?

    Tuesday, June 2, 2020 8:48 PM
  • User1120430333 posted

    I would sort this out by using a stored procedure.

    Tuesday, June 2, 2020 8:55 PM
  • User1122355199 posted

    You mean call a stored procedure from EF?

    Tuesday, June 2, 2020 9:05 PM
  • User1120430333 posted


    You mean call a stored procedure from EF?

    https://www.entityframeworktutorial.net/efcore/working-with-stored-procedure-in-ef-core.aspx

    Wednesday, June 3, 2020 12:08 AM
  • User1686398519 posted

    Hi,  kmcnet

    You can use the LINQ statement to query.

    var test=db.Tests.Where(t => t.messageId == messageId).SingleOrDefault();

     Best Regards,

    YihuiSun

    Wednesday, June 3, 2020 8:27 AM
  • User303363814 posted

    Show your code

    Don't make us guess what you are doing.  Help us to help you.

    Thursday, June 4, 2020 5:45 AM
  • User1122355199 posted

    Sorry for the lack of response.  Not trying to make you guess.  I'm reading docs on EF and stored procedures along with looking at alternatives on my models.  Will post an update this afternoon.

    Thursday, June 4, 2020 12:49 PM
  • User1122355199 posted

    OK.  Sorry again for the delay.  I have the problem partially solved.  Here is what the code currently looks like:

            public UpdateSMSStatus(string SMSSid, string SMSStatus, string ErrorCode)
            {
                try
                {
    
                    using (var ctx = new myDBContext())
                    {
    
                        var entity = ctx.tbl_Log_SMSMessages.FirstOrDefault(item => item.SmsMessageSid == SMSSid);
                        entity.SmsStatus = SMSStatus;
                        if (ErrorCode != "")
                        {
                            entity.ErrorCode = ErrorCode;
                        }
                        
                        ctx.SaveChanges();

    My original problem seemed to stem from a couple of areas.  First, the Twilio Api seems to send a different model on SMS status updates than on the original send.  Thus the use o strings rather than a model.  Next, my model the table failed to have a [Key] decoration and also failed to account for a nullable field which would have come into play on the select portion of the update.  My model now looks like this:

        public class tbl_Log_SMSMessages
        {
            [Key]
            public int ID { get; set; }
            public string? AccountSid { get; set; }
            public string? ApiVersion { get; set; }
            public string Body { get; set; }
            public string? Direction { get; set; }
            public string? ErrorCode { get; set; }
            public string? From { get; set; }
            public string? FromCity { get; set; }
            public string? FromCountry { get; set; }
            public string? FromState { get; set; }
            public string? FromZip { get; set; }
            public string? MessageSid { get; set; }
            public int? NumMedia { get; set; }
            public int? NumSegments { get; set; }
            public string? SmsMessageSid { get; set; }
            public string? SmsStatus { get; set; }        
            public string? To { get; set; }
            public string? ToCity { get; set; }
            public string? ToCountry { get; set; }
            public string? ToState { get; set; }
            public string? ToZip { get; set; }
        }

    So the original error has not resolved.  However, I am now receiving the following error on SOME updates:

    System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseSqlServer' call

    From this message, I referred to the article:  https://docs.microsoft.com/en-us/dotnet/architecture/microservices/implement-resilient-applications/implement-resilient-entity-framework-core-sql-connections

    I then modified my Startup.cs file 

            public void ConfigureServices(IServiceCollection services)
            {
                services.AddControllersWithViews();
                //
    
                services.AddDbContext<CeCemContext>(options =>
                {
                    options.UseSqlServer(Configuration["ConnectionString"], sqlServerOptionsAction: sqlOptions =>
                    {
                        sqlOptions.EnableRetryOnFailure(
                        maxRetryCount: 10,
                        maxRetryDelay: TimeSpan.FromSeconds(30),
                        errorNumbersToAdd: null);
                    });
                });
    
            }

    The controller that calls the update routine:

            [ValidateTwilioRequest]
            [HttpPost]
    
            public TwiMLResult StatusCallback()
            {
    
    
    
                try
                {
    
    
                    string ErrorMessage = Request.Form["ErrorMessage"];
                    string MessageSid = Request.Form["MessageSid"];
                    string From = Request.Form["From"];
                    string ErrorCode = "";
                    if (String.IsNullOrEmpty(Request.Form["ErrorCode"]))
                    {
    
                    }
                    else
                    {
                        ErrorCode = Request.Form["ErrorCode"];
                    }
    
    
                    UpdateSMSStatus updateSMSStatus = new UpdateSMSStatus(SMSSid, SMSStatus, ErrorCode);
                }
                catch (Exception ex)
                {
    
                }
                
    
                return null;
            }

    The controller is currently returning a 500 error that I am trying to debug.  I'm trying to debug this now, but not sure if I'm on the right track to solve the problem.

    Wednesday, June 10, 2020 12:08 AM
  • User1120430333 posted

    I'm trying to debug this now, but not sure if I'm on the right track to solve the problem.

    Your program threw an unhandled exception. The Web server swallowed it and returned a HTTP 500 error.

    It could help you debug quicker.

    https://stackify.com/csharp-catch-all-exceptions/

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 10, 2020 1:47 AM
  • User1122355199 posted

    Thanks for the help.  Now getting the following error:

    ArgumentException: AddDbContext was called with configuration, but the context type 'myDbContext' only declares a parameterless constructor. This means that the configuration passed to AddDbContext will never be used. If configuration is passed to AddDbContext, then 'myDbContext' should declare a constructor that accepts a DbContextOptions<myDbContext> and must pass it to the base constructor for DbContext.

    Context looks like this:

        public class myDbContext : DbContext
        {
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
    
                optionsBuilder.UseSqlServer(connectionString);
            }
    
        }

    Trying to figure out what the next step is.

    Thursday, June 11, 2020 1:57 AM
  • User475983607 posted

    The DbContext should have the follow shape for DI registration.

        public class myDbContext : DbContext
        {
            public myDbContext(DbContextOptions<myDbContext> options) : base(options)
            {
    
            }
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
    
               // optionsBuilder.UseSqlServer(connectionString);
            }
    
        }

    Based on your questions and code, I recommend the Data Access tutorials.

    https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/?view=aspnetcore-3.1

    https://docs.microsoft.com/en-us/aspnet/core/data/ef-rp/intro?view=aspnetcore-3.1&tabs=visual-studio

    The tutorials cover basics like setting up the DbContext, model binding, and working with data.

    Thursday, June 11, 2020 10:23 AM
  • User1120430333 posted

    You should configure Dbcontext based on your needs.

    https://docs.microsoft.com/en-us/ef/core/miscellaneous/configuring-dbcontext

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 11, 2020 6:01 PM
  • User1122355199 posted

    This was exactly what I was looking for.  Looks like all errors are gone.  One other question.  Is it advisable, while using resilient connections, to combine with some type of async to avoid bottlenecks?

    Thursday, June 11, 2020 8:16 PM
  • User475983607 posted

    Is it advisable, while using resilient connections, to combine with some type of async to avoid bottlenecks?

    Always use the async/await pattern when making calls to asynchronous APIs.  These are things like an DB queries or HTTP requests. 

    Thursday, June 11, 2020 8:43 PM