Jasinski Technical Wiki

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

PoweredBy

Extensions Method to Sync Join Table Data - EF Core

RSS
Modified on Tue, Jan 19, 2021, 2:36 PM by Administrator Categorized as 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 }
    );

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2021, Patrick Jasinski.