具有两个实体的慢速LINQ查询
本文关键字：LINQ 查询 实体 两个 | 更新日期: 2023-09-27 18:19:41
这个查询非常慢。我不知道从哪里开始。当remove.Include（"Pessoa"）时，返回速度非常快。
[Table("Pessoa")]
public class Pessoa
{
public int PessoaId { get; set; }
public string NomPessoa { get; set; }
public string FlgTipoPessoa { get; set; }
public DateTime DatCadastro { get; set; }
public string ObsPessoa { get; set; }
public virtual ICollection<Endereco> Enderecos { get; set; }
public Pessoa()
{
Enderecos = new List<Endereco>();
}
}
[Table("PessoaFisica")]
public class PessoaFisica : Pessoa
{
public string NumCpf { get; set; }
public string NumRg { get; set; }
public DateTime? DatNascimento { get; set; }
public string FlgSexo { get; set; }
}
[Table("PessoaJuridica")]
public class PessoaJuridica : Pessoa
{
public string NumCnpj { get; set; }
public string NumInscricaoEstadual { get; set; }
public string NumInscricaoMunicipal { get; set; }
}
[Table("Funcionario")]
public class Funcionario : PessoaFisica
{
public DateTime DatAdmissao { get; set; }
public DateTime? DatDemissao { get; set; }
public decimal PercRoyalty { get; set; }
}
[Table("FluxoDeCaixa")]
public class FluxoDeCaixa
{
public int FluxoDeCaixaId { get; set; }
public int PlanoDeContasId { get; set; }
public PlanoDeContas PlanoDeContas { get; set; }
public string DscFluxoDeCaixa { get; set; }
public int FuncionarioId { get; set; }
[ForeignKey("FuncionarioId")]
public Funcionario Funcionario { get; set; }
public decimal ValFluxoDeCaixa { get; set; }
public DateTime DatEmissao { get; set; }
public DateTime DatVencimento { get; set; }
public DateTime DatFluxo { get; set; }
public int PessoaId { get; set; }
[ForeignKey("PessoaId")]
public virtual Pessoa Pessoa { get; set; }
public string NumDocto { get; set; }
public string NumCheque { get; set; }
public string FlgSituacaoCheque { get; set; }
public string ObsFluxoDeCaixa { get; set; }
}
public override IQueryable<Entity.Modelos.FluxoDeCaixa> Filtro(System.Linq.Expressions.Expression<System.Func<Entity.Modelos.FluxoDeCaixa, bool>> expressao)
{
return _contexto.FluxoDeCaixa.Include("Pessoa").Where(expressao);
}
.Filtro(f => f.DatFluxo >= dataInicial.Date && f.DatFluxo <= dataFinal.Date).ToList();
这是实体框架生成的查询，我不明白为什么用了这么多左联接。我相信这就是慢慢来的原因。
{SELECT
[Extent1].[FluxoDeCaixaId] AS [FluxoDeCaixaId],
[Extent1].[PlanoDeContasId] AS [PlanoDeContasId],
[Extent1].[DscFluxoDeCaixa] AS [DscFluxoDeCaixa],
[Extent1].[FuncionarioId] AS [FuncionarioId],
[Extent1].[ValFluxoDeCaixa] AS [ValFluxoDeCaixa],
[Extent1].[DatEmissao] AS [DatEmissao],
[Extent1].[DatVencimento] AS [DatVencimento],
[Extent1].[DatFluxo] AS [DatFluxo],
[Extent1].[PessoaId] AS [PessoaId],
[Extent1].[NumDocto] AS [NumDocto],
[Extent1].[NumCheque] AS [NumCheque],
[Extent1].[FlgSituacaoCheque] AS [FlgSituacaoCheque],
[Extent1].[ObsFluxoDeCaixa] AS [ObsFluxoDeCaixa],
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN N'2X' WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN N'2X0X' WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN N'2X0X0X' ELSE N'2X1X' END AS [C1],
[Join3].[PessoaId1] AS [PessoaId1],
[Join3].[NomPessoa] AS [NomPessoa],
[Join3].[FlgTipoPessoa] AS [FlgTipoPessoa],
[Join3].[DatCadastro] AS [DatCadastro],
[Join3].[ObsPessoa] AS [ObsPessoa],
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN [Join3].[NumCpf] WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[NumCpf] END AS [C2],
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN [Join3].[NumRg] WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[NumRg] END AS [C3],
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS datetime) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN [Join3].[DatNascimento] WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[DatNascimento] END AS [C4],
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN [Join3].[FlgSexo] WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[FlgSexo] END AS [C5],
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS datetime) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS datetime) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[DatAdmissao] END AS [C6],
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS datetime) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS datetime) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[DatDemissao] END AS [C7],
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS decimal(18,2)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS decimal(18,2)) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN [Join3].[PercRoyalty] END AS [C8],
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN CAST(NULL AS nvarchar(1)) ELSE [Join3].[NumCnpj] END AS [C9],
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN CAST(NULL AS nvarchar(1)) ELSE [Join3].[NumInscricaoEstadual] END AS [C10],
CASE WHEN (( NOT (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL))) AND ( NOT (([Join3].[C12] = 1) AND ([Join3].[C12] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C11] = 1) AND ([Join3].[C11] IS NOT NULL) AND ( NOT (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)))) THEN CAST(NULL AS nvarchar(1)) WHEN (([Join3].[C2] = 1) AND ([Join3].[C2] IS NOT NULL)) THEN CAST(NULL AS nvarchar(1)) ELSE [Join3].[NumInscricaoMunicipal] END AS [C11]
FROM [FluxoDeCaixa] AS [Extent1]
INNER JOIN (SELECT [Extent2].[PessoaId] AS [PessoaId1], [Extent2].[NomPessoa] AS [NomPessoa], [Extent2].[FlgTipoPessoa] AS [FlgTipoPessoa], [Extent2].[DatCadastro] AS [DatCadastro], [Extent2].[ObsPessoa] AS [ObsPessoa], [Project1].[PessoaId] AS [PessoaId2], [Project1].[NumCnpj] AS [NumCnpj], [Project1].[NumInscricaoEstadual] AS [NumInscricaoEstadual], [Project1].[NumInscricaoMunicipal] AS [NumInscricaoMunicipal], [Project1].[C1] AS [C12], [Project3].[PessoaId] AS [PessoaId3], [Project3].[NumCpf] AS [NumCpf], [Project3].[NumRg] AS [NumRg], [Project3].[DatNascimento] AS [DatNascimento], [Project3].[FlgSexo] AS [FlgSexo], [Project3].[C1] AS [C11], [Project3].[DatAdmissao] AS [DatAdmissao], [Project3].[DatDemissao] AS [DatDemissao], [Project3].[PercRoyalty] AS [PercRoyalty], [Project3].[C2] AS [C2]
FROM [Pessoa] AS [Extent2]
LEFT OUTER JOIN (SELECT
[Extent3].[PessoaId] AS [PessoaId],
[Extent3].[NumCnpj] AS [NumCnpj],
[Extent3].[NumInscricaoEstadual] AS [NumInscricaoEstadual],
[Extent3].[NumInscricaoMunicipal] AS [NumInscricaoMunicipal],
cast(1 as bit) AS [C1]
FROM [PessoaJuridica] AS [Extent3] ) AS [Project1] ON [Extent2].[PessoaId] = [Project1].[PessoaId]
LEFT OUTER JOIN (SELECT
[Extent4].[PessoaId] AS [PessoaId],
[Extent4].[NumCpf] AS [NumCpf],
[Extent4].[NumRg] AS [NumRg],
[Extent4].[DatNascimento] AS [DatNascimento],
[Extent4].[FlgSexo] AS [FlgSexo],
cast(1 as bit) AS [C1],
[Project2].[DatAdmissao] AS [DatAdmissao],
[Project2].[DatDemissao] AS [DatDemissao],
[Project2].[PercRoyalty] AS [PercRoyalty],
CASE WHEN (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL)) THEN cast(1 as bit) WHEN ( NOT (([Project2].[C1] = 1) AND ([Project2].[C1] IS NOT NULL))) THEN cast(0 as bit) END AS [C2]
FROM [PessoaFisica] AS [Extent4]
LEFT OUTER JOIN (SELECT
[Extent5].[PessoaId] AS [PessoaId],
[Extent5].[DatAdmissao] AS [DatAdmissao],
[Extent5].[DatDemissao] AS [DatDemissao],
[Extent5].[PercRoyalty] AS [PercRoyalty],
cast(1 as bit) AS [C1]
FROM [Funcionario] AS [Extent5] ) AS [Project2] ON [Extent4].[PessoaId] = [Project2].[PessoaId] ) AS [Project3] ON [Extent2].[PessoaId] = [Project3].[PessoaId] ) AS [Join3] ON [Extent1].[PessoaId] = [Join3].[PessoaId1]
WHERE ([Extent1].[DatFluxo] >= @p__linq__0) AND ([Extent1].[DatFluxo] <= @p__linq__1)}
您的问题可能是由于在数据模型中使用了按类型表（TPT）继承。我现在正在为一个类似的问题而挣扎。为了在选择基类型时实现正确的派生类型，除非使用OfType<>()方法，否则Entity Framework会将基类型表与所有可能的派生表连接起来。
context.BaseEntities.OfType<DerivedEntityX>();
我发现最好只在没有太多派生类型并且继承深度只有一个的简单情况下使用TPT。在使用TPT的情况下，只尝试执行简单的查询。您可能会发现，预生成视图可能会减少最初的冷查询执行时间。投影到匿名类型或视图模型，在该模型中您可以只选择必要的属性和/或使用OfType<>()方法，这也可能有助于提高性能。当然，您也可以考虑使用TablePerHeirarchy（TPH），以便所有派生类型映射到一个表。
首先，如果您不需要查询Pessoa，那么就不要包含它。
其次，这可能是因为Pessoa实体的以下属性：
public virtual ICollection<Endereco> Enderecos { get; set; }
由于您将Enderecos声明为虚拟的，所以每次查询Pessoa实体时都会延迟加载它。
有关加载相关属性的详细信息，请阅读此链接。
不确定您使用的是什么ef版本，但
1） 在数据库中有适当的索引，使用生成的sql作为有效负载（修改为硬编码参数）：使用数据库引擎优化顾问
2） 如果你直接使用objectcontext，那么这可能会加快速度（取决于日期范围有多大），然后像这样修改Filtro主体：
var dataList= _contexto.FluxoDeCaixa.Where(expressao).ToList();
for (int x = 0; x < dataList.Count; x++)
_contexto.LoadProperty(dataList[x], p => p.Pessoa);
return dataList;
3） 理想情况下，如果你想获得最大的性能，重做它，这样你就不需要返回每一列，只返回需要的内容，比如：
_contexto.FluxoDeCaixa.Select(t => new { t.DatFluxo , t.DatVencimento}).Where ....