none
Async/Await problem RRS feed

  • Question

  • I do have a problem with regards to using async in my code. I have an async query that pulls data from the database.

    My code will look something like this

    //do a query to check if the data exist

    var result=await MyAsyncCallToDatabase();

    if(result==null) { //insert the data to the database

    }

    Do I need to wait for my async call to finish getting all the data I want before doing the checking if it does return something because what I am experiencing right now is it returns null and then when I am trying to do the insert which is inside the if condition, SQL server returned an exception which says that I can't insert the duplicate data.

    UPDATE: Provide more information

     public class CustomerCreate
        {
            private readonly ICustomerRepository _customerPo;
    
            public CustomerCreate(ICustomerRepository customerPo)
            {
                _customerPo = customerPo;
            }
    
            public async Task<int> CreateCustomer(PoCustomer custShipping)
            {
                var getCustomerShipping = await _customerPo.GetCustomerByKeyAsync(custShipping.CustomerId);
                if (getCustomerShipping == null)
                {
                  return  await _customerPo.InsertAsync(custShipping);
                }
                return -1;
            }
        }
    
        public interface ICustomerRepository
        {
            Task<int> InsertAsync(PoCustomer customer);
            Task<PoCustomer> GetCustomerByKeyAsync(Guid key);
        }
    
        public class CustomerReposity : ICustomerRepository
        {
            public Task<int> InsertAsync(PoCustomer customer)
            {
                
                    string sql = @"Insert into Customer
                              (FName, LName, BusinessName, Phone, Address1, Address2, City, State, Zip, Email,CustomerId )
                              Values
                              (@FName, @LName, @BusinessName, @Phone, @Address1, @Address2, @City, @State, @Zip, @Email,@CustomerId)";
    
                    using (var con = new SqlConnection(ConnectionString))
                    {
                        return (await con.ExecuteAsync(sql, customer));
                    }
                
            }
    
            public async Task<PoCustomer> GetCustomerByKeyAsync(Guid key)
            {
                string sql = "Select * from Customer Where CustomerId=@CustomerId";
    
                using (var con = new SqlConnection(ConnectionString))
                {
                    return (await con.QueryAsync<PoCustomer>(sql, new { CustomerId = key })).SingleOrDefault();
                }
            }
        }

    Error Received: 

    Violation of PRIMARY KEY constraint 'PK__MYTABLE__A4AE64D80D0838CF'. Cannot insert duplicate key in object 'dbo.MYTABLE'. The duplicate key value is (e002b592-0abd-e811-8392-90b11c601f63).
    The statement has been terminated.'


    Regards




    • Edited by Dikong42 Tuesday, October 16, 2018 9:42 PM
    Tuesday, October 16, 2018 8:44 PM

All replies

  • What's the signature of MyAsyncCallToDatabase()?
    Tuesday, October 16, 2018 9:12 PM
  • if(result==null) {

    You forgot one '=' sign. It should be if(result == null).

    Greetings, Chris

    • Edited by DerChris88 Tuesday, October 16, 2018 9:21 PM
    Tuesday, October 16, 2018 9:19 PM
  • MyAsyncCallToDatabase() is truly an async method call. 

    It could be something like this, I'm using dapper by the way.

     public async  Task<List<string>> ThisIsAnAsyncCall()
            {
                string sql = @"SELECT * from Table1";
                using (var con = new SqlConnection(ConnectionString))
                {
                    return (await con.QueryAsync<string>(sql)).ToList();
                }
            }

    Tuesday, October 16, 2018 9:22 PM
  • Edited Thanks
    Tuesday, October 16, 2018 9:26 PM
  • What is MyAsyncCallToDatabase doing exactly, maybe you can show us some code? Is there really a row in you db after calling that method? How is the SQL-query looking? 
    Tuesday, October 16, 2018 9:27 PM
  • Added additional information.
    Tuesday, October 16, 2018 9:35 PM
  • I think "public Task<int> InsertAsync(PoCustomer customer)" should be async too:

    ...
    public async Task<int> InsertAsync(PoCustomer customer)
    ...

    Tuesday, October 16, 2018 9:47 PM
  • Sorry about the typo.. .
    Tuesday, October 16, 2018 9:54 PM
  • What is dbo.MYTABLE?
    Tuesday, October 16, 2018 10:05 PM
  • Hi Dikong42,

    Thank you for posting here.

    According to your description, please try to set the ID like below in your SQL database.

    T-SQL

      [Id]    INT   IDENTITY (1, 1) NOT NULL,

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 17, 2018 7:19 AM
    Moderator
  • My ID is an auto  generated Guid  like so

    ALTER TABLE [dbo].[MYTALBE] ADD  CONSTRAINT [DF__MYTALBE__Custom__778AC167]  DEFAULT (NEWSEQUENTIALID()) FOR [TableId]
    GO


    • Edited by Dikong42 Wednesday, October 17, 2018 1:04 PM
    Wednesday, October 17, 2018 1:03 PM
  • Your await looks fine. You have to wait for it to finish before you can check for null. The await unwraps the method call. The return type for MyAsyncCallToDatabase is going to be Task<T>. So if you don't await you get back a Task (that isn't going to be null). The await effectively does this.

    //Your code
    var task = MyAsyncCallToDatabase();
    task.Wait();
    var result = task.Result;
    
    //Or more concisely
    var result = MyAsyncCallToDatabase().Result;
    
    //All that is equivalent to this
    var result = await MyAsyncCallToDatabase();

    Note that for non-UI code you should almost always use ConfigureAwait(false) on the end of your await call.

    The error you are receiving has nothing to do with the await you posted. The error is occurring because you're trying to insert data into your DB where one of the rows has a (database-defined) primary key that is already in the database. Hence you get a constraint error from the DB. The issue is with the code that is trying to insert into the DB.

    I assume this is happening in your InsertAsync call so put a breakpoint on that DB call. Then look at the insert statement it is generating. The value the error is complaining about is a GUID. My gut instinct is that it is your CustomerId column. You are inserting into a Customer table. Standard DB design says the primary key is either Id or TableId so CustomerId is probably the primary key table. Since it is the primary key the DB is responsible for setting it. Remove the CustomerId from the list of columns and the list of values you're sending and see if the error goes away. You'll have to subsequently get the inserted ID from the DB but that generally comes back from the results of the insert.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, October 17, 2018 2:09 PM
    Moderator