WhereBulkContains returns duplicate rows from the DB

[SClifford1]

Description

WhereBulkContains does not behave as I would have expected when filtering with a list containing duplicate entries. I expected filtered rows to appear at most once in the results but instead, I see rows duplicated.

This query returns a list of size 4, with Groups with ID 1 and 2 included twice. Id is the primary key of the groups table.

var targetGroups1 = await this.targetRepository.Set<Group>()
	.WhereBulkContains(( new List<int>() {1,1,2,2 } ), g => g.Id)
	.ToListAsync();

I would have expected the same behaviour as using a Contains with EF. This query returns a list of size 2, with Groups 1/2 included once.

var targetGroups2 = await this.targetRepository.Set<Group>()
	.Where(g => ( new List<int>() {1,1,2,2 } ).Contains(g.Id))
	.ToListAsync();

I can obviously work around this by providing a distinct list of Ids above but it would be good if this could be fixed all the same.

Further technical details

• EF version: [EF Core v7.0.13]
• EF Extensions version: [EFE Core v7.100.0.5]
• Database Server version: [Azure SQL Database V12]
• Database Provider version (NuGet): [Microsoft.Data.SqlClient v5.1.1]

[JonathanMagnan]

Hello @SClifford1 ,

Thank you for reporting. We will look into it.

Best Regards,

Jon

[albert-tan]

Hi, we encountered the same issue, and I have created a dotnetfiddle sample code that you may run to reproduce the issue.

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
					
public class Program
{
	public static void Main()
	{
		Console.WriteLine("Hello World");
		
		using(var dbContext = new TestDbContext())
		{
			dbContext.Database.EnsureCreated();
			dbContext.Add(new Record() {Value = "Val 1"} );
			dbContext.Add(new Record() {Value = "Val 2"} );
			dbContext.Add(new Record() {Value = "Val 3"} );
			dbContext.Add(new Record() {Value = "Val 4"} );
			dbContext.Add(new Record() {Value = "Val 5"} );
			dbContext.SaveChanges();
			
			var includeList = new List<int>() { 1, 2, 3, 3, 3 };
			
			var filteredResult1 = dbContext.Records.WhereBulkContains(includeList, rec => rec.Id).ToList();
			Console.WriteLine($"Filtered IDs by WhereBulkContains(): {string.Join(", ", filteredResult1.Select(rec => rec.Id))}");
			
			var filteredResult2 = dbContext.Records.Select(rec => rec.Id).Intersect(includeList).ToList();
			Console.WriteLine($"Filtered IDs by Intersect(): {string.Join(", ", filteredResult2)}");
		}
	}
	
	public class TestDbContext : DbContext
	{
		public DbSet<Record> Records { get; set; }
		
		protected override void OnConfiguring(DbContextOptionsBuilder options)
		{
			options.UseSqlServer(FiddleHelper.GetConnectionStringSqlServer());
		}
	}
	
	public class Record
	{
		public int Id { get; set; }
		public string Value { get; set; }
	}
}