Batching Items with EF 6.0

Headline: It’s not too hard to batch items with or without async capabilities in Entity Framework (EF) 6.0.

Here I have a large number (ok… not in the sample test, but in real life) of items and I’d like to pull them in as in batches rather than wait for all of them to be returned. I’ll go ahead and use async here, even though I know I’m not using it properly…

So here is my sample. It reads 3 items at t time and adds them to the collection. With a little rearranging of the code you could have a decent solution.

Batch Sample

[TestMethod]
public void Batching_Items()
{
// Arrange
const int expected = 7;
var take = 3;
var skip = 0;
var locations = new Collection<FieldLocation>();
using (var repository = new MyRepository())
{
repository.Context.Database.Log = Console.Write;
var count = repository.Items.Count();
var remaining = count;
// Act
while (remaining > 0)
{
var task = repository.Items.OrderBy(f => f.ReservoirId).Skip(skip).Take(take).ToListAsync();
foreach (var result in task.Result)
locations.Add(result);
skip += take;
remaining = count – skip;
if (take > remaining) take = remaining;
}
// Assert
var actual = locations.Count;
Assert.AreEqual(expected, actual);
}
}

You can see that the key is the use of the Skip and Take items (and Skip requires the OrderBy). If that code is isolated in a different method then you could use the async to better advantage.

And here’s what happened in the SQL Server:

Test Name:    Batching_Items
Test Outcome:    Passed
Result StandardOutput:
Opened connection at 3/14/2015 2:36:37 PM -06:00
SELECT
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        COUNT(1) AS [A1]
        FROM (SELECT
    [FieldLocationView].[ReservoirCodeId] AS [ReservoirCodeId],
    [FieldLocationView].[Latitude] AS [Latitude],
    [FieldLocationView].[Longitude] AS [Longitude]
    FROM [dbo].[FieldLocationView] AS [FieldLocationView]) AS [Extent1]
    ) AS [GroupBy1]
— Executing at 3/14/2015 2:36:38 PM -06:00
— Completed in 158 ms with result: SqlDataReader

Closed connection at 3/14/2015 2:36:38 PM -06:00
Opened connection asynchronously at 3/14/2015 2:36:38 PM -06:00
SELECT
    [Extent1].[ReservoirCodeId] AS [ReservoirCodeId],
    [Extent1].[Latitude] AS [Latitude],
    [Extent1].[Longitude] AS [Longitude]
    FROM (SELECT
    [FieldLocationView].[ReservoirCodeId] AS [ReservoirCodeId],
    [FieldLocationView].[Latitude] AS [Latitude],
    [FieldLocationView].[Longitude] AS [Longitude]
    FROM [dbo].[FieldLocationView] AS [FieldLocationView]) AS [Extent1]
    ORDER BY [Extent1].[ReservoirCodeId] ASC
    OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
— Executing asynchronously at 3/14/2015 2:36:38 PM -06:00
— Completed in 145 ms with result: SqlDataReader

Closed connection at 3/14/2015 2:36:38 PM -06:00
Opened connection asynchronously at 3/14/2015 2:36:38 PM -06:00
SELECT
    [Extent1].[ReservoirCodeId] AS [ReservoirCodeId],
    [Extent1].[Latitude] AS [Latitude],
    [Extent1].[Longitude] AS [Longitude]
    FROM (SELECT
    [FieldLocationView].[ReservoirCodeId] AS [ReservoirCodeId],
    [FieldLocationView].[Latitude] AS [Latitude],
    [FieldLocationView].[Longitude] AS [Longitude]
    FROM [dbo].[FieldLocationView] AS [FieldLocationView]) AS [Extent1]
    ORDER BY [Extent1].[ReservoirCodeId] ASC
    OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY
— Executing asynchronously at 3/14/2015 2:36:38 PM -06:00
— Completed in 76 ms with result: SqlDataReader

Closed connection at 3/14/2015 2:36:39 PM -06:00
Opened connection asynchronously at 3/14/2015 2:36:39 PM -06:00
SELECT
    [Extent1].[ReservoirCodeId] AS [ReservoirCodeId],
    [Extent1].[Latitude] AS [Latitude],
    [Extent1].[Longitude] AS [Longitude]
    FROM (SELECT
    [FieldLocationView].[ReservoirCodeId] AS [ReservoirCodeId],
    [FieldLocationView].[Latitude] AS [Latitude],
    [FieldLocationView].[Longitude] AS [Longitude]
    FROM [dbo].[FieldLocationView] AS [FieldLocationView]) AS [Extent1]
    ORDER BY [Extent1].[ReservoirCodeId] ASC
    OFFSET 6 ROWS FETCH NEXT 1 ROWS ONLY
— Executing asynchronously at 3/14/2015 2:36:39 PM -06:00
— Completed in 161 ms with result: SqlDataReader

Closed connection at 3/14/2015 2:36:39 PM -06:00

So, with this approach you could easily build an application that will progressively load your items.

Cheers,

Karl

Batching Items with EF 6.0

Comments

Leave a Reply Cancel reply