Dapper insert or Update?

Question

I haven't started using Dapper just yet but just stumbled across it today as I was researching about bulk insert/updates. Currently I'm using EF6 but I would like to look at using Dapper in the future of my bulk stuff. For this app there could be ~15k records but I have other apps that could be ~100k records.

I'm trying to research a way that I could convert the following EF code into Dapper. All it does is reads in a record from a file, look to see if that employee exists in the DB, if so it updates the properties with the values from the file and if not it creates a new object with the values from the file.

I couldn't find any examples when I was looking around. All I could find was how to do a simple insert or an update. I didn't really find a good examples of bulk insert/update. It's very possible I'm just not understanding how to use Dapper just yet.

How would I do this with Dapper?

int count = 1;
using (ctx = new DataContext())
{
    ctx.Configuration.AutoDetectChangesEnabled = false;      
    ctx.Configuration.ValidateOnSaveEnabled = false;

    while ((record = srFile.ReadLine()) != null)
    {
        int employeeId = int.Parse(record.Substring(2, 8));

        bio_employee employee = ctx.bio_employee.FirstOrDefault(e => e.emp_id == employeeId);

        if (employee != null)
        {
            SetEmployeeData(employee, record);

            ctx.Entry(employee).State = System.Data.Entity.EntityState.Modified;
        }
        else
        {
            employee = new bio_employee();
            employee.emp_id = employeeId;

            SetEmployeeData(employee, record);

            ctx.bio_employee.Add(employee);
        }


        if (count % batchSize == 0)
        {
            ctx.SaveChanges();
            ctx.Dispose();
            ctx = new DataContext();
        }

        count++;
    }
    ctx.SaveChanges();      //save any remaining
}

Answer 1

Dapper provides multiple methods to query data but none to perform saving operations other than using a command like you normally do without an ORM.

A lot of third party libraries cover however this scenario for Dapper:

• Dapper Plus (Recommended)
• Dapper Contrib
• Dapper Extensions
• Dapper FastCRUD
• Dapper SimpleCRUD

Disclaimer: I'm the owner of the project Dapper Plus

Dapper Plus is by far the fastest library by providing: BulkInsert, BulkDelete, BulkUpdate, and BulkMerge operations. It can easily support scenarios with millions of records.

// CONFIGURE & MAP entity
DapperPlusManager.Entity<Employee>()
                 .Table("Employee")
                 .Identity(x => x.EmployeeID);

// SAVE entity
connection.BulkMerge(employeeList);

EDIT: Answer subquestion

Is the .BulkMerge in your DapperPlus doing an Upsert

Yes, BulkMerge is an upsert operation.

You can also specify more than one mapping for the same entity by using a mapping key.

// Using key from database (x => x.EmployeeID)
DapperPlusManager.Entity<Employee>()
                 .Table("Employee");

connection.BulkInsert(employees);

// Using custom key
DapperPlusManager.Entity<Employee>("customKey")
                 .Table("Employee")
                 .Key(x => x.Code);

connection.BulkInsert("customKey", employees);