Sync data to database with Entity-Framework Core

We already know how to add data to database. That's simple:

_dbContext.MyDbSet.Add(myObject);

But there may already exists some data in the database. We need to delete the obsolete data, and try to add the missing data.

For example, I have some numbers:

1, 1, 2, 2, 3, 3

While in the database there is:

1, 1, 1, 5

To seed the database to the way we expected, we shall delete the first 1 and the 5. And then insert 2, 2, 3, 3.

We call the process:

Modify the table in the database to the data we expected, and delete the data what we don't need

DbSet.Sync().

For example, in my dbContext, there is dataset Numbers:

    public class MyNumber
    {
        [Key]
        public int Id { get; set; }
        public int Value { get; set; }
    }


    public class ApplicationDbContext : IdentityDbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        public DbSet<MyNumber> Numbers { get; set; }
    }

We want to change the numbers in the database to the collection we need.

First, we gonna declare what we need in the memory. The data source model:

    public class MyDataSourceNumber
    {
        public int ValueInMemory { get; set; }
    }

To make the database souce mappable to entity, we need to declare a new interface

    public interface ISyncable<T>
    {
        bool EqualsInDb(T obj);
        T Map();
    }

And implement the interface:

    public class MyDataSourceNumber : ISyncable<MyNumber>
    {
        public int ValueInMemory { get; set; }
        public bool EqualsInDb(MyNumber obj)
        {
            return ValueInMemory == obj.Value;
        }

        public MyNumber Map()
        {
            return new MyNumber()
            {
                Value = ValueInMemory
            };
        }
    }

This declares that the entity MyDataSource is a type which can be mapped to database. If the data shall be modified in database shall be considered from method EqualsInDb. And to really insert data to database, Map method shall be called to create entity.

And paste the following code to allow syncing data:

    public static class EFExtends
    {
        public static IEnumerable<M> DistinctBySync<T, M>(this IEnumerable<M> query) where M : ISyncable<T>
        {
            var knownKeys = new HashSet<M>();
            foreach (M element in query)
            {
                if (!knownKeys.Any(k => k.EqualsInDb(element.Map())))
                {
                    knownKeys.Add(element);
                    yield return element;
                }
            }
        }

        public static void Sync<T, M>(this DbSet<T> dbSet,
            M[] collection)
            where T : class
            where M : ISyncable<T>
        {
            dbSet.Sync(t => true, collection);
        }

        public static void Sync<T, M>(this DbSet<T> dbSet,
            Func<T, bool> filter,
            M[] collection)
            where T : class
            where M : ISyncable<T>
        {
            foreach (var item in collection.DistinctBySync<T, M>())
            {
                var itemCountShallBe = collection.Count(t => t.EqualsInDb(item.Map()));
                var itemQuery = dbSet
                    .IgnoreQueryFilters()
                    .Where(filter)
                    .AsEnumerable()
                    .Where(t => item.EqualsInDb(t));
                var itemCount = itemQuery
                    .Count();

                if (itemCount > itemCountShallBe)
                {
                    dbSet.RemoveRange(itemQuery.Skip(itemCountShallBe));
                }
                else if (itemCount < itemCountShallBe)
                {
                    for (int i = 0; i < itemCountShallBe - itemCount; i++)
                    {
                        dbSet.Add(item.Map());
                    }
                }
            }
            var toDelete = dbSet
                .AsEnumerable()
                .Where(filter)
                .Where(t => !collection.Any(p => p.EqualsInDb(t)));
            dbSet.RemoveRange(toDelete);
        }
    }

After doing that, you can simply sync your data.

var targetCollection = (new int[] { 1, 1, 2, 2, 3, 3 }) // The data you want to sync to database.
    .Select(t => new MyDataSourceNumber
    {
        ValueInMemory = t
    })
.ToArray();

_dbContext.Numbers.Sync(targetCollection);
await _dbContext.SaveChangesAsync();

You don't have to care about the process. The Sync method will delete obsolete data and migrate the database data to what you input with minimum changes.

For example, if your existing data is:

2, 3, 4

It will delete data 4 and insert 1, 1, 2, 3 to the database.