using Binus.WS.Pattern.Entities;
using Microsoft.EntityFrameworkCore.Metadata;
using SSG5.C1.BSQWebAPI.Model;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using Microsoft.Extensions.Logging;

namespace SSG5.C1.BSQWebAPI.Helper
{
    public class ElectricHistoryHelper
    {
        private static readonly ILogger<ElectricHistoryHelper> _logger = LoggerFactory.Create(builder => builder.AddConsole()).CreateLogger<ElectricHistoryHelper>();

        public static List<ElectricHistory> GetElectricHistory(ElectricHistory model, string binusianId)
        {
            // Fetch the active period ID
            var PeriodActive = EntityHelper.Get<MsPeriod>(t => t.AuditedActivity != "D" && t.ActivePeriod == true)
                                           .Select(t => t.PeriodId)
                                           .FirstOrDefault();

            _logger.LogInformation($"Active Period ID: {PeriodActive}");

            using (var context = new EntityContext<ElectricHistory, TrRoomAllocation, TrBoarderRegistration>())
            {
                DateTime? startDate = DateTime.ParseExact(model.StartDate, "MM-dd-yyyy", CultureInfo.InvariantCulture);
                DateTime? endDate = DateTime.ParseExact(model.EndDate, "MM-dd-yyyy", CultureInfo.InvariantCulture);

                _logger.LogInformation($"Fetching data for BinusianId: {binusianId}, StartDate: {startDate}, EndDate: {endDate}");

                // Fetch data without transformations
                var electricHistories = context.Set<ElectricHistory>()
                    .Where(e => e.Tanggal >= startDate && e.Tanggal <= endDate)
                    .ToList();

                _logger.LogInformation($"Fetched {electricHistories.Count} electricHistories");

                var roomAllocations = context.Set<TrRoomAllocation>()
                    .Where(r => r.AuditedActivity != "D" && r.PeriodId == PeriodActive)
                    .ToList();

                _logger.LogInformation($"Fetched {roomAllocations.Count} roomAllocations");

                var boarderRegistrations = context.Set<TrBoarderRegistration>()
                    .Where(b => b.BinusianId == binusianId)
                    .ToList();

                _logger.LogInformation($"Fetched {boarderRegistrations.Count} boarderRegistrations");

                // Log room allocations and boarder registrations
                foreach (var r in roomAllocations)
                {
                    _logger.LogInformation($"RoomAllocation - RoomNo: {r.RoomNo}, RegistrationId: {r.RegistrationId}");
                }

                foreach (var b in boarderRegistrations)
                {
                    _logger.LogInformation($"BoarderRegistration - BinusianId: {b.BinusianId}, RegistrationId: {b.RegistrationId}");
                }

                // Perform the join and transformation in memory
                var result = (from e in electricHistories
                              let transformedKete = e.Kete.Substring(e.Kete.Length - 7).Replace("-", ".")
                              join r in roomAllocations on transformedKete equals r.RoomNo
                              join b in boarderRegistrations on r.RegistrationId equals b.RegistrationId
                              orderby e.Tanggal descending
                              select new
                              {
                                  ElectricHistory = e,
                                  TransformedKete = transformedKete,
                                  RoomNo = r.RoomNo,
                                  RegistrationId = r.RegistrationId
                              })
                              .Skip((model.Page - 1) * model.PageSize)
                              .Take(model.PageSize)
                              .ToList();

                // okay so this part isn't returning because ganemu results trus berarti di result nya ada salah join atau ga, ga nemu apa2
                foreach (var item in result)
                {
                    _logger.LogInformation($"Matched Record - Kete: {item.ElectricHistory.Kete}, TransformedKete: {item.TransformedKete}, RoomNo: {item.RoomNo}, RegistrationId: {item.RegistrationId}");
                }

                _logger.LogInformation($"Retrieved {result.Count} records");

                return result.Select(item => item.ElectricHistory).ToList();
            }
        }

        public static int GetElectricHistoryCount(string binusianId, string startDate, string endDate)
        {
            var PeriodActive = EntityHelper.Get<MsPeriod>(t => t.AuditedActivity != "D" && t.ActivePeriod == true)
                                .Select(t => t.PeriodId)
                                .FirstOrDefault();

            _logger.LogInformation($"Active Period ID: {PeriodActive}");

            using (var context = new EntityContext<ElectricHistory, TrRoomAllocation, TrBoarderRegistration>())
            {
                DateTime? startDateParsed = DateTime.ParseExact(startDate, "MM-dd-yyyy", CultureInfo.InvariantCulture);
                DateTime? endDateParsed = DateTime.ParseExact(endDate, "MM-dd-yyyy", CultureInfo.InvariantCulture);

                _logger.LogInformation($"Counting data for BinusianId: {binusianId}, StartDate: {startDateParsed}, EndDate: {endDateParsed}");

                // Fetch data without transformations
                var electricHistories = context.Set<ElectricHistory>()
                    .Where(e => e.Tanggal >= startDateParsed && e.Tanggal <= endDateParsed)
                    .ToList();

                _logger.LogInformation($"Fetched {electricHistories.Count} electricHistories");

                var roomAllocations = context.Set<TrRoomAllocation>()
                    .Where(r => r.AuditedActivity != "D" && r.PeriodId == PeriodActive)
                    .ToList();

                _logger.LogInformation($"Fetched {roomAllocations.Count} roomAllocations");

                var boarderRegistrations = context.Set<TrBoarderRegistration>()
                    .Where(b => b.BinusianId == binusianId)
                    .ToList();

                _logger.LogInformation($"Fetched {boarderRegistrations.Count} boarderRegistrations");

                // Log room allocations and boarder registrations
                foreach (var r in roomAllocations)
                {
                    _logger.LogInformation($"RoomAllocation - RoomNo: {r.RoomNo}, RegistrationId: {r.RegistrationId}");
                }

                foreach (var b in boarderRegistrations)
                {
                    _logger.LogInformation($"BoarderRegistration - BinusianId: {b.BinusianId}, RegistrationId: {b.RegistrationId}");
                }

                // Perform the join and transformation in memory
                var count = (from e in electricHistories
                             let transformedKete = e.Kete.Substring(e.Kete.Length - 7).Replace("-", ".")
                             join r in roomAllocations on transformedKete equals r.RoomNo
                             join b in boarderRegistrations on r.RegistrationId equals b.RegistrationId
                             select e)
                             .Count();

                _logger.LogInformation($"Total records count: {count}");

                return count;
            }
        }
    }
}

This is my helper file that basically retrieves data from 3 tables, and joins it to get the desired columns, and returns it, but in the logging kept on logging 0 records.

My guess is somethings wrong when joining the tables, but I still don’t know how to fix it. Any help would be very much appreciated, thank you!

Initially I tried using the Substring and Replace LINQ functions directly in the LINQ to entities, but I tried asking GPT on an error before, and it said it was because using LINQ functions like those directly in the LINQ to Entities won’t work because it isn’t supported and it suggested to do the operations in memory, then it can work, but this is the wall that I’ve been hitting for the last 2 days.

foreach (var item in result)
{
    _logger.LogInformation($"Matched Record - Kete: {item.ElectricHistory.Kete}, TransformedKete: {item.TransformedKete}, RoomNo: {item.RoomNo}, RegistrationId: {item.RegistrationId}");
}

This is the console logs if needed:

RoomAllocation - RoomNo: C.04.26, RegistrationId: 222e6978-de5f-4766-b55c-acfe298aba53
info: SSG5.C1.BSQWebAPI.Helper.ElectricHistoryHelper[0]

BoarderRegistration - BinusianId: BN123481406, RegistrationId: ec40a5bc-f735-485a-a67c-efff6a25dcf5
info: SSG5.C1.BSQWebAPI.Helper.ElectricHistoryHelper[0]

Retrieved 0 records