none
Linq to Objects - complex query question. RRS feed

  • Question

  • Hello fellow linq developers:

     

    I need hep  regarding the following issue :

    I have a colletion of items of this class:

     

    public class Booking_BO

       {

         public string Classroom {Get; Set;}           //  Consider (Classroom,StartDate) as the

         public DateTime StartDate {  Get; Set;  }  //  ‘Primary key’

            public DateTime EndDate         { Get; Set;  }

     }

     

        public class BookingsCollection : List< Booking_BO >

        {

            ...

        }

     

    I need to do the following query in linq-to-objects  (NOT Linq-To-SQL) :

     

    Give me a list of all the Classrooms that have been booked at least once during the last X days, with the start date and end date of the first time they were booked.

     

    For example, assume this data:

     

    Classroom           Start Date             End Date

       302-S                    11/10/2010         11/12/2010  

       302-S                    12/24/2010         12/26/2010

       302-S                    1/1/2011              1/7/2011

       1000                      12/30/2010         12/31/2010

       1000                      1/2/2010             1/11/2011

       101                         1/1/2011             1/3/2011

     

    If I run my query and provided that I wanted results for the last 60 days, this should be the result :

     

    Classroom           Start Date             EndDate

       302-S                    12/24/2010         12/26/2010

       1000                      12/30/2010         12/31/2010

       101                         1/1/2011             1/3/2011

     

    For me, it has been painful to figure out how to write that in linq,especially to get the EndDate because it depends on the startDate 'inside' this query.  But it took me 4 minutes to do the same in SQL:

     

                     SQL = string.Format(

                              "SELECT distinct dbo.Bookings.Classroom,dbo.Bookings.StartDate, " +

                              "dbo.Bookings.EndDate FROM dbo.Bookings INNER JOIN " +

                              "(SELECT dbo.Bookings.Classroom clsroom ,MIN(StartDate) mindate " +

                              "FROM dbo.Bookings " +

                              "WHERE StartDate > {0} " +

                              "GROUP BY Classroom) T " +

                              "ON dbo.Bookings.StartDate = T. mindate " +

                              "AND dbo.Bookings.Classroom = T. clsroom  ",

                              System.DateTime.Now.AddDays(numberofDays * -1));

     

    Any help is highly appreciated.

     

    Thanks!.

     

    Pedro.



    QPLQ
    Wednesday, February 23, 2011 2:16 PM

Answers

  • Hi Pedro;


    A query like this should give you what you need.


    public Form1()
    {
      InitializeComponent();
    
      bookingList.AddRange(new List<Booking_BO>() {new Booking_BO() { Classroom = "302-S", StartDate = DateTime.Parse("11/10/2010"), EndDate = DateTime.Parse("11/12/2010")},
                 new Booking_BO() { Classroom = "302-S", StartDate = DateTime.Parse("12/24/2010"), EndDate = DateTime.Parse("12/26/2010")},
                 new Booking_BO() { Classroom = "302-S", StartDate = DateTime.Parse("1/1/2011"), EndDate = DateTime.Parse("1/7/2011")},
                 new Booking_BO() { Classroom = "1000", StartDate = DateTime.Parse("12/30/2010"), EndDate = DateTime.Parse("12/31/2010")},
                 new Booking_BO() { Classroom = "1000", StartDate = DateTime.Parse("1/2/2010"), EndDate = DateTime.Parse("1/11/2011")},
                 new Booking_BO() { Classroom = "101", StartDate = DateTime.Parse("1/1/2011"), EndDate = DateTime.Parse("1/3/2011")}});
    
    }
    
    List<Booking_BO> bookingList = new List<Booking_BO>();
    
    private void button1_Click(object sender, EventArgs e)
    {
      int lastXDays = 60;
      DateTime today = DateTime.Today;
    
      var classrooms = from bk in bookingList
               where TimeSpan.FromTicks(today.Ticks - bk.StartDate.Ticks).Days <= lastXDays
               orderby bk.Classroom, bk.StartDate
               select bk;
    
      foreach (Booking_BO b in classrooms)
      {
        Console.WriteLine("{0}\t\t{1}\t{2}", b.Classroom, b.StartDate, b.EndDate);
      }
    }
    
    
    public class Booking_BO
    {
      public string Classroom {get; set;}
      public DateTime StartDate { get; set; }
      public DateTime EndDate { get; set; }
    }


    Fernando

     


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by PEDROQUINTERO Thursday, February 24, 2011 10:13 PM
    Wednesday, February 23, 2011 9:03 PM

All replies

  • Hi Pedro;


    A query like this should give you what you need.


    public Form1()
    {
      InitializeComponent();
    
      bookingList.AddRange(new List<Booking_BO>() {new Booking_BO() { Classroom = "302-S", StartDate = DateTime.Parse("11/10/2010"), EndDate = DateTime.Parse("11/12/2010")},
                 new Booking_BO() { Classroom = "302-S", StartDate = DateTime.Parse("12/24/2010"), EndDate = DateTime.Parse("12/26/2010")},
                 new Booking_BO() { Classroom = "302-S", StartDate = DateTime.Parse("1/1/2011"), EndDate = DateTime.Parse("1/7/2011")},
                 new Booking_BO() { Classroom = "1000", StartDate = DateTime.Parse("12/30/2010"), EndDate = DateTime.Parse("12/31/2010")},
                 new Booking_BO() { Classroom = "1000", StartDate = DateTime.Parse("1/2/2010"), EndDate = DateTime.Parse("1/11/2011")},
                 new Booking_BO() { Classroom = "101", StartDate = DateTime.Parse("1/1/2011"), EndDate = DateTime.Parse("1/3/2011")}});
    
    }
    
    List<Booking_BO> bookingList = new List<Booking_BO>();
    
    private void button1_Click(object sender, EventArgs e)
    {
      int lastXDays = 60;
      DateTime today = DateTime.Today;
    
      var classrooms = from bk in bookingList
               where TimeSpan.FromTicks(today.Ticks - bk.StartDate.Ticks).Days <= lastXDays
               orderby bk.Classroom, bk.StartDate
               select bk;
    
      foreach (Booking_BO b in classrooms)
      {
        Console.WriteLine("{0}\t\t{1}\t{2}", b.Classroom, b.StartDate, b.EndDate);
      }
    }
    
    
    public class Booking_BO
    {
      public string Classroom {get; set;}
      public DateTime StartDate { get; set; }
      public DateTime EndDate { get; set; }
    }


    Fernando

     


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by PEDROQUINTERO Thursday, February 24, 2011 10:13 PM
    Wednesday, February 23, 2011 9:03 PM
  • Thanks a lot Fernandoo.  I tested it and it does work.

    I do need to understand in more what causes the late entries to be excluded with this query...  very interesting.


    QPLQ
    Thursday, February 24, 2011 10:17 PM
  • Hi Pedro;

    I hope that this helps.

    var classrooms =
      // This iterates through ther list of bookingList
      from bk in bookingList
      // TimeSpan is the number of Ticks since the begining of time.
      // By subtracting today from the start date and applying the Days method we get
      // the total number of days between the two dates. Comparing that to the number
      // of days you want to look at, in this case 60, we can filter the results to the
      // dates we want.
      where TimeSpan.FromTicks(today.Ticks - bk.StartDate.Ticks).Days <= lastXDays
      // Order the results by Classroom then by date within each Classroom. Results
      // may not look sorted because numers are used in Classroom
      orderby bk.Classroom, bk.StartDate
      // Return the wanted results
      select bk;
    
    

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, February 24, 2011 10:39 PM