Linq to entity projection: is this projection ineffective?
I don't know if this will be "noticed" by LINQ or not.
dbContext.Bills.Select(b => new
{
code = b.Code,
date = b.Date,
weight = b.Package.Weight,
quantity = b.BillRows.Sum(r => (int)r.Quantity) ?? 0,
total = b.Package.Weight * (b.BillRows.Sum(r => (int)r.Quantity) ?? 0),
});
As you can see, the projection has two times this piece of code:
b.BillRows.Sum(r => (int)r.Quantity) ?? 0
Question: LINQ is going to reuse the result of the first - or is it going to calculate that sum twice?
(*) This whole projection is converted to SQL, so nothing is computed in memory here.
I know I could do the following (but ... kind of ugly / inelegant)
dbContext.Bills.Select(b => new
{
code = b.Code,
date = b.Date,
weight = b.Package.Weight,
quantity = b.BillRows.Sum(r => (int)r.Quantity) ?? 0,
total = 0,
}).Select(b => new
{
code = b.code,
date = b.date,
weight = b.weight,
quantity = b.quantity,
total = b.weight * b.quantity
});
source to share
He will do it twice. I made a rough example of summing IDs (didn't want to write all these classes, etc.),
Here's my request:
BookingRequests.Select(br => new {
quantity = br.BookingRequestCalendars.Sum(brc => brc.CalendarID),
total = br.BookingRequestCalendars.Sum(brc => brc.CalendarID) * br.Id
}).Dump();
Which creates the following SQL:
SELECT (
SELECT SUM([t1].[CalendarID])
FROM [BookingRequestCalendar] AS [t1]
WHERE [t1].[BookingRequestID] = [t0].[Id]
) AS [quantity], ((
SELECT SUM([t2].[CalendarID])
FROM [BookingRequestCalendar] AS [t2]
WHERE [t2].[BookingRequestID] = [t0].[Id]
)) * [t0].[Id] AS [total]
FROM [BookingRequests] AS [t0]
As for the performance hit, I couldn't tell you. Perhaps DB will optimize this for you. I would do some profiling of this code to check if the query is worth optimizing
The second query looks a little better:
BookingRequests.Select(br => new {
weight = br.Id,
quantity = br.BookingRequestCalendars.Sum(brc => brc.CalendarID),
total = 0
})
.Select(b => new {
quality = b.quantity,
total = b.weight * b.quantity
})
.Dump();
Outputs:
SELECT [t2].[value] AS [quality], [t2].[Id] * [t2].[value] AS [total]
FROM (
SELECT [t0].[Id], (
SELECT SUM([t1].[CalendarID])
FROM [BookingRequestCalendar] AS [t1]
WHERE [t1].[BookingRequestID] = [t0].[Id]
) AS [value]
FROM [BookingRequests] AS [t0]
) AS [t2]
In response to a comment about a more convenient way to do this, maybe something like this?
BookingRequests.Select(br => new {
quantity = br.BookingRequestCalendars.Sum(brc => brc.CalendarID),
b = br
})
.Select(br => new {
// code = br.b.code,
// date = br.b.date,
quality = br.quantity,
total = br.quantity * br.b.Id
}).Dump();
It gives this SQL:
SELECT [t2].[value] AS [quality], [t2].[value] * [t2].[Id] AS [br]
FROM (
SELECT (
SELECT SUM([t1].[CalendarID])
FROM [BookingRequestCalendar] AS [t1]
WHERE [t1].[BookingRequestID] = [t0].[Id]
) AS [value], [t0].[Id]
FROM [BookingRequests] AS [t0]
) AS [t2]
That is, you are returning the sum as a variable, plus the entire string. The advantage is that you don't need to duplicate the rest of the properties (you only need to fill in the second selection). The downside is that it br.b.Id
doesn't look too neat. It's really a matter of preference. I'm not sure if there is a really elegant solution for this.
Another option is to write a view and request that view instead. It might look better in code, but it might not be worth the effort.
source to share