EF Core中通过LINQ关联C#列表实现高效数据过滤的问题

问题背景 #

用Contains查询时，EF Core会生成带IN子句的SQL，当ID列表长度变化频繁时，SQL Server会生成大量不同的查询计划，导致查询计划污染，拖慢性能。而直接用LINQ的join或交叉查询内存列表，EF Core无法转换为临时表关联的SQL，只会展开成IN子句或多条件判断，达不到预期效果。

可行方案：创建临时表并关联查询 #

通过手动创建数据库临时表，将ID列表插入后再关联查询Foo表，EF Core能正确转换为关联SQL，且查询计划可复用，彻底避免污染问题。

步骤1：定义临时表对应的实体类

public class TempId
{
    public int Id { get; set; }
}

步骤2：在DbContext中配置临时表映射

无需添加迁移，直接在OnModelCreating中完成映射配置：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TempId>()
        .ToTable("#TempIds") // 临时表前缀#，标识会话级临时表
        .HasKey(t => t.Id);
}

步骤3：创建临时表并插入ID列表

var idsList = new List<int> { 1, 4, ..., 9999 };

// 创建临时表结构
await context.Database.ExecuteSqlRawAsync("CREATE TABLE #TempIds (Id INT PRIMARY KEY)");

// 批量插入ID（用参数化方式避免SQL注入）
var parameters = new List<SqlParameter>();
var valuePlaceholders = new List<string>();

for (int i = 0; i < idsList.Count; i++)
{
    var paramName = $"@Id{i}";
    parameters.Add(new SqlParameter(paramName, idsList[i]));
    valuePlaceholders.Add($"({paramName})");
}

var insertSql = $"INSERT INTO #TempIds (Id) VALUES {string.Join(", ", valuePlaceholders)}";
await context.Database.ExecuteSqlRawAsync(insertSql, parameters.ToArray());

步骤4：关联临时表查询Foo数据

var filteredFoos = await context.Foos
    .Join(context.Set<TempId>(),
          foo => foo.Id,
          tempId => tempId.Id,
          (foo, _) => foo)
    .ToListAsync();

注意事项 #

• 临时表是会话级别的，当前数据库连接关闭后会自动删除，无需手动清理。
• 如果ID列表长度超过SQL Server的参数限制（默认2100），可以分批次插入临时表。
• 该方法适用于EF Core 3.0及以上版本，确保ExecuteSqlRawAsync和临时表映射能正常工作。

内容的提问来源于stack exchange，提问作者Kilas