Saturday, June 25, 2016

Returning an IEnumerable from a database

This is probably rather obvious, but... when returning an `IEnumerable` from a database, like the records from a table, don't do this:

using (var db = GetDatabase())
{
  return db.GetTable("Table1");
}

because it will throw an exception when trying to enumerate those records, since the database connection has already been disposed.

Don't do this either:

using (var db = GetDatabase())
{
  return db.GetTable("Table1").ToList();
}

because it will retrieve all records from the table, even if you only need a small subset.

A slightly better way is to do this:

using (var db = GetDatabase())
{
  foreach (var item in db.GetTable("Table1"))
    yield return item;
}

This way, the `Dispose` method doesn't get called until the enumeration is over and if you only `Take()` a limited number of records from the result, it won't load the whole table. On the other hand, if you only add `Where()` clauses to the result, it will still enumerate everything (the "where" doesn't get passed on to the database engine).

This is not perfect - it would be best if I could return an IQueryable so that additional filters get passed on to the database engine, but I haven't yet figured out how to do that.

No comments: