请求将含UNION的多表SQL查询转换为Entity Framework查询

1. 定义结果DTO #

三个UNION分支返回的结构完全一致，先创建统一的DTO类承载查询结果：

public class TransferenciaResult
{
    public DateTime Atracacao { get; set; }
    public string Local { get; set; }
    public string Navio { get; set; }
    public int ViagemNavio { get; set; }
    public string TransferenciaTipo { get; set; }
    public string TransferenciaNumero { get; set; }
    public string Terminal { get; set; }
}

2. 确认实体映射 #

确保DbContext已正确映射所有涉及的跨schema表（terminal和tresos），若表/列名与实体类名称不匹配，使用特性配置映射关系，示例：

[Table("blalf", Schema = "terminal")]
public class Blalf
{
    [Column("navio")]
    public int Navio { get; set; }
    [Column("terminal")]
    public int Terminal { get; set; }
    // 其他字段映射...
}

[Table("vw_autorizacaorecebimento_navio", Schema = "tresos")]
public class AutorizacaoRecebimentoNavio
{
    [Column("viagemnavio")]
    public int ViagemNavio { get; set; }
    [Column("tipo")]
    public int Tipo { get; set; }
    [Column("DeclaracaoTransitoEntrada")]
    public int DeclaracaoTransitoEntrada { get; set; }
    // 其他字段映射...
}

3. 构建三个查询分支 #

对应原SQL的三个UNION分支，分别关联不同的单据表：

分支1：Tipo=2，关联EDT表 #

var queryDT = from bl in context.Blalf.AsNoTracking()
              join nav in context.Navios.AsNoTracking() on bl.Navio equals nav.NavioId
              join rap in context.AvisoChegada.AsNoTracking() on nav.ViagemAnoAvc equals rap.NumeroViagemAno
              join loc in context.LocalAtracAlf.AsNoTracking() on rap.Armazem equals loc.CodigoLocal
              join autnav in context.AutorizacaoRecebimentoNavio.AsNoTracking() 
                  on nav.NavioId equals autnav.ViagemNavio
              where autnav.Tipo == 2
              join edt in context.Edt.AsNoTracking() 
                  on autnav.DeclaracaoTransitoEntrada equals edt.Id
              join loct in context.Localidades.AsNoTracking() on bl.Terminal equals loct.Id
              select new TransferenciaResult
              {
                  Atracacao = rap.AtracacaoData,
                  Local = loc.NomeLocal,
                  Navio = nav.Nome,
                  ViagemNavio = nav.NavioId,
                  TransferenciaTipo = "DT ",
                  TransferenciaNumero = edt.Numero,
                  Terminal = loct.Local
              };

分支2：Tipo=3，关联EDTA表 #

var queryDTA = from bl in context.Blalf.AsNoTracking()
               join nav in context.Navios.AsNoTracking() on bl.Navio equals nav.NavioId
               join rap in context.AvisoChegada.AsNoTracking() on nav.ViagemAnoAvc equals rap.NumeroViagemAno
               join loc in context.LocalAtracAlf.AsNoTracking() on rap.Armazem equals loc.CodigoLocal
               join autnav in context.AutorizacaoRecebimentoNavio.AsNoTracking() 
                   on nav.NavioId equals autnav.ViagemNavio
               where autnav.Tipo == 3
               join edta in context.Edta.AsNoTracking() 
                   on autnav.DeclaracaoTransitoEntrada equals edta.Id
               join loct in context.Localidades.AsNoTracking() on bl.Terminal equals loct.Id
               select new TransferenciaResult
               {
                   Atracacao = rap.AtracacaoData,
                   Local = loc.NomeLocal,
                   Navio = nav.Nome,
                   ViagemNavio = nav.NavioId,
                   TransferenciaTipo = "DTA ",
                   TransferenciaNumero = edta.Numero,
                   Terminal = loct.Local
               };

分支3：Tipo=4，关联EPRC表 #

var queryPCI = from bl in context.Blalf.AsNoTracking()
               join nav in context.Navios.AsNoTracking() on bl.Navio equals nav.NavioId
               join rap in context.AvisoChegada.AsNoTracking() on nav.ViagemAnoAvc equals rap.NumeroViagemAno
               join loc in context.LocalAtracAlf.AsNoTracking() on rap.Armazem equals loc.CodigoLocal
               join autnav in context.AutorizacaoRecebimentoNavio.AsNoTracking() 
                   on nav.NavioId equals autnav.ViagemNavio
               where autnav.Tipo == 4
               join eprc in context.Eprc.AsNoTracking() 
                   on autnav.DeclaracaoTransitoEntrada equals eprc.Id
               join loct in context.Localidades.AsNoTracking() on bl.Terminal equals loct.Id
               select new TransferenciaResult
               {
                   Atracacao = rap.AtracacaoData,
                   Local = loc.NomeLocal,
                   Navio = nav.Nome,
                   ViagemNavio = nav.NavioId,
                   TransferenciaTipo = "PCI ",
                   TransferenciaNumero = eprc.Numero,
                   Terminal = loct.Local
               };

4. 合并查询并执行 #

使用Union方法合并三个分支（Union自动去重，对应原SQL的UNION逻辑，无需额外加Distinct）：

var finalQuery = queryDT.Union(queryDTA).Union(queryPCI);
var results = finalQuery.ToList();

关键注意事项 #

• NoLock等效处理：AsNoTracking()仅关闭EF实体跟踪，若要完全对应原SQL的(nolock)脏读行为，需设置事务隔离级别为ReadUncommitted：

  using var transaction = context.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
  var results = finalQuery.ToList();
  transaction.Commit();
  

• 类型一致性：三个查询分支必须返回完全相同的类型（TransferenciaResult），字段类型严格匹配，否则Union会报错。
• 性能优化：原SQL中的DISTINCT可省略，因为Union已自动去重；若需保留重复项，改用UnionAll后再手动调用Distinct()。

内容的提问来源于stack exchange，提问作者Danilo Carneiro Neri