New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Query: optimize queries projecting correlated collections, so that they don't result in N+1 database queries #9282

Closed
maumar opened this Issue Jul 27, 2017 · 18 comments

Comments

Projects
None yet
8 participants
@maumar
Contributor

maumar commented Jul 27, 2017

In #8584 we allowed include pipeline to be reused for queries that project collection navigations as long as they are not composed on. This results in generating much better queries for those cases - no longer producing N+1 queries.

This could be further enhanced to support collection navigations that are composed on (e.g. customers.Select(c => c.Orders.Where(o => o.City == "London")), so that those queries also stop producing N+1 queries.

@sir-boformer

This comment has been minimized.

sir-boformer commented Oct 31, 2017

This feature is quite important for complex DTO/ViewModel generation. Here is a simple example

public class ChatInfo
{
	public long Id { get; set; }

	public string Title { get; set; }

	public List<ChatEntry> Entries { get; set; } = new List<ChatEntry>();
	public List<ChatAssignment> Assignments { get; set; } = new List<ChatAssignment>();
}

public class ChatEntry
{
	public long Id { get; set; }

	public ChatInfo Chat { get; set; }

	public string UserId { get; set; }
	public string Message { get; set; }
	public DateTime Time { get; set; }
}

public class ChatAssignment
{
	public long Id { get; set; }

	public ChatInfo Chat { get; set; }

	public string UserId { get; set; }
}

A common view model for a list of chat items:

public class ChatListDto
{
	public string Title { get; set; }

	public List<string> AssignedUserIds { get; set; }
	public string LatestEntry { get; set; }
}

public class ChatEntryDto
{
	public string UserId { get; set; }
	public string Message { get; set; }
	public DateTime Time { get; set; }
}

A typical query to retrieve a list of chat list view models:

await _context.Chats
	.Include(c => c.Assignments)
	.Where(c => c.Assignments.Any(a => a.UserId == "Steinmeier"))
	.Select(c => new ChatListDto
	{
		Title = c.Title,

		AssignedUserIds = c.Assignments.Select(a => a.UserId).ToList(),
		LatestEntry = c.Entries
			.OrderByDescending(e => e.Time)
			.Select(e => new ChatEntryDto {
				UserId = e.UserId,
				Message = e.Message,
				Time = e.Time
			})
			.FirstOrDefault()
	})
   .ToListAsync();

This generates n+1 queries (2 for each list item):

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [c].[Title], [c].[Id]
FROM [Chats] AS [c]
WHERE EXISTS (
    SELECT 1
    FROM [ChatAssignments] AS [a]
    WHERE ([a].[UserId] = N'Steinmeier') AND ([c].[Id] = [a].[ChatId]))

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (23ms) [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='30']
SELECT [a0].[UserId]
FROM [ChatAssignments] AS [a0]
WHERE @_outer_Id = [a0].[ChatId]

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (3ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [e].[UserId], [e].[Message], [e].[Time]
FROM [ChatEntries] AS [e]
WHERE @_outer_Id1 = [e].[ChatId]
ORDER BY [e].[Time] DESC

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='30']
SELECT [a0].[UserId]
FROM [ChatAssignments] AS [a0]
WHERE @_outer_Id = [a0].[ChatId]

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [e].[UserId], [e].[Message], [e].[Time]
FROM [ChatEntries] AS [e]
WHERE @_outer_Id1 = [e].[ChatId]
ORDER BY [e].[Time] DESC

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='30']
SELECT [a0].[UserId]
FROM [ChatAssignments] AS [a0]
WHERE @_outer_Id = [a0].[ChatId]

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [e].[UserId], [e].[Message], [e].[Time]
FROM [ChatEntries] AS [e]
WHERE @_outer_Id1 = [e].[ChatId]
ORDER BY [e].[Time] DESC

EFPlayground.Controllers.HomeController:Debug: [[ SPD-Chat - UserId: Schulz, Message: Geh sterben!]]
EFPlayground.Controllers.HomeController:Debug: [[ Ausland-Chat - UserId: Trump, Message: MAGA <3 Putin]]
EFPlayground.Controllers.HomeController:Debug: [[ Führer-Chat - UserId: Steinmeier, Message: Hey Mutti!]]
@jemiller0

This comment has been minimized.

jemiller0 commented Nov 6, 2017

I think I'm running into the same problem with the following projection query. The one-to-manys/collections are causing N+1 queries to occur. This is different than the behavior in Entity Framework 6 which creates nested JOINs and no N+1 queries. This is a use case that is important for a reporting application that I have. I have been wanting to switch to Entity Framework Core for everything, but, have run into issues like this. I think this is one of the last remaining issues that I have.

var q = from oi in libraryContext.OrderItems
        select new
        {
            oi.Id,
            Order = oi.Order.Id,
            oi.OrderId,
            oi.Order.CreationTime,
            OrderStatus = oi.Order.OrderStatus.Name,
            oi.Order.OrderStatusId,
            OrderType = oi.Order.OrderType.Name,
            oi.Order.OrderTypeId,
            ReceiptStatus = oi.Holding.ReceiptStatus.Name,
            oi.Holding.ReceiptStatusId,
            Vendor = oi.Order.Vendor.Name,
            oi.Order.Vendor.EmailAddress,
            oi.Order.VendorId,
            Accounts = oi.OrderItemAccounts.Select(oia => oia.Account.Code),
            Donors = oi.OrderItemDonors.Select(oid => oid.Donor.Code),
            oi.VendorNotes,
            oi.SpecialNotes,
            oi.MiscellaneousNotes,
            oi.SelectorNotes,
            oi.Order.VendorCustomerId,
            DeliveryRoom = oi.Order.DeliveryRoom.Name,
            oi.Order.DeliveryRoomId,
            ExtendedCost = oi.UnitPrice * oi.Quantity,
            oi.VendorItemId,
            Bib = oi.Bib.Title,
            oi.BibId,
            oi.Bib.Author,
            oi.Bib.OclcNumber,
            OclcNumbers = oi.Bib.OclcNumbers.Select(n => n.Content),
            Isbns = oi.Bib.Isbns.Select(n => n.Content),
            Issns = oi.Bib.Issns.Select(n => n.Content),
            OtherNumbers = oi.Bib.Numbers.Where(n => n.Type == "024").Select(n => n.Content),
            PublisherNumbers = oi.Bib.Numbers.Where(n => n.Type == "028").Select(n => n.Content),
            Editions = oi.Bib.Editions.Select(n => n.Content),
            Publications = oi.Bib.Publications.Select(n => n.Content)
        };

Does anyone know if this issue will be solved sometime soon?

maumar added a commit that referenced this issue Dec 20, 2017

Fix to #9282 - Query: improve include pipeline so it can be reused fo…
…r queries projecting composed collection navigations (filters and projections)

This feature optimizes a number of queries that project correlated collections. Previously those would produce N+1 queries. Now, we rewrite queries similarly to how Include pipeline does it, producing only two queries and correlating them on the client.
To enable the feature the inner subquery needs to be wrapped around ToList() or ToArray() call.

Current limitations:
- only works for sync queries,
- child entities are not being tracked,
- no fixup between parent and child,
- doesn't work if the parent query results in a CROSS JOIN,
- doesn't work with result operators (i.e. Skip/Take/Distinct)
- doesn't work if outer query needs client evaluation anywhere outside projection (e.g. order by or filter by NonMapped property)
- doesn't work if inner query is correlated with query two (or more) levels up, (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name == c.Name)))

Optimization process:

original query:

from c in ctx.Customers
where c.CustomerID != "ALFKI"
orderby c.City descending
select (from o in c.Orders
        where o.OrderID > 100
        orderby o.EmployeeID
        select new { o.OrderID, o.CustomerID }).ToList()

nav rewrite converts it to:

from c in customers
where c.CustomerID != "ALFKI"
order by c.City descending
select
   (from o in orders
    where o.OrderID > 100
    order by o.EmployeeID
    where c.CustomerID ?= o.CustomerID
    select new { o.OrderID, o.CustomerID }).ToList()

which gets rewritten to (simplified):

from c in customers
where c.CustomerID != "ALFKI"
order by c.City desc, c.CustomerID asc
select CorrelateSubquery(
    outerKey: new { c.CustomerID },
    correlationPredicate: (outer, inner) => outer.GetValue(0) == null || inner.GetValue(0) == null ? false : outer.GetValue(0) == inner.GetValue(0)
    correlatedCollectionFactory: () =>
        from o in orders
        where o.OrderID > 100
        join _c in
            from c in customers
            where c.CustomerID != "ALFKI"
            select new { c.City, c.CustomerID }
        on o.CustomerID equals _c.GetValue(1)
        order by _c.GetValue(0) descending, _c.GetValue(1), o.EmployeeID
        select new
        {
            InnerResult = new { o.OrderID, o.CustomerID }
            InnerKey = new { o.CustomerID },
            OriginKey = new { _c.GetValue(1) }
        }).ToList()

CorrelateSubquery is the method that combines results of outer and inner queries. Because order for both queries is the same we can perform only one pass thru inner query.
We use correlation predicate (between outerKey parameter passed to CorrelateSubquery and InnerKey which is part of the final result) to determine whether giver result of the inner query belongs to the outer.
We also remember latest origin key (i.e. PK of the outer, which is not always the same as outer key). If the origin key changes, it means that all inners for that outer have already been encountered.

maumar added a commit that referenced this issue Jan 10, 2018

Fix to #9282 - Query: improve include pipeline so it can be reused fo…
…r queries projecting composed collection navigations (filters and projections)

This feature optimizes a number of queries that project correlated collections. Previously those would produce N+1 queries. Now, we rewrite queries similarly to how Include pipeline does it, producing only two queries and correlating them on the client.
To enable the feature the inner subquery needs to be wrapped around ToList() or ToArray() call.

Current limitations:
- only works for sync queries,
- child entities are not being tracked,
- no fixup between parent and child,
- doesn't work if the parent query results in a CROSS JOIN,
- doesn't work with result operators (i.e. Skip/Take/Distinct)
- doesn't work if outer query needs client evaluation anywhere outside projection (e.g. order by or filter by NonMapped property)
- doesn't work if inner query is correlated with query two (or more) levels up, (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name == c.Name).ToList()).ToList())
- doesn't work in nested scenarios where the outer collection is streaming (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name != "Foo").ToList())) - to make it work, outer collection must also be wrapped in ToList(). However it is OK to stream inner collection - in that case outer collection will take advantage of the optimization.

Optimization process:

original query:

from c in ctx.Customers
where c.CustomerID != "ALFKI"
orderby c.City descending
select (from o in c.Orders
        where o.OrderID > 100
        orderby o.EmployeeID
        select new { o.OrderID, o.CustomerID }).ToList()

nav rewrite converts it to:

from c in customers
where c.CustomerID != "ALFKI"
order by c.City descending
select
   (from o in orders
    where o.OrderID > 100
    order by o.EmployeeID
    where c.CustomerID ?= o.CustomerID
    select new { o.OrderID, o.CustomerID }).ToList()

which gets rewritten to (simplified):

from c in customers
where c.CustomerID != "ALFKI"
order by c.City desc, c.CustomerID asc
select CorrelateSubquery(
    outerKey: new { c.CustomerID },
    correlationPredicate: (outer, inner) => outer.GetValue(0) == null || inner.GetValue(0) == null ? false : outer.GetValue(0) == inner.GetValue(0)
    correlatedCollectionFactory: () =>
        from o in orders
        where o.OrderID > 100
        join _c in
            from c in customers
            where c.CustomerID != "ALFKI"
            select new { c.City, c.CustomerID }
        on o.CustomerID equals _c.GetValue(1)
        order by _c.GetValue(0) descending, _c.GetValue(1), o.EmployeeID
        select new
        {
            InnerResult = new { o.OrderID, o.CustomerID }
            InnerKey = new { o.CustomerID },
            OriginKey = new { _c.GetValue(1) }
        }).ToList()

CorrelateSubquery is the method that combines results of outer and inner queries. Because order for both queries is the same we can perform only one pass thru inner query.
We use correlation predicate (between outerKey parameter passed to CorrelateSubquery and InnerKey which is part of the final result) to determine whether giver result of the inner query belongs to the outer.
We also remember latest origin key (i.e. PK of the outer, which is not always the same as outer key). If the origin key changes, it means that all inners for that outer have already been encountered.

maumar added a commit that referenced this issue Jan 10, 2018

Fix to #9282 - Query: improve include pipeline so it can be reused fo…
…r queries projecting composed collection navigations (filters and projections)

This feature optimizes a number of queries that project correlated collections. Previously those would produce N+1 queries. Now, we rewrite queries similarly to how Include pipeline does it, producing only two queries and correlating them on the client.
To enable the feature the inner subquery needs to be wrapped around ToList() or ToArray() call.

Current limitations:
- only works for sync queries,
- child entities are not being tracked,
- no fixup between parent and child,
- doesn't work if the parent query results in a CROSS JOIN,
- doesn't work with result operators (i.e. Skip/Take/Distinct)
- doesn't work if outer query needs client evaluation anywhere outside projection (e.g. order by or filter by NonMapped property)
- doesn't work if inner query is correlated with query two (or more) levels up, (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name == c.Name).ToList()).ToList())
- doesn't work in nested scenarios where the outer collection is streaming (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name != "Foo").ToList())) - to make it work, outer collection must also be wrapped in ToList(). However it is OK to stream inner collection - in that case outer collection will take advantage of the optimization.

Optimization process:

original query:

from c in ctx.Customers
where c.CustomerID != "ALFKI"
orderby c.City descending
select (from o in c.Orders
        where o.OrderID > 100
        orderby o.EmployeeID
        select new { o.OrderID, o.CustomerID }).ToList()

nav rewrite converts it to:

from c in customers
where c.CustomerID != "ALFKI"
order by c.City descending
select
   (from o in orders
    where o.OrderID > 100
    order by o.EmployeeID
    where c.CustomerID ?= o.CustomerID
    select new { o.OrderID, o.CustomerID }).ToList()

which gets rewritten to (simplified):

from c in customers
where c.CustomerID != "ALFKI"
order by c.City desc, c.CustomerID asc
select CorrelateSubquery(
    outerKey: new { c.CustomerID },
    correlationPredicate: (outer, inner) => outer.GetValue(0) == null || inner.GetValue(0) == null ? false : outer.GetValue(0) == inner.GetValue(0)
    correlatedCollectionFactory: () =>
        from o in orders
        where o.OrderID > 100
        join _c in
            from c in customers
            where c.CustomerID != "ALFKI"
            select new { c.City, c.CustomerID }
        on o.CustomerID equals _c.GetValue(1)
        order by _c.GetValue(0) descending, _c.GetValue(1), o.EmployeeID
        select new
        {
            InnerResult = new { o.OrderID, o.CustomerID }
            InnerKey = new { o.CustomerID },
            OriginKey = new { _c.GetValue(1) }
        }).ToList()

CorrelateSubquery is the method that combines results of outer and inner queries. Because order for both queries is the same we can perform only one pass thru inner query.
We use correlation predicate (between outerKey parameter passed to CorrelateSubquery and InnerKey which is part of the final result) to determine whether giver result of the inner query belongs to the outer.
We also remember latest origin key (i.e. PK of the outer, which is not always the same as outer key). If the origin key changes, it means that all inners for that outer have already been encountered.

maumar added a commit that referenced this issue Jan 10, 2018

Fix to #9282 - Query: improve include pipeline so it can be reused fo…
…r queries projecting composed collection navigations (filters and projections)

This feature optimizes a number of queries that project correlated collections. Previously those would produce N+1 queries. Now, we rewrite queries similarly to how Include pipeline does it, producing only two queries and correlating them on the client.
To enable the feature the inner subquery needs to be wrapped around ToList() or ToArray() call.

Current limitations:
- only works for sync queries,
- child entities are not being tracked,
- no fixup between parent and child,
- doesn't work if the parent query results in a CROSS JOIN,
- doesn't work with result operators (i.e. Skip/Take/Distinct)
- doesn't work if outer query needs client evaluation anywhere outside projection (e.g. order by or filter by NonMapped property)
- doesn't work if inner query is correlated with query two (or more) levels up, (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name == c.Name).ToList()).ToList())
- doesn't work in nested scenarios where the outer collection is streaming (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name != "Foo").ToList())) - to make it work, outer collection must also be wrapped in ToList(). However it is OK to stream inner collection - in that case outer collection will take advantage of the optimization.

Optimization process:

original query:

from c in ctx.Customers
where c.CustomerID != "ALFKI"
orderby c.City descending
select (from o in c.Orders
        where o.OrderID > 100
        orderby o.EmployeeID
        select new { o.OrderID, o.CustomerID }).ToList()

nav rewrite converts it to:

from c in customers
where c.CustomerID != "ALFKI"
order by c.City descending
select
   (from o in orders
    where o.OrderID > 100
    order by o.EmployeeID
    where c.CustomerID ?= o.CustomerID
    select new { o.OrderID, o.CustomerID }).ToList()

which gets rewritten to (simplified):

from c in customers
where c.CustomerID != "ALFKI"
order by c.City desc, c.CustomerID asc
select CorrelateSubquery(
    outerKey: new { c.CustomerID },
    correlationPredicate: (outer, inner) => outer.GetValue(0) == null || inner.GetValue(0) == null ? false : outer.GetValue(0) == inner.GetValue(0)
    correlatedCollectionFactory: () =>
        from o in orders
        where o.OrderID > 100
        join _c in
            from c in customers
            where c.CustomerID != "ALFKI"
            select new { c.City, c.CustomerID }
        on o.CustomerID equals _c.GetValue(1)
        order by _c.GetValue(0) descending, _c.GetValue(1), o.EmployeeID
        select new
        {
            InnerResult = new { o.OrderID, o.CustomerID }
            InnerKey = new { o.CustomerID },
            OriginKey = new { _c.GetValue(1) }
        }).ToList()

CorrelateSubquery is the method that combines results of outer and inner queries. Because order for both queries is the same we can perform only one pass thru inner query.
We use correlation predicate (between outerKey parameter passed to CorrelateSubquery and InnerKey which is part of the final result) to determine whether giver result of the inner query belongs to the outer.
We also remember latest origin key (i.e. PK of the outer, which is not always the same as outer key). If the origin key changes, it means that all inners for that outer have already been encountered.

@maumar maumar changed the title from Query: improve include pipeline so it can be reused for queries projecting composed collection navigations (filters and projections) to Query: optimize queries projecting correlated collections, so that they don't result in N+1 database queries Jan 10, 2018

maumar added a commit that referenced this issue Jan 10, 2018

Fix to #9282 - Query: optimize queries projecting correlated collecti…
…ons, so that they don't result in N+1 database queries

This feature optimizes a number of queries that project correlated collections. Previously those would produce N+1 queries. Now, we rewrite queries similarly to how Include pipeline does it, producing only two queries and correlating them on the client.
To enable the feature the inner subquery needs to be wrapped around ToList() or ToArray() call.

Current limitations:
- only works for sync queries,
- child entities are not being tracked,
- no fixup between parent and child,
- doesn't work if the parent query results in a CROSS JOIN,
- doesn't work with result operators (i.e. Skip/Take/Distinct)
- doesn't work if outer query needs client evaluation anywhere outside projection (e.g. order by or filter by NonMapped property)
- doesn't work if inner query is correlated with query two (or more) levels up, (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name == c.Name).ToList()).ToList())
- doesn't work in nested scenarios where the outer collection is streaming (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name != "Foo").ToList())) - to make it work, outer collection must also be wrapped in ToList(). However it is OK to stream inner collection - in that case outer collection will take advantage of the optimization.

Optimization process:

original query:

from c in ctx.Customers
where c.CustomerID != "ALFKI"
orderby c.City descending
select (from o in c.Orders
        where o.OrderID > 100
        orderby o.EmployeeID
        select new { o.OrderID, o.CustomerID }).ToList()

nav rewrite converts it to:

from c in customers
where c.CustomerID != "ALFKI"
order by c.City descending
select
   (from o in orders
    where o.OrderID > 100
    order by o.EmployeeID
    where c.CustomerID ?= o.CustomerID
    select new { o.OrderID, o.CustomerID }).ToList()

which gets rewritten to (simplified):

from c in customers
where c.CustomerID != "ALFKI"
order by c.City desc, c.CustomerID asc
select CorrelateSubquery(
    outerKey: new { c.CustomerID },
    correlationPredicate: (outer, inner) => outer.GetValue(0) == null || inner.GetValue(0) == null ? false : outer.GetValue(0) == inner.GetValue(0)
    correlatedCollectionFactory: () =>
        from o in orders
        where o.OrderID > 100
        join _c in
            from c in customers
            where c.CustomerID != "ALFKI"
            select new { c.City, c.CustomerID }
        on o.CustomerID equals _c.GetValue(1)
        order by _c.GetValue(0) descending, _c.GetValue(1), o.EmployeeID
        select new
        {
            InnerResult = new { o.OrderID, o.CustomerID }
            InnerKey = new { o.CustomerID },
            OriginKey = new { _c.GetValue(1) }
        }).ToList()

CorrelateSubquery is the method that combines results of outer and inner queries. Because order for both queries is the same we can perform only one pass thru inner query.
We use correlation predicate (between outerKey parameter passed to CorrelateSubquery and InnerKey which is part of the final result) to determine whether giver result of the inner query belongs to the outer.
We also remember latest origin key (i.e. PK of the outer, which is not always the same as outer key). If the origin key changes, it means that all inners for that outer have already been encountered.

maumar added a commit that referenced this issue Jan 10, 2018

Fix to #9282 - Query: optimize queries projecting correlated collecti…
…ons, so that they don't result in N+1 database queries

This feature optimizes a number of queries that project correlated collections. Previously those would produce N+1 queries. Now, we rewrite queries similarly to how Include pipeline does it, producing only two queries and correlating them on the client.
To enable the feature the inner subquery needs to be wrapped around ToList() or ToArray() call.

Current limitations:
- only works for sync queries,
- child entities are not being tracked,
- no fixup between parent and child,
- doesn't work if the parent query results in a CROSS JOIN,
- doesn't work with result operators (i.e. Skip/Take/Distinct)
- doesn't work if outer query needs client evaluation anywhere outside projection (e.g. order by or filter by NonMapped property)
- doesn't work if inner query is correlated with query two (or more) levels up, (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name == c.Name).ToList()).ToList())
- doesn't work in nested scenarios where the outer collection is streaming (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name != "Foo").ToList())) - to make it work, outer collection must also be wrapped in ToList(). However it is OK to stream inner collection - in that case outer collection will take advantage of the optimization.

Optimization process:

original query:

from c in ctx.Customers
where c.CustomerID != "ALFKI"
orderby c.City descending
select (from o in c.Orders
        where o.OrderID > 100
        orderby o.EmployeeID
        select new { o.OrderID, o.CustomerID }).ToList()

nav rewrite converts it to:

from c in customers
where c.CustomerID != "ALFKI"
order by c.City descending
select
   (from o in orders
    where o.OrderID > 100
    order by o.EmployeeID
    where c.CustomerID ?= o.CustomerID
    select new { o.OrderID, o.CustomerID }).ToList()

which gets rewritten to (simplified):

from c in customers
where c.CustomerID != "ALFKI"
order by c.City desc, c.CustomerID asc
select CorrelateSubquery(
    outerKey: new { c.CustomerID },
    correlationPredicate: (outer, inner) => outer.GetValue(0) == null || inner.GetValue(0) == null ? false : outer.GetValue(0) == inner.GetValue(0)
    correlatedCollectionFactory: () =>
        from o in orders
        where o.OrderID > 100
        join _c in
            from c in customers
            where c.CustomerID != "ALFKI"
            select new { c.City, c.CustomerID }
        on o.CustomerID equals _c.GetValue(1)
        order by _c.GetValue(0) descending, _c.GetValue(1), o.EmployeeID
        select new
        {
            InnerResult = new { o.OrderID, o.CustomerID }
            InnerKey = new { o.CustomerID },
            OriginKey = new { _c.GetValue(1) }
        }).ToList()

CorrelateSubquery is the method that combines results of outer and inner queries. Because order for both queries is the same we can perform only one pass thru inner query.
We use correlation predicate (between outerKey parameter passed to CorrelateSubquery and InnerKey which is part of the final result) to determine whether giver result of the inner query belongs to the outer.
We also remember latest origin key (i.e. PK of the outer, which is not always the same as outer key). If the origin key changes, it means that all inners for that outer have already been encountered.

maumar added a commit that referenced this issue Jan 10, 2018

Fix to #9282 - Query: optimize queries projecting correlated collecti…
…ons, so that they don't result in N+1 database queries

This feature optimizes a number of queries that project correlated collections. Previously those would produce N+1 queries. Now, we rewrite queries similarly to how Include pipeline does it, producing only two queries and correlating them on the client.
To enable the feature the inner subquery needs to be wrapped around ToList() or ToArray() call.

Current limitations:
- only works for sync queries,
- child entities are not being tracked,
- no fixup between parent and child,
- doesn't work if the parent query results in a CROSS JOIN,
- doesn't work with result operators (i.e. Skip/Take/Distinct)
- doesn't work if outer query needs client evaluation anywhere outside projection (e.g. order by or filter by NonMapped property)
- doesn't work if inner query is correlated with query two (or more) levels up, (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name == c.Name).ToList()).ToList())
- doesn't work in nested scenarios where the outer collection is streaming (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name != "Foo").ToList())) - to make it work, outer collection must also be wrapped in ToList(). However it is OK to stream inner collection - in that case outer collection will take advantage of the optimization.

Optimization process:

original query:

from c in ctx.Customers
where c.CustomerID != "ALFKI"
orderby c.City descending
select (from o in c.Orders
        where o.OrderID > 100
        orderby o.EmployeeID
        select new { o.OrderID, o.CustomerID }).ToList()

nav rewrite converts it to:

from c in customers
where c.CustomerID != "ALFKI"
order by c.City descending
select
   (from o in orders
    where o.OrderID > 100
    order by o.EmployeeID
    where c.CustomerID ?= o.CustomerID
    select new { o.OrderID, o.CustomerID }).ToList()

which gets rewritten to (simplified):

from c in customers
where c.CustomerID != "ALFKI"
order by c.City desc, c.CustomerID asc
select CorrelateSubquery(
    outerKey: new { c.CustomerID },
    correlationPredicate: (outer, inner) => outer.GetValue(0) == null || inner.GetValue(0) == null ? false : outer.GetValue(0) == inner.GetValue(0)
    correlatedCollectionFactory: () =>
        from o in orders
        where o.OrderID > 100
        join _c in
            from c in customers
            where c.CustomerID != "ALFKI"
            select new { c.City, c.CustomerID }
        on o.CustomerID equals _c.GetValue(1)
        order by _c.GetValue(0) descending, _c.GetValue(1), o.EmployeeID
        select new
        {
            InnerResult = new { o.OrderID, o.CustomerID }
            InnerKey = new { o.CustomerID },
            OriginKey = new { _c.GetValue(1) }
        }).ToList()

CorrelateSubquery is the method that combines results of outer and inner queries. Because order for both queries is the same we can perform only one pass thru inner query.
We use correlation predicate (between outerKey parameter passed to CorrelateSubquery and InnerKey which is part of the final result) to determine whether giver result of the inner query belongs to the outer.
We also remember latest origin key (i.e. PK of the outer, which is not always the same as outer key). If the origin key changes, it means that all inners for that outer have already been encountered.

maumar added a commit that referenced this issue Jan 12, 2018

Fix to #9282 - Query: optimize queries projecting correlated collecti…
…ons, so that they don't result in N+1 database queries

This feature optimizes a number of queries that project correlated collections. Previously those would produce N+1 queries. Now, we rewrite queries similarly to how Include pipeline does it, producing only two queries and correlating them on the client.
To enable the feature the inner subquery needs to be wrapped around ToList() or ToArray() call.

Current limitations:
- only works for sync queries,
- doesn't work if the parent query results in a CROSS JOIN,
- doesn't work with result operators (i.e. Skip/Take/Distinct)
- doesn't work if outer query needs client evaluation anywhere outside projection (e.g. order by or filter by NonMapped property)
- doesn't work if inner query is correlated with query two (or more) levels up, (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name == c.Name).ToList()).ToList())
- doesn't work in nested scenarios where the outer collection is streaming (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name != "Foo").ToList())) - to make it work, outer collection must also be wrapped in ToList(). However it is OK to stream inner collection - in that case outer collection will take advantage of the optimization.

Optimization process:

original query:

from c in ctx.Customers
where c.CustomerID != "ALFKI"
orderby c.City descending
select (from o in c.Orders
        where o.OrderID > 100
        orderby o.EmployeeID
        select new { o.OrderID, o.CustomerID }).ToList()

nav rewrite converts it to:

from c in customers
where c.CustomerID != "ALFKI"
order by c.City descending
select
   (from o in orders
    where o.OrderID > 100
    order by o.EmployeeID
    where c.CustomerID ?= o.CustomerID
    select new { o.OrderID, o.CustomerID }).ToList()

which gets rewritten to (simplified):

from c in customers
where c.CustomerID != "ALFKI"
order by c.City desc, c.CustomerID asc
select CorrelateSubquery(
    outerKey: new { c.CustomerID },
    correlationPredicate: (outer, inner) => outer.GetValue(0) == null || inner.GetValue(0) == null ? false : outer.GetValue(0) == inner.GetValue(0)
    correlatedCollectionFactory: () =>
        from o in orders
        where o.OrderID > 100
        join _c in
            from c in customers
            where c.CustomerID != "ALFKI"
            select new { c.City, c.CustomerID }
        on o.CustomerID equals _c.GetValue(1)
        order by _c.GetValue(0) descending, _c.GetValue(1), o.EmployeeID
        select new
        {
            InnerResult = new { o.OrderID, o.CustomerID }
            InnerKey = new { o.CustomerID },
            OriginKey = new { _c.GetValue(1) }
        }).ToList()

CorrelateSubquery is the method that combines results of outer and inner queries. Because order for both queries is the same we can perform only one pass thru inner query.
We use correlation predicate (between outerKey parameter passed to CorrelateSubquery and InnerKey which is part of the final result) to determine whether giver result of the inner query belongs to the outer.
We also remember latest origin key (i.e. PK of the outer, which is not always the same as outer key). If the origin key changes, it means that all inners for that outer have already been encountered.

@ajcvickers ajcvickers modified the milestones: 2.1.0-preview1, 2.1.0 Jan 17, 2018

maumar added a commit that referenced this issue Jan 18, 2018

Fix to #9282 - Query: optimize queries projecting correlated collecti…
…ons, so that they don't result in N+1 database queries

This feature optimizes a number of queries that project correlated collections. Previously those would produce N+1 queries. Now, we rewrite queries similarly to how Include pipeline does it, producing only two queries and correlating them on the client.
To enable the feature the inner subquery needs to be wrapped around ToList() or ToArray() call.

Current limitations:
- only works for sync queries,
- doesn't work if the parent query results in a CROSS JOIN,
- doesn't work with result operators (i.e. Skip/Take/Distinct)
- doesn't work if outer query needs client evaluation anywhere outside projection (e.g. order by or filter by NonMapped property)
- doesn't work if inner query is correlated with query two (or more) levels up, (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name == c.Name).ToList()).ToList())
- doesn't work in nested scenarios where the outer collection is streaming (e.g. customers.Select(c => c.Orders.Select(o => o.OrderDetails.Where(od => od.Name != "Foo").ToList())) - to make it work, outer collection must also be wrapped in ToList(). However it is OK to stream inner collection - in that case outer collection will take advantage of the optimization.

Optimization process:

original query:

from c in ctx.Customers
where c.CustomerID != "ALFKI"
orderby c.City descending
select (from o in c.Orders
        where o.OrderID > 100
        orderby o.EmployeeID
        select new { o.OrderID, o.CustomerID }).ToList()

nav rewrite converts it to:

from c in customers
where c.CustomerID != "ALFKI"
order by c.City descending
select
   (from o in orders
    where o.OrderID > 100
    order by o.EmployeeID
    where c.CustomerID ?= o.CustomerID
    select new { o.OrderID, o.CustomerID }).ToList()

which gets rewritten to (simplified):

from c in customers
where c.CustomerID != "ALFKI"
order by c.City desc, c.CustomerID asc
select CorrelateSubquery(
    outerKey: new { c.CustomerID },
    correlationPredicate: (outer, inner) => outer.GetValue(0) == null || inner.GetValue(0) == null ? false : outer.GetValue(0) == inner.GetValue(0)
    correlatedCollectionFactory: () =>
        from o in orders
        where o.OrderID > 100
        join _c in
            from c in customers
            where c.CustomerID != "ALFKI"
            select new { c.City, c.CustomerID }
        on o.CustomerID equals _c.GetValue(1)
        order by _c.GetValue(0) descending, _c.GetValue(1), o.EmployeeID
        select new
        {
            InnerResult = new { o.OrderID, o.CustomerID }
            InnerKey = new { o.CustomerID },
            OriginKey = new { _c.GetValue(1) }
        }).ToList()

CorrelateSubquery is the method that combines results of outer and inner queries. Because order for both queries is the same we can perform only one pass thru inner query.
We use correlation predicate (between outerKey parameter passed to CorrelateSubquery and InnerKey which is part of the final result) to determine whether giver result of the inner query belongs to the outer.
We also remember latest origin key (i.e. PK of the outer, which is not always the same as outer key). If the origin key changes, it means that all inners for that outer have already been encountered.
@maumar

This comment has been minimized.

Contributor

maumar commented Jan 19, 2018

fixed in b95f23f

@maumar maumar closed this Jan 19, 2018

@maumar maumar added the closed-fixed label Jan 19, 2018

@maumar maumar removed this from the 2.1.0 milestone Jan 19, 2018

@jemiller0

This comment has been minimized.

jemiller0 commented Jan 19, 2018

Much thanks. Definitely looking forward to testing this one out.

@maumar

This comment has been minimized.

Contributor

maumar commented Jan 19, 2018

@jemiller0 please let us know about any issues you encounter. Given how complex your model/queries are, the feedback would be extremely helpful in finding bugs that slipped through our internal testing.

@jemiller0

This comment has been minimized.

jemiller0 commented Jan 19, 2018

@maumar I just tested the following query using
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.1.0-preview1-28120" />
which I downloaded from MyGet.

using (var lc = new LibraryContext())
{
    var q = from oi in lc.OrderItems
            select new
            {
                oi.Id,
                Order = oi.Order.Id,
                oi.OrderId,
                oi.Order.CreationTime,
                OrderStatus = oi.Order.OrderStatus.Name,
                oi.Order.OrderStatusId,
                OrderType = oi.Order.OrderType.Name,
                oi.Order.OrderTypeId,
                ReceiptStatus = oi.Holding.ReceiptStatus.Name,
                oi.Holding.ReceiptStatusId,
                Vendor = oi.Order.Vendor.Name,
                oi.Order.Vendor.EmailAddress,
                oi.Order.VendorId,
                Accounts = oi.OrderItemAccounts.Select(oia => oia.Account.Code),
                Donors = oi.OrderItemDonors.Select(oid => oid.Donor.Code),
                oi.VendorNotes,
                oi.SpecialNotes,
                oi.MiscellaneousNotes,
                oi.SelectorNotes,
                oi.Order.VendorCustomerId,
                DeliveryRoom = oi.Order.DeliveryRoom.Name,
                oi.Order.DeliveryRoomId,
                ExtendedCost = oi.UnitPrice * oi.Quantity,
                oi.VendorItemId,
                Bib = oi.Bib.Title,
                oi.BibId,
                oi.Bib.Author,
                oi.Bib.OclcNumber,
                OclcNumbers = oi.Bib.OclcNumbers.Select(n => n.Content),
                Isbns = oi.Bib.Isbns.Select(n => n.Content),
                Issns = oi.Bib.Issns.Select(n => n.Content),
                OtherNumbers = oi.Bib.Numbers.Where(n => n.Type == "024").Select(n => n.Content),
                PublisherNumbers = oi.Bib.Numbers.Where(n => n.Type == "028").Select(n => n.Content),
                Editions = oi.Bib.Editions.Select(n => n.Content),
                Publications = oi.Bib.Publications.Select(n => n.Content)
            };
    var l = q.Take(5).ToArray();
    foreach (var oi in l) oi.Accounts.ToArray();
}

Unfortunately, it looks like it's still using N+1 queries for my query.

LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='90']
SELECT TOP(@__p_0) [oi].[Id] AS [Id0], [oi.Order].[Id] AS [Order], [oi].[OrderId], [oi.Order].[CreationTime], [oi.Order.OrderStatus].[Name] AS [OrderStatus], [oi.Order].[OrderStatusId], [oi.Order.OrderType].[Name] AS [OrderType], [oi.Order].[OrderTypeId], [oi.Holding.ReceiptStatus].[Name] AS [ReceiptStatus], [oi.Holding].[ReceiptStatusId], [oi.Order.Vendor].[Name] AS [Vendor], [oi.Order.Vendor].[EmailAddress], [oi.Order].[VendorId], [oi].[VendorNotes], [oi].[SpecialNotes], [oi].[MiscellaneousNotes], [oi].[SelectorNotes], [oi.Order].[VendorCustomerId], [oi.Order.DeliveryRoom].[Name] AS [DeliveryRoom], [oi.Order].[DeliveryRoomId], [oi].[UnitPrice] * [oi].[Quantity] AS [ExtendedCost], [oi].[VendorItemId], [oi.Bib].[Title] AS [Bib], [oi].[BibId], [oi.Bib].[Author], [oi.Bib].[OclcNumber], [oi.Bib].[Id]
FROM [OrderItems] AS [oi]
LEFT JOIN [Bibs] AS [oi.Bib] ON [oi].[BibId] = [oi.Bib].[Id]
LEFT JOIN [Holdings] AS [oi.Holding] ON [oi].[HoldingId] = [oi.Holding].[Id]
LEFT JOIN [ReceiptStatuses] AS [oi.Holding.ReceiptStatus] ON [oi.Holding].[ReceiptStatusId] = [oi.Holding.ReceiptStatus].[Id]
INNER JOIN [Orders] AS [oi.Order] ON [oi].[OrderId] = [oi.Order].[Id]
INNER JOIN [OrderStatuses] AS [oi.Order.OrderStatus] ON [oi.Order].[OrderStatusId] = [oi.Order.OrderStatus].[Id]
INNER JOIN [OrderTypes] AS [oi.Order.OrderType] ON [oi.Order].[OrderTypeId] = [oi.Order.OrderType].[Id]
INNER JOIN [Vendors] AS [oi.Order.Vendor] ON [oi.Order].[VendorId] = [oi.Order.Vendor].[Id]
LEFT JOIN [Rooms] AS [oi.Order.DeliveryRoom] ON [oi.Order].[DeliveryRoomId] = [oi.Order.DeliveryRoom].[Id]
    ThreadId=1
    DateTime=2018-01-19T23:28:17.8817185Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account].[Code]
FROM [OrderItemAccounts] AS [oia]
INNER JOIN [Accounts] AS [oia.Account] ON [oia].[AccountId] = [oia.Account].[Id]
WHERE @_outer_Id = [oia].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-19T23:28:18.0271409Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account].[Code]
FROM [OrderItemAccounts] AS [oia]
INNER JOIN [Accounts] AS [oia.Account] ON [oia].[AccountId] = [oia.Account].[Id]
WHERE @_outer_Id = [oia].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-19T23:28:18.0389157Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account].[Code]
FROM [OrderItemAccounts] AS [oia]
INNER JOIN [Accounts] AS [oia.Account] ON [oia].[AccountId] = [oia.Account].[Id]
WHERE @_outer_Id = [oia].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-19T23:28:18.0447473Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account].[Code]
FROM [OrderItemAccounts] AS [oia]
INNER JOIN [Accounts] AS [oia.Account] ON [oia].[AccountId] = [oia.Account].[Id]
WHERE @_outer_Id = [oia].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-19T23:28:18.0499281Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account].[Code]
FROM [OrderItemAccounts] AS [oia]
INNER JOIN [Accounts] AS [oia.Account] ON [oia].[AccountId] = [oia.Account].[Id]
WHERE @_outer_Id = [oia].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-19T23:28:18.0555723Z
LibraryConsoleApplication Information: 0 : 00:00:02.2725718 elapsed
    ThreadId=1
    DateTime=2018-01-19T23:28:18.0689110Z

Can you tell me of the fix is included in the MyGet package that I'm using?

@SSkovboiSS

This comment has been minimized.

Contributor

SSkovboiSS commented Jan 20, 2018

@jemiller0 optimization cannot be applied if you are streaming a collection.
Try use ToList or ToArray:
oi.OrderItemAccounts.Select(oia => oia.Account.Code).ToList()

@maumar while I understand the purpose of not applying optimization for streamed collections, in most cases that's not what people need. Sometimes you can just forget to add ToList. Issuing a warning is also not a good option because sometimes such a behavior is intended, e. g. when included collection is huge.

@jemiller0

This comment has been minimized.

jemiller0 commented Jan 20, 2018

@SSkovboiSS Thanks for the tip. I was going to say, I actually don't mind the fact that you need to use ToArray() or ToList() as it would allow you to control how the query is executed. In some cases N+1 might actually be better. At least compared to how EF 6 worked. However, this works differently than EF 6. EF 6 generated one big SELECT statement. The way EF Core works, it creates a separate SELECT for each of the collections. Which seems like it would probably work better because you don't get a giant multiply of the data. However, personally, I wish there was a way to enable the EF 6 behavior. It worked pretty well for the query that I have. At least with SQL Server. SQL Server was apparently able to optimize it well enough that it worked pretty well. Even though the query EF 6 was generating looked pretty hellish.

I added .ToArray()s to the query like the following.

            using (var lc = new LibraryContext())
            {
                var q = from oi in lc.OrderItems
                        select new
                        {
                            oi.Id,
                            Order = oi.Order.Id,
                            oi.OrderId,
                            oi.Order.CreationTime,
                            OrderStatus = oi.Order.OrderStatus.Name,
                            oi.Order.OrderStatusId,
                            OrderType = oi.Order.OrderType.Name,
                            oi.Order.OrderTypeId,
                            ReceiptStatus = oi.Holding.ReceiptStatus.Name,
                            oi.Holding.ReceiptStatusId,
                            Vendor = oi.Order.Vendor.Name,
                            oi.Order.Vendor.EmailAddress,
                            oi.Order.VendorId,
                            Accounts = oi.OrderItemAccounts.Select(oia => oia.Account.Code).ToArray(),
                            Donors = oi.OrderItemDonors.Select(oid => oid.Donor.Code).ToArray(),
                            oi.VendorNotes,
                            oi.SpecialNotes,
                            oi.MiscellaneousNotes,
                            oi.SelectorNotes,
                            oi.Order.VendorCustomerId,
                            DeliveryRoom = oi.Order.DeliveryRoom.Name,
                            oi.Order.DeliveryRoomId,
                            ExtendedCost = oi.UnitPrice * oi.Quantity,
                            oi.VendorItemId,
                            Bib = oi.Bib.Title,
                            oi.BibId,
                            oi.Bib.Author,
                            oi.Bib.OclcNumber,
                            OclcNumbers = oi.Bib.OclcNumbers.Select(n => n.Content).ToArray(),
                            Isbns = oi.Bib.Isbns.Select(n => n.Content),
                            Issns = oi.Bib.Issns.Select(n => n.Content),
                            OtherNumbers = oi.Bib.Numbers.Where(n => n.Type == "024").Select(n => n.Content).ToArray(),
                            PublisherNumbers = oi.Bib.Numbers.Where(n => n.Type == "028").Select(n => n.Content).ToArray(),
                            Editions = oi.Bib.Editions.Select(n => n.Content).ToArray(),
                            Publications = oi.Bib.Publications.Select(n => n.Content).ToArray()
                        };
                var l = q.Where(oi => oi.Accounts.Any(s => s == "XPUTT")).Take(5).ToArray();
                foreach (var oi in l) oi.Accounts.ToArray();
            }

That works as far as preventing it from performing N+1 queries. However, if you have a .Where() on one of the collections, it doesn't add a WHERE to the main query. Instead, it generates tons of N+1 queries.

LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='90']
SELECT [oi].[Id] AS [Id0], [oi.Order].[Id] AS [Order], [oi].[OrderId], [oi.Order].[CreationTime], [oi.Order.OrderStatus].[Name] AS [OrderStatus], [oi.Order].[OrderStatusId], [oi.Order.OrderType].[Name] AS [OrderType], [oi.Order].[OrderTypeId], [oi.Holding.ReceiptStatus].[Name] AS [ReceiptStatus], [oi.Holding].[ReceiptStatusId], [oi.Order.Vendor].[Name] AS [Vendor], [oi.Order.Vendor].[EmailAddress], [oi.Order].[VendorId], [oi].[VendorNotes], [oi].[SpecialNotes], [oi].[MiscellaneousNotes], [oi].[SelectorNotes], [oi.Order].[VendorCustomerId], [oi.Order.DeliveryRoom].[Name] AS [DeliveryRoom], [oi.Order].[DeliveryRoomId], [oi].[UnitPrice] * [oi].[Quantity] AS [ExtendedCost], [oi].[VendorItemId], [oi.Bib].[Title] AS [Bib], [oi].[BibId], [oi.Bib].[Author], [oi.Bib].[OclcNumber], [oi.Bib].[Id]
FROM [OrderItems] AS [oi]
LEFT JOIN [Bibs] AS [oi.Bib] ON [oi].[BibId] = [oi.Bib].[Id]
LEFT JOIN [Holdings] AS [oi.Holding] ON [oi].[HoldingId] = [oi.Holding].[Id]
LEFT JOIN [ReceiptStatuses] AS [oi.Holding.ReceiptStatus] ON [oi.Holding].[ReceiptStatusId] = [oi.Holding.ReceiptStatus].[Id]
INNER JOIN [Orders] AS [oi.Order] ON [oi].[OrderId] = [oi.Order].[Id]
INNER JOIN [OrderStatuses] AS [oi.Order.OrderStatus] ON [oi.Order].[OrderStatusId] = [oi.Order.OrderStatus].[Id]
INNER JOIN [OrderTypes] AS [oi.Order.OrderType] ON [oi.Order].[OrderTypeId] = [oi.Order.OrderType].[Id]
INNER JOIN [Vendors] AS [oi.Order.Vendor] ON [oi.Order].[VendorId] = [oi.Order.Vendor].[Id]
LEFT JOIN [Rooms] AS [oi.Order.DeliveryRoom] ON [oi.Order].[DeliveryRoomId] = [oi.Order.DeliveryRoom].[Id]
    ThreadId=1
    DateTime=2018-01-20T15:08:32.9384755Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account].[Code]
FROM [OrderItemAccounts] AS [oia]
INNER JOIN [Accounts] AS [oia.Account] ON [oia].[AccountId] = [oia.Account].[Id]
WHERE @_outer_Id = [oia].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-20T15:08:50.5037581Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account].[Code]
FROM [OrderItemAccounts] AS [oia]
INNER JOIN [Accounts] AS [oia.Account] ON [oia].[AccountId] = [oia.Account].[Id]
WHERE @_outer_Id = [oia].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-20T15:08:50.5752513Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account].[Code]
FROM [OrderItemAccounts] AS [oia]
INNER JOIN [Accounts] AS [oia.Account] ON [oia].[AccountId] = [oia.Account].[Id]
WHERE @_outer_Id = [oia].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-20T15:08:50.5807935Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account].[Code]
FROM [OrderItemAccounts] AS [oia]
INNER JOIN [Accounts] AS [oia.Account] ON [oia].[AccountId] = [oia.Account].[Id]
WHERE @_outer_Id = [oia].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-20T15:08:50.5855761Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account].[Code]
FROM [OrderItemAccounts] AS [oia]
INNER JOIN [Accounts] AS [oia.Account] ON [oia].[AccountId] = [oia.Account].[Id]
WHERE @_outer_Id = [oia].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-20T15:08:50.5903397Z
...

Without the filter, it looks like what it's doing is acceptable, and seems like it's probably better than what EF 6 did with the big single query. If I take off the .ToArray() off the Accounts collection, it adds a WHERE to the main query and subqueries like I want it to, but, then it fetches the Accounts data using N+1 queries at the bottom. That might be somewhat acceptable for something like a web page that is only displaying a page of data. However, my web pages also allow the user to export the query results to a file. In that case, it's not restricted to just a page of data (say 100 results). This is where the EF 6 behavior (one big query) works well.

LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='90']
SELECT TOP(@__p_0) [oi].[Id] AS [Id0], [oi.Order].[Id] AS [Order], [oi].[OrderId], [oi.Order].[CreationTime], [oi.Order.OrderStatus].[Name] AS [OrderStatus], [oi.Order].[OrderStatusId], [oi.Order.OrderType].[Name] AS [OrderType], [oi.Order].[OrderTypeId], [oi.Holding.ReceiptStatus].[Name] AS [ReceiptStatus], [oi.Holding].[ReceiptStatusId], [oi.Order.Vendor].[Name] AS [Vendor], [oi.Order.Vendor].[EmailAddress], [oi.Order].[VendorId], [oi].[VendorNotes], [oi].[SpecialNotes], [oi].[MiscellaneousNotes], [oi].[SelectorNotes], [oi.Order].[VendorCustomerId], [oi.Order.DeliveryRoom].[Name] AS [DeliveryRoom], [oi.Order].[DeliveryRoomId], [oi].[UnitPrice] * [oi].[Quantity] AS [ExtendedCost], [oi].[VendorItemId], [oi.Bib].[Title] AS [Bib], [oi].[BibId], [oi.Bib].[Author], [oi.Bib].[OclcNumber], [oi.Bib].[Id]
FROM [OrderItems] AS [oi]
LEFT JOIN [Bibs] AS [oi.Bib] ON [oi].[BibId] = [oi.Bib].[Id]
LEFT JOIN [Holdings] AS [oi.Holding] ON [oi].[HoldingId] = [oi.Holding].[Id]
LEFT JOIN [ReceiptStatuses] AS [oi.Holding.ReceiptStatus] ON [oi.Holding].[ReceiptStatusId] = [oi.Holding.ReceiptStatus].[Id]
INNER JOIN [Orders] AS [oi.Order] ON [oi].[OrderId] = [oi.Order].[Id]
INNER JOIN [OrderStatuses] AS [oi.Order.OrderStatus] ON [oi.Order].[OrderStatusId] = [oi.Order.OrderStatus].[Id]
INNER JOIN [OrderTypes] AS [oi.Order.OrderType] ON [oi.Order].[OrderTypeId] = [oi.Order.OrderType].[Id]
INNER JOIN [Vendors] AS [oi.Order.Vendor] ON [oi.Order].[VendorId] = [oi.Order.Vendor].[Id]
LEFT JOIN [Rooms] AS [oi.Order.DeliveryRoom] ON [oi.Order].[DeliveryRoomId] = [oi.Order.DeliveryRoom].[Id]
WHERE EXISTS (
    SELECT 1
    FROM [OrderItemAccounts] AS [oia]
    INNER JOIN [Accounts] AS [oia.Account] ON [oia].[AccountId] = [oia.Account].[Id]
    WHERE ([oia.Account].[Code] = N'XPUTT') AND ([oi].[Id] = [oia].[OrderItemId]))
ORDER BY [oi].[Id], [oi.Bib].[Id]
    ThreadId=1
    DateTime=2018-01-20T15:25:18.2492238Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='90']
SELECT [t].[Id], [oid.Donor].[Code], [oi.OrderItemDonors].[OrderItemId]
FROM [OrderItemDonors] AS [oi.OrderItemDonors]
INNER JOIN [Donors] AS [oid.Donor] ON [oi.OrderItemDonors].[DonorId] = [oid.Donor].[Id]
INNER JOIN (
    SELECT TOP(@__p_0) [oi0].[Id]
    FROM [OrderItems] AS [oi0]
    LEFT JOIN [Bibs] AS [oi.Bib0] ON [oi0].[BibId] = [oi.Bib0].[Id]
    LEFT JOIN [Holdings] AS [oi.Holding0] ON [oi0].[HoldingId] = [oi.Holding0].[Id]
    LEFT JOIN [ReceiptStatuses] AS [oi.Holding.ReceiptStatus0] ON [oi.Holding0].[ReceiptStatusId] = [oi.Holding.ReceiptStatus0].[Id]
    INNER JOIN [Orders] AS [oi.Order0] ON [oi0].[OrderId] = [oi.Order0].[Id]
    INNER JOIN [OrderStatuses] AS [oi.Order.OrderStatus0] ON [oi.Order0].[OrderStatusId] = [oi.Order.OrderStatus0].[Id]
    INNER JOIN [OrderTypes] AS [oi.Order.OrderType0] ON [oi.Order0].[OrderTypeId] = [oi.Order.OrderType0].[Id]
    INNER JOIN [Vendors] AS [oi.Order.Vendor0] ON [oi.Order0].[VendorId] = [oi.Order.Vendor0].[Id]
    LEFT JOIN [Rooms] AS [oi.Order.DeliveryRoom0] ON [oi.Order0].[DeliveryRoomId] = [oi.Order.DeliveryRoom0].[Id]
    WHERE EXISTS (
        SELECT 1
        FROM [OrderItemAccounts] AS [oia1]
        INNER JOIN [Accounts] AS [oia.Account1] ON [oia1].[AccountId] = [oia.Account1].[Id]
        WHERE ([oia.Account1].[Code] = N'XPUTT') AND ([oi0].[Id] = [oia1].[OrderItemId]))
) AS [t] ON [oi.OrderItemDonors].[OrderItemId] = [t].[Id]
ORDER BY [t].[Id]
    ThreadId=1
    DateTime=2018-01-20T15:25:18.5401290Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='90']
SELECT [t0].[Id], [t0].[Id0], [oi.Bib.OclcNumbers].[Content], [oi.Bib.OclcNumbers].[BibId]
FROM [OclcNumbers] AS [oi.Bib.OclcNumbers]
INNER JOIN (
    SELECT TOP(@__p_0) [oi1].[Id], [oi.Bib1].[Id] AS [Id0]
    FROM [OrderItems] AS [oi1]
    LEFT JOIN [Bibs] AS [oi.Bib1] ON [oi1].[BibId] = [oi.Bib1].[Id]
    LEFT JOIN [Holdings] AS [oi.Holding1] ON [oi1].[HoldingId] = [oi.Holding1].[Id]
    LEFT JOIN [ReceiptStatuses] AS [oi.Holding.ReceiptStatus1] ON [oi.Holding1].[ReceiptStatusId] = [oi.Holding.ReceiptStatus1].[Id]
    INNER JOIN [Orders] AS [oi.Order1] ON [oi1].[OrderId] = [oi.Order1].[Id]
    INNER JOIN [OrderStatuses] AS [oi.Order.OrderStatus1] ON [oi.Order1].[OrderStatusId] = [oi.Order.OrderStatus1].[Id]
    INNER JOIN [OrderTypes] AS [oi.Order.OrderType1] ON [oi.Order1].[OrderTypeId] = [oi.Order.OrderType1].[Id]
    INNER JOIN [Vendors] AS [oi.Order.Vendor1] ON [oi.Order1].[VendorId] = [oi.Order.Vendor1].[Id]
    LEFT JOIN [Rooms] AS [oi.Order.DeliveryRoom1] ON [oi.Order1].[DeliveryRoomId] = [oi.Order.DeliveryRoom1].[Id]
    WHERE EXISTS (
        SELECT 1
        FROM [OrderItemAccounts] AS [oia2]
        INNER JOIN [Accounts] AS [oia.Account2] ON [oia2].[AccountId] = [oia.Account2].[Id]
        WHERE ([oia.Account2].[Code] = N'XPUTT') AND ([oi1].[Id] = [oia2].[OrderItemId]))
) AS [t0] ON [oi.Bib.OclcNumbers].[BibId] = [t0].[Id0]
ORDER BY [t0].[Id], [t0].[Id0]
    ThreadId=1
    DateTime=2018-01-20T15:25:18.6516621Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='90']
SELECT [t1].[Id], [t1].[Id0], [oi.Bib.Numbers].[Content], [oi.Bib.Numbers].[BibId]
FROM [Numbers] AS [oi.Bib.Numbers]
INNER JOIN (
    SELECT TOP(@__p_0) [oi2].[Id], [oi.Bib2].[Id] AS [Id0]
    FROM [OrderItems] AS [oi2]
    LEFT JOIN [Bibs] AS [oi.Bib2] ON [oi2].[BibId] = [oi.Bib2].[Id]
    LEFT JOIN [Holdings] AS [oi.Holding2] ON [oi2].[HoldingId] = [oi.Holding2].[Id]
    LEFT JOIN [ReceiptStatuses] AS [oi.Holding.ReceiptStatus2] ON [oi.Holding2].[ReceiptStatusId] = [oi.Holding.ReceiptStatus2].[Id]
    INNER JOIN [Orders] AS [oi.Order2] ON [oi2].[OrderId] = [oi.Order2].[Id]
    INNER JOIN [OrderStatuses] AS [oi.Order.OrderStatus2] ON [oi.Order2].[OrderStatusId] = [oi.Order.OrderStatus2].[Id]
    INNER JOIN [OrderTypes] AS [oi.Order.OrderType2] ON [oi.Order2].[OrderTypeId] = [oi.Order.OrderType2].[Id]
    INNER JOIN [Vendors] AS [oi.Order.Vendor2] ON [oi.Order2].[VendorId] = [oi.Order.Vendor2].[Id]
    LEFT JOIN [Rooms] AS [oi.Order.DeliveryRoom2] ON [oi.Order2].[DeliveryRoomId] = [oi.Order.DeliveryRoom2].[Id]
    WHERE EXISTS (
        SELECT 1
        FROM [OrderItemAccounts] AS [oia3]
        INNER JOIN [Accounts] AS [oia.Account3] ON [oia3].[AccountId] = [oia.Account3].[Id]
        WHERE ([oia.Account3].[Code] = N'XPUTT') AND ([oi2].[Id] = [oia3].[OrderItemId]))
) AS [t1] ON [oi.Bib.Numbers].[BibId] = [t1].[Id0]
WHERE [oi.Bib.Numbers].[Type] = N'024'
ORDER BY [t1].[Id], [t1].[Id0]
    ThreadId=1
    DateTime=2018-01-20T15:25:18.7777899Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='90']
SELECT [t2].[Id], [t2].[Id0], [oi.Bib.Numbers0].[Content], [oi.Bib.Numbers0].[BibId]
FROM [Numbers] AS [oi.Bib.Numbers0]
INNER JOIN (
    SELECT TOP(@__p_0) [oi3].[Id], [oi.Bib3].[Id] AS [Id0]
    FROM [OrderItems] AS [oi3]
    LEFT JOIN [Bibs] AS [oi.Bib3] ON [oi3].[BibId] = [oi.Bib3].[Id]
    LEFT JOIN [Holdings] AS [oi.Holding3] ON [oi3].[HoldingId] = [oi.Holding3].[Id]
    LEFT JOIN [ReceiptStatuses] AS [oi.Holding.ReceiptStatus3] ON [oi.Holding3].[ReceiptStatusId] = [oi.Holding.ReceiptStatus3].[Id]
    INNER JOIN [Orders] AS [oi.Order3] ON [oi3].[OrderId] = [oi.Order3].[Id]
    INNER JOIN [OrderStatuses] AS [oi.Order.OrderStatus3] ON [oi.Order3].[OrderStatusId] = [oi.Order.OrderStatus3].[Id]
    INNER JOIN [OrderTypes] AS [oi.Order.OrderType3] ON [oi.Order3].[OrderTypeId] = [oi.Order.OrderType3].[Id]
    INNER JOIN [Vendors] AS [oi.Order.Vendor3] ON [oi.Order3].[VendorId] = [oi.Order.Vendor3].[Id]
    LEFT JOIN [Rooms] AS [oi.Order.DeliveryRoom3] ON [oi.Order3].[DeliveryRoomId] = [oi.Order.DeliveryRoom3].[Id]
    WHERE EXISTS (
        SELECT 1
        FROM [OrderItemAccounts] AS [oia4]
        INNER JOIN [Accounts] AS [oia.Account4] ON [oia4].[AccountId] = [oia.Account4].[Id]
        WHERE ([oia.Account4].[Code] = N'XPUTT') AND ([oi3].[Id] = [oia4].[OrderItemId]))
) AS [t2] ON [oi.Bib.Numbers0].[BibId] = [t2].[Id0]
WHERE [oi.Bib.Numbers0].[Type] = N'028'
ORDER BY [t2].[Id], [t2].[Id0]
    ThreadId=1
    DateTime=2018-01-20T15:25:18.9223931Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='90']
SELECT [t3].[Id], [t3].[Id0], [oi.Bib.Editions].[Content], [oi.Bib.Editions].[BibId]
FROM [Editions] AS [oi.Bib.Editions]
INNER JOIN (
    SELECT TOP(@__p_0) [oi4].[Id], [oi.Bib4].[Id] AS [Id0]
    FROM [OrderItems] AS [oi4]
    LEFT JOIN [Bibs] AS [oi.Bib4] ON [oi4].[BibId] = [oi.Bib4].[Id]
    LEFT JOIN [Holdings] AS [oi.Holding4] ON [oi4].[HoldingId] = [oi.Holding4].[Id]
    LEFT JOIN [ReceiptStatuses] AS [oi.Holding.ReceiptStatus4] ON [oi.Holding4].[ReceiptStatusId] = [oi.Holding.ReceiptStatus4].[Id]
    INNER JOIN [Orders] AS [oi.Order4] ON [oi4].[OrderId] = [oi.Order4].[Id]
    INNER JOIN [OrderStatuses] AS [oi.Order.OrderStatus4] ON [oi.Order4].[OrderStatusId] = [oi.Order.OrderStatus4].[Id]
    INNER JOIN [OrderTypes] AS [oi.Order.OrderType4] ON [oi.Order4].[OrderTypeId] = [oi.Order.OrderType4].[Id]
    INNER JOIN [Vendors] AS [oi.Order.Vendor4] ON [oi.Order4].[VendorId] = [oi.Order.Vendor4].[Id]
    LEFT JOIN [Rooms] AS [oi.Order.DeliveryRoom4] ON [oi.Order4].[DeliveryRoomId] = [oi.Order.DeliveryRoom4].[Id]
    WHERE EXISTS (
        SELECT 1
        FROM [OrderItemAccounts] AS [oia5]
        INNER JOIN [Accounts] AS [oia.Account5] ON [oia5].[AccountId] = [oia.Account5].[Id]
        WHERE ([oia.Account5].[Code] = N'XPUTT') AND ([oi4].[Id] = [oia5].[OrderItemId]))
) AS [t3] ON [oi.Bib.Editions].[BibId] = [t3].[Id0]
ORDER BY [t3].[Id], [t3].[Id0]
    ThreadId=1
    DateTime=2018-01-20T15:25:19.0402723Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@__p_0='?'], CommandType='Text', CommandTimeout='90']
SELECT [t4].[Id], [t4].[Id0], [oi.Bib.Publications].[Content], [oi.Bib.Publications].[BibId]
FROM [Publications] AS [oi.Bib.Publications]
INNER JOIN (
    SELECT TOP(@__p_0) [oi5].[Id], [oi.Bib5].[Id] AS [Id0]
    FROM [OrderItems] AS [oi5]
    LEFT JOIN [Bibs] AS [oi.Bib5] ON [oi5].[BibId] = [oi.Bib5].[Id]
    LEFT JOIN [Holdings] AS [oi.Holding5] ON [oi5].[HoldingId] = [oi.Holding5].[Id]
    LEFT JOIN [ReceiptStatuses] AS [oi.Holding.ReceiptStatus5] ON [oi.Holding5].[ReceiptStatusId] = [oi.Holding.ReceiptStatus5].[Id]
    INNER JOIN [Orders] AS [oi.Order5] ON [oi5].[OrderId] = [oi.Order5].[Id]
    INNER JOIN [OrderStatuses] AS [oi.Order.OrderStatus5] ON [oi.Order5].[OrderStatusId] = [oi.Order.OrderStatus5].[Id]
    INNER JOIN [OrderTypes] AS [oi.Order.OrderType5] ON [oi.Order5].[OrderTypeId] = [oi.Order.OrderType5].[Id]
    INNER JOIN [Vendors] AS [oi.Order.Vendor5] ON [oi.Order5].[VendorId] = [oi.Order.Vendor5].[Id]
    LEFT JOIN [Rooms] AS [oi.Order.DeliveryRoom5] ON [oi.Order5].[DeliveryRoomId] = [oi.Order.DeliveryRoom5].[Id]
    WHERE EXISTS (
        SELECT 1
        FROM [OrderItemAccounts] AS [oia6]
        INNER JOIN [Accounts] AS [oia.Account6] ON [oia6].[AccountId] = [oia.Account6].[Id]
        WHERE ([oia.Account6].[Code] = N'XPUTT') AND ([oi5].[Id] = [oia6].[OrderItemId]))
) AS [t4] ON [oi.Bib.Publications].[BibId] = [t4].[Id0]
ORDER BY [t4].[Id], [t4].[Id0]
    ThreadId=1
    DateTime=2018-01-20T15:25:19.1856054Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account0].[Code]
FROM [OrderItemAccounts] AS [oia0]
INNER JOIN [Accounts] AS [oia.Account0] ON [oia0].[AccountId] = [oia.Account0].[Id]
WHERE @_outer_Id = [oia0].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-20T15:25:19.3084017Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account0].[Code]
FROM [OrderItemAccounts] AS [oia0]
INNER JOIN [Accounts] AS [oia.Account0] ON [oia0].[AccountId] = [oia.Account0].[Id]
WHERE @_outer_Id = [oia0].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-20T15:25:19.3258079Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account0].[Code]
FROM [OrderItemAccounts] AS [oia0]
INNER JOIN [Accounts] AS [oia.Account0] ON [oia0].[AccountId] = [oia.Account0].[Id]
WHERE @_outer_Id = [oia0].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-20T15:25:19.3440917Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account0].[Code]
FROM [OrderItemAccounts] AS [oia0]
INNER JOIN [Accounts] AS [oia.Account0] ON [oia0].[AccountId] = [oia.Account0].[Id]
WHERE @_outer_Id = [oia0].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-20T15:25:19.3624442Z
LibraryLibrary Verbose: 0 : Executing DbCommand [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='90']
SELECT [oia.Account0].[Code]
FROM [OrderItemAccounts] AS [oia0]
INNER JOIN [Accounts] AS [oia.Account0] ON [oia0].[AccountId] = [oia.Account0].[Id]
WHERE @_outer_Id = [oia0].[OrderItemId]
    ThreadId=1
    DateTime=2018-01-20T15:25:19.3732502Z
LibraryConsoleApplication Information: 0 : 00:00:03.2879782 elapsed
    ThreadId=1
    DateTime=2018-01-20T15:25:19.3885980Z
@jemiller0

This comment has been minimized.

jemiller0 commented Jan 20, 2018

The following is what the query roughly looks like in EF 6. It looks horrid, but, actually works not that bad.

If EF Core could just add the filters rather than doing N+1s, probably it would be better than EF 6.

It seems like it would be nice if there was a way to specify that you want to use the one big query method though. People could use it for backwards compatibility if nothing else.

LibraryLibrary Verbose: 0 : SELECT
    [UnionAll8].[Id] AS [C1],
    [UnionAll8].[Id1] AS [C2],
    [UnionAll8].[Id2] AS [C3],
    [UnionAll8].[Id3] AS [C4],
    [UnionAll8].[Id4] AS [C5],
    [UnionAll8].[Id5] AS [C6],
    [UnionAll8].[Id6] AS [C7],
    [UnionAll8].[Id7] AS [C8],
    [UnionAll8].[Id8] AS [C9],
    [UnionAll8].[Id9] AS [C10],
    [UnionAll8].[OrderId] AS [C11],
    [UnionAll8].[OrderId1] AS [C12],
    [UnionAll8].[CreationTime1] AS [C13],
    [UnionAll8].[Name] AS [C14],
    [UnionAll8].[OrderStatusId] AS [C15],
    [UnionAll8].[Name1] AS [C16],
    [UnionAll8].[OrderTypeId] AS [C17],
    [UnionAll8].[Name2] AS [C18],
    [UnionAll8].[ReceiptStatusId] AS [C19],
    [UnionAll8].[Name3] AS [C20],
    [UnionAll8].[EmailAddress] AS [C21],
    [UnionAll8].[VendorId] AS [C22],
    [UnionAll8].[VendorNotes] AS [C23],
    [UnionAll8].[SpecialNotes] AS [C24],
    [UnionAll8].[MiscellaneousNotes] AS [C25],
    [UnionAll8].[SelectorNotes] AS [C26],
    [UnionAll8].[VendorCustomerId] AS [C27],
    [UnionAll8].[Name4] AS [C28],
    [UnionAll8].[DeliveryRoomId] AS [C29],
    [UnionAll8].[C2] AS [C30],
    [UnionAll8].[VendorItemId] AS [C31],
    [UnionAll8].[Title] AS [C32],
    [UnionAll8].[BibId] AS [C33],
    [UnionAll8].[Author] AS [C34],
    [UnionAll8].[OclcNumber] AS [C35],
    [UnionAll8].[C1] AS [C36],
    [UnionAll8].[Code] AS [C37],
    [UnionAll8].[C3] AS [C38],
    [UnionAll8].[C4] AS [C39],
    [UnionAll8].[C5] AS [C40],
    [UnionAll8].[C6] AS [C41],
    [UnionAll8].[C7] AS [C42],
    [UnionAll8].[C8] AS [C43],
    [UnionAll8].[C9] AS [C44],
    [UnionAll8].[C10] AS [C45]
    FROM  (SELECT
        CASE WHEN ([Join10].[AccountId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
        [Limit1].[CreationTime] AS [CreationTime],
        [Limit1].[Id1] AS [Id],
        [Limit1].[Id2] AS [Id1],
        [Limit1].[Id3] AS [Id2],
        [Limit1].[Id4] AS [Id3],
        [Limit1].[Id5] AS [Id4],
        [Limit1].[Id6] AS [Id5],
        [Limit1].[Id7] AS [Id6],
        [Limit1].[Id8] AS [Id7],
        [Limit1].[Id] AS [Id8],
        [Limit1].[Id] AS [Id9],
        [Limit1].[OrderId] AS [OrderId],
        [Limit1].[OrderId] AS [OrderId1],
        [Limit1].[CreationTime] AS [CreationTime1],
        [Limit1].[Name] AS [Name],
        [Limit1].[OrderStatusId] AS [OrderStatusId],
        [Limit1].[Name1] AS [Name1],
        [Limit1].[OrderTypeId] AS [OrderTypeId],
        [Limit1].[Name2] AS [Name2],
        [Limit1].[ReceiptStatusId] AS [ReceiptStatusId],
        [Limit1].[Name3] AS [Name3],
        [Limit1].[EmailAddress] AS [EmailAddress],
        [Limit1].[VendorId] AS [VendorId],
        [Limit1].[VendorNotes] AS [VendorNotes],
        [Limit1].[SpecialNotes] AS [SpecialNotes],
        [Limit1].[MiscellaneousNotes] AS [MiscellaneousNotes],
        [Limit1].[SelectorNotes] AS [SelectorNotes],
        [Limit1].[VendorCustomerId] AS [VendorCustomerId],
        [Limit1].[Name4] AS [Name4],
        [Limit1].[DeliveryRoomId] AS [DeliveryRoomId],
        [Limit1].[C1] AS [C2],
        [Limit1].[VendorItemId] AS [VendorItemId],
        [Limit1].[Title] AS [Title],
        [Limit1].[BibId] AS [BibId],
        [Limit1].[Author] AS [Author],
        [Limit1].[OclcNumber] AS [OclcNumber],
        [Join10].[Code] AS [Code],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS int) AS [C4],
        CAST(NULL AS varchar(1)) AS [C5],
        CAST(NULL AS varchar(1)) AS [C6],
        CAST(NULL AS varchar(1)) AS [C7],
        CAST(NULL AS varchar(1)) AS [C8],
        CAST(NULL AS varchar(1)) AS [C9],
        CAST(NULL AS varchar(1)) AS [C10]
        FROM   (SELECT [Project3].[Id] AS [Id], [Project3].[OrderId] AS [OrderId], [Project3].[VendorItemId] AS [VendorItemId], [Project3].[VendorNotes] AS [VendorNotes], [Project3].[SpecialNotes] AS [SpecialNotes], [Project3].[MiscellaneousNotes] AS [MiscellaneousNotes], [Project3].[SelectorNotes] AS [SelectorNotes], [Project3].[BibId] AS [BibId], [Project3].[Id1] AS [Id1], [Project3].[VendorId] AS [VendorId], [Project3].[OrderTypeId] AS [OrderTypeId], [Project3].[OrderStatusId] AS [OrderStatusId], [Project3].[VendorCustomerId] AS [VendorCustomerId], [Project3].[DeliveryRoomId] AS [DeliveryRoomId], [Project3].[CreationTime] AS [CreationTime], [Project3].[Id2] AS [Id2], [Project3].[Name] AS [Name], [Project3].[Id3] AS [Id3], [Project3].[Name1] AS [Name1], [Project3].[Id4] AS [Id4], [Project3].[ReceiptStatusId] AS [ReceiptStatusId], [Project3].[Id5] AS [Id5], [Project3].[Name2] AS [Name2], [Project3].[Id6] AS [Id6], [Project3].[Name3] AS [Name3], [Project3].[EmailAddress] AS [EmailAddress], [Project3].[Id7] AS [Id7], [Project3].[Name4] AS [Name4], [Project3].[Id8] AS [Id8], [Project3].[OclcNumber] AS [OclcNumber], [Project3].[Title] AS [Title], [Project3].[Author] AS [Author], [Project3].[C1] AS [C1]
            FROM ( SELECT
                [Project1].[Id] AS [Id],
                [Project1].[OrderId] AS [OrderId],
                [Project1].[VendorItemId] AS [VendorItemId],
                [Project1].[VendorNotes] AS [VendorNotes],
                [Project1].[SpecialNotes] AS [SpecialNotes],
                [Project1].[MiscellaneousNotes] AS [MiscellaneousNotes],
                [Project1].[SelectorNotes] AS [SelectorNotes],
                [Project1].[BibId] AS [BibId],
                [Project1].[Id1] AS [Id1],
                [Project1].[VendorId] AS [VendorId],
                [Project1].[OrderTypeId] AS [OrderTypeId],
                [Project1].[OrderStatusId] AS [OrderStatusId],
                [Project1].[VendorCustomerId] AS [VendorCustomerId],
                [Project1].[DeliveryRoomId] AS [DeliveryRoomId],
                [Project1].[CreationTime] AS [CreationTime],
                [Project1].[Id2] AS [Id2],
                [Project1].[Name] AS [Name],
                [Project1].[Id3] AS [Id3],
                [Project1].[Name1] AS [Name1],
                [Project1].[Id4] AS [Id4],
                [Project1].[ReceiptStatusId] AS [ReceiptStatusId],
                [Project1].[Id5] AS [Id5],
                [Project1].[Name2] AS [Name2],
                [Project1].[Id6] AS [Id6],
                [Project1].[Name3] AS [Name3],
                [Project1].[EmailAddress] AS [EmailAddress],
                [Project1].[Id7] AS [Id7],
                [Project1].[Name4] AS [Name4],
                [Project1].[Id8] AS [Id8],
                [Project1].[OclcNumber] AS [OclcNumber],
                [Project1].[Title] AS [Title],
                [Project1].[Author] AS [Author],
                [Project1].[C1] AS [C1]
                FROM ( SELECT
                    [Extent1].[Id] AS [Id],
                    [Extent1].[OrderId] AS [OrderId],
                    [Extent1].[VendorItemId] AS [VendorItemId],
                    [Extent1].[VendorNotes] AS [VendorNotes],
                    [Extent1].[SpecialNotes] AS [SpecialNotes],
                    [Extent1].[MiscellaneousNotes] AS [MiscellaneousNotes],
                    [Extent1].[SelectorNotes] AS [SelectorNotes],
                    [Extent1].[BibId] AS [BibId],
                    [Extent2].[Id] AS [Id1],
                    [Extent2].[VendorId] AS [VendorId],
                    [Extent2].[OrderTypeId] AS [OrderTypeId],
                    [Extent2].[OrderStatusId] AS [OrderStatusId],
                    [Extent2].[VendorCustomerId] AS [VendorCustomerId],
                    [Extent2].[DeliveryRoomId] AS [DeliveryRoomId],
                    [Extent2].[CreationTime] AS [CreationTime],
                    [Extent3].[Id] AS [Id2],
                    [Extent3].[Name] AS [Name],
                    [Extent4].[Id] AS [Id3],
                    [Extent4].[Name] AS [Name1],
                    [Extent5].[Id] AS [Id4],
                    [Extent5].[ReceiptStatusId] AS [ReceiptStatusId],
                    [Extent6].[Id] AS [Id5],
                    [Extent6].[Name] AS [Name2],
                    [Extent7].[Id] AS [Id6],
                    [Extent7].[Name] AS [Name3],
                    [Extent7].[EmailAddress] AS [EmailAddress],
                    [Extent8].[Id] AS [Id7],
                    [Extent8].[Name] AS [Name4],
                    [Extent9].[Id] AS [Id8],
                    [Extent9].[OclcNumber] AS [OclcNumber],
                    [Extent9].[Title] AS [Title],
                    [Extent9].[Author] AS [Author],
                    [Extent1].[UnitPrice] *  CAST( [Extent1].[Quantity] AS decimal(19,0)) AS [C1]
                    FROM         [dbo].[OrderItems] AS [Extent1]
                    INNER JOIN [dbo].[Orders] AS [Extent2] ON [Extent1].[OrderId] = [Extent2].[Id]
                    INNER JOIN [dbo].[OrderStatuses] AS [Extent3] ON [Extent2].[OrderStatusId] = [Extent3].[Id]
                    INNER JOIN [dbo].[OrderTypes] AS [Extent4] ON [Extent2].[OrderTypeId] = [Extent4].[Id]
                    LEFT OUTER JOIN [dbo].[Holdings] AS [Extent5] ON [Extent1].[HoldingId] = [Extent5].[Id]
                    LEFT OUTER JOIN [dbo].[ReceiptStatuses] AS [Extent6] ON [Extent5].[ReceiptStatusId] = [Extent6].[Id]
                    INNER JOIN [dbo].[Vendors] AS [Extent7] ON [Extent2].[VendorId] = [Extent7].[Id]
                    LEFT OUTER JOIN [dbo].[Rooms] AS [Extent8] ON [Extent2].[DeliveryRoomId] = [Extent8].[Id]
                    LEFT OUTER JOIN [dbo].[Bibs] AS [Extent9] ON [Extent1].[BibId] = [Extent9].[Id]
                    WHERE [Extent2].[CreationTime] >= @p__linq__0
                )  AS [Project1]
                WHERE  EXISTS (SELECT
                    1 AS [C1]
                    FROM  [dbo].[OrderItemAccounts] AS [Extent10]
                    INNER JOIN [dbo].[Accounts] AS [Extent11] ON [Extent10].[AccountId] = [Extent11].[Id]
                    WHERE ([Project1].[Id] = [Extent10].[OrderItemId]) AND ([Extent11].[Code] LIKE N'XPUTT%')
                )
            )  AS [Project3]
            ORDER BY row_number() OVER (ORDER BY [Project3].[CreationTime] DESC)
            OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY  ) AS [Limit1]
        LEFT OUTER JOIN  (SELECT [Extent12].[OrderItemId] AS [OrderItemId], [Extent12].[AccountId] AS [AccountId], [Extent13].[Code] AS [Code]
            FROM  [dbo].[OrderItemAccounts] AS [Extent12]
            INNER JOIN [dbo].[Accounts] AS [Extent13] ON [Extent12].[AccountId] = [Extent13].[Id] ) AS [Join10] ON [Limit1].[Id] = [Join10].[OrderItemId]
    UNION ALL
        SELECT
        2 AS [C1],
        [Limit2].[CreationTime] AS [CreationTime],
        [Limit2].[Id1] AS [Id],
        [Limit2].[Id2] AS [Id1],
        [Limit2].[Id3] AS [Id2],
        [Limit2].[Id4] AS [Id3],
        [Limit2].[Id5] AS [Id4],
        [Limit2].[Id6] AS [Id5],
        [Limit2].[Id7] AS [Id6],
        [Limit2].[Id8] AS [Id7],
        [Limit2].[Id] AS [Id8],
        [Limit2].[Id] AS [Id9],
        [Limit2].[OrderId] AS [OrderId],
        [Limit2].[OrderId] AS [OrderId1],
        [Limit2].[CreationTime] AS [CreationTime1],
        [Limit2].[Name] AS [Name],
        [Limit2].[OrderStatusId] AS [OrderStatusId],
        [Limit2].[Name1] AS [Name1],
        [Limit2].[OrderTypeId] AS [OrderTypeId],
        [Limit2].[Name2] AS [Name2],
        [Limit2].[ReceiptStatusId] AS [ReceiptStatusId],
        [Limit2].[Name3] AS [Name3],
        [Limit2].[EmailAddress] AS [EmailAddress],
        [Limit2].[VendorId] AS [VendorId],
        [Limit2].[VendorNotes] AS [VendorNotes],
        [Limit2].[SpecialNotes] AS [SpecialNotes],
        [Limit2].[MiscellaneousNotes] AS [MiscellaneousNotes],
        [Limit2].[SelectorNotes] AS [SelectorNotes],
        [Limit2].[VendorCustomerId] AS [VendorCustomerId],
        [Limit2].[Name4] AS [Name4],
        [Limit2].[DeliveryRoomId] AS [DeliveryRoomId],
        [Limit2].[C1] AS [C2],
        [Limit2].[VendorItemId] AS [VendorItemId],
        [Limit2].[Title] AS [Title],
        [Limit2].[BibId] AS [BibId],
        [Limit2].[Author] AS [Author],
        [Limit2].[OclcNumber] AS [OclcNumber],
        CAST(NULL AS varchar(1)) AS [C3],
        [Join21].[Code] AS [Code],
        CAST(NULL AS int) AS [C4],
        CAST(NULL AS varchar(1)) AS [C5],
        CAST(NULL AS varchar(1)) AS [C6],
        CAST(NULL AS varchar(1)) AS [C7],
        CAST(NULL AS varchar(1)) AS [C8],
        CAST(NULL AS varchar(1)) AS [C9],
        CAST(NULL AS varchar(1)) AS [C10]
        FROM   (SELECT [Project7].[Id] AS [Id], [Project7].[OrderId] AS [OrderId], [Project7].[VendorItemId] AS [VendorItemId], [Project7].[VendorNotes] AS [VendorNotes], [Project7].[SpecialNotes] AS [SpecialNotes], [Project7].[MiscellaneousNotes] AS [MiscellaneousNotes], [Project7].[SelectorNotes] AS [SelectorNotes], [Project7].[BibId] AS [BibId], [Project7].[Id1] AS [Id1], [Project7].[VendorId] AS [VendorId], [Project7].[OrderTypeId] AS [OrderTypeId], [Project7].[OrderStatusId] AS [OrderStatusId], [Project7].[VendorCustomerId] AS [VendorCustomerId], [Project7].[DeliveryRoomId] AS [DeliveryRoomId], [Project7].[CreationTime] AS [CreationTime], [Project7].[Id2] AS [Id2], [Project7].[Name] AS [Name], [Project7].[Id3] AS [Id3], [Project7].[Name1] AS [Name1], [Project7].[Id4] AS [Id4], [Project7].[ReceiptStatusId] AS [ReceiptStatusId], [Project7].[Id5] AS [Id5], [Project7].[Name2] AS [Name2], [Project7].[Id6] AS [Id6], [Project7].[Name3] AS [Name3], [Project7].[EmailAddress] AS [EmailAddress], [Project7].[Id7] AS [Id7], [Project7].[Name4] AS [Name4], [Project7].[Id8] AS [Id8], [Project7].[OclcNumber] AS [OclcNumber], [Project7].[Title] AS [Title], [Project7].[Author] AS [Author], [Project7].[C1] AS [C1]
            FROM ( SELECT
                [Project5].[Id] AS [Id],
                [Project5].[OrderId] AS [OrderId],
                [Project5].[VendorItemId] AS [VendorItemId],
                [Project5].[VendorNotes] AS [VendorNotes],
                [Project5].[SpecialNotes] AS [SpecialNotes],
                [Project5].[MiscellaneousNotes] AS [MiscellaneousNotes],
                [Project5].[SelectorNotes] AS [SelectorNotes],
                [Project5].[BibId] AS [BibId],
                [Project5].[Id1] AS [Id1],
                [Project5].[VendorId] AS [VendorId],
                [Project5].[OrderTypeId] AS [OrderTypeId],
                [Project5].[OrderStatusId] AS [OrderStatusId],
                [Project5].[VendorCustomerId] AS [VendorCustomerId],
                [Project5].[DeliveryRoomId] AS [DeliveryRoomId],
                [Project5].[CreationTime] AS [CreationTime],
                [Project5].[Id2] AS [Id2],
                [Project5].[Name] AS [Name],
                [Project5].[Id3] AS [Id3],
                [Project5].[Name1] AS [Name1],
                [Project5].[Id4] AS [Id4],
                [Project5].[ReceiptStatusId] AS [ReceiptStatusId],
                [Project5].[Id5] AS [Id5],
                [Project5].[Name2] AS [Name2],
                [Project5].[Id6] AS [Id6],
                [Project5].[Name3] AS [Name3],
                [Project5].[EmailAddress] AS [EmailAddress],
                [Project5].[Id7] AS [Id7],
                [Project5].[Name4] AS [Name4],
                [Project5].[Id8] AS [Id8],
                [Project5].[OclcNumber] AS [OclcNumber],
                [Project5].[Title] AS [Title],
                [Project5].[Author] AS [Author],
                [Project5].[C1] AS [C1]
                FROM ( SELECT
                    [Extent14].[Id] AS [Id],
                    [Extent14].[OrderId] AS [OrderId],
                    [Extent14].[VendorItemId] AS [VendorItemId],
                    [Extent14].[VendorNotes] AS [VendorNotes],
                    [Extent14].[SpecialNotes] AS [SpecialNotes],
                    [Extent14].[MiscellaneousNotes] AS [MiscellaneousNotes],
                    [Extent14].[SelectorNotes] AS [SelectorNotes],
                    [Extent14].[BibId] AS [BibId],
                    [Extent15].[Id] AS [Id1],
                    [Extent15].[VendorId] AS [VendorId],
                    [Extent15].[OrderTypeId] AS [OrderTypeId],
                    [Extent15].[OrderStatusId] AS [OrderStatusId],
                    [Extent15].[VendorCustomerId] AS [VendorCustomerId],
                    [Extent15].[DeliveryRoomId] AS [DeliveryRoomId],
                    [Extent15].[CreationTime] AS [CreationTime],
                    [Extent16].[Id] AS [Id2],
                    [Extent16].[Name] AS [Name],
                    [Extent17].[Id] AS [Id3],
                    [Extent17].[Name] AS [Name1],
                    [Extent18].[Id] AS [Id4],
                    [Extent18].[ReceiptStatusId] AS [ReceiptStatusId],
                    [Extent19].[Id] AS [Id5],
                    [Extent19].[Name] AS [Name2],
                    [Extent20].[Id] AS [Id6],
                    [Extent20].[Name] AS [Name3],
                    [Extent20].[EmailAddress] AS [EmailAddress],
                    [Extent21].[Id] AS [Id7],
                    [Extent21].[Name] AS [Name4],
                    [Extent22].[Id] AS [Id8],
                    [Extent22].[OclcNumber] AS [OclcNumber],
                    [Extent22].[Title] AS [Title],
                    [Extent22].[Author] AS [Author],
                    [Extent14].[UnitPrice] *  CAST( [Extent14].[Quantity] AS decimal(19,0)) AS [C1]
                    FROM         [dbo].[OrderItems] AS [Extent14]
                    INNER JOIN [dbo].[Orders] AS [Extent15] ON [Extent14].[OrderId] = [Extent15].[Id]
                    INNER JOIN [dbo].[OrderStatuses] AS [Extent16] ON [Extent15].[OrderStatusId] = [Extent16].[Id]
                    INNER JOIN [dbo].[OrderTypes] AS [Extent17] ON [Extent15].[OrderTypeId] = [Extent17].[Id]
                    LEFT OUTER JOIN [dbo].[Holdings] AS [Extent18] ON [Extent14].[HoldingId] = [Extent18].[Id]
                    LEFT OUTER JOIN [dbo].[ReceiptStatuses] AS [Extent19] ON [Extent18].[ReceiptStatusId] = [Extent19].[Id]
                    INNER JOIN [dbo].[Vendors] AS [Extent20] ON [Extent15].[VendorId] = [Extent20].[Id]
                    LEFT OUTER JOIN [dbo].[Rooms] AS [Extent21] ON [Extent15].[DeliveryRoomId] = [Extent21].[Id]
                    LEFT OUTER JOIN [dbo].[Bibs] AS [Extent22] ON [Extent14].[BibId] = [Extent22].[Id]
                    WHERE [Extent15].[CreationTime] >= @p__linq__0
                )  AS [Project5]
                WHERE  EXISTS (SELECT
                    1 AS [C1]
                    FROM  [dbo].[OrderItemAccounts] AS [Extent23]
                    INNER JOIN [dbo].[Accounts] AS [Extent24] ON [Extent23].[AccountId] = [Extent24].[Id]
                    WHERE ([Project5].[Id] = [Extent23].[OrderItemId]) AND ([Extent24].[Code] LIKE N'XPUTT%')
                )
            )  AS [Project7]
            ORDER BY row_number() OVER (ORDER BY [Project7].[CreationTime] DESC)
            OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY  ) AS [Limit2]
        INNER JOIN  (SELECT [Extent25].[OrderItemId] AS [OrderItemId], [Extent26].[Code] AS [Code]
            FROM  [dbo].[OrderItemDonors] AS [Extent25]
            INNER JOIN [dbo].[Donors] AS [Extent26] ON [Extent25].[DonorId] = [Extent26].[Id] ) AS [Join21] ON [Limit2].[Id] = [Join21].[OrderItemId]
    UNION ALL
        SELECT
        3 AS [C1],
        [Limit3].[CreationTime] AS [CreationTime],
        [Limit3].[Id1] AS [Id],
        [Limit3].[Id2] AS [Id1],
        [Limit3].[Id3] AS [Id2],
        [Limit3].[Id4] AS [Id3],
        [Limit3].[Id5] AS [Id4],
        [Limit3].[Id6] AS [Id5],
        [Limit3].[Id7] AS [Id6],
        [Limit3].[Id8] AS [Id7],
        [Limit3].[Id] AS [Id8],
        [Limit3].[Id] AS [Id9],
        [Limit3].[OrderId] AS [OrderId],
        [Limit3].[OrderId] AS [OrderId1],
        [Limit3].[CreationTime] AS [CreationTime1],
        [Limit3].[Name] AS [Name],
        [Limit3].[OrderStatusId] AS [OrderStatusId],
        [Limit3].[Name1] AS [Name1],
        [Limit3].[OrderTypeId] AS [OrderTypeId],
        [Limit3].[Name2] AS [Name2],
        [Limit3].[ReceiptStatusId] AS [ReceiptStatusId],
        [Limit3].[Name3] AS [Name3],
        [Limit3].[EmailAddress] AS [EmailAddress],
        [Limit3].[VendorId] AS [VendorId],
        [Limit3].[VendorNotes] AS [VendorNotes],
        [Limit3].[SpecialNotes] AS [SpecialNotes],
        [Limit3].[MiscellaneousNotes] AS [MiscellaneousNotes],
        [Limit3].[SelectorNotes] AS [SelectorNotes],
        [Limit3].[VendorCustomerId] AS [VendorCustomerId],
        [Limit3].[Name4] AS [Name4],
        [Limit3].[DeliveryRoomId] AS [DeliveryRoomId],
        [Limit3].[C1] AS [C2],
        [Limit3].[VendorItemId] AS [VendorItemId],
        [Limit3].[Title] AS [Title],
        [Limit3].[BibId] AS [BibId],
        [Limit3].[Author] AS [Author],
        [Limit3].[OclcNumber] AS [OclcNumber],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS varchar(1)) AS [C4],
        [Extent38].[Content] AS [Content],
        CAST(NULL AS varchar(1)) AS [C5],
        CAST(NULL AS varchar(1)) AS [C6],
        CAST(NULL AS varchar(1)) AS [C7],
        CAST(NULL AS varchar(1)) AS [C8],
        CAST(NULL AS varchar(1)) AS [C9],
        CAST(NULL AS varchar(1)) AS [C10]
        FROM   (SELECT [Project11].[Id] AS [Id], [Project11].[OrderId] AS [OrderId], [Project11].[VendorItemId] AS [VendorItemId], [Project11].[VendorNotes] AS [VendorNotes], [Project11].[SpecialNotes] AS [SpecialNotes], [Project11].[MiscellaneousNotes] AS [MiscellaneousNotes], [Project11].[SelectorNotes] AS [SelectorNotes], [Project11].[BibId] AS [BibId], [Project11].[Id1] AS [Id1], [Project11].[VendorId] AS [VendorId], [Project11].[OrderTypeId] AS [OrderTypeId], [Project11].[OrderStatusId] AS [OrderStatusId], [Project11].[VendorCustomerId] AS [VendorCustomerId], [Project11].[DeliveryRoomId] AS [DeliveryRoomId], [Project11].[CreationTime] AS [CreationTime], [Project11].[Id2] AS [Id2], [Project11].[Name] AS [Name], [Project11].[Id3] AS [Id3], [Project11].[Name1] AS [Name1], [Project11].[Id4] AS [Id4], [Project11].[ReceiptStatusId] AS [ReceiptStatusId], [Project11].[Id5] AS [Id5], [Project11].[Name2] AS [Name2], [Project11].[Id6] AS [Id6], [Project11].[Name3] AS [Name3], [Project11].[EmailAddress] AS [EmailAddress], [Project11].[Id7] AS [Id7], [Project11].[Name4] AS [Name4], [Project11].[Id8] AS [Id8], [Project11].[OclcNumber] AS [OclcNumber], [Project11].[Title] AS [Title], [Project11].[Author] AS [Author], [Project11].[C1] AS [C1]
            FROM ( SELECT
                [Project9].[Id] AS [Id],
                [Project9].[OrderId] AS [OrderId],
                [Project9].[VendorItemId] AS [VendorItemId],
                [Project9].[VendorNotes] AS [VendorNotes],
                [Project9].[SpecialNotes] AS [SpecialNotes],
                [Project9].[MiscellaneousNotes] AS [MiscellaneousNotes],
                [Project9].[SelectorNotes] AS [SelectorNotes],
                [Project9].[BibId] AS [BibId],
                [Project9].[Id1] AS [Id1],
                [Project9].[VendorId] AS [VendorId],
                [Project9].[OrderTypeId] AS [OrderTypeId],
                [Project9].[OrderStatusId] AS [OrderStatusId],
                [Project9].[VendorCustomerId] AS [VendorCustomerId],
                [Project9].[DeliveryRoomId] AS [DeliveryRoomId],
                [Project9].[CreationTime] AS [CreationTime],
                [Project9].[Id2] AS [Id2],
                [Project9].[Name] AS [Name],
                [Project9].[Id3] AS [Id3],
                [Project9].[Name1] AS [Name1],
                [Project9].[Id4] AS [Id4],
                [Project9].[ReceiptStatusId] AS [ReceiptStatusId],
                [Project9].[Id5] AS [Id5],
                [Project9].[Name2] AS [Name2],
                [Project9].[Id6] AS [Id6],
                [Project9].[Name3] AS [Name3],
                [Project9].[EmailAddress] AS [EmailAddress],
                [Project9].[Id7] AS [Id7],
                [Project9].[Name4] AS [Name4],
                [Project9].[Id8] AS [Id8],
                [Project9].[OclcNumber] AS [OclcNumber],
                [Project9].[Title] AS [Title],
                [Project9].[Author] AS [Author],
                [Project9].[C1] AS [C1]
                FROM ( SELECT
                    [Extent27].[Id] AS [Id],
                    [Extent27].[OrderId] AS [OrderId],
                    [Extent27].[VendorItemId] AS [VendorItemId],
                    [Extent27].[VendorNotes] AS [VendorNotes],
                    [Extent27].[SpecialNotes] AS [SpecialNotes],
                    [Extent27].[MiscellaneousNotes] AS [MiscellaneousNotes],
                    [Extent27].[SelectorNotes] AS [SelectorNotes],
                    [Extent27].[BibId] AS [BibId],
                    [Extent28].[Id] AS [Id1],
                    [Extent28].[VendorId] AS [VendorId],
                    [Extent28].[OrderTypeId] AS [OrderTypeId],
                    [Extent28].[OrderStatusId] AS [OrderStatusId],
                    [Extent28].[VendorCustomerId] AS [VendorCustomerId],
                    [Extent28].[DeliveryRoomId] AS [DeliveryRoomId],
                    [Extent28].[CreationTime] AS [CreationTime],
                    [Extent29].[Id] AS [Id2],
                    [Extent29].[Name] AS [Name],
                    [Extent30].[Id] AS [Id3],
                    [Extent30].[Name] AS [Name1],
                    [Extent31].[Id] AS [Id4],
                    [Extent31].[ReceiptStatusId] AS [ReceiptStatusId],
                    [Extent32].[Id] AS [Id5],
                    [Extent32].[Name] AS [Name2],
                    [Extent33].[Id] AS [Id6],
                    [Extent33].[Name] AS [Name3],
                    [Extent33].[EmailAddress] AS [EmailAddress],
                    [Extent34].[Id] AS [Id7],
                    [Extent34].[Name] AS [Name4],
                    [Extent35].[Id] AS [Id8],
                    [Extent35].[OclcNumber] AS [OclcNumber],
                    [Extent35].[Title] AS [Title],
                    [Extent35].[Author] AS [Author],
                    [Extent27].[UnitPrice] *  CAST( [Extent27].[Quantity] AS decimal(19,0)) AS [C1]
                    FROM         [dbo].[OrderItems] AS [Extent27]
                    INNER JOIN [dbo].[Orders] AS [Extent28] ON [Extent27].[OrderId] = [Extent28].[Id]
                    INNER JOIN [dbo].[OrderStatuses] AS [Extent29] ON [Extent28].[OrderStatusId] = [Extent29].[Id]
                    INNER JOIN [dbo].[OrderTypes] AS [Extent30] ON [Extent28].[OrderTypeId] = [Extent30].[Id]
                    LEFT OUTER JOIN [dbo].[Holdings] AS [Extent31] ON [Extent27].[HoldingId] = [Extent31].[Id]
                    LEFT OUTER JOIN [dbo].[ReceiptStatuses] AS [Extent32] ON [Extent31].[ReceiptStatusId] = [Extent32].[Id]
                    INNER JOIN [dbo].[Vendors] AS [Extent33] ON [Extent28].[VendorId] = [Extent33].[Id]
                    LEFT OUTER JOIN [dbo].[Rooms] AS [Extent34] ON [Extent28].[DeliveryRoomId] = [Extent34].[Id]
                    LEFT OUTER JOIN [dbo].[Bibs] AS [Extent35] ON [Extent27].[BibId] = [Extent35].[Id]
                    WHERE [Extent28].[CreationTime] >= @p__linq__0
                )  AS [Project9]
                WHERE  EXISTS (SELECT
                    1 AS [C1]
                    FROM  [dbo].[OrderItemAccounts] AS [Extent36]
                    INNER JOIN [dbo].[Accounts] AS [Extent37] ON [Extent36].[AccountId] = [Extent37].[Id]
                    WHERE ([Project9].[Id] = [Extent36].[OrderItemId]) AND ([Extent37].[Code] LIKE N'XPUTT%')
                )
            )  AS [Project11]
            ORDER BY row_number() OVER (ORDER BY [Project11].[CreationTime] DESC)
            OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY  ) AS [Limit3]
        INNER JOIN [dbo].[OclcNumbers] AS [Extent38] ON [Limit3].[BibId] = [Extent38].[BibId]
    UNION ALL
        SELECT
        4 AS [C1],
        [Limit4].[CreationTime] AS [CreationTime],
        [Limit4].[Id1] AS [Id],
        [Limit4].[Id2] AS [Id1],
        [Limit4].[Id3] AS [Id2],
        [Limit4].[Id4] AS [Id3],
        [Limit4].[Id5] AS [Id4],
        [Limit4].[Id6] AS [Id5],
        [Limit4].[Id7] AS [Id6],
        [Limit4].[Id8] AS [Id7],
        [Limit4].[Id] AS [Id8],
        [Limit4].[Id] AS [Id9],
        [Limit4].[OrderId] AS [OrderId],
        [Limit4].[OrderId] AS [OrderId1],
        [Limit4].[CreationTime] AS [CreationTime1],
        [Limit4].[Name] AS [Name],
        [Limit4].[OrderStatusId] AS [OrderStatusId],
        [Limit4].[Name1] AS [Name1],
        [Limit4].[OrderTypeId] AS [OrderTypeId],
        [Limit4].[Name2] AS [Name2],
        [Limit4].[ReceiptStatusId] AS [ReceiptStatusId],
        [Limit4].[Name3] AS [Name3],
        [Limit4].[EmailAddress] AS [EmailAddress],
        [Limit4].[VendorId] AS [VendorId],
        [Limit4].[VendorNotes] AS [VendorNotes],
        [Limit4].[SpecialNotes] AS [SpecialNotes],
        [Limit4].[MiscellaneousNotes] AS [MiscellaneousNotes],
        [Limit4].[SelectorNotes] AS [SelectorNotes],
        [Limit4].[VendorCustomerId] AS [VendorCustomerId],
        [Limit4].[Name4] AS [Name4],
        [Limit4].[DeliveryRoomId] AS [DeliveryRoomId],
        [Limit4].[C1] AS [C2],
        [Limit4].[VendorItemId] AS [VendorItemId],
        [Limit4].[Title] AS [Title],
        [Limit4].[BibId] AS [BibId],
        [Limit4].[Author] AS [Author],
        [Limit4].[OclcNumber] AS [OclcNumber],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS varchar(1)) AS [C4],
        CAST(NULL AS int) AS [C5],
        [Extent50].[Content] AS [Content],
        CAST(NULL AS varchar(1)) AS [C6],
        CAST(NULL AS varchar(1)) AS [C7],
        CAST(NULL AS varchar(1)) AS [C8],
        CAST(NULL AS varchar(1)) AS [C9],
        CAST(NULL AS varchar(1)) AS [C10]
        FROM   (SELECT [Project15].[Id] AS [Id], [Project15].[OrderId] AS [OrderId], [Project15].[VendorItemId] AS [VendorItemId], [Project15].[VendorNotes] AS [VendorNotes], [Project15].[SpecialNotes] AS [SpecialNotes], [Project15].[MiscellaneousNotes] AS [MiscellaneousNotes], [Project15].[SelectorNotes] AS [SelectorNotes], [Project15].[BibId] AS [BibId], [Project15].[Id1] AS [Id1], [Project15].[VendorId] AS [VendorId], [Project15].[OrderTypeId] AS [OrderTypeId], [Project15].[OrderStatusId] AS [OrderStatusId], [Project15].[VendorCustomerId] AS [VendorCustomerId], [Project15].[DeliveryRoomId] AS [DeliveryRoomId], [Project15].[CreationTime] AS [CreationTime], [Project15].[Id2] AS [Id2], [Project15].[Name] AS [Name], [Project15].[Id3] AS [Id3], [Project15].[Name1] AS [Name1], [Project15].[Id4] AS [Id4], [Project15].[ReceiptStatusId] AS [ReceiptStatusId], [Project15].[Id5] AS [Id5], [Project15].[Name2] AS [Name2], [Project15].[Id6] AS [Id6], [Project15].[Name3] AS [Name3], [Project15].[EmailAddress] AS [EmailAddress], [Project15].[Id7] AS [Id7], [Project15].[Name4] AS [Name4], [Project15].[Id8] AS [Id8], [Project15].[OclcNumber] AS [OclcNumber], [Project15].[Title] AS [Title], [Project15].[Author] AS [Author], [Project15].[C1] AS [C1]
            FROM ( SELECT
                [Project13].[Id] AS [Id],
                [Project13].[OrderId] AS [OrderId],
                [Project13].[VendorItemId] AS [VendorItemId],
                [Project13].[VendorNotes] AS [VendorNotes],
                [Project13].[SpecialNotes] AS [SpecialNotes],
                [Project13].[MiscellaneousNotes] AS [MiscellaneousNotes],
                [Project13].[SelectorNotes] AS [SelectorNotes],
                [Project13].[BibId] AS [BibId],
                [Project13].[Id1] AS [Id1],
                [Project13].[VendorId] AS [VendorId],
                [Project13].[OrderTypeId] AS [OrderTypeId],
                [Project13].[OrderStatusId] AS [OrderStatusId],
                [Project13].[VendorCustomerId] AS [VendorCustomerId],
                [Project13].[DeliveryRoomId] AS [DeliveryRoomId],
                [Project13].[CreationTime] AS [CreationTime],
                [Project13].[Id2] AS [Id2],
                [Project13].[Name] AS [Name],
                [Project13].[Id3] AS [Id3],
                [Project13].[Name1] AS [Name1],
                [Project13].[Id4] AS [Id4],
                [Project13].[ReceiptStatusId] AS [ReceiptStatusId],
                [Project13].[Id5] AS [Id5],
                [Project13].[Name2] AS [Name2],
                [Project13].[Id6] AS [Id6],
                [Project13].[Name3] AS [Name3],
                [Project13].[EmailAddress] AS [EmailAddress],
                [Project13].[Id7] AS [Id7],
                [Project13].[Name4] AS [Name4],
                [Project13].[Id8] AS [Id8],
                [Project13].[OclcNumber] AS [OclcNumber],
                [Project13].[Title] AS [Title],
                [Project13].[Author] AS [Author],
                [Project13].[C1] AS [C1]
                FROM ( SELECT
                    [Extent39].[Id] AS [Id],
                    [Extent39].[OrderId] AS [OrderId],
                    [Extent39].[VendorItemId] AS [VendorItemId],
                    [Extent39].[VendorNotes] AS [VendorNotes],
                    [Extent39].[SpecialNotes] AS [SpecialNotes],
                    [Extent39].[MiscellaneousNotes] AS [MiscellaneousNotes],
                    [Extent39].[SelectorNotes] AS [SelectorNotes],
                    [Extent39].[BibId] AS [BibId],
                    [Extent40].[Id] AS [Id1],
                    [Extent40].[VendorId] AS [VendorId],
                    [Extent40].[OrderTypeId] AS [OrderTypeId],
                    [Extent40].[OrderStatusId] AS [OrderStatusId],
                    [Extent40].[VendorCustomerId] AS [VendorCustomerId],
                    [Extent40].[DeliveryRoomId] AS [DeliveryRoomId],
                    [Extent40].[CreationTime] AS [CreationTime],
                    [Extent41].[Id] AS [Id2],
                    [Extent41].[Name] AS [Name],
                    [Extent42].[Id] AS [Id3],
                    [Extent42].[Name] AS [Name1],
                    [Extent43].[Id] AS [Id4],
                    [Extent43].[ReceiptStatusId] AS [ReceiptStatusId],
                    [Extent44].[Id] AS [Id5],
                    [Extent44].[Name] AS [Name2],
                    [Extent45].[Id] AS [Id6],
                    [Extent45].[Name] AS [Name3],
                    [Extent45].[EmailAddress] AS [EmailAddress],
                    [Extent46].[Id] AS [Id7],
                    [Extent46].[Name] AS [Name4],
                    [Extent47].[Id] AS [Id8],
                    [Extent47].[OclcNumber] AS [OclcNumber],
                    [Extent47].[Title] AS [Title],
                    [Extent47].[Author] AS [Author],
                    [Extent39].[UnitPrice] *  CAST( [Extent39].[Quantity] AS decimal(19,0)) AS [C1]
                    FROM         [dbo].[OrderItems] AS [Extent39]
                    INNER JOIN [dbo].[Orders] AS [Extent40] ON [Extent39].[OrderId] = [Extent40].[Id]
                    INNER JOIN [dbo].[OrderStatuses] AS [Extent41] ON [Extent40].[OrderStatusId] = [Extent41].[Id]
                    INNER JOIN [dbo].[OrderTypes] AS [Extent42] ON [Extent40].[OrderTypeId] = [Extent42].[Id]
                    LEFT OUTER JOIN [dbo].[Holdings] AS [Extent43] ON [Extent39].[HoldingId] = [Extent43].[Id]
                    LEFT OUTER JOIN [dbo].[ReceiptStatuses] AS [Extent44] ON [Extent43].[ReceiptStatusId] = [Extent44].[Id]
                    INNER JOIN [dbo].[Vendors] AS [Extent45] ON [Extent40].[VendorId] = [Extent45].[Id]
                    LEFT OUTER JOIN [dbo].[Rooms] AS [Extent46] ON [Extent40].[DeliveryRoomId] = [Extent46].[Id]
                    LEFT OUTER JOIN [dbo].[Bibs] AS [Extent47] ON [Extent39].[BibId] = [Extent47].[Id]
                    WHERE [Extent40].[CreationTime] >= @p__linq__0
                )  AS [Project13]
                WHERE  EXISTS (SELECT
                    1 AS [C1]
                    FROM  [dbo].[OrderItemAccounts] AS [Extent48]
                    INNER JOIN [dbo].[Accounts] AS [Extent49] ON [Extent48].[AccountId] = [Extent49].[Id]
                    WHERE ([Project13].[Id] = [Extent48].[OrderItemId]) AND ([Extent49].[Code] LIKE N'XPUTT%')
                )
            )  AS [Project15]
            ORDER BY row_number() OVER (ORDER BY [Project15].[CreationTime] DESC)
            OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY  ) AS [Limit4]
        INNER JOIN [dbo].[Isbns] AS [Extent50] ON [Limit4].[BibId] = [Extent50].[BibId]
    UNION ALL
        SELECT
        5 AS [C1],
        [Limit5].[CreationTime] AS [CreationTime],
        [Limit5].[Id1] AS [Id],
        [Limit5].[Id2] AS [Id1],
        [Limit5].[Id3] AS [Id2],
        [Limit5].[Id4] AS [Id3],
        [Limit5].[Id5] AS [Id4],
        [Limit5].[Id6] AS [Id5],
        [Limit5].[Id7] AS [Id6],
        [Limit5].[Id8] AS [Id7],
        [Limit5].[Id] AS [Id8],
        [Limit5].[Id] AS [Id9],
        [Limit5].[OrderId] AS [OrderId],
        [Limit5].[OrderId] AS [OrderId1],
        [Limit5].[CreationTime] AS [CreationTime1],
        [Limit5].[Name] AS [Name],
        [Limit5].[OrderStatusId] AS [OrderStatusId],
        [Limit5].[Name1] AS [Name1],
        [Limit5].[OrderTypeId] AS [OrderTypeId],
        [Limit5].[Name2] AS [Name2],
        [Limit5].[ReceiptStatusId] AS [ReceiptStatusId],
        [Limit5].[Name3] AS [Name3],
        [Limit5].[EmailAddress] AS [EmailAddress],
        [Limit5].[VendorId] AS [VendorId],
        [Limit5].[VendorNotes] AS [VendorNotes],
        [Limit5].[SpecialNotes] AS [SpecialNotes],
        [Limit5].[MiscellaneousNotes] AS [MiscellaneousNotes],
        [Limit5].[SelectorNotes] AS [SelectorNotes],
        [Limit5].[VendorCustomerId] AS [VendorCustomerId],
        [Limit5].[Name4] AS [Name4],
        [Limit5].[DeliveryRoomId] AS [DeliveryRoomId],
        [Limit5].[C1] AS [C2],
        [Limit5].[VendorItemId] AS [VendorItemId],
        [Limit5].[Title] AS [Title],
        [Limit5].[BibId] AS [BibId],
        [Limit5].[Author] AS [Author],
        [Limit5].[OclcNumber] AS [OclcNumber],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS varchar(1)) AS [C4],
        CAST(NULL AS int) AS [C5],
        CAST(NULL AS varchar(1)) AS [C6],
        [Extent62].[Content] AS [Content],
        CAST(NULL AS varchar(1)) AS [C7],
        CAST(NULL AS varchar(1)) AS [C8],
        CAST(NULL AS varchar(1)) AS [C9],
        CAST(NULL AS varchar(1)) AS [C10]
        FROM   (SELECT [Project19].[Id] AS [Id], [Project19].[OrderId] AS [OrderId], [Project19].[VendorItemId] AS [VendorItemId], [Project19].[VendorNotes] AS [VendorNotes], [Project19].[SpecialNotes] AS [SpecialNotes], [Project19].[MiscellaneousNotes] AS [MiscellaneousNotes], [Project19].[SelectorNotes] AS [SelectorNotes], [Project19].[BibId] AS [BibId], [Project19].[Id1] AS [Id1], [Project19].[VendorId] AS [VendorId], [Project19].[OrderTypeId] AS [OrderTypeId], [Project19].[OrderStatusId] AS [OrderStatusId], [Project19].[VendorCustomerId] AS [VendorCustomerId], [Project19].[DeliveryRoomId] AS [DeliveryRoomId], [Project19].[CreationTime] AS [CreationTime], [Project19].[Id2] AS [Id2], [Project19].[Name] AS [Name], [Project19].[Id3] AS [Id3], [Project19].[Name1] AS [Name1], [Project19].[Id4] AS [Id4], [Project19].[ReceiptStatusId] AS [ReceiptStatusId], [Project19].[Id5] AS [Id5], [Project19].[Name2] AS [Name2], [Project19].[Id6] AS [Id6], [Project19].[Name3] AS [Name3], [Project19].[EmailAddress] AS [EmailAddress], [Project19].[Id7] AS [Id7], [Project19].[Name4] AS [Name4], [Project19].[Id8] AS [Id8], [Project19].[OclcNumber] AS [OclcNumber], [Project19].[Title] AS [Title], [Project19].[Author] AS [Author], [Project19].[C1] AS [C1]
            FROM ( SELECT
                [Project17].[Id] AS [Id],
                [Project17].[OrderId] AS [OrderId],
                [Project17].[VendorItemId] AS [VendorItemId],
                [Project17].[VendorNotes] AS [VendorNotes],
                [Project17].[SpecialNotes] AS [SpecialNotes],
                [Project17].[MiscellaneousNotes] AS [MiscellaneousNotes],
                [Project17].[SelectorNotes] AS [SelectorNotes],
                [Project17].[BibId] AS [BibId],
                [Project17].[Id1] AS [Id1],
                [Project17].[VendorId] AS [VendorId],
                [Project17].[OrderTypeId] AS [OrderTypeId],
                [Project17].[OrderStatusId] AS [OrderStatusId],
                [Project17].[VendorCustomerId] AS [VendorCustomerId],
                [Project17].[DeliveryRoomId] AS [DeliveryRoomId],
                [Project17].[CreationTime] AS [CreationTime],
                [Project17].[Id2] AS [Id2],
                [Project17].[Name] AS [Name],
                [Project17].[Id3] AS [Id3],
                [Project17].[Name1] AS [Name1],
                [Project17].[Id4] AS [Id4],
                [Project17].[ReceiptStatusId] AS [ReceiptStatusId],
                [Project17].[Id5] AS [Id5],
                [Project17].[Name2] AS [Name2],
                [Project17].[Id6] AS [Id6],
                [Project17].[Name3] AS [Name3],
                [Project17].[EmailAddress] AS [EmailAddress],
                [Project17].[Id7] AS [Id7],
                [Project17].[Name4] AS [Name4],
                [Project17].[Id8] AS [Id8],
                [Project17].[OclcNumber] AS [OclcNumber],
                [Project17].[Title] AS [Title],
                [Project17].[Author] AS [Author],
                [Project17].[C1] AS [C1]
                FROM ( SELECT
                    [Extent51].[Id] AS [Id],
                    [Extent51].[OrderId] AS [OrderId],
                    [Extent51].[VendorItemId] AS [VendorItemId],
                    [Extent51].[VendorNotes] AS [VendorNotes],
                    [Extent51].[SpecialNotes] AS [SpecialNotes],
                    [Extent51].[MiscellaneousNotes] AS [MiscellaneousNotes],
                    [Extent51].[SelectorNotes] AS [SelectorNotes],
                    [Extent51].[BibId] AS [BibId],
                    [Extent52].[Id] AS [Id1],
                    [Extent52].[VendorId] AS [VendorId],
                    [Extent52].[OrderTypeId] AS [OrderTypeId],
                    [Extent52].[OrderStatusId] AS [OrderStatusId],
                    [Extent52].[VendorCustomerId] AS [VendorCustomerId],
                    [Extent52].[DeliveryRoomId] AS [DeliveryRoomId],
                    [Extent52].[CreationTime] AS [CreationTime],
                    [Extent53].[Id] AS [Id2],
                    [Extent53].[Name] AS [Name],
                    [Extent54].[Id] AS [Id3],
                    [Extent54].[Name] AS [Name1],
                    [Extent55].[Id] AS [Id4],
                    [Extent55].[ReceiptStatusId] AS [ReceiptStatusId],
                    [Extent56].[Id] AS [Id5],
                    [Extent56].[Name] AS [Name2],
                    [Extent57].[Id] AS [Id6],
                    [Extent57].[Name] AS [Name3],
                    [Extent57].[EmailAddress] AS [EmailAddress],
                    [Extent58].[Id] AS [Id7],
                    [Extent58].[Name] AS [Name4],
                    [Extent59].[Id] AS [Id8],
                    [Extent59].[OclcNumber] AS [OclcNumber],
                    [Extent59].[Title] AS [Title],
                    [Extent59].[Author] AS [Author],
                    [Extent51].[UnitPrice] *  CAST( [Extent51].[Quantity] AS decimal(19,0)) AS [C1]
                    FROM         [dbo].[OrderItems] AS [Extent51]
                    INNER JOIN [dbo].[Orders] AS [Extent52] ON [Extent51].[OrderId] = [Extent52].[Id]
                    INNER JOIN [dbo].[OrderStatuses] AS [Extent53] ON [Extent52].[OrderStatusId] = [Extent53].[Id]
                    INNER JOIN [dbo].[OrderTypes] AS [Extent54] ON [Extent52].[OrderTypeId] = [Extent54].[Id]
                    LEFT OUTER JOIN [dbo].[Holdings] AS [Extent55] ON [Extent51].[HoldingId] = [Extent55].[Id]
                    LEFT OUTER JOIN [dbo].[ReceiptStatuses] AS [Extent56] ON [Extent55].[ReceiptStatusId] = [Extent56].[Id]
                    INNER JOIN [dbo].[Vendors] AS [Extent57] ON [Extent52].[VendorId] = [Extent57].[Id]
                    LEFT OUTER JOIN [dbo].[Rooms] AS [Extent58] ON [Extent52].[DeliveryRoomId] = [Extent58].[Id]
                    LEFT OUTER JOIN [dbo].[Bibs] AS [Extent59] ON [Extent51].[BibId] = [Extent59].[Id]
                    WHERE [Extent52].[CreationTime] >= @p__linq__0
                )  AS [Project17]
                WHERE  EXISTS (SELECT
                    1 AS [C1]
                    FROM  [dbo].[OrderItemAccounts] AS [Extent60]
                    INNER JOIN [dbo].[Accounts] AS [Extent61] ON [Extent60].[AccountId] = [Extent61].[Id]
                    WHERE ([Project17].[Id] = [Extent60].[OrderItemId]) AND ([Extent61].[Code] LIKE N'XPUTT%')
                )
            )  AS [Project19]
            ORDER BY row_number() OVER (ORDER BY [Project19].[CreationTime] DESC)
            OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY  ) AS [Limit5]
        INNER JOIN [dbo].[Issns] AS [Extent62] ON [Limit5].[BibId] = [Extent62].[BibId]
    UNION ALL
        SELECT
        6 AS [C1],
        [Limit6].[CreationTime] AS [CreationTime],
        [Limit6].[Id1] AS [Id],
        [Limit6].[Id2] AS [Id1],
        [Limit6].[Id3] AS [Id2],
        [Limit6].[Id4] AS [Id3],
        [Limit6].[Id5] AS [Id4],
        [Limit6].[Id6] AS [Id5],
        [Limit6].[Id7] AS [Id6],
        [Limit6].[Id8] AS [Id7],
        [Limit6].[Id] AS [Id8],
        [Limit6].[Id] AS [Id9],
        [Limit6].[OrderId] AS [OrderId],
        [Limit6].[OrderId] AS [OrderId1],
        [Limit6].[CreationTime] AS [CreationTime1],
        [Limit6].[Name] AS [Name],
        [Limit6].[OrderStatusId] AS [OrderStatusId],
        [Limit6].[Name1] AS [Name1],
        [Limit6].[OrderTypeId] AS [OrderTypeId],
        [Limit6].[Name2] AS [Name2],
        [Limit6].[ReceiptStatusId] AS [ReceiptStatusId],
        [Limit6].[Name3] AS [Name3],
        [Limit6].[EmailAddress] AS [EmailAddress],
        [Limit6].[VendorId] AS [VendorId],
        [Limit6].[VendorNotes] AS [VendorNotes],
        [Limit6].[SpecialNotes] AS [SpecialNotes],
        [Limit6].[MiscellaneousNotes] AS [MiscellaneousNotes],
        [Limit6].[SelectorNotes] AS [SelectorNotes],
        [Limit6].[VendorCustomerId] AS [VendorCustomerId],
        [Limit6].[Name4] AS [Name4],
        [Limit6].[DeliveryRoomId] AS [DeliveryRoomId],
        [Limit6].[C1] AS [C2],
        [Limit6].[VendorItemId] AS [VendorItemId],
        [Limit6].[Title] AS [Title],
        [Limit6].[BibId] AS [BibId],
        [Limit6].[Author] AS [Author],
        [Limit6].[OclcNumber] AS [OclcNumber],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS varchar(1)) AS [C4],
        CAST(NULL AS int) AS [C5],
        CAST(NULL AS varchar(1)) AS [C6],
        CAST(NULL AS varchar(1)) AS [C7],
        [Extent74].[Content] AS [Content],
        CAST(NULL AS varchar(1)) AS [C8],
        CAST(NULL AS varchar(1)) AS [C9],
        CAST(NULL AS varchar(1)) AS [C10]
        FROM   (SELECT [Project23].[Id] AS [Id], [Project23].[OrderId] AS [OrderId], [Project23].[VendorItemId] AS [VendorItemId], [Project23].[VendorNotes] AS [VendorNotes], [Project23].[SpecialNotes] AS [SpecialNotes], [Project23].[MiscellaneousNotes] AS [MiscellaneousNotes], [Project23].[SelectorNotes] AS [SelectorNotes], [Project23].[BibId] AS [BibId], [Project23].[Id1] AS [Id1], [Project23].[VendorId] AS [VendorId], [Project23].[OrderTypeId] AS [OrderTypeId], [Project23].[OrderStatusId] AS [OrderStatusId], [Project23].[VendorCustomerId] AS [VendorCustomerId], [Project23].[DeliveryRoomId] AS [DeliveryRoomId], [Project23].[CreationTime] AS [CreationTime], [Project23].[Id2] AS [Id2], [Project23].[Name] AS [Name], [Project23].[Id3] AS [Id3], [Project23].[Name1] AS [Name1], [Project23].[Id4] AS [Id4], [Project23].[ReceiptStatusId] AS [ReceiptStatusId], [Project23].[Id5] AS [Id5], [Project23].[Name2] AS [Name2], [Project23].[Id6] AS [Id6], [Project23].[Name3] AS [Name3], [Project23].[EmailAddress] AS [EmailAddress], [Project23].[Id7] AS [Id7], [Project23].[Name4] AS [Name4], [Project23].[Id8] AS [Id8], [Project23].[OclcNumber] AS [OclcNumber], [Project23].[Title] AS [Title], [Project23].[Author] AS [Author], [Project23].[C1] AS [C1]
            FROM ( SELECT
                [Project21].[Id] AS [Id],
                [Project21].[OrderId] AS [OrderId],
                [Project21].[VendorItemId] AS [VendorItemId],
                [Project21].[VendorNotes] AS [VendorNotes],
                [Project21].[SpecialNotes] AS [SpecialNotes],
                [Project21].[MiscellaneousNotes] AS [MiscellaneousNotes],
                [Project21].[SelectorNotes] AS [SelectorNotes],
                [Project21].[BibId] AS [BibId],
                [Project21].[Id1] AS [Id1],
                [Project21].[VendorId] AS [VendorId],
                [Project21].[OrderTypeId] AS [OrderTypeId],
                [Project21].[OrderStatusId] AS [OrderStatusId],
                [Project21].[VendorCustomerId] AS [VendorCustomerId],
                [Project21].[DeliveryRoomId] AS [DeliveryRoomId],
                [Project21].[CreationTime] AS [CreationTime],
                [Project21].[Id2] AS [Id2],
                [Project21].[Name] AS [Name],
                [Project21].[Id3] AS [Id3],
                [Project21].[Name1] AS [Name1],
                [Project21].[Id4] AS [Id4],
                [Project21].[ReceiptStatusId] AS [ReceiptStatusId],
                [Project21].[Id5] AS [Id5],
                [Project21].[Name2] AS [Name2],
                [Project21].[Id6] AS [Id6],
                [Project21].[Name3] AS [Name3],
                [Project21].[EmailAddress] AS [EmailAddress],
                [Project21].[Id7] AS [Id7],
                [Project21].[Name4] AS [Name4],
                [Project21].[Id8] AS [Id8],
                [Project21].[OclcNumber] AS [OclcNumber],
                [Project21].[Title] AS [Title],
                [Project21].[Author] AS [Author],
                [Project21].[C1] AS [C1]
                FROM ( SELECT
                    [Extent63].[Id] AS [Id],
                    [Extent63].[OrderId] AS [OrderId],
                    [Extent63].[VendorItemId] AS [VendorItemId],
                    [Extent63].[VendorNotes] AS [VendorNotes],
                    [Extent63].[SpecialNotes] AS [SpecialNotes],
                    [Extent63].[MiscellaneousNotes] AS [MiscellaneousNotes],
                    [Extent63].[SelectorNotes] AS [SelectorNotes],
                    [Extent63].[BibId] AS [BibId],
                    [Extent64].[Id] AS [Id1],
                    [Extent64].[VendorId] AS [VendorId],
                    [Extent64].[OrderTypeId] AS [OrderTypeId],
                    [Extent64].[OrderStatusId] AS [OrderStatusId],
                    [Extent64].[VendorCustomerId] AS [VendorCustomerId],
                    [Extent64].[DeliveryRoomId] AS [DeliveryRoomId],
                    [Extent64].[CreationTime] AS [CreationTime],
                    [Extent65].[Id] AS [Id2],
                    [Extent65].[Name] AS [Name],
                    [Extent66].[Id] AS [Id3],
                    [Extent66].[Name] AS [Name1],
                    [Extent67].[Id] AS [Id4],
                    [Extent67].[ReceiptStatusId] AS [ReceiptStatusId],
                    [Extent68].[Id] AS [Id5],
                    [Extent68].[Name] AS [Name2],
                    [Extent69].[Id] AS [Id6],
                    [Extent69].[Name] AS [Name3],
                    [Extent69].[EmailAddress] AS [EmailAddress],
                    [Extent70].[Id] AS [Id7],
                    [Extent70].[Name] AS [Name4],
                    [Extent71].[Id] AS [Id8],
                    [Extent71].[OclcNumber] AS [OclcNumber],
                    [Extent71].[Title] AS [Title],
                    [Extent71].[Author] AS [Author],
                    [Extent63].[UnitPrice] *  CAST( [Extent63].[Quantity] AS decimal(19,0)) AS [C1]
                    FROM         [dbo].[OrderItems] AS [Extent63]
                    INNER JOIN [dbo].[Orders] AS [Extent64] ON [Extent63].[OrderId] = [Extent64].[Id]
                    INNER JOIN [dbo].[OrderStatuses] AS [Extent65] ON [Extent64].[OrderStatusId] = [Extent65].[Id]
                    INNER JOIN [dbo].[OrderTypes] AS [Extent66] ON [Extent64].[OrderTypeId] = [Extent66].[Id]
                    LEFT OUTER JOIN [dbo].[Holdings] AS [Extent67] ON [Extent63].[HoldingId] = [Extent67].[Id]
                    LEFT OUTER JOIN [dbo].[ReceiptStatuses] AS [Extent68] ON [Extent67].[ReceiptStatusId] = [Extent68].[Id]
                    INNER JOIN [dbo].[Vendors] AS [Extent69] ON [Extent64].[VendorId] = [Extent69].[Id]
                    LEFT OUTER JOIN [dbo].[Rooms] AS [Extent70] ON [Extent64].[DeliveryRoomId] = [Extent70].[Id]
                    LEFT OUTER JOIN [dbo].[Bibs] AS [Extent71] ON [Extent63].[BibId] = [Extent71].[Id]
                    WHERE [Extent64].[CreationTime] >= @p__linq__0
                )  AS [Project21]
                WHERE  EXISTS (SELECT
                    1 AS [C1]
                    FROM  [dbo].[OrderItemAccounts] AS [Extent72]
                    INNER JOIN [dbo].[Accounts] AS [Extent73] ON [Extent72].[AccountId] = [Extent73].[Id]
                    WHERE ([Project21].[Id] = [Extent72].[OrderItemId]) AND ([Extent73].[Code] LIKE N'XPUTT%')
                )
            )  AS [Project23]
            ORDER BY row_number() OVER (ORDER BY [Project23].[CreationTime] DESC)
            OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY  ) AS [Limit6]
        INNER JOIN [dbo].[Numbers] AS [Extent74] ON ([Limit6].[BibId] = [Extent74].[BibId]) AND (N'024' = [Extent74].[Type])
    UNION ALL
        SELECT
        7 AS [C1],
        [Limit7].[CreationTime] AS [CreationTime],
        [Limit7].[Id1] AS [Id],
        [Limit7].[Id2] AS [Id1],
        [Limit7].[Id3] AS [Id2],
        [Limit7].[Id4] AS [Id3],
        [Limit7].[Id5] AS [Id4],
        [Limit7].[Id6] AS [Id5],
        [Limit7].[Id7] AS [Id6],
        [Limit7].[Id8] AS [Id7],
        [Limit7].[Id] AS [Id8],
        [Limit7].[Id] AS [Id9],
        [Limit7].[OrderId] AS [OrderId],
        [Limit7].[OrderId] AS [OrderId1],
        [Limit7].[CreationTime] AS [CreationTime1],
        [Limit7].[Name] AS [Name],
        [Limit7].[OrderStatusId] AS [OrderStatusId],
        [Limit7].[Name1] AS [Name1],
        [Limit7].[OrderTypeId] AS [OrderTypeId],
        [Limit7].[Name2] AS [Name2],
        [Limit7].[ReceiptStatusId] AS [ReceiptStatusId],
        [Limit7].[Name3] AS [Name3],
        [Limit7].[EmailAddress] AS [EmailAddress],
        [Limit7].[VendorId] AS [VendorId],
        [Limit7].[VendorNotes] AS [VendorNotes],
        [Limit7].[SpecialNotes] AS [SpecialNotes],
        [Limit7].[MiscellaneousNotes] AS [MiscellaneousNotes],
        [Limit7].[SelectorNotes] AS [SelectorNotes],
        [Limit7].[VendorCustomerId] AS [VendorCustomerId],
        [Limit7].[Name4] AS [Name4],
        [Limit7].[DeliveryRoomId] AS [DeliveryRoomId],
        [Limit7].[C1] AS [C2],
        [Limit7].[VendorItemId] AS [VendorItemId],
        [Limit7].[Title] AS [Title],
        [Limit7].[BibId] AS [BibId],
        [Limit7].[Author] AS [Author],
        [Limit7].[OclcNumber] AS [OclcNumber],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS varchar(1)) AS [C4],
        CAST(NULL AS int) AS [C5],
        CAST(NULL AS varchar(1)) AS [C6],
        CAST(NULL AS varchar(1)) AS [C7],
        CAST(NULL AS varchar(1)) AS [C8],
        [Extent86].[Content] AS [Content],
        CAST(NULL AS varchar(1)) AS [C9],
        CAST(NULL AS varchar(1)) AS [C10]
        FROM   (SELECT [Project27].[Id] AS [Id], [Project27].[OrderId] AS [OrderId], [Project27].[VendorItemId] AS [VendorItemId], [Project27].[VendorNotes] AS [VendorNotes], [Project27].[SpecialNotes] AS [SpecialNotes], [Project27].[MiscellaneousNotes] AS [MiscellaneousNotes], [Project27].[SelectorNotes] AS [SelectorNotes], [Project27].[BibId] AS [BibId], [Project27].[Id1] AS [Id1], [Project27].[VendorId] AS [VendorId], [Project27].[OrderTypeId] AS [OrderTypeId], [Project27].[OrderStatusId] AS [OrderStatusId], [Project27].[VendorCustomerId] AS [VendorCustomerId], [Project27].[DeliveryRoomId] AS [DeliveryRoomId], [Project27].[CreationTime] AS [CreationTime], [Project27].[Id2] AS [Id2], [Project27].[Name] AS [Name], [Project27].[Id3] AS [Id3], [Project27].[Name1] AS [Name1], [Project27].[Id4] AS [Id4], [Project27].[ReceiptStatusId] AS [ReceiptStatusId], [Project27].[Id5] AS [Id5], [Project27].[Name2] AS [Name2], [Project27].[Id6] AS [Id6], [Project27].[Name3] AS [Name3], [Project27].[EmailAddress] AS [EmailAddress], [Project27].[Id7] AS [Id7], [Project27].[Name4] AS [Name4], [Project27].[Id8] AS [Id8], [Project27].[OclcNumber] AS [OclcNumber], [Project27].[Title] AS [Title], [Project27].[Author] AS [Author], [Project27].[C1] AS [C1]
            FROM ( SELECT
                [Project25].[Id] AS [Id],
                [Project25].[OrderId] AS [OrderId],
                [Project25].[VendorItemId] AS [VendorItemId],
                [Project25].[VendorNotes] AS [VendorNotes],
                [Project25].[SpecialNotes] AS [SpecialNotes],
                [Project25].[MiscellaneousNotes] AS [MiscellaneousNotes],
                [Project25].[SelectorNotes] AS [SelectorNotes],
                [Project25].[BibId] AS [BibId],
                [Project25].[Id1] AS [Id1],
                [Project25].[VendorId] AS [VendorId],
                [Project25].[OrderTypeId] AS [OrderTypeId],
                [Project25].[OrderStatusId] AS [OrderStatusId],
                [Project25].[VendorCustomerId] AS [VendorCustomerId],
                [Project25].[DeliveryRoomId] AS [DeliveryRoomId],
                [Project25].[CreationTime] AS [CreationTime],
                [Project25].[Id2] AS [Id2],
                [Project25].[Name] AS [Name],
                [Project25].[Id3] AS [Id3],
                [Project25].[Name1] AS [Name1],
                [Project25].[Id4] AS [Id4],
                [Project25].[ReceiptStatusId] AS [ReceiptStatusId],
                [Project25].[Id5] AS [Id5],
                [Project25].[Name2] AS [Name2],
                [Project25].[Id6] AS [Id6],
                [Project25].[Name3] AS [Name3],
                [Project25].[EmailAddress] AS [EmailAddress],
                [Project25].[Id7] AS [Id7],
                [Project25].[Name4] AS [Name4],
                [Project25].[Id8] AS [Id8],
                [Project25].[OclcNumber] AS [OclcNumber],
                [Project25].[Title] AS [Title],
                [Project25].[Author] AS [Author],
                [Project25].[C1] AS [C1]
                FROM ( SELECT
                    [Extent75].[Id] AS [Id],
                    [Extent75].[OrderId] AS [OrderId],
                    [Extent75].[VendorItemId] AS [VendorItemId],
                    [Extent75].[VendorNotes] AS [VendorNotes],
                    [Extent75].[SpecialNotes] AS [SpecialNotes],
                    [Extent75].[MiscellaneousNotes] AS [MiscellaneousNotes],
                    [Extent75].[SelectorNotes] AS [SelectorNotes],
                    [Extent75].[BibId] AS [BibId],
                    [Extent76].[Id] AS [Id1],
                    [Extent76].[VendorId] AS [VendorId],
                    [Extent76].[OrderTypeId] AS [OrderTypeId],
                    [Extent76].[OrderStatusId] AS [OrderStatusId],
                    [Extent76].[VendorCustomerId] AS [VendorCustomerId],
                    [Extent76].[DeliveryRoomId] AS [DeliveryRoomId],
                    [Extent76].[CreationTime] AS [CreationTime],
                    [Extent77].[Id] AS [Id2],
                    [Extent77].[Name] AS [Name],
                    [Extent78].[Id] AS [Id3],
                    [Extent78].[Name] AS [Name1],
                    [Extent79].[Id] AS [Id4],
                    [Extent79].[ReceiptStatusId] AS [ReceiptStatusId],
                    [Extent80].[Id] AS [Id5],
                    [Extent80].[Name] AS [Name2],
                    [Extent81].[Id] AS [Id6],
                    [Extent81].[Name] AS [Name3],
                    [Extent81].[EmailAddress] AS [EmailAddress],
                    [Extent82].[Id] AS [Id7],
                    [Extent82].[Name] AS [Name4],
                    [Extent83].[Id] AS [Id8],
                    [Extent83].[OclcNumber] AS [OclcNumber],
                    [Extent83].[Title] AS [Title],
                    [Extent83].[Author] AS [Author],
                    [Extent75].[UnitPrice] *  CAST( [Extent75].[Quantity] AS decimal(19,0)) AS [C1]
                    FROM         [dbo].[OrderItems] AS [Extent75]
                    INNER JOIN [dbo].[Orders] AS [Extent76] ON [Extent75].[OrderId] = [Extent76].[Id]
                    INNER JOIN [dbo].[OrderStatuses] AS [Extent77] ON [Extent76].[OrderStatusId] = [Extent77].[Id]
                    INNER JOIN [dbo].[OrderTypes] AS [Extent78] ON [Extent76].[OrderTypeId] = [Extent78].[Id]
                    LEFT OUTER JOIN [dbo].[Holdings] AS [Extent79] ON [Extent75].[HoldingId] = [Extent79].[Id]
                    LEFT OUTER JOIN [dbo].[ReceiptStatuses] AS [Extent80] ON [Extent79].[ReceiptStatusId] = [Extent80].[Id]
                    INNER JOIN [dbo].[Vendors] AS [Extent81] ON [Extent76].[VendorId] = [Extent81].[Id]
                    LEFT OUTER JOIN [dbo].[Rooms] AS [Extent82] ON [Extent76].[DeliveryRoomId] = [Extent82].[Id]
                    LEFT OUTER JOIN [dbo].[Bibs] AS [Extent83] ON [Extent75].[BibId] = [Extent83].[Id]
                    WHERE [Extent76].[CreationTime] >= @p__linq__0
                )  AS [Project25]
                WHERE  EXISTS (SELECT
                    1 AS [C1]
                    FROM  [dbo].[OrderItemAccounts] AS [Extent84]
                    INNER JOIN [dbo].[Accounts] AS [Extent85] ON [Extent84].[AccountId] = [Extent85].[Id]
                    WHERE ([Project25].[Id] = [Extent84].[OrderItemId]) AND ([Extent85].[Code] LIKE N'XPUTT%')
                )
            )  AS [Project27]
            ORDER BY row_number() OVER (ORDER BY [Project27].[CreationTime] DESC)
            OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY  ) AS [Limit7]
        INNER JOIN [dbo].[Numbers] AS [Extent86] ON ([Limit7].[BibId] = [Extent86].[BibId]) AND (N'028' = [Extent86].[Type])
    UNION ALL
        SELECT
        8 AS [C1],
        [Limit8].[CreationTime] AS [CreationTime],
        [Limit8].[Id1] AS [Id],
        [Limit8].[Id2] AS [Id1],
        [Limit8].[Id3] AS [Id2],
        [Limit8].[Id4] AS [Id3],
        [Limit8].[Id5] AS [Id4],
        [Limit8].[Id6] AS [Id5],
        [Limit8].[Id7] AS [Id6],
        [Limit8].[Id8] AS [Id7],
        [Limit8].[Id] AS [Id8],
        [Limit8].[Id] AS [Id9],
        [Limit8].[OrderId] AS [OrderId],
        [Limit8].[OrderId] AS [OrderId1],
        [Limit8].[CreationTime] AS [CreationTime1],
        [Limit8].[Name] AS [Name],
        [Limit8].[OrderStatusId] AS [OrderStatusId],
        [Limit8].[Name1] AS [Name1],
        [Limit8].[OrderTypeId] AS [OrderTypeId],
        [Limit8].[Name2] AS [Name2],
        [Limit8].[ReceiptStatusId] AS [ReceiptStatusId],
        [Limit8].[Name3] AS [Name3],
        [Limit8].[EmailAddress] AS [EmailAddress],
        [Limit8].[VendorId] AS [VendorId],
        [Limit8].[VendorNotes] AS [VendorNotes],
        [Limit8].[SpecialNotes] AS [SpecialNotes],
        [Limit8].[MiscellaneousNotes] AS [MiscellaneousNotes],
        [Limit8].[SelectorNotes] AS [SelectorNotes],
        [Limit8].[VendorCustomerId] AS [VendorCustomerId],
        [Limit8].[Name4] AS [Name4],
        [Limit8].[DeliveryRoomId] AS [DeliveryRoomId],
        [Limit8].[C1] AS [C2],
        [Limit8].[VendorItemId] AS [VendorItemId],
        [Limit8].[Title] AS [Title],
        [Limit8].[BibId] AS [BibId],
        [Limit8].[Author] AS [Author],
        [Limit8].[OclcNumber] AS [OclcNumber],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS varchar(1)) AS [C4],
        CAST(NULL AS int) AS [C5],
        CAST(NULL AS varchar(1)) AS [C6],
        CAST(NULL AS varchar(1)) AS [C7],
        CAST(NULL AS varchar(1)) AS [C8],
        CAST(NULL AS varchar(1)) AS [C9],
        [Extent98].[Content] AS [Content],
        CAST(NULL AS varchar(1)) AS [C10]
        FROM   (SELECT [Project31].[Id] AS [Id], [Project31].[OrderId] AS [OrderId], [Project31].[VendorItemId] AS [VendorItemId], [Project31].[VendorNotes] AS [VendorNotes], [Project31].[SpecialNotes] AS [SpecialNotes], [Project31].[MiscellaneousNotes] AS [MiscellaneousNotes], [Project31].[SelectorNotes] AS [SelectorNotes], [Project31].[BibId] AS [BibId], [Project31].[Id1] AS [Id1], [Project31].[VendorId] AS [VendorId], [Project31].[OrderTypeId] AS [OrderTypeId], [Project31].[OrderStatusId] AS [OrderStatusId], [Project31].[VendorCustomerId] AS [VendorCustomerId], [Project31].[DeliveryRoomId] AS [DeliveryRoomId], [Project31].[CreationTime] AS [CreationTime], [Project31].[Id2] AS [Id2], [Project31].[Name] AS [Name], [Project31].[Id3] AS [Id3], [Project31].[Name1] AS [Name1], [Project31].[Id4] AS [Id4], [Project31].[ReceiptStatusId] AS [ReceiptStatusId], [Project31].[Id5] AS [Id5], [Project31].[Name2] AS [Name2], [Project31].[Id6] AS [Id6], [Project31].[Name3] AS [Name3], [Project31].[EmailAddress] AS [EmailAddress], [Project31].[Id7] AS [Id7], [Project31].[Name4] AS [Name4], [Project31].[Id8] AS [Id8], [Project31].[OclcNumber] AS [OclcNumber], [Project31].[Title] AS [Title], [Project31].[Author] AS [Author], [Project31].[C1] AS [C1]
            FROM ( SELECT
                [Project29].[Id] AS [Id],
                [Project29].[OrderId] AS [OrderId],
                [Project29].[VendorItemId] AS [VendorItemId],
                [Project29].[VendorNotes] AS [VendorNotes],
                [Project29].[SpecialNotes] AS [SpecialNotes],
                [Project29].[MiscellaneousNotes] AS [MiscellaneousNotes],
                [Project29].[SelectorNotes] AS [SelectorNotes],
                [Project29].[BibId] AS [BibId],
                [Project29].[Id1] AS [Id1],
                [Project29].[VendorId] AS [VendorId],
                [Project29].[OrderTypeId] AS [OrderTypeId],
                [Project29].[OrderStatusId] AS [OrderStatusId],
                [Project29].[VendorCustomerId] AS [VendorCustomerId],
                [Project29].[DeliveryRoomId] AS [DeliveryRoomId],
                [Project29].[CreationTime] AS [CreationTime],
                [Project29].[Id2] AS [Id2],
                [Project29].[Name] AS [Name],
                [Project29].[Id3] AS [Id3],
                [Project29].[Name1] AS [Name1],
                [Project29].[Id4] AS [Id4],
                [Project29].[ReceiptStatusId] AS [ReceiptStatusId],
                [Project29].[Id5] AS [Id5],
                [Project29].[Name2] AS [Name2],
                [Project29].[Id6] AS [Id6],
                [Project29].[Name3] AS [Name3],
                [Project29].[EmailAddress] AS [EmailAddress],
                [Project29].[Id7] AS [Id7],
                [Project29].[Name4] AS [Name4],
                [Project29].[Id8] AS [Id8],
                [Project29].[OclcNumber] AS [OclcNumber],
                [Project29].[Title] AS [Title],
                [Project29].[Author] AS [Author],
                [Project29].[C1] AS [C1]
                FROM ( SELECT
                    [Extent87].[Id] AS [Id],
                    [Extent87].[OrderId] AS [OrderId],
                    [Extent87].[VendorItemId] AS [VendorItemId],
                    [Extent87].[VendorNotes] AS [VendorNotes],
                    [Extent87].[SpecialNotes] AS [SpecialNotes],
                    [Extent87].[MiscellaneousNotes] AS [MiscellaneousNotes],
                    [Extent87].[SelectorNotes] AS [SelectorNotes],
                    [Extent87].[BibId] AS [BibId],
                    [Extent88].[Id] AS [Id1],
                    [Extent88].[VendorId] AS [VendorId],
                    [Extent88].[OrderTypeId] AS [OrderTypeId],
                    [Extent88].[OrderStatusId] AS [OrderStatusId],
                    [Extent88].[VendorCustomerId] AS [VendorCustomerId],
                    [Extent88].[DeliveryRoomId] AS [DeliveryRoomId],
                    [Extent88].[CreationTime] AS [CreationTime],
                    [Extent89].[Id] AS [Id2],
                    [Extent89].[Name] AS [Name],
                    [Extent90].[Id] AS [Id3],
                    [Extent90].[Name] AS [Name1],
                    [Extent91].[Id] AS [Id4],
                    [Extent91].[ReceiptStatusId] AS [ReceiptStatusId],
                    [Extent92].[Id] AS [Id5],
                    [Extent92].[Name] AS [Name2],
                    [Extent93].[Id] AS [Id6],
                    [Extent93].[Name] AS [Name3],
                    [Extent93].[EmailAddress] AS [EmailAddress],
                    [Extent94].[Id] AS [Id7],
                    [Extent94].[Name] AS [Name4],
                    [Extent95].[Id] AS [Id8],
                    [Extent95].[OclcNumber] AS [OclcNumber],
                    [Extent95].[Title] AS [Title],
                    [Extent95].[Author] AS [Author],
                    [Extent87].[UnitPrice] *  CAST( [Extent87].[Quantity] AS decimal(19,0)) AS [C1]
                    FROM         [dbo].[OrderItems] AS [Extent87]
                    INNER JOIN [dbo].[Orders] AS [Extent88] ON [Extent87].[OrderId] = [Extent88].[Id]
                    INNER JOIN [dbo].[OrderStatuses] AS [Extent89] ON [Extent88].[OrderStatusId] = [Extent89].[Id]
                    INNER JOIN [dbo].[OrderTypes] AS [Extent90] ON [Extent88].[OrderTypeId] = [Extent90].[Id]
                    LEFT OUTER JOIN [dbo].[Holdings] AS [Extent91] ON [Extent87].[HoldingId] = [Extent91].[Id]
                    LEFT OUTER JOIN [dbo].[ReceiptStatuses] AS [Extent92] ON [Extent91].[ReceiptStatusId] = [Extent92].[Id]
                    INNER JOIN [dbo].[Vendors] AS [Extent93] ON [Extent88].[VendorId] = [Extent93].[Id]
                    LEFT OUTER JOIN [dbo].[Rooms] AS [Extent94] ON [Extent88].[DeliveryRoomId] = [Extent94].[Id]
                    LEFT OUTER JOIN [dbo].[Bibs] AS [Extent95] ON [Extent87].[BibId] = [Extent95].[Id]
                    WHERE [Extent88].[CreationTime] >= @p__linq__0
                )  AS [Project29]
                WHERE  EXISTS (SELECT
                    1 AS [C1]
                    FROM  [dbo].[OrderItemAccounts] AS [Extent96]
                    INNER JOIN [dbo].[Accounts] AS [Extent97] ON [Extent96].[AccountId] = [Extent97].[Id]
                    WHERE ([Project29].[Id] = [Extent96].[OrderItemId]) AND ([Extent97].[Code] LIKE N'XPUTT%')
                )
            )  AS [Project31]
            ORDER BY row_number() OVER (ORDER BY [Project31].[CreationTime] DESC)
            OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY  ) AS [Limit8]
        INNER JOIN [dbo].[Editions] AS [Extent98] ON [Limit8].[BibId] = [Extent98].[BibId]
    UNION ALL
        SELECT
        9 AS [C1],
        [Limit9].[CreationTime] AS [CreationTime],
        [Limit9].[Id1] AS [Id],
        [Limit9].[Id2] AS [Id1],
        [Limit9].[Id3] AS [Id2],
        [Limit9].[Id4] AS [Id3],
        [Limit9].[Id5] AS [Id4],
        [Limit9].[Id6] AS [Id5],
        [Limit9].[Id7] AS [Id6],
        [Limit9].[Id8] AS [Id7],
        [Limit9].[Id] AS [Id8],
        [Limit9].[Id] AS [Id9],
        [Limit9].[OrderId] AS [OrderId],
        [Limit9].[OrderId] AS [OrderId1],
        [Limit9].[CreationTime] AS [CreationTime1],
        [Limit9].[Name] AS [Name],
        [Limit9].[OrderStatusId] AS [OrderStatusId],
        [Limit9].[Name1] AS [Name1],
        [Limit9].[OrderTypeId] AS [OrderTypeId],
        [Limit9].[Name2] AS [Name2],
        [Limit9].[ReceiptStatusId] AS [ReceiptStatusId],
        [Limit9].[Name3] AS [Name3],
        [Limit9].[EmailAddress] AS [EmailAddress],
        [Limit9].[VendorId] AS [VendorId],
        [Limit9].[VendorNotes] AS [VendorNotes],
        [Limit9].[SpecialNotes] AS [SpecialNotes],
        [Limit9].[MiscellaneousNotes] AS [MiscellaneousNotes],
        [Limit9].[SelectorNotes] AS [SelectorNotes],
        [Limit9].[VendorCustomerId] AS [VendorCustomerId],
        [Limit9].[Name4] AS [Name4],
        [Limit9].[DeliveryRoomId] AS [DeliveryRoomId],
        [Limit9].[C1] AS [C2],
        [Limit9].[VendorItemId] AS [VendorItemId],
        [Limit9].[Title] AS [Title],
        [Limit9].[BibId] AS [BibId],
        [Limit9].[Author] AS [Author],
        [Limit9].[OclcNumber] AS [OclcNumber],
        CAST(NULL AS varchar(1)) AS [C3],
        CAST(NULL AS varchar(1)) AS [C4],
        CAST(NULL AS int) AS [C5],
        CAST(NULL AS varchar(1)) AS [C6],
        CAST(NULL AS varchar(1)) AS [C7],
        CAST(NULL AS varchar(1)) AS [C8],
        CAST(NULL AS varchar(1)) AS [C9],
        CAST(NULL AS varchar(1)) AS [C10],
        [Extent110].[Content] AS [Content]
        FROM   (SELECT [Project35].[Id] AS [Id], [Project35].[OrderId] AS [OrderId], [Project35].[VendorItemId] AS [VendorItemId], [Project35].[VendorNotes] AS [VendorNotes], [Project35].[SpecialNotes] AS [SpecialNotes], [Project35].[MiscellaneousNotes] AS [MiscellaneousNotes], [Project35].[SelectorNotes] AS [SelectorNotes], [Project35].[BibId] AS [BibId], [Project35].[Id1] AS [Id1], [Project35].[VendorId] AS [VendorId], [Project35].[OrderTypeId] AS [OrderTypeId], [Project35].[OrderStatusId] AS [OrderStatusId], [Project35].[VendorCustomerId] AS [VendorCustomerId], [Project35].[DeliveryRoomId] AS [DeliveryRoomId], [Project35].[CreationTime] AS [CreationTime], [Project35].[Id2] AS [Id2], [Project35].[Name] AS [Name], [Project35].[Id3] AS [Id3], [Project35].[Name1] AS [Name1], [Project35].[Id4] AS [Id4], [Project35].[ReceiptStatusId] AS [ReceiptStatusId], [Project35].[Id5] AS [Id5], [Project35].[Name2] AS [Name2], [Project35].[Id6] AS [Id6], [Project35].[Name3] AS [Name3], [Project35].[EmailAddress] AS [EmailAddress], [Project35].[Id7] AS [Id7], [Project35].[Name4] AS [Name4], [Project35].[Id8] AS [Id8], [Project35].[OclcNumber] AS [OclcNumber], [Project35].[Title] AS [Title], [Project35].[Author] AS [Author], [Project35].[C1] AS [C1]
            FROM ( SELECT
                [Project33].[Id] AS [Id],
                [Project33].[OrderId] AS [OrderId],
                [Project33].[VendorItemId] AS [VendorItemId],
                [Project33].[VendorNotes] AS [VendorNotes],
                [Project33].[SpecialNotes] AS [SpecialNotes],
                [Project33].[MiscellaneousNotes] AS [MiscellaneousNotes],
                [Project33].[SelectorNotes] AS [SelectorNotes],
                [Project33].[BibId] AS [BibId],
                [Project33].[Id1] AS [Id1],
                [Project33].[VendorId] AS [VendorId],
                [Project33].[OrderTypeId] AS [OrderTypeId],
                [Project33].[OrderStatusId] AS [OrderStatusId],
                [Project33].[VendorCustomerId] AS [VendorCustomerId],
                [Project33].[DeliveryRoomId] AS [DeliveryRoomId],
                [Project33].[CreationTime] AS [CreationTime],
                [Project33].[Id2] AS [Id2],
                [Project33].[Name] AS [Name],
                [Project33].[Id3] AS [Id3],
                [Project33].[Name1] AS [Name1],
                [Project33].[Id4] AS [Id4],
                [Project33].[ReceiptStatusId] AS [ReceiptStatusId],
                [Project33].[Id5] AS [Id5],
                [Project33].[Name2] AS [Name2],
                [Project33].[Id6] AS [Id6],
                [Project33].[Name3] AS [Name3],
                [Project33].[EmailAddress] AS [EmailAddress],
                [Project33].[Id7] AS [Id7],
                [Project33].[Name4] AS [Name4],
                [Project33].[Id8] AS [Id8],
                [Project33].[OclcNumber] AS [OclcNumber],
                [Project33].[Title] AS [Title],
                [Project33].[Author] AS [Author],
                [Project33].[C1] AS [C1]
                FROM ( SELECT
                    [Extent99].[Id] AS [Id],
                    [Extent99].[OrderId] AS [OrderId],
                    [Extent99].[VendorItemId] AS [VendorItemId],
                    [Extent99].[VendorNotes] AS [VendorNotes],
                    [Extent99].[SpecialNotes] AS [SpecialNotes],
                    [Extent99].[MiscellaneousNotes] AS [MiscellaneousNotes],
                    [Extent99].[SelectorNotes] AS [SelectorNotes],
                    [Extent99].[BibId] AS [BibId],
                    [Extent100].[Id] AS [Id1],
                    [Extent100].[VendorId] AS [VendorId],
                    [Extent100].[OrderTypeId] AS [OrderTypeId],
                    [Extent100].[OrderStatusId] AS [OrderStatusId],
                    [Extent100].[VendorCustomerId] AS [VendorCustomerId],
                    [Extent100].[DeliveryRoomId] AS [DeliveryRoomId],
                    [Extent100].[CreationTime] AS [CreationTime],
                    [Extent101].[Id] AS [Id2],
                    [Extent101].[Name] AS [Name],
                    [Extent102].[Id] AS [Id3],
                    [Extent102].[Name] AS [Name1],
                    [Extent103].[Id] AS [Id4],
                    [Extent103].[ReceiptStatusId] AS [ReceiptStatusId],
                    [Extent104].[Id] AS [Id5],
                    [Extent104].[Name] AS [Name2],
                    [Extent105].[Id] AS [Id6],
                    [Extent105].[Name] AS [Name3],
                    [Extent105].[EmailAddress] AS [EmailAddress],
                    [Extent106].[Id] AS [Id7],
                    [Extent106].[Name] AS [Name4],
                    [Extent107].[Id] AS [Id8],
                    [Extent107].[OclcNumber] AS [OclcNumber],
                    [Extent107].[Title] AS [Title],
                    [Extent107].[Author] AS [Author],
                    [Extent99].[UnitPrice] *  CAST( [Extent99].[Quantity] AS decimal(19,0)) AS [C1]
                    FROM         [dbo].[OrderItems] AS [Extent99]
                    INNER JOIN [dbo].[Orders] AS [Extent100] ON [Extent99].[OrderId] = [Extent100].[Id]
                    INNER JOIN [dbo].[OrderStatuses] AS [Extent101] ON [Extent100].[OrderStatusId] = [Extent101].[Id]
                    INNER JOIN [dbo].[OrderTypes] AS [Extent102] ON [Extent100].[OrderTypeId] = [Extent102].[Id]
                    LEFT OUTER JOIN [dbo].[Holdings] AS [Extent103] ON [Extent99].[HoldingId] = [Extent103].[Id]
                    LEFT OUTER JOIN [dbo].[ReceiptStatuses] AS [Extent104] ON [Extent103].[ReceiptStatusId] = [Extent104].[Id]
                    INNER JOIN [dbo].[Vendors] AS [Extent105] ON [Extent100].[VendorId] = [Extent105].[Id]
                    LEFT OUTER JOIN [dbo].[Rooms] AS [Extent106] ON [Extent100].[DeliveryRoomId] = [Extent106].[Id]
                    LEFT OUTER JOIN [dbo].[Bibs] AS [Extent107] ON [Extent99].[BibId] = [Extent107].[Id]
                    WHERE [Extent100].[CreationTime] >= @p__linq__0
                )  AS [Project33]
                WHERE  EXISTS (SELECT
                    1 AS [C1]
                    FROM  [dbo].[OrderItemAccounts] AS [Extent108]
                    INNER JOIN [dbo].[Accounts] AS [Extent109] ON [Extent108].[AccountId] = [Extent109].[Id]
                    WHERE ([Project33].[Id] = [Extent108].[OrderItemId]) AND ([Extent109].[Code] LIKE N'XPUTT%')
                )
            )  AS [Project35]
            ORDER BY row_number() OVER (ORDER BY [Project35].[CreationTime] DESC)
            OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY  ) AS [Limit9]
        INNER JOIN [dbo].[Publications] AS [Extent110] ON [Limit9].[BibId] = [Extent110].[BibId]) AS [UnionAll8]
    ORDER BY [UnionAll8].[CreationTime] DESC, [UnionAll8].[Id] ASC, [UnionAll8].[Id1] ASC, [UnionAll8].[Id2] ASC, [UnionAll8].[Id3] ASC, [UnionAll8].[Id4] ASC, [UnionAll8].[Id5] ASC, [UnionAll8].[Id6] ASC, [UnionAll8].[Id7] ASC, [UnionAll8].[Id9] ASC, [UnionAll8].[C1] ASC
    ThreadId=13
    DateTime=2018-01-20T15:36:47.8427688Z
LibraryLibrary Verbose: 0 :

    ThreadId=13
    DateTime=2018-01-20T15:36:47.8427688Z
LibraryLibrary Verbose: 0 : -- p__linq__0: '11/20/2017 12:00:00 AM' (Type = DateTime2)

    ThreadId=13
    DateTime=2018-01-20T15:36:47.8427688Z
LibraryLibrary Verbose: 0 : -- Executing at 1/20/2018 9:36:47 AM -06:00

    ThreadId=13
    DateTime=2018-01-20T15:36:47.8427688Z
LibraryLibrary Verbose: 0 : -- Completed in 49 ms with result: SqlDataReader

    ThreadId=13
    DateTime=2018-01-20T15:36:47.8928168Z
LibraryLibrary Verbose: 0 :

    ThreadId=13
    DateTime=2018-01-20T15:36:47.8928168Z
LibraryLibrary Verbose: 0 : Closed connection at 1/20/2018 9:36:47 AM -06:00

    ThreadId=13
    DateTime=2018-01-20T15:36:47.8928168Z
LibraryWebApplication Verbose: 0 : 00:00:00.0585159 elapsed
    ThreadId=13
    DateTime=2018-01-20T15:36:47.8928168Z
LibraryWebApplication Information: 0 : https://localhost:44310/OrderItem1s/Default.aspx
    UserName=ADLOCAL\jemiller
    LocalDateTime=1/20/2018 9:36:47 AM
    ElapsedTime=00:00:00.1766330
    ThreadId=13
    DateTime=2018-01-20T15:36:47.9648445Z
@maumar

This comment has been minimized.

Contributor

maumar commented Jan 21, 2018

@SSkovboiSS the main reason why we don't apply optimization for streaming collection is practical - it get's quite tricky to do for some edge cases, e.g. when the inner streaming collection enumerator is enumerated multiple times, or out of order. We would have to keep track of quite a bit of data to determine when the inner query should be re-issued, and when it is safe to continue enumerating. We were considering making all collections buffered and apply the optimization globally, with a switch to disable it altogether but that would not allow for mixing buffering and streaming in a single query.

@maumar

This comment has been minimized.

Contributor

maumar commented Jan 21, 2018

@jemiller0 what you encountered looks a like a bug. I believe the reason why optimization is not being applied is Take() - optimization currently can't handle paging operations, the translation of a correlated paged query is much more complicated. However, we should have detected that and fallback to the old behavior - essentially there should be no difference between queries with ToArray() and without it.

Can you double check that the Take() is culprit here?

@maumar

This comment has been minimized.

Contributor

maumar commented Jan 24, 2018

@jemiller0 I looked at the query you provided, and the problem is that adding filter after the projection makes it a much complicated query, from EF perspective - we create quite a complicated predicate, which gets client-evaluated and that disables correlated collection optimization and also this is why you don't see a WHERE clause in your query.

You can modify the query slightly (move the filter before the projection):

using (var lc = new LibraryContext())
            {
                var q = from oi in lc.OrderItems
                        where oi.OrderItemAccounts.Select(oia => oia.Account.Code).Any(s => s == "XPUTT")
                        select new
                        {
                            oi.Id,
                            Order = oi.Order.Id,
                            oi.OrderId,
                            oi.Order.CreationTime,
                            OrderStatus = oi.Order.OrderStatus.Name,
                            oi.Order.OrderStatusId,
                            OrderType = oi.Order.OrderType.Name,
                            oi.Order.OrderTypeId,
                            ReceiptStatus = oi.Holding.ReceiptStatus.Name,
                            oi.Holding.ReceiptStatusId,
                            Vendor = oi.Order.Vendor.Name,
                            oi.Order.Vendor.EmailAddress,
                            oi.Order.VendorId,
                            Accounts = oi.OrderItemAccounts.Select(oia => oia.Account.Code).ToArray(),
                            Donors = oi.OrderItemDonors.Select(oid => oid.Donor.Code).ToArray(),
                            oi.VendorNotes,
                            oi.SpecialNotes,
                            oi.MiscellaneousNotes,
                            oi.SelectorNotes,
                            oi.Order.VendorCustomerId,
                            DeliveryRoom = oi.Order.DeliveryRoom.Name,
                            oi.Order.DeliveryRoomId,
                            ExtendedCost = oi.UnitPrice * oi.Quantity,
                            oi.VendorItemId,
                            Bib = oi.Bib.Title,
                            oi.BibId,
                            oi.Bib.Author,
                            oi.Bib.OclcNumber,
                            OclcNumbers = oi.Bib.OclcNumbers.Select(n => n.Content).ToArray(),
                            Isbns = oi.Bib.Isbns.Select(n => n.Content),
                            Issns = oi.Bib.Issns.Select(n => n.Content),
                            OtherNumbers = oi.Bib.Numbers.Where(n => n.Type == "024").Select(n => n.Content).ToArray(),
                            PublisherNumbers = oi.Bib.Numbers.Where(n => n.Type == "028").Select(n => n.Content).ToArray(),
                            Editions = oi.Bib.Editions.Select(n => n.Content).ToArray(),
                            Publications = oi.Bib.Publications.Select(n => n.Content).ToArray()
                        };
                var l = q.Take(5).ToArray();
                foreach (var oi in l) oi.Accounts.ToArray();
            }

and you will get much better queries. (Disclaimer: I only tested it with a simplified model so there still might be more issues.)

@jemiller0

This comment has been minimized.

jemiller0 commented Jan 24, 2018

Thanks @maumar. I'm sorry I didn't get back to you yet. Some things came up a work and I haven't had a chance to test. The main reason I'm putting the filter at the bottom like that is that I'm using the query with a grid control (RadGrid) from a company called Telerik. The grid control allows you to enable filtering controls at the top of the grid and generates Dynamic LINQ filter strings. The output projection field names are what the grid knows. Otherwise, I agree, it would be best to do the filtering at the top.

@maumar

This comment has been minimized.

Contributor

maumar commented Jan 30, 2018

filed #10811 to track the RadGrid issue, perhaps we can do some QM rewrite to improve the queries, making them take advantage of N+1 optimization (and others)

@Liero

This comment has been minimized.

Liero commented Mar 19, 2018

Hi, what about this query, which is still producing N+1 queries in EF2.1 preview:

dbContext.ForumThreads.Include(t => t.Posts)
.Select(t => new
{
t.Id,
t.Title,
PostAuhtors = t.Posts.Select(p => p.AuthorName).Take(5).ToArray()
}).ToArray();

https://stackoverflow.com/questions/49346001/how-to-avoid-n1-queries-in-ef-core-2-1/

@MaklaCof

This comment has been minimized.

MaklaCof commented Mar 19, 2018

I did not try this in 2.1, but I would like to mention another scenario (FirstOrDefault):

  context.Set<DbItem>()
    .Select(t => new {
      t.Id,
      t.Title,
      t.Detail.Where(t2 => t2.Lang == "ENG").Select(t2 => t2.Name).FirstOrDefault()
    }).ToList();
@ajcvickers

This comment has been minimized.

Member

ajcvickers commented Mar 19, 2018

@Liero @MaklaCof Can you please file new issues including a runnable project/solution or complete code listing so that we can investigate what you are seeing. (This issue is closed, so it is unlikely that posting here will result in any action.)

@maumar

This comment has been minimized.

Contributor

maumar commented Mar 19, 2018

@Liero this is a known limitation (there are other limitations that we are aware of, listed here: b95f23f)

@MaklaCof this is already fixed by #10813

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment