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.
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)
}