我的Linq查询在EF Core 3.1中是否是最佳性能和高性能? [英] Is my Linq Query Optimal and High performance in EF Core 3.1?

问题描述

我可以删除vg.First().Voucher吗?并替换更好的代码?最佳和最佳做法是什么?可以将此代码转换为另一种方法吗?喜欢连锁法吗?

can i remove vg.First().Voucher ? and replace the beter code? what is the optimal and best practice? is convertable this code to another method? like chain method?

        var query = from v in _journalLineRepository.TableNoTracking
                .Include(j => j.Voucher).AsEnumerable()
                    group v by v.AccountId into vg
                    select new // <-- temporary projection with group by fields needed
                    {
                        AccountId = vg.Key,
                        Credit = vg.Sum(v => v.Credit),
                        Debit = vg.Sum(v => v.Debit),
                        Voucher = vg.First().Voucher

                    } into vg
                    join p in _partyRepository.TableNoTracking.Include(p => p.PartyPhones).AsEnumerable() on vg.AccountId equals p.AccountId // <-- additional join(s)
                    select new PartyDeptorAndCreditorViewModel
                    {



                        PartyId = p.Id,
                        FullName = p.FullName,
                        PhoneNo = p.PartyPhones.FirstOrDefault(p => p.IsActive)?.Phone,
                        ProjectId = vg.Voucher.ProjectId,
                        AccountId = vg.AccountId.Value,
                        Creditor = vg.Credit,
                        Deptor = vg.Debit,
                        Balance = vg.Credit - vg.Debit,
                        VoucherDate = vg.Voucher.VoucherDate,
                        VoucherRegisterDate = vg.Voucher.VoucherDate,
                        BalanceType =
                            vg.Debit > vg.Credit ? AccountingComplexEnum.ShowPartyBalanceParamSearch.Deptor.ToDisplay(DisplayProperty.Name) :
                            vg.Debit < vg.Credit ? AccountingComplexEnum.ShowPartyBalanceParamSearch.Creditor.ToDisplay(DisplayProperty.Name) :
                             AccountingComplexEnum.ShowPartyBalanceParamSearch.ZeroBalance.ToDisplay(DisplayProperty.Name),

                    };

推荐答案

我肯定会查看生成的SQL查询.从表面上看，我看到一些警告标志，它可能不是在组成查询，而是可能预先执行内存效率低下的内存处理.首先，它取决于这些 .TableNoTracking 方法/属性返回的内容，以及急切的负载联接上 .AsEnumerable 的使用.

I'd certainly be looking at the SQL query generated. At face value I see a few warning flags that it may not be composing a query but possibly pre-executing to in-memory processing which would be inefficient. It would firstly depend on what these .TableNoTracking methods/properties return, and the use of .AsEnumerable on the eager load joins.

首先，当使用 Select 进行投影时，不需要急切的加载联接( .Include ).投影将为您处理联接，但前提是它将投影到SQL.如果您删除了 .Include().AsEnumerable()调用，并且您的查询仍然有效，则可能会向下投射到SQL.如果它不再起作用，那么它将在内存中处理，并且效率不高.

Firstly, when projecting with Select, eager load joins (.Include) are not necessary. The projections will take care of the joins for you, provided it is projecting down to SQL. If you take out the .Include().AsEnumerable() calls and your query still works then it is likely projecting down to SQL. If it is no longer working then it's processing in memory and not efficiently.

不，内部投影无法解析:关于 .Voucher ，您的最终投影使用的是该实体的2个字段，因此可以在以下位置替换它:初步预测:

Nope, the inner projection won't resolve: Regarding the .Voucher, your final projection is using 2 fields from this entity, so it stands you could replace this in the initial projection:

                select new // <-- temporary projection with group by fields needed
                {
                    AccountId = vg.Key,
                    Credit = vg.Sum(v => v.Credit),
                    Debit = vg.Sum(v => v.Debit),
                    Voucher = vg.Select(v => new { v.ProjectId, v.VoucherDate }).First()
                } into vg

当涉及到这样的转换时:

When it comes to transformations like this:

BalanceType = vg.Debit > vg.Credit 
    ?        AccountingComplexEnum.ShowPartyBalanceParamSearch.Deptor.ToDisplay(DisplayProperty.Name) 
    : vg.Debit < vg.Credit 
        ? AccountingComplexEnum.ShowPartyBalanceParamSearch.Creditor.ToDisplay(DisplayProperty.Name) 
        : AccountingComplexEnum.ShowPartyBalanceParamSearch.ZeroBalance.ToDisplay(DisplayProperty.Name),

...在投影内部，这会发出警告标志，因为Linq2EF需要根据SQL编写投影，因此不会理解诸如 ToDisplay 之类的方法/扩展.相反，由于这仅基于贷方/借方金额，因此我将其移动以由视图模型中的属性计算:

... inside a projection, this sends off warning flags as Linq2EF needs to compose projections down to SQL so methods/extensions like ToDisplay won't be understood. Instead, since this is based solely on the Credit/Debit amounts, I'd move this to be computed by the property in the view model:

select new PartyDeptorAndCreditorViewModel
{
    PartyId = p.Id,
    FullName = p.FullName,
    PhoneNo = p.PartyPhones
        .Where(p => p.IsActive)
        .Select(p => p.Phone)
        .FirstOrDefault(),
    ProjectId = vg.Voucher.ProjectId,
    AccountId = vg.AccountId.Value,
    Creditor = vg.Credit,
    Deptor = vg.Debit,
    Balance = vg.Credit - vg.Debit,
    VoucherDate = vg.Voucher.VoucherDate,
    VoucherRegisterDate = vg.Voucher.VoucherDate
};

然后在视图模型中:

[Serializable]
public class PartyDebtorAndCreditorViewModel
{
    // ... 
    public decimal Balance { get; set; }

    public string BalanceType 
    {
        get 
        { 
             return Balance < 0 
                ? AccountingComplexEnum.ShowPartyBalanceParamSearch.Deptor.ToDisplay(DisplayProperty.Name) 
                : Balance > 0
                    ? AccountingComplexEnum.ShowPartyBalanceParamSearch.Creditor.ToDisplay(DisplayProperty.Name) 
                    : AccountingComplexEnum.ShowPartyBalanceParamSearch.ZeroBalance.ToDisplay(DisplayProperty.Name);
        }
    }
}

这篇关于我的Linq查询在EF Core 3.1中是否是最佳性能和高性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！