When To Use Single or Split Queries in Entity Framework

The purpose of this article is to examine the difference between single and split queries, and to identify the advantages to using one over the other.

Single Query

Entity Framework by default runs its queries as single queries. This means the entity you request along with all related or included entities are resolved in a single query. Consider the first example below in which a single user and its locations are queried.

Entity Framework Query

var singleQuery = dbContext.Users
    .Include(u => u.Locations)
    .Where(u => u.UserID == 1)
    .ToList();

Translated SQL Query

SELECT
  [u].[UserID],
  [u].[EmailAddress],
  [u].[FirstName],
  [u].[LastName],
  [l].[LocationID],
  [l].[City],
  [l].[State],
  [l].[StreetAddress1],
  [l].[StreetAddress2],
  [l].[UserID],
  [l].[ZipCode]
FROM
  [User].[Users] AS [u]
  LEFT JOIN [User].[Locations] AS [l] ON [u].[UserID] = [l].[UserID]
WHERE
  [u].[UserID] = 1
ORDER BY
  [u].[UserID]

Result Set

Entity Framework uses a join to gather data about the user and its locations in a single query. If a one-to-many relationship is present, this single query will result in duplicate data being returned. If more joins are present with one-to-many or many-to-many relationships, then this data duplication may increase exponentially as all the data from all the tables are present in each row.

This problem may be solved by using Entity Framework's split query functionality.

Split Query

Entity Framework can be instructed to run its queries as split queries. This means the entity you request and each of its related or included entities are resovled in their own separate queries. Consider the second example below in which all users, all their locations, and all their purchases are queried.

Entity Framework Query

var singleQuery = dbContext.Users
    .Include(u => u.Locations)
    .Include(u => u.Purchases)
    .ThenInclude(p => p.PurchaseItems)
    .ThenInclude(pi => pi.Items)
    .ToList();

Translated SQL Query

SELECT
  [u].[UserID],
  [u].[EmailAddress],
  [u].[FirstName],
  [u].[LastName],
  [l].[LocationID],
  [l].[City],
  [l].[State],
  [l].[StreetAddress1],
  [l].[StreetAddress2],
  [l].[UserID],
  [l].[ZipCode],
  [t0].[PurchaseID],
  [t0].[PurchaseDate],
  [t0].[PurchaserUserID],
  [t0].[PurchaseItemID],
  [t0].[ItemID],
  [t0].[PurchaseID0],
  [t0].[ItemID0],
  [t0].[ItemName]
FROM
  [User].[Users] AS [u]
  LEFT JOIN [User].[Locations] AS [l] ON [u].[UserID] = [l].[UserID]
  LEFT JOIN (
    SELECT
      [p].[PurchaseID],
      [p].[PurchaseDate],
      [p].[PurchaserUserID],
      [t].[PurchaseItemID],
      [t].[ItemID],
      [t].[PurchaseID] AS [PurchaseID0],
      [t].[ItemID0],
      [t].[ItemName]
    FROM
      [Purchase].[Purchases] AS [p]
      LEFT JOIN (
        SELECT
          [p0].[PurchaseItemID],
          [p0].[ItemID],
          [p0].[PurchaseID],
          [i].[ItemID] AS [ItemID0],
          [i].[ItemName]
        FROM
          [Purchase].[PurchaseItems] AS [p0]
          LEFT JOIN [Purchase].[Items] AS [i] ON [p0].[PurchaseItemID] = [i].[ItemID]
      ) AS [t] ON [p].[PurchaseID] = [t].[PurchaseID]
  ) AS [t0] ON [u].[UserID] = [t0].[PurchaserUserID]
ORDER BY
  [u].[UserID],
  [l].[LocationID],
  [t0].[PurchaseID],
  [t0].[PurchaseItemID]

Result Set

Since there are more relationships to resolve, more data duplication has occurred. Consider the third example, in which the same query is re-run but with split query enabled.

Entity Framework Query

var splitQuery = dbContext.Users
    .Include(u => u.Locations)
    .Include(u => u.Purchases)
    .ThenInclude(p => p.PurchaseItems)
    .AsSplitQuery()
    .ToList();

Translated SQL Query

SELECT
  [u].[UserID],
  [u].[EmailAddress],
  [u].[FirstName],
  [u].[LastName]
FROM
  [User].[Users] AS [u]
ORDER BY
  [u].[UserID]

SELECT
  [l].[LocationID],
  [l].[City],
  [l].[State],
  [l].[StreetAddress1],
  [l].[StreetAddress2],
  [l].[UserID],
  [l].[ZipCode],
  [u].[UserID]
FROM
  [User].[Users] AS [u]
  INNER JOIN [User].[Locations] AS [l] ON [u].[UserID] = [l].[UserID]
ORDER BY
  [u].[UserID]

SELECT
  [p].[PurchaseID],
  [p].[PurchaseDate],
  [p].[PurchaserUserID],
  [u].[UserID]
FROM
  [User].[Users] AS [u]
  INNER JOIN [Purchase].[Purchases] AS [p] ON [u].[UserID] = [p].[PurchaserUserID]
ORDER BY
  [u].[UserID],
  [p].[PurchaseID]

SELECT
  [p0].[PurchaseItemID],
  [p0].[ItemID],
  [p0].[PurchaseID],
  [u].[UserID],
  [p].[PurchaseID]
FROM
  [User].[Users] AS [u]
  INNER JOIN [Purchase].[Purchases] AS [p] ON [u].[UserID] = [p].[PurchaserUserID]
  INNER JOIN [Purchase].[PurchaseItems] AS [p0] ON [p].[PurchaseID] = [p0].[PurchaseID]
ORDER BY
  [u].[UserID],
  [p].[PurchaseID],
  [p0].[PurchaseItemID]

SELECT
  [i].[ItemID],
  [i].[ItemName],
  [u].[UserID],
  [p].[PurchaseID],
  [p0].[PurchaseItemID]
FROM
  [User].[Users] AS [u]
  INNER JOIN [Purchase].[Purchases] AS [p] ON [u].[UserID] = [p].[PurchaserUserID]
  INNER JOIN [Purchase].[PurchaseItems] AS [p0] ON [p].[PurchaseID] = [p0].[PurchaseID]
  INNER JOIN [Purchase].[Items] AS [i] ON [p0].[PurchaseItemID] = [i].[ItemID]
ORDER BY
  [u].[UserID],
  [p].[PurchaseID],
  [p0].[PurchaseItemID]

Result Set 1 (User)

Result Set 2 (Locations)

Result Set 3 (Purchases)

Result Set 4 (Purchase Items)

Result Set 5 (Item Definitions)

Notice that multiple queries have now been run. One query resolves the user. Another query resolves the related locations. A third query resolves the related purchases. A fourth query resolves the items of each purchase. The last query resolves the definitions of each of those items. The use of five separate SQL queries is abstracted from the caller, as Entity Framework manages the relationships in the background. This has obvious advantages in removing data duplication. However, a major disadvantage of using split queries is each query is performed in a separate round trip to the database. This means when determining whether to use split queries or a single query, the trade-off of data duplication with network latency must be considered.

Both Options Considered

Single query and split queries both have their uses. Like most performance considerations in a database, deciding which to use comes down to knowing the data and anticipating how it will be used. The factors to consider when determining which to use are as follows:

• Data Duplication. If it is expected that a large amount of data duplication may occur as a result of a query, using split queries should be considered. Alternatively, if duplication is limited, then the network latency that may be avoided by using a single query may be more effective.
• Expected Network Conditions. The network bandwidth and latency between an application and a database varies by scenario. A database running on the same machine as its application counterpart may not have many network latency or bandwidth considerations to consider at all. A database running in a different region of the world from its application counterpart may be subject to more strict latency considerations. Lastly, a database running on a lower tier network may be subject to more strict bandwidth considerations.
• Data Usage. The last factor to consider is the context of when the query is run. For example, a query that's run once a day on an isolated set of data may have no need to consider performance to the degree of fine-tuning Entity Framework queries. As a counter example, a query that's running 1000s of times a day on a particularly busy set of tables will likely need to ensure this query runs as optimally as possible.

Final Notes

Microsoft has published an article on the use of split queries vs. single queries. More information about the considerations of this decision can be found there.