Skip to main content

Entity collection limit

An Entity Collection reference in a command model (ICollection<T> in code) can be filled in two ways:

  • with records picked one by one - from a multiselect in the command's window, or from a parameter mapping that returns a list of entities;
  • with a query - a Data grid batch command hands over its selection this way ((selection, model, db, ctx) => selection), and so does a business event parameter that returns an IQueryable.

A collection filled with a query holds the query, not the records. Nothing is read from the database until the command body reads the collection, and a selection of "all rows" costs nothing to hand over, however many rows the grid has.

What loads the records​

In the command bodyWhat happens
model.Selection.Query()an IQueryable<T> over the selection, evaluated in the database; no limit
model.Selection.Counta COUNT in the database; no limit
foreach, .ToList(), .Where(...), .Select(...), .Contains(...) on model.Selectionloads the records into memory
the collection shown in a multiselect of the command's windowloads the records into memory

Loading is limited to 1000 records. A collection filled with a query that selects more fails with:

EntityCollectionLimitExceededException: An entity collection of Order holds more than 1000 entities and cannot be loaded into memory. Query it with .Query() and iterate it with .Batch() / .BatchWithDefaultOrder() instead.

The limit protects the application: one batch command over "all rows" of a large grid would otherwise load every record into the memory of the application at once.

Note: .Where(...), .Select(...) and the other LINQ operators called directly on the collection run in memory, because the collection is an ICollection<T>. Call .Query() first to run them in the database.

How to fix it​

Query instead of loading​

Most commands only need to read or aggregate the selection. Do it in the database:

(model, db, ctx) =>
{
    var total = model.Selection.Query().Sum(o => o.Price);
    var hasOpen = model.Selection.Query().Any(o => o.State == OrderState.Open);
    ...
}

Iterate in batches​

When the command has to change every selected record, iterate the query in batches. BatchWithDefaultOrder() orders the query by id and loads it a batch at a time (15 records by default); Batch() does the same for a query you have ordered yourself:

(model, db, ctx) =>
{
    foreach (var order in model.Selection.Query().BatchWithDefaultOrder(100))
        order.Closed = true;
}
(model, db, ctx) =>
{
    var ordered = model.Selection.Query().OrderBy(o => o.CreatedOn);

    foreach (var order in ordered.Batch(100))
        order.Closed = true;
}

Both read one batch per database query (Skip/Take). If the loop saves (db.SaveChanges()) a change that takes records out of the selection - for example it sets the state the grid filters on - the following batches shift and records can be skipped. Read the ids first in that case, and work through them:

(model, db, ctx) =>
{
    var ids = model.Selection.Query().Select(o => o.Id).ToList();

    foreach (var chunk in ids.Chunk(100))
    {
        foreach (var order in db.OrderSet.Where(o => chunk.Contains(o.Id)))
            order.State = OrderState.Closed;

        db.SaveChanges();
    }
}

Do not show a large selection in the command window​

A batch command in the Open command window mode prefills its window with the selection. If the window shows the Entity Collection in a multiselect, the multiselect loads it and a selection over 1000 records fails. Leave the Entity Collection out of the command's view when the selection can be large; the command body still receives it.

See also​