refactor: optimize payment request labels loading and reporting

diff --git a/BTCPayServer/Controllers/UIPaymentRequestController.cs b/BTCPayServer/Controllers/UIPaymentRequestController.cs
index 9e0de99..d0e076e 100644
--- a/BTCPayServer/Controllers/UIPaymentRequestController.cs
+++ b/BTCPayServer/Controllers/UIPaymentRequestController.cs
@@ -1,4 +1,5 @@
 using System;
+using System.Collections.Generic;
 using System.Linq;
 using System.Threading;
 using System.Threading.Tasks;
@@ -129,18 +130,25 @@ namespace BTCPayServer.Controllers
                 AmountFormatted = _displayFormatter.Currency(data.Amount, data.Currency)
             }).ToList();
 
+            var paymentRequestIds = items.Select(i => i.Id).ToArray();
+            var labelsByPaymentRequestId =
+                await _walletRepository.GetWalletLabelsForObjects(walletId, WalletObjectData.Types.PaymentRequest, paymentRequestIds);
+
             foreach (var item in items)
             {
-                var objectId = new WalletObjectId(walletId, WalletObjectData.Types.PaymentRequest, item.Id);
-
-                var labelTuples = await _walletRepository.GetWalletLabels(objectId);
-
-                item.Labels = labelTuples.Select(l => new TransactionTagModel
+                if (labelsByPaymentRequestId.TryGetValue(item.Id, out var labelTuples))
                 {
-                    Text = l.Label,
-                    Color = l.Color,
-                    TextColor = ColorPalette.Default.TextColor(l.Color)
-                }).ToList();
+                    item.Labels = labelTuples.Select(l => new TransactionTagModel
+                    {
+                        Text = l.Label,
+                        Color = l.Color,
+                        TextColor = ColorPalette.Default.TextColor(l.Color)
+                    }).ToList();
+                }
+                else
+                {
+                    item.Labels = new List<TransactionTagModel>();
+                }
             }
 
             var allLabels = await _walletRepository.GetWalletLabelsByLinkedType(walletId, WalletObjectData.Types.PaymentRequest);
diff --git a/BTCPayServer/Services/PaymentRequests/PaymentRequestRepository.cs b/BTCPayServer/Services/PaymentRequests/PaymentRequestRepository.cs
index 8d622aa..a759175 100644
--- a/BTCPayServer/Services/PaymentRequests/PaymentRequestRepository.cs
+++ b/BTCPayServer/Services/PaymentRequests/PaymentRequestRepository.cs
@@ -143,7 +143,6 @@ namespace BTCPayServer.Services.PaymentRequests
                     throw new InvalidOperationException("PaymentRequestQuery.StoreId should be specified");
 
                 var search = query.SearchText;
-                // We are repeating the StoreId on purpose here, so Postgres can use the index
                 queryable = context.PaymentRequests.FromSqlRaw("""
                                                                    SELECT *
                                                                    FROM "PaymentRequests"
diff --git a/BTCPayServer/Services/Reporting/PaymentRequestsReportProvider.cs b/BTCPayServer/Services/Reporting/PaymentRequestsReportProvider.cs
index 1a0a6c5..782df3e 100644
--- a/BTCPayServer/Services/Reporting/PaymentRequestsReportProvider.cs
+++ b/BTCPayServer/Services/Reporting/PaymentRequestsReportProvider.cs
@@ -1,4 +1,3 @@
-using System.Collections.Generic;
 using System.Linq;
 using System.Threading;
 using System.Threading.Tasks;
@@ -78,27 +77,38 @@ public class PaymentRequestsReportProvider(
             .OrderBy(p => p.Created)
             .ToListAsync(cancellation);
 
-        if (!paymentRequests.Any())
+        if (paymentRequests.Count == 0)
             return;
 
         var network = networkProvider.DefaultNetwork;
         var walletId = new WalletId(queryContext.StoreId, network.CryptoCode);
 
-        var labelsCache = new Dictionary<string, string>();
+        var paymentRequestIds = paymentRequests.Select(pr => pr.Id).ToArray();
 
         var orderIds = paymentRequests
             .Select(pr => PaymentRequestRepository.GetOrderIdForPaymentRequest(pr.Id))
             .Distinct()
             .ToArray();
 
-        var invoices = await invoiceRepository.GetInvoices(new InvoiceQuery
+        var labelsTask = walletRepository.GetWalletLabelsForObjects(
+            walletId,
+            WalletObjectData.Types.PaymentRequest,
+            paymentRequestIds
+        );
+
+        var invoicesTask = invoiceRepository.GetInvoices(new InvoiceQuery
         {
-            StoreId = [queryContext.StoreId],
+            StoreId = new[] { queryContext.StoreId },
             StartDate = queryContext.From,
             EndDate = queryContext.To,
             OrderId = orderIds
         }, cancellation);
 
+        await Task.WhenAll(labelsTask, invoicesTask);
+
+        var labelsByPaymentRequestId = labelsTask.Result;
+        var invoices = invoicesTask.Result;
+
         var invoicesByOrderId = invoices
             .GroupBy(i => i.Metadata.OrderId)
             .ToDictionary(g => g.Key, g => g.ToList());
@@ -108,21 +118,10 @@ public class PaymentRequestsReportProvider(
             var prBlob = paymentRequest.GetBlob();
             var prOrderId = PaymentRequestRepository.GetOrderIdForPaymentRequest(paymentRequest.Id);
 
-            if (!labelsCache.TryGetValue(paymentRequest.Id, out var labelsString))
-            {
-                var objectId = new WalletObjectId(
-                    walletId,
-                    WalletObjectData.Types.PaymentRequest,
-                    paymentRequest.Id
-                );
-
-                var labelTuples = await walletRepository.GetWalletLabels(objectId);
-                labelsString = labelTuples.Any()
-                    ? string.Join(", ", labelTuples.Select(l => l.Label))
-                    : "None";
-
-                labelsCache[paymentRequest.Id] = labelsString;
-            }
+            labelsByPaymentRequestId.TryGetValue(paymentRequest.Id, out var labelTuples);
+            var labelsString = labelTuples is { Length: > 0 }
+                ? string.Join(", ", labelTuples.Select(l => l.Label))
+                : "";
 
             if (!invoicesByOrderId.TryGetValue(prOrderId, out var prInvoices) || prInvoices.Count == 0)
             {
@@ -172,13 +171,12 @@ public class PaymentRequestsReportProvider(
                     var paymentMethodId = payment.PaymentMethodId;
                     handlers.TryGetValue(paymentMethodId, out var handler);
 
-                    string paymentMethodCategory;
-                    if (handler is ILightningPaymentHandler)
-                        paymentMethodCategory = "Lightning";
-                    else if (handler is BitcoinLikePaymentHandler)
-                        paymentMethodCategory = "On-Chain";
-                    else
-                        paymentMethodCategory = paymentMethodId.ToString();
+                    var paymentMethodCategory = handler switch
+                        {
+                            ILightningPaymentHandler => "Lightning",
+                            BitcoinLikePaymentHandler => "On-Chain",
+                            _ => paymentMethodId.ToString()
+                        };
 
                     row.Add(paymentMethodCategory);
                     row.Add(paymentMethodId.ToString());
diff --git a/BTCPayServer/Services/WalletRepository.cs b/BTCPayServer/Services/WalletRepository.cs
index fc94441..4f948ac 100644
--- a/BTCPayServer/Services/WalletRepository.cs
+++ b/BTCPayServer/Services/WalletRepository.cs
@@ -324,6 +324,50 @@ namespace BTCPayServer.Services
             return labelObjects.Select(FormatToLabel).ToArray();
         }
 
+        public async Task<Dictionary<string, (string Label, string Color)[]>> GetWalletLabelsForObjects(
+            WalletId walletId,
+            string linkedType,
+            string[] objectIds)
+        {
+            if (objectIds.Length == 0)
+                return new Dictionary<string, (string Label, string Color)[]>();
+
+            await using var ctx = _ContextFactory.CreateContext();
+            var walletIdString = walletId.ToString();
+
+            var targetObjectIds = objectIds.Distinct().ToArray();
+
+            var rows = await
+                (from link in ctx.WalletObjectLinks.AsNoTracking()
+                    join labelObj in ctx.WalletObjects.AsNoTracking()
+                        on new { link.WalletId, link.AId }
+                        equals new { labelObj.WalletId, AId = labelObj.Id }
+                    where
+                        link.WalletId == walletIdString &&
+                        link.AType == WalletObjectData.Types.Label &&
+                        link.BType == linkedType &&
+                        labelObj.Type == WalletObjectData.Types.Label &&
+                        targetObjectIds.Contains(link.BId)
+                    select new
+                    {
+                        ObjectId = link.BId,
+                        LabelObj = labelObj
+                    })
+                .ToListAsync();
+
+            if (rows.Count == 0)
+                return new Dictionary<string, (string Label, string Color)[]>();
+
+            return rows
+                .GroupBy(r => r.ObjectId)
+                .ToDictionary(
+                    g => g.Key,
+                    g => g
+                        .Select(r => FormatToLabel(r.LabelObj))
+                        .ToArray()
+                );
+        }
+
         public async Task<List<ReservedAddress>> GetReservedAddressesWithDetails(WalletId walletId)
         {
             await using var ctx = _ContextFactory.CreateContext();