如何用C# LINQ通过分组与组间交集找出所有表共有的字段？

1. 定义数据实体类 #

先定义对应字段结构的实体类，用来承载数据：

public class FieldInfo
{
    public string TableSchema { get; set; }
    public string TableName { get; set; }
    public string FieldName { get; set; }
    public string FieldType { get; set; }
}

2. 模拟测试数据 #

根据你提供的表格数据，构造测试集合：

var fields = new List<FieldInfo>
{
    new() { TableSchema = "public", TableName = "tableA", FieldName = "fieldA", FieldType = "character varying" },
    new() { TableSchema = "public", TableName = "tableA", FieldName = "fieldB", FieldType = "timestamp" },
    new() { TableSchema = "public", TableName = "tableA", FieldName = "fieldC", FieldType = "bytea" },
    new() { TableSchema = "public", TableName = "tableB", FieldName = "fieldA", FieldType = "character varying" },
    new() { TableSchema = "public", TableName = "tableB", FieldName = "fieldD", FieldType = "integer" },
    new() { TableSchema = "other", TableName = "tableC", FieldName = "fieldA", FieldType = "character varying" },
    new() { TableSchema = "other", TableName = "tableC", FieldName = "fieldE", FieldType = "integer" }
};

3. 核心LINQ实现 #

通过分组+交集操作筛选出所有表共有的字段：

// 按「表架构+表名」分组，提取每组的(字段名, 字段类型)集合
var tableFieldSets = fields
    .GroupBy(f => new { f.TableSchema, f.TableName })
    .Select(group => group.Select(f => (f.FieldName, f.FieldType)).ToHashSet())
    .ToList();

// 计算所有表字段集合的交集
HashSet<(string FieldName, string FieldType)> commonFields = null;
if (tableFieldSets.Any())
{
    commonFields = tableFieldSets.Aggregate((currentIntersection, nextTableFields) =>
    {
        currentIntersection.IntersectWith(nextTableFields);
        return currentIntersection;
    });
}

// 输出结果
if (commonFields != null)
{
    Console.WriteLine("field_name | field_type");
    foreach (var field in commonFields)
    {
        Console.WriteLine($"{field.FieldName,-12} | {field.FieldType}");
    }
}

代码说明 #

• 分组逻辑：用new { f.TableSchema, f.TableName }作为分组键，确保同一个表的字段被归为一组
• 字段集合转换：将每个表的字段转换为值元组(FieldName, FieldType)的HashSet，值元组默认按值比较，适合作为交集判断的依据
• 交集计算：用Aggregate方法迭代所有表的字段集合，逐步求交集，最终得到所有表都存在的字段组合

运行代码后，输出结果与预期一致：

field_name | field_type
fieldA       | character varying

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