My Entity structure is as follows:

public class DisbursementItem
{
    public int DisbursementNumber;
    public int IDDisbursementItem;
    public int IDReceiptItem;
    public decimal Amount;
    public decimal MeasureUnit;
    public decimal PricePerMU;
    public decimal PriceTotal;
    public Disbursement Disbursement_IDDisbursement;
    public int IDDisbursementNumber;
}

public class Disbursement
{
    public int DisbursementNumber;
    DateTime date;
    DisbursementType DType;
    string Note;
    string Subscriber;
    Subscriber SubscriberModel;
    string ItemType;
    int ProcessNumber;
}

public class Subscriber 
{
    public string Name
    public string Address;
    public string City;
}

public class DisbursementDescription
{
    public int IDDisbursementItem;
    public string Description;
}

public class Receipt
{
    public int IDReceiptItem;
    public int ItemNumber;
}

public class StorageCard 
{
    public int ItemNumber;
    public string StorageCardGroup;
    public string StorageCardName;
}

And my EF6 LINQ query is:

DateTime from;
DateTime to;
var result = context.DisbursementItem
    .Where(x => x.Disbursement_IDDisbursement.Date <= to && x.Disbursement_IDDisbursement.Date >= from)
    .Join(context.DisbursementDescription, di => di.IDDisbursementItem, dd => dd.IDDisbursementItem, (di, dd) => new {di = di, desc = dd.Description})
    .Join(context.Receipt, x => x.di.IDReceiptItem, r => r.IDReceiptItem, (x, r) => new { di = x.di, desc = x.desc, r = r })
    .Join(context.StorageCard, x => x.r.ItemNumber, sc => sc.ItemNumber, (x, sc) => new { di = x.di, desc = x.desc, r = x.r, sc = sc})
    .GroupBy(g => new {g.di.DisbursementNumber, g.sc.ItemNumber, g.di.MeasureUnit})
    .Select(x => new
    {
        Date = x.FirstOrDefault().di.Disbursement_IDDisbursement.Date,
        DisbursementNumber = x.Key.DisbursementNumber,
        DType = x.FirstOrDefault().di.Disbursement_IDDisbursement.DType,
        Note = x.FirstOrDefault().di.Disbursement_IDDisbursement.Note,
        Subscriber = x.FirstOrDefault().di.Disbursement_IDDisbursement.Subscriber,
        SubscriberName = x.FirstOrDefault().di.Disbursement_IDDisbursement.SubscriberModel.Name,
        SubscriberAddress = x.FirstOrDefault().di.Disbursement_IDDisbursement.SubscriberModel.Address,
        SubscriberCity = x.FirstOrDefault().di.Disbursement_IDDisbursement.SubscriberModel.City,
        ItemNumber = x.FirstOrDefault().sc.ItemNumber,
        StorageCardGroup = x.FirstOrDefault().sc.StorageCardGroup,
        StorageCardName = x.FirstOrDefault().sc.StorageCardName,
        Amount = x.Sum(y => y.di.Amount),
        PricePerMU = x.FirstOrDefault().di.PricePerMU,
        PriceTotal = x.Sum(y => y.di.PriceTotal),
        MeasureUnit = x.Key.MeasureUnit
        Desc = x.FirstOrDefault().desc,
    })

SELECT
    di.Date,
    di.DisbursementNumber,
    d.DType,
    d.Note,
    d.Subscriber,
    subs.Name,
    subs.Address,
    subs.City,
    sc.ItemNumber,
    sc.StorageCardGroup,
    sc.StorageCardName,
    Sum(di.Amount) as Amount,
    di.PricePerMU,
    Sum(di.PriceTotal) as PriceTotal,
    di.MeasureUnit,
    dd.Description

FROM
    DisbursementItem as di

INNER JOIN Disbursement as d 
ON di.IDDisbursementNumber = d.DisbursementNumber

INNER JOIN Receipt as r 
ON di.IDReceiptItem = r.IDReceiptItem

INNER JOIN StorageCard as sc
ON r.ItemNumber = sc.ItemNumber

INNER JOIN DisbursementDescription dd 
ON di.IDDisbuzrsementItem = dd.IDDisbursementItem

WHERE 
    di.Date <= ... and di.Date >= ...

GROUP BY
di.DisbursementNumber, sc.ItemNumber, di.MeasureUnit

That is the query in SQL that I want to achieve in EF

This query can take over a minute for a few hundred rows. How can I optimize it? I suspect the multiple joins is a problem and maybe also the Sum of some fields.

Also the database schema cannot be modified.

The query it generate is enormous. It's like a SELECT in SELECT in SELECT for like 40 times.

CodePudding user response：

Easiest way is to add all fields which are needed for result to grouping key. Rewritten query to Query syntax for readability and maintainability:

DateTime from;
DateTime to;

var query = 
    from di in context.DisbursementItem
    where di.Disbursement_IDDisbursement.Date <= to && di.Disbursement_IDDisbursement.Date >= from
    join dd in context.DisbursementDescription on di.IDDisbursementItem equals dd.IDDisbursementItem
    join r in context.Receipt on di.IDReceiptItem equals r.IDReceiptItem
    join sc in context.StorageCard on r.ItemNumber equals sc.ItemNumber
    group di by new 
    {
        di.DisbursementNumber, 
        sc.ItemNumber, 
        di.MeasureUnit, 
        di.Disbursement_IDDisbursement.Date, 
        di.Disbursement_IDDisbursement.DType, 
        di.Disbursement_IDDisbursement.Note,
        Subscriber = di.Disbursement_IDDisbursement.Subscriber,
        SubscriberName = di.Disbursement_IDDisbursement.SubscriberModel.Name,
        SubscriberAddress = di.Disbursement_IDDisbursement.SubscriberModel.Address,
        SubscriberCity = di.Disbursement_IDDisbursement.SubscriberModel.City,
        sc.ItemNumber,
        sc.StorageCardGroup,
        sc.StorageCardName,
        di.PricePerMU,
        Desc = dd.Description
    } into g
    select new 
    {
        g.Key.Date,
        g.Key.DisbursementNumber,
        g.Key.DType,
        g.Key.Note,
        g.Key.Subscriber,
        g.Key.SubscriberName,
        g.Key.SubscriberAddress,
        g.Key.SubscriberCity,
        g.Key.ItemNumber,
        g.Key.StorageCardGroup,
        g.Key.StorageCardName,
        g.Key.PricePerMU,
        g.Key.MeasureUnit,
        g.Key.Desc,

        Amount = g.Sum(x => x.Amount),
        PriceTotal = g.Sum(x => x.PriceTotal)        
    }

CodePudding user response：

you could try some kind of multithreading

the query could be splitted in parts and each part assigned to a task. in here you should find something useful (parallel section):

https://docs.microsoft.com/en-us/dotnet/api/system.threading.tasks?view=net-6.0