List<Task> - UPSERT database record using C# Entity Framework

2k views Asked by At

I have an Employee object, I'm trying to update a record (i.e., Update / Remove) using a multiple task (Parallel Execution) using single DB Entity Context. But I'm getting the following exception

Message = "Object reference not set to an instance of an object."

Consider the following DTO's

public class Employee
{
    public int EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List<ContactPhone> ContactPhoneNumbers { get; set; }
    public List<ContactEmail> ContactEmailAddress { get; set; }
}

public class ContactPhone
{
    public int ContactId { get; set; }
    public string Type { get; set; }
    public string Number { get; set; }
}

public class ContactEmail
{
    public int ContactId { get; set; }
    public string Type { get; set; }
    public string Number { get; set; }
}

Employee Table:

EmployeeId  FirstName   LastName
_________________________________
1           Bala        Manigandan

ContactPhone Table:

ContactId   EmployeeId  Type    Number
__________________________________________
1           1           Fax     9123456789
2           1           Mobile  9123456789

ContactPhone Table:

ContactId   EmployeeId  Type    EmailAddress
______________________________________________
1           1           Private [email protected]
2           1           Public  [email protected]

In-Coming API Object is

DTO.Employee emp = new DTO.Employee()
{
    EmployeeId = 1,
    FirstName = "Bala",
    LastName = "Manigandan",
    ContactPhoneNumbers = new List<DTO.ContactPhone>
        {
            new DTO.ContactPhone()
            {
                Type = "Mobile",
                Number = "9000012345"
            }
        },
    ContactEmailAddress = new List<DTO.ContactEmail>()
        {
            new DTO.ContactEmail()
            {
                Type = "Private",
                EmailAddress = "[email protected]"
            },
            new DTO.ContactEmail()
            {
                Type = "Public",
                EmailAddress = "[email protected]"
            }
        }
};

I'm getting an API request to update Mobile number and to remove the Fax number for a specified Employee.

Consider the task methods:

public void ProcessEmployee(DTO.Employee employee)
{
    if(employee != null)
    {
        DevDBEntities dbContext = new DevDBEntities();

        DbContextTransaction dbTransaction = dbContext.Database.BeginTransaction();

        List<Task> taskList = new List<Task>();
        List<bool> transactionStatus = new List<bool>();

        try
        {
            Employee emp = dbContext.Employees.FirstOrDefault(m => m.EmployeeId == employee.EmployeeId);

            if (emp != null)
            {
                Task task1 = Task.Factory.StartNew(() =>
                {
                    bool flag = UpdateContactPhone(emp.EmployeeId, employee.ContactPhoneNumbers.FirstOrDefault().Type, employee.ContactPhoneNumbers.FirstOrDefault().Number, dbContext).Result;
                    transactionStatus.Add(flag);
                });

                taskList.Add(task1);

                Task task2 = Task.Factory.StartNew(() =>
                {
                    bool flag = RemoveContactPhone(emp.EmployeeId, "Fax", dbContext).Result;
                    transactionStatus.Add(flag);
                });

                taskList.Add(task2);
            }

            if(taskList.Any())
            {
                Task.WaitAll(taskList.ToArray());
            }
        }
        catch
        {
            dbTransaction.Rollback();
        }
        finally
        {
            if(transactionStatus.Any(m => !m))
            {
                dbTransaction.Rollback();
            }
            else
            {
                dbTransaction.Commit();
            }

            dbTransaction.Dispose();
            dbContext.Dispose();
        }
    }
}

public async Task<bool> UpdateContactPhone(int empId, string type, string newPhone, DevDBEntities dbContext)
{
    bool flag = false;

    try
    {
        var empPhone = dbContext.ContactPhones.FirstOrDefault(m => (m.EmployeeId == empId) && (m.Type == type));
        if (empPhone != null)
        {
            empPhone.Number = newPhone;
            await dbContext.SaveChangesAsync();
            flag = true;
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }

    return flag;
}

public async Task<bool> RemoveContactPhone(int empId, string type, DevDBEntities dbContext)
{
    bool flag = false;

    try
    {
        var empPhone = dbContext.ContactPhones.FirstOrDefault(m => (m.EmployeeId == empId) && (m.Type == type));
        if (empPhone != null)
        {
            dbContext.ContactPhones.Remove(empPhone);
            await dbContext.SaveChangesAsync();
            flag = true;
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }

    return flag;
}

I'm getting following exception:

Message = "Object reference not set to an instance of an object."

Here with I'm attaching the screenshot for your reference

enter image description here

My requirement is to do all the database UPSERT processes in parallel execution, kindly assist me how to achieve this without any exception using Task

2

There are 2 answers

6
George Vovos On BEST ANSWER

1st)Stop using the context in different threads.
DbContext is NOT thread safe,that alone can cause many strange problems ,even a crazy NullReference exception

Now,are you sure your Parallel code is faster than a non parallel implementation?
I very much doubt that.

From what I see you are don't even changing your Employee object so I don't see why you should load it (twice)

I think all you need is
1)Load the phone which you need to update and set the new Number
2)Delete the unused Mobile
DON'T have to load this record.Just use the default constructor and set the Id.
EF can handle the rest (Of course you need to attach the newly created object)

3)Save your changes
(Do 1,2,3 in 1 method using the same context)

If for some reason you do decide to go with multiple tasks

  1. Create a new context within each Task
  2. Wrap your code in a TransactionScope

Update
I just noticed this:

catch (Exception ex) { throw ex;    }

This is bad (you lose the stacktrace)
Either remove the try/catch or use

catch (Exception ex) { throw ; }

Update 2
Some sample code (I assume your input contains the Ids of the entities you want to update/delete)

 var toUpdate= ctx.ContactPhones.Find(YourIdToUpdate);
 toUpdate.Number = newPhone;

 var toDelete= new ContactPhone{ Id = 1 };
 ctx.ContactPhones.Attach(toDelete);
 ctx.ContactPhones.Remove(toDelete);
 ctx.SaveChanges();

If you go with the parallel approach

using(TransactionScope tran = new TransactionScope()) {
    //Create and Wait both Tasks(Each task should create it own context)
    tran.Complete();
}
9
Developer On

Possible places where this error could occur are - employee.ContactPhoneNumbers.FirstOrDefault().Type, employee.ContactPhoneNumbers.FirstOrDefault()

The employee.ContactPhoneNumbers will be possibly null as you are not eager loading it nor you have marked the property as virtual so that it would lazy load.

So to fix this issue: 1. Mark the navigational properties as virtual so that lazy load

public virtual List<ContactPhone> ContactPhoneNumbers { get; set; }
public virtual List<ContactEmail> ContactEmailAddress { get; set; }
  1. Or Eager load the entities using .Include
  2. Or Explicit load the entities

dbContext.Entry(emp).Collection(s => s.ContactPhoneNumbers).Load(); dbContext.Entry(emp).Collection(s => s.ContactEmailAddress ).Load();