I’m encountering an issue in my .NET service where I’m executing stored procedures for invoice creation (InsertFactura) in a foreach loop for multiple orders. Despite distinct entries being created in the database table “comprobantes” with unique invoice numbers, the InsertFactura method returns the same invoice number for different orders.

I’ve attempted to address this by using a lock within the foreach loop to handle concurrency, and I’ve also tried modifying the method implementations to be asynchronous. However, the issue persists.

For context, here’s a simplified version of my code structure:

if (pedidos != null)
                    {
                        _logger.LogInformation(pedidos.Count.ToString() + " pedidos por procesar");
                        int indexPedido = 0;

                        foreach (var item in pedidos)
                        {
                            try
                            {
                                indexPedido++;
                                _logger.LogInformation("Ingresando detalle de pedido " + indexPedido.ToString() + " de " + pedidos.Count.ToString());

                                if (repository.existePedido(item.IdPedido).count > 0)
                                {
                                    _logger.LogInformation("Cambiando estado del pedido existente " + item.IdPedido.ToString());
                                    await apiSdk.setPedidoAsDescargado(item.IdPedido);
                                }
                                else
                                {
                                    _logger.LogInformation("#" + item.IdPedido.ToString() + " Precio: " + item.PrecioFinal.ToString() + " Cuit: " + item.Cuit + " DNI:" + item.Dni);
                                    lock(lockObject)
                                    { 
                                        PedidoImport datosFactura = repository.InsertFactura(item.PrecioFinal, conf.Facturacion.nroVendedor, item.Cuit, item.Dni, conf.Facturacion.puntoDeVenta, item.IdPedido, item.Direccion, item.Nombre, item.Apellido);
                                        _logger.LogInformation("#" + item.IdPedido.ToString() + " Factura insertada: " + datosFactura.nroComprobante.ToString());
                                        foreach (var itemDetalle in item.PedidoDetalles)
                                        {
                                            repository.InsertDetalleFactura(datosFactura.nroComprobante, Convert.ToInt32(itemDetalle.CodigoArticulo), itemDetalle.Descripcion, itemDetalle.Cantidad, itemDetalle.PrecioUnitario, 0, conf.Facturacion.idListaPrecio, conf.Facturacion.deposito);
                                            _logger.LogInformation("Insertando detalle de pedido NroComprobante" + datosFactura.nroComprobante + " CodArticulo:" + itemDetalle.CodigoArticulo + " " + itemDetalle.Descripcion + " CANT:" + itemDetalle.Cantidad.ToString() + "PrecioUnitario:" + itemDetalle.PrecioUnitario.ToString());
                                        }
                                    }
                                    await apiSdk.setPedidoAsDescargado(item.IdPedido);
                                }
                            }
                            catch (Exception ex)
                            {
                                repository.logError("Creación Factura Error", "Error insertando pedido " + item.IdPedido.ToString() + ", Detalles: " + ex.Message + " " + ex.StackTrace);
                                _logger.LogError("Error insertando pedido " + ex.Message + ex.StackTrace);
                            }

                        }
                    }

And here are the relevant implementations of the stored procedure methods:

public PedidoImport InsertFactura(decimal totalComprobante, int vendedor, string cuit, string dni, int puntoVenta, int idPedido, string Direccion, string Nombre, string Apellido)
        {
            NumberFormatInfo nfi = new NumberFormatInfo();
            nfi.NumberDecimalSeparator = ".";

            
            string sqlFTS = string.Empty;
            if (!String.IsNullOrEmpty(cuit))
            {
              sqlFTS= "EXEC  [dbo].[dvr_FACTURA_GENERAL_InsertFromEcommerce] @TotalComprobante =" + totalComprobante.ToString(nfi)+ ", @ven_idesec=" + vendedor + ", @cuit=" + cuit + " , @dni=" + dni + " , @pdv_idesec=" + puntoVenta ;
            }
            else
            {
            sqlFTS = "EXEC  [dbo].[dvr_FACTURA_GENERAL_InsertFromEcommerce] @TotalComprobante =" + totalComprobante.ToString(nfi) + ", @ven_idesec=" + vendedor + ", @dni=" + dni + " , @pdv_idesec=" + puntoVenta ; ;
            }
            var pedido = dataContext.Set<PedidoImport>().FromSqlRaw(sqlFTS.ToString());
            return pedido.ToList().FirstOrDefault();
        }

        public void InsertDetalleFactura(int nroComprobante, int codigoArticulo, string descripcionItem, decimal cantidad, decimal precioUnitario,decimal ajuste,int listaDePrecio, int deposito)
        {
            NumberFormatInfo nfi = new NumberFormatInfo();
            nfi.NumberDecimalSeparator = ".";

            string sqlFTS = string.Empty;

            sqlFTS = "EXEC  [dbo].[dvr_DETALLE_FACTURA_GENERAL_insertFromEcommerce] @com_idesec =" + nroComprobante + ", @art_idesec=" + codigoArticulo + ", @dfg_des='" + descripcionItem.Replace("'","''") + "' , @dfg_can=" + cantidad.ToString(nfi) + " , @dfg_preuni=" + precioUnitario.ToString(nfi) + ",@dfg_aju=" + ajuste.ToString(nfi) + ",@tdp_idesec=" + listaDePrecio + ",@lda_idesec=" + deposito;
            
            var pedido = dataContext.Set<PedidoImport>().FromSqlRaw(sqlFTS.ToString());
            dataContext.Database.ExecuteSqlRaw(sqlFTS);
           
        }

        public PedidoCount existePedido(int idPedido)
        {
            var sqlFTS = "EXEC [dbo].[existePedido] @idPedido = " + idPedido;
            var pedido = dataContext.Set<PedidoCount>().FromSqlRaw(sqlFTS.ToString());

            return pedido.ToList().FirstOrDefault();
        }

I’ve attempted to address this by using a lock within the foreach loop to handle concurrency, and I’ve also tried modifying the method implementations to be asynchronous. However, the issue persists.

I’m looking for guidance on how to ensure that each order receives a unique invoice number without encountering concurrency issues. Any insights or suggestions would be greatly appreciated. Thanks in advance!