Code Bug Fix: .Net core and Npgsql error on query A command is already in progress

Original Source Link

Im using Postgres and i get an error while want to run the following query:

     var devices = (from m in db.turbineDevices.AsEnumerable()
                       join c in db.accessinfMaster.AsEnumerable() on
                       m.turbine_ip equals c.turbine_id into tmp
                       from tm in tmp.DefaultIfEmpty()
                       join t in db.v_vpn_gateway.AsEnumerable()
                       on m.turbine_ip equals t.turbine_id into tmp2
                       from tm2 in tmp2.DefaultIfEmpty()
                       where m.turbine_ip.ToString() == id

                       select new TurbineDvce
                       {

                           DeviceIP = m.device_ip.ToString(),
                           DeviceType = m.device_type,
                           FirmwareVersion = m.firmware_version,                           
                       }).AsEnumerable().Union(from t in db.v_vpn_gateway
                                               where t.turbine_id.ToString() == id
                                               select new TurbineDvce
                                               {
                                                   Comments = "VPN Gateway",
                                                   Description = string.Empty,
                                                   DeviceIP = t.vpn_gateway.ToString(),
                                                   DeviceType = t.device_type,
                                                   FirmwareVersion = string.Empty,
                                                   Model = t.model,
                                                   Password = string.Empty,
                                                   Phone = string.Empty,
                                                   Producer = t.producer,
                                                   PublicIP = t.vpn_public_ip.ToString(),
                                                   TurbineId = t.turbine_id.ToString(),
                                                   Username = string.Empty
                                               } ).OrderBy(m => m.DeviceIP).ThenByDescending(m => m.Description);

and here is my DbContext:

    services.AddDbContext<DbContextClass>(options =>
        options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection")));

in my controller i inject my context:

    public class AcessInfoData:IAccessInfo
  {
    private DbContextClass db;
    public AcessInfoData(DbContextClass context)
    {
        db = context;
    }

when it reachs my LINQ it gives me an error :Npgsql.NpgsqlOperationInProgressException: A command is already in progress:

 SELECT v.turbine_ip, v.comments, v.description, v.device_ip, v.device_type, v.firmware_version, v.model, v.producer, v.psw, v.public_ip, v.tel_number, v.username

Your multiple AsEnumerable calls inside your query are triggering separate commands to be executed, and since they attempt to run concurrently on the same connection, you get an exception (this is not possible).

Any reason for these AsEnumerables? It seems like your query should be translatable to SQL for executing on the server – can you try removing them?

Tagged : / /

Leave a Reply

Your email address will not be published. Required fields are marked *