License report

public async Task<List<LicenseReportRow>> GetLicenseReport(int? vendorUserId)
        {
            var vendorId = vendorUserId.HasValue ? (await _dbContext.VendorUsers.FirstAsync(vu => vu.Id == vendorUserId)).VendorId : (int?)null;

            var userBackupSizesQuery = from userBackup in _dbContext.UserBackup.AsNoTracking()
                                       join user in _dbContext.Users.AsNoTracking() on userBackup.UserId equals user.Id
                                       join organization in _dbContext.Organization.AsNoTracking() on user.OrganizationId equals organization.Id
                                       group userBackup by organization.Id into g
                                       select new { Id = g.Key, Total = g.Sum(b => b.TotalSizeOnStorage + b.TotalSizeOnStorageTeams) };
            var enabledUsers = (await (from userBackup in _dbContext.UserBackup.AsNoTracking()
                                       join user in _dbContext.Users.AsNoTracking() on userBackup.UserId equals user.Id
                                       join organization in _dbContext.Organization.AsNoTracking() on user.OrganizationId equals organization.Id
                                       where userBackup.Enabled
                                       select new { organization.Id, userBackup }).ToListAsync()).GroupBy(ub => ub.Id).ToDictionary(g => g.Key, g => g.Select(u => u.userBackup.UserId).Distinct().Count());
            var spaceQuery = from organizationBackup in _dbContext.OrganizationBackup.AsNoTracking()
                             join organization in _dbContext.Organization.AsNoTracking() on organizationBackup.OrganizationId equals organization.Id
                             group organizationBackup by organization.Id into g
                             select new { Id = g.Key, Size = g.Sum(b => b.TotalSizeOnStorage + b.TotalSizeOnStorageTeams) };

            var spaceLicenses = from license in await _dbContext.Licenses.AsNoTracking().ToListAsync()
                                group license by license.OrganizationId into g
                                select new { Id = g.Key, Space = g.Sum(l => l.LicenseType == "O365 user" ? int.TryParse(l.NumberOfLicenses, out var num) ? num * 50 : 0 : int.TryParse(l.TotalDiskSpace, out var space) ? space : 0) };

            var query = from userSize in userBackupSizesQuery
                        join userLicenses in _dbContext.Licenses.AsNoTracking().Where(l => l.LicenseType == "O365 user") on userSize.Id equals userLicenses.OrganizationId into sub
                        from subLicense in sub.DefaultIfEmpty()
                        join organization in _dbContext.Organization.AsNoTracking() on userSize.Id equals organization.Id
                        join vendorOrganizations in _dbContext.VendorOrganizations.AsNoTracking() on organization.Id equals vendorOrganizations.OrganizationId into sub2
                        from subVendorOrganization in sub2.DefaultIfEmpty()
                        join vendor in _dbContext.Vendors.AsNoTracking() on subVendorOrganization.VendorId equals vendor.Id into sub3
                        from subVendor in sub3.DefaultIfEmpty()
                        join organizationSize in spaceQuery on organization.Id equals organizationSize.Id
                        where (!vendorId.HasValue || subVendor.Id == vendorId) && organization.Active
                        select new LicenseReportRow
                        {
                            Id = userSize.Id,
                            UsedSpaceGB = (userSize.Total + organizationSize.Size) / 1e9,
                            Customer = organization.CustomerName,
                            Reseller = subVendor == null ? "" : subVendor.Name,
                            Domain = organization.Domain,
                            UserLicenses = subLicense == null || string.IsNullOrEmpty(subLicense.NumberOfLicenses) ? 0 : int.Parse(subLicense.NumberOfLicenses)
                        };

            var rows = await query.ToListAsync();

            foreach (var row in rows)
            {
                row.EnabledUsers = enabledUsers.ContainsKey(row.Id) ? enabledUsers[row.Id] : 0;
                row.LicenseSpaceGB = spaceLicenses.FirstOrDefault(s => s.Id == row.Id)?.Space ?? 0;
                row.ExceedByGB = row.UsedSpaceGB - row.LicenseSpaceGB;
                row.Exceed = row.ExceedByGB > 0.01;
                row.ExceedByPercent = row.LicenseSpaceGB > 0.01 ? row.ExceedByGB / row.LicenseSpaceGB : 0;
            }

            return rows;
        }