EF Core如何排除指定字段查询，避免逐个列举字段？

问题描述 #

我用LINQ查询把CariHesap表的字段映射到自定义实体，但因为字段太多，现在只想排除image字段，却不得不逐个列出所有要选的字段，太繁琐了。SQL里有类似NOT SELECT的写法，想知道EF Core有没有对应的实现，不用写这么长的代码？

用户当前的实现代码：

var result = from ch in _tenantDbContext.CariHesap
             where ch.KartTipi == cariHesapKartTip
             select new CariHesap
             {
                 Id = ch.Id,
                 HesapKodu = ch.HesapKodu,
                 Unvan = ch.Unvan,
                 Ad = ch.Ad,
                 Soyad = ch.Soyad,
                 AktifPasif = ch.AktifPasif,
                 KartTipi = ch.KartTipi,
                 IslemTipi = ch.IslemTipi,
                 BakGostSekli = ch.BakGostSekli,
                 DvzTL = ch.DvzTL,
                 DovizCinsi = ch.DovizCinsi,
                 KrediLimiti = ch.KrediLimiti,
                 DvzKrediLimiti = ch.DvzKrediLimiti,
                 BolgeKod = ch.BolgeKod,
                 OzelKod = ch.OzelKod,
                 GrupKod = ch.GrupKod,
                 TipKod = ch.TipKod,
                 MhsKod = ch.MhsKod,
                 MasrafMerkezi = ch.MasrafMerkezi,
                 VergiDairesi = ch.VergiDairesi,
                 HesapNo = ch.HesapNo,
                 FaturaChk = ch.FaturaChk,
                 IskontoOran = ch.IskontoOran,
                 OpsiyonGunu = ch.OpsiyonGunu,
                 OdemeGunu = ch.OdemeGunu,
                 OdemePlani = ch.OdemePlani,
                 KulSatisFiyat = ch.KulSatisFiyat,
                 CHKodu = ch.CHKodu,
                 KDVTevfikatUygula = ch.KDVTevfikatUygula,
                 KDVMuaf = ch.KDVMuaf,
                 KDVMuafAck = ch.KDVMuafAck,
                 FormBaBsUnvan = ch.FormBaBsUnvan,
                 SirketEMail = ch.SirketEMail,
                 SirketWebAdres = ch.SirketWebAdres,
                 SatIslemEMail = ch.SatIslemEMail,
                 SatAlmaIslemEMail = ch.SatAlmaIslemEMail,
                 FinIslemEMail = ch.FinIslemEMail,
                 UnvanFaturadan = ch.UnvanFaturadan,
                 EFatSenaryo = ch.EFatSenaryo,
                 EIrsSenaryo = ch.EIrsSenaryo,
                 EMMSenaryo = ch.EMMSenaryo,
                 EFatEtiket = ch.EFatEtiket,
                 EIrsEtiket = ch.EIrsEtiket,
                 MusBrmSubeTanim = ch.MusBrmSubeTanim,
                 MusBrmSubeDeger = ch.MusBrmSubeDeger,
                 MusCHKoduTanim = ch.MusCHKoduTanim,
                 MusCHKoduDeger = ch.MusCHKoduDeger,
                 LegalEntityID = ch.LegalEntityID,
                 LegalEntityName = ch.LegalEntityName,
                 EArsivTeslimSekli = ch.EArsivTeslimSekli,
                 EArsivTeslimEMail1 = ch.EArsivTeslimEMail1,
                 EArsivTeslimEMail2 = ch.EArsivTeslimEMail2,
                 EArsivTeslimEMail3 = ch.EArsivTeslimEMail3,
                 FaturaXSLT = ch.FaturaXSLT,
                 IrsaliyeXSLT = ch.IrsaliyeXSLT,
                 SGKChk = ch.SGKChk,
                 SGKFaturaTipi = ch.SGKFaturaTipi,
                 KamuChk = ch.KamuChk,
                 Kod1 = ch.Kod1,
                 Kod2 = ch.Kod2,
                 Kod3 = ch.Kod3,
                 Kod4 = ch.Kod4,
                 Kod5 = ch.Kod5,
                 Kod6 = ch.Kod6,
                 Kod7 = ch.Kod7,
                 Kod8 = ch.Kod8,
                 Kod9 = ch.Kod9,
                 Kod10 = ch.Kod10,
                 Kod11 = ch.Kod11,
                 Kod12 = ch.Kod12,
                 Kod13 = ch.Kod13,
                 Kod14 = ch.Kod14,
                 Kod15 = ch.Kod15,
                 Nesne1 = ch.Nesne1,
                 Nesne2 = ch.Nesne2,
             };

解决方案 #

方法1：写通用扩展方法动态排除字段

通过反射获取实体属性，动态构建LINQ选择表达式，实现类似“排除指定字段”的效果：

先定义扩展方法：

public static IQueryable<T> SelectAllExcept<T>(this IQueryable<T> query, params string[] excludedProperties)
{
    var entityType = typeof(T);
    // 获取所有不被排除的属性
    var includedProps = entityType.GetProperties()
        .Where(p => !excludedProperties.Contains(p.Name))
        .ToList();

    // 构建表达式树
    var param = Expression.Parameter(entityType, "x");
    var bindings = includedProps.Select(p =>
        Expression.Bind(entityType.GetProperty(p.Name), Expression.Property(param, p.Name))
    );

    var memberInit = Expression.MemberInit(Expression.New(entityType), bindings);
    var selector = Expression.Lambda<Func<T, T>>(memberInit, param);

    return query.Select(selector);
}

使用时直接调用：

var result = _tenantDbContext.CariHesap
    .Where(ch => ch.KartTipi == cariHesapKartTip)
    .SelectAllExcept("image"); // 传入要排除的字段名

这个方法的好处是通用，任何实体都能复用，不用每次改查询。

方法2：拆分实体类（长期维护更友好）

如果image字段属于不常用的大字段，可以把实体拆分成基础类和完整类：

// 基础类：包含除image外的所有字段
public class CariHesapBase
{
    public int Id { get; set; }
    public string HesapKodu { get; set; }
    // ... 其他所有需要的字段
}

// 完整类：继承基础类并添加image字段
public class CariHesap : CariHesapBase
{
    public byte[] Image { get; set; }
}

查询时直接用基础类：

var result = _tenantDbContext.CariHesap
    .Where(ch => ch.KartTipi == cariHesapKartTip)
    .Select(ch => new CariHesapBase
    {
        Id = ch.Id,
        HesapKodu = ch.HesapKodu,
        // ... 如果用AutoMapper，甚至可以直接ProjectTo<CariHesapBase>，不用手动赋值
    });

这种方式更清晰，后续加字段只要在基础类里加，查询代码不用改。

方法3：用匿名类型+映射工具简化

先查询匿名类型（不包含image），再用AutoMapper等工具映射到目标实体：

// 先查匿名类型，只写需要的字段（不用写image）
var tempData = _tenantDbContext.CariHesap
    .Where(ch => ch.KartTipi == cariHesapKartTip)
    .Select(ch => new 
    {
        ch.Id,
        ch.HesapKodu,
        ch.Unvan,
        // ... 其他字段，不用写image
    });

// 用AutoMapper映射到CariHesap实体
var result = _mapper.Map<List<CariHesap>>(tempData);

虽然还是要列字段，但后续实体加字段时，只要AutoMapper配置了自动映射，查询代码不用修改。

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