Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Extensions Method to Sync Join Table Data - EF Core

Overview¶

This article provides an extension method for use with EF Core which will sync data from a DTO to a "join table" in the database. A "join table" is one that is in the middle of a many to many relationship.

One common example of a join table is a user role table, which has UserId and RoleId columns.

Extension Method Code¶

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;

public static class LinqExtensions
{
    public static void SyncJoinTable<TKey, Tdto, Tdb>(
        this DbSet<Tdb> target,
        IEnumerable<Tdto> dtoItems,
        Func<Tdto, TKey> selectDtoKey,
        IEnumerable<Tdb> dbItems,
        Func<Tdb, TKey> selectDbKey,
        Func<TKey, Tdb> selectNewEntity
        )
        where Tdto: class
        where Tdb: class
    {
        /*--- Get IDs ---*/
        var dbIds = dbItems.Select(selectDbKey).ToList();

        var dtoIds = dtoItems.Select(selectDtoKey).ToList();

        /*--- Add New Items ---*/
        var newItems = dtoIds
            .Except(dbIds)
            .Select(selectNewEntity)
            .ToList();

        target.AddRange(newItems);

        /*--- Remove Defunct Items ---*/
        foreach (var dbItem in dbItems)
        {
            var itemKey = new List<Tdb>() { dbItem }
                            .Select(selectDbKey)
                            .FirstOrDefault();

            if (!dtoIds.Contains(itemKey))
            {
                target.Remove(dbItem);
            }
        }

    }

}

Sample Usage¶

myDbContext.UserRoles.SyncJoinTable(
    userDTO.Roles,
    x => x.Id,
    user.UserRoles,
    x => x.RoleId,
    x => new AppUserRole { RoleId = x, UserId = userDTO.Id }
    );