NpGsql EntityFramework 6 - "An operation is already in progress"

Question

I am working on a project to connect to PostgreSQL database using NpGsql EntityFramework 6. I am getting the exception in question heading, when I try to execute the query in GetAdminUsersCount:

public class GenieRepository : IDisposable
{
    GenieDbContext db = new GenieDbContext();
    public IEnumerable<User> GetUsers()
    {
        return db.Users;
    }   
}

public int GetAdminUsersCount()
{
    return repo.GetUsers().Where(u => u.Role.RoleName == "Administrator").Count();
}

What is the reason for this error and how to resolve it?

SideNote: **PostGre Sql** :O,huh??? Use [Postgres or PostgreSQL](http://stackoverflow.com/tags/postgresql/info) — Vivek S., Dec 31 '15 at 09:36
Which version of NpgSql do you use? If you deploy on mono which version is it? — Orif Khodjaev, Dec 31 '15 at 11:37
Similar issue was found in Mono 3.x but was solved after upgrade to 4.x. Try to downgrade to NpgSql 2.2.7 — Orif Khodjaev, Dec 31 '15 at 13:38
There is an open defect at https://github.com/npgsql/npgsql/issues/826. We see this on Mono 4.2.2 — Matt Z, Feb 12 '16 at 18:30

score 8 · Answer 1 · edited Oct 20 '22 at 14:04

Use a ToList<T> right after the LINQ query like this:

using (ElisContext db = new ElisContext()) {
    var q = from a in db.aktie select a;
    List<aktie> akties = q.ToList<aktie>();
    foreach (aktie a in akties) {
        Console.WriteLine("aktie: id {0}, name {1}, market name {2}"
                 , a.id, a.name, a.marked.name);
    }
}

Note the q.ToList<T> which does the trick. .NET delays the execution of the linq statement to the latest moment, which may be part of the problem. I have tried to use q in the foreach without success.

score 4 · Answer 2 · answered Jul 20 '17 at 23:08

The issue is caused by the return type of the GetUsers() method. Since it is IEnumerable<User>, LINQ-to-SQL will load the result into memory (row by row) and subsequent Where, OrderBy, Select, Count, etc. calls will be executed in memory. When the Where condition is evaluated, the original result set is still being iterated over, but the relation User.Role needs to be resolved on the DB (that's where the "An operation is already in progess" error message comes from).

If the return type is changed to IQueryable<User>, the query won't be executed until the Count method is called, and furthermore, the whole query will be translated into SQL returning only the count without ever loading any User records into memory.

See also this related question/answer: Returning IEnumerable<T> vs. IQueryable<T>

The answer suggesting to call ToList() on the query, will load the entire result set into memory, which makes your error go away, but depending on the size of the result set, could also be very inefficient.

NpGsql EntityFramework 6 - "An operation is already in progress"

2 Answers2