Multiple select statements using one transaction and two repositories

Question

I just made a working Get method for Orders combining two methods:

1. This method gets all orders and data associated with it:

       private async Task<(List<Order>, int)> GetOrdersWithDataAsync(NpgsqlConnection connection, NpgsqlTransaction transaction, OrderFilter filtering, Sorting sorting)
   {
       List<Order> orders = new List<Order>();
       int totalCount = 0;
   
       StringBuilder cmdBuilder = new StringBuilder("select o.\"Id\", o.\"ShippingAddressId\", o.\"BillingAddressId\", o.\"PersonId\", o.\"TotalPrice\", " +
                       " pe.\"Id\" as \"PersonId\", pe.\"FirstName\", pe.\"LastName\", pe.\"Email\", sa.\"Id\" as \"ShippingAddressId\" sa.\"StreetName\" as \"ShippingStreetName\", " +
                       "sa.\"StreetNumber\" as \"ShippingStreetNumber\",sa.\"City\" as \"ShippingCity\", sa.\"Zipcode\" as \"ShippingZipcode\", " +
                       "ba.\"Id\" as \"BillingAddressId\", ba.\"StreetName\" as \"BillingStreetName\", ba.\"StreetNumber\" as \"BillingStreetNumber\", ba.\"City\" as \"BillingCity\", " +
                       "ba.\"Zipcode\" as \"BillingZipcode\", Count(*) Over() as \"TotalCount\" from \"Order\" o " +
                       "inner join \"Person\" pe on o.\"PersonId\" = pe.\"Id\" inner join \"Address\" sa on sa.\"Id\" = o.\"ShippingAddressId\" inner join \"Address\" ba on ba.\"Id\" = " +
                       "o.\"BillingAddressId\"");
   
       NpgsqlCommand cmd = new NpgsqlCommand("" , connection);
   
   
       cmdBuilder.Append(" WHERE 1 = 1");
       if(!string.IsNullOrEmpty(filtering.SearchQuery))
       {
           cmdBuilder.Append(" AND pe.\"FirstName\" LIKE \'%@SearchQuery%\' OR pe.\"LastName\" LIKE \'%@SearchQuery%\'");
           cmd.Parameters.AddWithValue("@SearchQuery", filtering.SearchQuery);
       } 
       if(filtering.MinPrice.HasValue && filtering.MinPrice.Value != 0)
       {
           cmdBuilder.Append(" AND o.\"TotalPrice\" > @MinPrice");
           cmd.Parameters.AddWithValue("@MinPrice", filtering.MinPrice);
       }
       if (filtering.MaxPrice.HasValue && filtering.MaxPrice.Value != 0)
       {
           cmdBuilder.Append(" AND o.\"TotalPrice\" < @MaxPrice");
           cmd.Parameters.AddWithValue("@MaxPrice", filtering.MaxPrice);
       }
   
       cmd.CommandText = cmdBuilder.ToString();
       cmd.Transaction = transaction;
       cmd.Connection = connection;
   
       NpgsqlDataReader reader = await cmd.ExecuteReaderAsync();
       try
       {
           using (reader)
           {
               if (reader.HasRows)
               {
                   while (reader.Read())
                   {
                       Guid orderId = (Guid)reader["Id"];
   
                       Order order = new Order();
                       order.ShippingAddress = new Address();
                       order.BillingAddress = new Address();
                       order.Products = new List<Product>();
   
                       order.Id = (Guid)reader["Id"];
                       order.TotalPrice = (decimal)reader["TotalPrice"];
                       order.Person = new Person();
                       order.Person.Id = (Guid)reader["PersonId"];
                       order.Person.FirstName = (string)reader["FirstName"];
                       order.Person.LastName = (string)reader["LastName"];
                       order.Person.Email = (string)reader["Email"];
                       order.PersonId = (Guid)reader["PersonId"];
                       order.ShippingAddress.Id = (Guid)reader["ShippingAddressId"];
                       order.ShippingAddress.StreetName = (string)reader["ShippingStreetName"];
                       order.ShippingAddress.StreetNumber = (string)reader["ShippingStreetNumber"];
                       order.ShippingAddress.City = (string)reader["ShippingCity"];
                       order.ShippingAddress.ZipCode = (int)reader["ShippingZipcode"];
                       order.BillingAddress.Id = (Guid)reader["BillingAddressId"];
                       order.BillingAddress.StreetName = (string)reader["BillingStreetName"];
                       order.BillingAddress.StreetNumber = (string)reader["BillingStreetNumber"];
                       order.BillingAddress.City = (string)reader["BillingCity"];
                       order.BillingAddress.ZipCode = (int)reader["BillingZipcode"];
   
                       orders.Add(order);
                   }
               }
               return (orders, totalCount);
           }
       }
       catch(Exception ex)
       {
           throw ex;
       }
   
   }
   

2. A method that gets products for each order from the product repository which I injected into OrderRepository:

       public async Task<List<Order>> GetProductsByOrderIdAsync(List<Order> orders, NpgsqlConnection connection, NpgsqlTransaction transaction)
   {
       foreach (var order in orders)
       {
           List<Product> products = new List<Product>();
   
           try
           {
               NpgsqlCommand cmd = new NpgsqlCommand("SELECT p.\"Id\", p.\"Name\", p.\"Price\", po.\"ProductQty\" as \"Quantity\" FROM \"Product\" as p INNER JOIN \"ProductOrder\" as po ON p.\"Id\" = po.\"ProductId\" WHERE po.\"OrderId\" = @id", connection);
               cmd.Parameters.AddWithValue("id", order.Id);
               cmd.Transaction = transaction;
               cmd.Connection = connection;
   
               NpgsqlDataReader reader = await cmd.ExecuteReaderAsync();
               if (reader.HasRows) 
               {
                   while (reader.Read())
                   {
                       Product product = new Product();
                       product.Id = (Guid)reader["Id"];
                       product.Name = (string)reader["Name"];
                       product.Price = (decimal)reader["Price"];
                       product.Quantity = (int)reader["Quantity"];
                       products.Add(product);
                   }
               }
               reader.Close();
           }
           catch (Exception ex)
           {
               throw ex;
           }
           order.Products = products;
       }
   
       return orders;
   }
   

Both of these are done through this one:

        public async Task<PagedList<Order>> GetOrdersAsync(Paginate paginate, OrderFilter filtering, Sorting sorting)
    {
        NpgsqlConnection connection = new NpgsqlConnection(_connectionProvider.GetConnectionString());
        await connection.OpenAsync();

        try
        {
            using (connection)
            {
                NpgsqlTransaction transaction = connection.BeginTransaction();

                try
                {
                    (List<Order> orders, int totalCount) = await GetOrdersWithDataAsync(connection, transaction, filtering, sorting);

                    List<Order> ordersWithProducts = await _productRepository.GetProductsByOrderIdAsync(orders, connection, transaction);

                    PagedList<Order> pagedOrders = new PagedList<Order>()
                    {
                        Results = ordersWithProducts,
                        CurrentPage = paginate.PageNumber,
                        PageSize = paginate.PageSize,
                        TotalCount = orders.Count
                    };

                    if (pagedOrders != null && pagedOrders.TotalCount > 0)
                    {
                        transaction.Commit();
                        return pagedOrders;
                    }
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        return null;
    }

The thing that worries me is the select statements for each order in the GetProductsByOrderId method, is there a better way to do this? I already did it using only one query in OrderRepository by checking existing order and adding product to it, but that seems slow also.

Also, would it be better to use two separate transactions and rollback the first one if the second one (for products) fails?

Answer 1

It's probably much better to just do one batch, returning just two resultsets, rather than doing effectively N+1 queries (N being the number of orders you get).

Simply fetch all the Orders and store them in a dictionary, then fetch the second resultset and map the Products into the Order using the dictionary to retrieve the relevant object.

You are also not actually doing any pagination. Consider using Keyset Pagination, rather than Rowset Pagination. This means you pass in a starting key (such as an OrderId) and page forward from there, rather than doing the looks-cool-but-really-slow OFFSET...FETCH. See also Is there any better option to apply pagination without applying OFFSET in SQL Server? I haven't added any code for this, but it should fit right in to your other dynamic filter building.

• Improve readability of your code:
  • Use verbatim strings @"" so you can embed newlines.
  • Use object initializer syntax.
  • Format your SQL properly.
  • Don't quote column names unless necessary.
  • Consider using a mapper such as Dapper to actually convert each row to an object.
• catch ... throw ex; is just plain wrong. Catching only to re-thro is anyway a waste of time, and throw ex; will wipe the stack trace. At the very least do just throw;. or better don't catch at all if you don't want to handle it.
• Specify the types of the parameters explicitly.
• if (reader.HasRows) is unnecessary, the loop won't run at all if there are no rows.
• You are not setting totalCount, but you don't need it anyway as the list itself has a Count.
• Use the async version of await reader.ReadAsync(). Consider also passing in a cancellation token.

private async Task<ICollection<Order>> GetOrdersWithDataAsync(OrderFilter filtering, Sorting sorting)
{
    using NpgsqlConnection connection = new NpgsqlConnection(_connectionProvider.GetConnectionString());
    using NpgsqlCommand cmd = new NpgsqlCommand("" , connection);

    var conditions = "WHERE 1 = 1";
    if(!string.IsNullOrEmpty(filtering.SearchQuery))
    {
        conditions += @"
  AND pe.FirstName LIKE '%' + @SearchQuery+ '%' OR pe.LastName LIKE '%' + @SearchQuery+ '%'");
        cmd.Parameters.Add("@SearchQuery", NpgsqlDbType.Text).Value = filtering.SearchQuery;
    } 
    if(filtering.MinPrice != 0)
    {
        conditions += @"
  AND o.TotalPrice > @MinPrice");
        cmd.Parameters.Add("@MinPrice", NpgsqlDbType.Numeric).Value = filtering.MinPrice.Value;
    }
    if (filtering.MaxPrice != 0)
    {
        conditions += @"
  AND o.TotalPrice < @MaxPrice");
        cmd.Parameters.Add("@MaxPrice", NpgsqlDbType.Numeric).Value filtering.MaxPrice;
    }

    cmd.CommandText = @$"
select
  o.Id,
  o.ShippingAddressId,
  o.BillingAddressId,
  o.PersonId,
  o.TotalPrice,
  pe.Id as PersonId,
  pe.FirstName,
  pe.LastName,
  pe.Email,
  sa.Id as ShippingAddressId,
  sa.StreetName as ShippingStreetName,
  sa.StreetNumber as ShippingStreetNumber,
  sa.City as ShippingCity,
  sa.Zipcode as ShippingZipcode,
  ba.Id as BillingAddressId,
  ba.StreetName as BillingStreetName,
  ba.StreetNumber as BillingStreetNumber,
  ba.City as BillingCity,
  ba.Zipcode as BillingZipcode
from ""Order"" o
inner join Person pe on o.PersonId = pe.Id
inner join Address sa on sa.Id = o.ShippingAddressId
inner join Address ba on ba.Id = o.BillingAddressId
{conditions}
;

SELECT
  p.Id,
  p.Name,
  p.Price,
  po.ProductQty as Quantity,
  po.OrderId
FROM Product as p
JOIN ProductOrder as po ON p.Id = po.ProductId
JOIN ""Order"" o ON o.Id = po.OrderId
inner join Person pe on o.PersonId = pe.Id
{conditions}
";

    var orders = new Dictionary<Guid, Order>();

    await connection.OpenAsync();
    using NpgsqlDataReader reader = await cmd.ExecuteReaderAsync();

    while (await reader.ReadAsync())
    {
        Order order = new Order {
            Products = new List<Product>(),
            Id = (Guid)reader["Id"],
            TotalPrice = (decimal)reader["TotalPrice"],
            Person = new Person {
                Id = (Guid)reader["PersonId"],
                FirstName = (string)reader["FirstName"],
                LastName = (string)reader["LastName"],
                Email = (string)reader["Email"],
            },
            PersonId = (Guid)reader["PersonId"],
            ShippingAddress = new Address( {
                Id = (Guid)reader["ShippingAddressId"],
                StreetName = (string)reader["ShippingStreetName"],
                StreetNumber = (string)reader["ShippingStreetNumber"],
                City = (string)reader["ShippingCity"],
                ZipCode = (int)reader["ShippingZipcode"],
            },
            BillingAddress = new Address {
                Id = (Guid)reader["BillingAddressId"],
                StreetName = (string)reader["BillingStreetName"],
                StreetNumber = (string)reader["BillingStreetNumber"],
                City = (string)reader["BillingCity"],
                ZipCode = (int)reader["BillingZipcode"],
            },
        };

        orders.Add(order.Id, order);
    }

    await reader.NextResultAsync();

    while (await reader.ReadAsync())
    {
        Product product = new Product {
            Id = (Guid)reader["Id"],
            Name = (string)reader["Name"],
            Price = (decimal)reader["Price"],
            Quantity = (int)reader["Quantity"],
        };
        if(orders.TryGetValue((Guid)reader["Id"], order)
            order.Products.Add(product);
    }

    return orders.Values;
}

A similar option might be to use Postgres' JSON functionality to construct a JSON array of Products. This means you can do it all in one batch. I'm not sure the exact way to get data out of that using NpgSql, but the Postgres syntax would use something like JSON_AGG aggregation in a subquery.