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

Question

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     [phone]
2           1           Mobile  [phone]

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

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

Hemostat

Possible places where this error could occur are - employee.ContactPhoneNumbers.FirstOrDefault().Type, employee.ContactPhoneNumbers.FirstOrDefault(). As a side note, DbContext is not thread safe, so be careful when you play around with context in threads – Patronymic

@Patronymic - Ok. How could we achieve in Parallel execution, is there is any other way? – Hemostat

That should be a different thread all together and I assume there will be lot of threads already available in SO re: datacontext and threads. Lets now focus on fixing your current problem. Check my answer and please let me know whether that works – Patronymic

Instead of using different Tasks, do the following: Load all your data in different context Instances using Task.WhenAll to await them. Dispose those contexts. Create a new context and attach your loaded data. Now do all your modifications on that context. Call SaveChangesAsync to commit all your changes in one transaction to the database. – Suspensory

@Suspensory - Could you please narrate your solution in terms of code please in the answer section. – Hemostat

It would be quite helpful if you provided us with the stacktrace from this exception you noticed. It will be then possible to (at least) say where this exception came from, and that usually allows to find a cause and a fix for that. Stacktraces are really helpful for any nontrivial case. Actually, as a rule of thumb, always examine the stacktraces of all exceptions that are bothering you. Always. – Moy

Your problem is you are using classes that do not support multiple threads from multiple threads, from a quick glance you touch both dbContext and transactionStatus inside of threads with no limit to access. You must lock around transactionStatus and give each thread their own dbContext. – Jacksmelt

Have you checked out msdn.microsoft.com/en-us/library/jj819165(v=vs.113).aspx ? – Patricio

I know you want to use Entity Framework for this but keep in mind if your looking for raw speed, there are better ways. jarloo.com/c-bulk-upsert-to-sql-server-tutorial I asked basically the same question in 2013 and got this response: #17068661 – Sandrasandro

Answer 1

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();
}

Symploce

could you please give me the sample method. – Hemostat

@Hemostat I added some pseudocode but I don't think you really need it... – Symploce

TransactionScope will try to start MSDTC when used in multiple threads. – Otilia

B.Balamanigandan check @GertArnold 's comment , if Distributed Transaction Coordinator is not already started you'll get an Exception "Failed to Enlist Transaction" or something like that – Symploce

Let me know, why we need to attach the entity before remove? Could you please brief me... – Hemostat

@Hemostat That's the best way to delete an entity because you don't have to load the record.if you don't attach it EF doesn't know that it has to delete it – Symploce

Answer 2

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; }

2. Or Eager load the entities using .Include
3. Or Explicit load the entities

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

Patronymic

By default the said property is marked as virtual in the database entity class public partial class DevDBEntities : DbContext – Hemostat

I mean to say, have it as virtual in the Employee entity for enabling lazy loading of those relations – Patronymic

There to its marked as virtual => public virtual ICollection<ContactPhone> ContactPhones { get; set; } in public partial class Employee – Hemostat

oops sorry, I confused it with the DTO class. So could you please verify whether you are getting values in employee.ContactPhoneNumbers ? – Patronymic

Could you please give me the actual explicit coding please. – Hemostat

@Hemostat - have this code var phoneDetails = employee.ContactPhoneNumbers just after Employee emp = dbContext.Employees.Fir...... and check what you are getting in phoneDetails – Patronymic

I'm getting a new exception Message = "Adding a relationship with an entity which is in the Deleted state is not allowed." – Hemostat

If I use var phoneDetails = employee.ContactPhoneNumbers.FirstOrDefault(), I'm getting the following exception Message = "Collection was modified; enumeration operation may not execute." – Hemostat

That is very common exception raised when you try to modify the collection in a loop or under the hood. Could you please comment out all other Task related codes and just check var phoneDetails = employee.ContactPhoneNumbers.FirstOrDefault() alone? – Patronymic