Consolidating a List of Dates with 2 columns being equal

Question

I have a List of that comes from a sql table, This is what the model looks like below:

public class ReservationReport
    {
        public string HotelName { get; set; }
        public string First_Name { get; set; }
        public string Last_Name { get; set; }
        public string Gender { get; set; }
        public DateTime Arrive { get; set; }
        public DateTime AMDepart { get; set; }
        public int companypay { get; set; }
        public int selfpay { get; set; }
        public int guestID { get; set; }
        public string RoomType { get; set; }
        public int Inventory_ID { get; set; }
        public int HotelID { get; set; }
    }

The list is currently all separated by each day. I need to combine it in a way that shows Hotel and which members stayed by how many days for example:

List coming in from sql will look like this

• -Hotel 1 John smith, Feb 1 - Feb 2

• -Hotel 1 John smith, Feb 2 - Feb 3

• -Hotel 1 Someone Else, Feb 1 - Feb2

• -Hotel 1 John smith , Feb 10- Feb 11

List should show something like this:

• Hotel 1 John smith, Feb 1- Feb3 (Stayed 2 days)
• Hotel 1 Someone else, Feb 1- Feb2 (stayed 1 day)
• Hotel 1 John Smith, Feb10- Feb11 (Stayed 1 day)

What I have currently is the following code. The problem I have is when a person has stayed in the same hotel but on days outside of touching they are only put once in the combined list. This is what I have currently

CombinedList = temp2.GroupBy(m => new { m.guestID, m.HotelID }).Select(group => group.First()).ToList();
                foreach (var item in CombinedList)
                {
                    // disposable list to get values for only the correct records 
                    List<ReservationReport> dispose = temp2.Where(x => x.HotelID == item.HotelID && x.guestID  == item.guestID ).ToList();
                    item.Arrive = dispose.Min(i => i.Arrive);
                    item.AMDepart = dispose.Max(i => i.AMDepart);
                    item.selfpay = dispose.Sum(i => i.selfpay);
                    item.companypay = dispose.Sum(i => i.companypay);
                }

After I run the above code my Combined List looks like the below

• Hotel 1 Someone else, Feb 1- Feb2 (stayed 1 day)
• Hotel 1 John Smith, Feb1- Feb11 (Stayed 10 day)

My combinedList call is only giving me 2 rows when it should contain 3, the problem is I am not sure how to lambda or linq with the 2 variables and where the dates are next to each other in this manner.

any help would be appreciated. Thank you.

Answer 1

I modified the codes you've shown in your controller,and it seems work well:

public IActionResult Index()
        {
            var reservationreport1 = new ReservationReport()
            {
                Report_ID = 1,
                guestID = 1,
                Name = "a1",
                HotelID = 1,
                HotelName = "h1",
                Arrive = new DateTime(2021, 1, 1),
                AMDepart = new DateTime(2021, 1, 2)
            };
            var reservationreport2 = new ReservationReport()
            {
                Report_ID = 2,
                guestID = 1,
                Name = "a1",
                HotelID = 1,
                HotelName = "h1",
                Arrive = new DateTime(2021, 1, 2),
                AMDepart = new DateTime(2021, 1, 3)
            };
            var reservationreport3 = new ReservationReport()
            {
                Report_ID = 3,
                guestID = 1,
                Name = "a1",
                HotelID = 1,
                HotelName = "h1",
                Arrive = new DateTime(2021, 1, 3),
                AMDepart = new DateTime(2021, 1, 4)
            };

            var reservationreport4 = new ReservationReport()
            {
                Report_ID = 4,
                guestID = 2,
                Name = "a2",
                HotelID = 1,
                HotelName = "h1",
                Arrive = new DateTime(2021, 1, 4),
                AMDepart = new DateTime(2021, 1, 5)
            };
            var reservationreport5 = new ReservationReport()
            {
                Report_ID = 5,
                guestID = 1,
                Name = "a1",
                HotelID = 1,
                HotelName = "h1",
                Arrive = new DateTime(2021, 1, 6),
                AMDepart = new DateTime(2021, 1, 7)
            };
            var reportlist = new List<ReservationReport>() { };
            reportlist.Add(reservationreport1);
            reportlist.Add(reservationreport2);
            reportlist.Add(reservationreport3);
            reportlist.Add(reservationreport4);
            reportlist.Add(reservationreport5);
            var templist = new List<ReservationReport>();
            reportlist.ForEach(m => { templist.Add(m); });
            foreach (var item in reportlist)
            {
                Ressetlist(templist, item);
            }
            var targetlist = templist.OrderBy(m => m.guestID).ThenBy(m => m.HotelID).ThenBy(m => m.Report_ID).ToList();
            return View();
        }




public List<ReservationReport> Ressetlist(List<ReservationReport> reservationreports, ReservationReport item)
        {
            List<ReservationReport> dispose = reservationreports.Where(x => x.HotelID == item.HotelID && x.guestID == item.guestID).ToList();
            var tempitem = dispose.FirstOrDefault(m => m.Arrive == item.AMDepart);
            if(tempitem!=null)
            {
                reservationreports.Remove(item);
                reservationreports.Remove(tempitem);
                item.AMDepart = tempitem.AMDepart;
                reservationreports.Add(item);
                Ressetlist( reservationreports,item);
            }
            return reservationreports;
        }

Result?