EF Extensions - WhereBulkNotContains - Example

CSharp · Console

/Program.cs

// Entity Framework Extensions
// Doc: https://entityframework-extensions.net/where-bulk-not-contains

// @nuget: Newtonsoft.Json
// @nuget: Microsoft.EntityFrameworkCore.SqlServer
// @nuget: Z.EntityFramework.Extensions.EFCore

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
					
public class Program
{
	public static void Main()
	{
		// Doc: https://entityframework-extensions.net/where-bulk-not-contains

		SeedDatabase();
		var deserializedCustomers = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Customer>>(GetSerializedCustomers());
		FiddleHelper.WriteTable("1 - DeserializedCustomers", deserializedCustomers);

		// ## How to use the method WhereBulkNotContains?
		// The most basic scenario is passing a list to the `WhereBulkNotContains` method.
		// The `WhereBulkNotContains` method will filter entities to exclude those contained in the list.
		{
			using (var context = new EntityContext())
			{
				// The `JOIN` statement will use the default entity key if none is provided (CustomerID)
				var customers = context.Customers.WhereBulkNotContains(deserializedCustomers);
				FiddleHelper.WriteTable("2 - WhereBulkNotContains + Default Key", customers);
			}

			using (var context = new EntityContext())
			{
				// You can specify a custom `JOIN` clause with one or many properties using a `Lambda Expression`
				var customers = context.Customers.WhereBulkNotContains(deserializedCustomers, x => x.Code);
				FiddleHelper.WriteTable("3 - WhereBulkNotContains + Lambda Expression", customers);
			}

			using (var context = new EntityContext())
			{
				// You can specify a custom `JOIN` clause with one or many properties using a `List<string>`
				var customers = context.Customers.WhereBulkNotContains(deserializedCustomers, new List<string> { "Code" });
				FiddleHelper.WriteTable("4 - WhereBulkNotContains + Params string[]", customers);
			}

			using (var context = new EntityContext())
			{
				// You can specify a custom `JOIN` clause with one or many properties using a `params string[]`
				var customers = context.Customers.WhereBulkNotContains(deserializedCustomers, "Code");
				FiddleHelper.WriteTable("5 - WhereBulkNotContains + List<string>", customers);
			}
		}

		// ## Scenario
		// For example, you deserialize a JSON file into a list of `Customer` with the `CustomerID` populated, and you want to retrieve customers not contained in this list to deleted them.

		{
			using (var context = new EntityContext())
			{
				FiddleHelper.WriteTable("6 - Customers Before", context.Customers.AsNoTracking().ToList());

				var customers = context.Customers.WhereBulkNotContains(deserializedCustomers);

				context.Customers.RemoveRange(customers);

				context.BulkSaveChanges();

				FiddleHelper.WriteTable("7 - Customers After", context.Customers.AsNoTracking().ToList());
			}
		}
	}

	public static void SeedDatabase()
	{
		using (var context = new EntityContext())
		{
			var list = new List<Customer>();

			for (int i = 0; i < 5; i++)
			{
				list.Add(new Customer() { Code = "Original_" + i, Email = "Original_" + i, FirstName = "Original_" + i, LastName = "Original_" + i });
			}

			context.BulkInsert(list);
		}
	}

	public static string GetSerializedCustomers()
	{
		var customers = new List<Customer>();

		for (int i = 0; i < 3; i++)
		{
			customers.Add(new Customer() { CustomerID = i + 1, Code = "Original_" + i, Email = "Updated_" + i, FirstName = "Updated_" + i, LastName = "Updated_" + i });
		}

		return JsonConvert.SerializeObject(customers);
	}

	public class EntityContext : DbContext
	{
		static EntityContext()
		{
			using (var context = new EntityContext())
			{
				context.Database.EnsureCreated();
			}
		}
		
		public DbSet<Customer> Customers { get; set; }

		protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
		{
			optionsBuilder.UseSqlServer(new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()));

			base.OnConfiguring(optionsBuilder);
		}
	}

	[Table("Customer")]
	public class Customer
	{
		public int CustomerID { get; set; }
		public string Code { get; set; }
		public string Email { get; set; }
		public string FirstName { get; set; }
		public string LastName { get; set; }
	}
}