none
How can I group this collection with linq? RRS feed

  • Question

  • Hi

    This probably could be made into one query but I am not sure how to make it into 2 queries let alone one.

    What I have is a linq to sql statement that gets all the users records from a table.
    // get all records
    return ContextDB.Calendars.Where(u => u.UserId == userId).ToList();

    Now I want to take this list of Calendars and group them by a column in the database.

    So say I have these fields

    Id<pk>
    startDate
    endDate
    bindingName

    I want to group all the calendars by binding name.

    So I could have this

    Record 1

    id = 1
    startDate = 01/01/2010  mm/dd/yyyy
    endDate = 01/02/2010
    bindingName = 1

    Record 2

    id = 2
    startDate = 01/03/2010
    endDate = 01/09/2010
    bindingName = 1

    Record 3
     id = 3
    startDate = 01/03/2010
    endDate = 01/05/2010
    bindingName = 2.

    so I want to group  record 1 and 2 together by binding name. Or merge them some how. Since I want to take the startDate of record 1 and the end date of record to so I would like to have a new Calendar object that has

    startDate = 01/01/2010
    endDate = 01/09/2010
    bindingName = 1

    then since record to has nothing it can bind too just leave. it. In the end I would have a new collection of Calendar object but instead of having 3 records I would have just one record since the first too where merged together.
    Thursday, January 7, 2010 2:36 AM

Answers


  • You want to use "group by" such as the following:


                    var myCalendars = from c in ContextDB.Calendars.Where(u => u.UserID == userId).ToList()
                                      orderby c.startDate
                                      group c by c.bindingName into cGroup
                                      select new
                                      {
                                          bindingName = cGroup.Key,
                                          startDate = cGroup.Min(g => g.startDate),
                                          endDate = cGroup.Max(g => g.endDate),
                                          Calendars = cGroup
                                      };
    
    • Marked as answer by chobo3 Saturday, January 9, 2010 11:01 PM
    Thursday, January 7, 2010 1:58 PM

  • Here is a sample loop you might use if you wanted to walk through your data:

                    foreach (var g in myCalendars)
                    {
                        var groupBindingName = g.bindingName;
                        var groupStartDate = g.startDate;
                        var groupEndDate = g.endDate;
                        foreach (Calendar c in g.Calendars)
                        {
                            var calStartDate = c.startDate;
                            var calEndDate = c.endDate;
                            // Do whatever you like here...
                        }
                    }
    

    As for Title... We'd need more information about how/where the title is stored and how you would get it.  In the above example, we defined a value of start date and end date based on all the elements of the group (min/max).  You could use a similar approach for your title but we'd need more details.
    • Marked as answer by chobo3 Saturday, January 9, 2010 11:01 PM
    Thursday, January 7, 2010 10:02 PM
  • Just use First()


                    var myCalendars = from c in ContextDB.Calendars.Where(u => u.UserID == userId).ToList()
                                      orderby c.startDate
                                      group c by c.bindingName into cGroup
                                      select new
                                      {
                                          bindingName = cGroup.Key,
                                          startDate = cGroup.Min(g => g.startDate),
                                          endDate = cGroup.Max(g => g.endDate),
                                          title = cGroup.First().title,
                                          Calendars = cGroup
                                      };
    
    

    • Marked as answer by chobo3 Saturday, January 9, 2010 11:01 PM
    Friday, January 8, 2010 7:22 PM

All replies


  • You want to use "group by" such as the following:


                    var myCalendars = from c in ContextDB.Calendars.Where(u => u.UserID == userId).ToList()
                                      orderby c.startDate
                                      group c by c.bindingName into cGroup
                                      select new
                                      {
                                          bindingName = cGroup.Key,
                                          startDate = cGroup.Min(g => g.startDate),
                                          endDate = cGroup.Max(g => g.endDate),
                                          Calendars = cGroup
                                      };
    
    • Marked as answer by chobo3 Saturday, January 9, 2010 11:01 PM
    Thursday, January 7, 2010 1:58 PM

  • You want to use "group by" such as the following:


                    var
    
    
     myCalendars = from
    
    
     c in
    
    
     ContextDB.Calendars.Where(u => u.UserID == userId).ToList()
                                      orderby
    
    
     c.startDate
                                      group
    
    
     c by
    
    
     c.bindingName into
    
    
     cGroup
                                      select
    
    
     new
    
    
    
                                      {
                                          bindingName = cGroup.Key,
                                          startDate = cGroup.Min(g => g.startDate),
                                          endDate = cGroup.Max(g => g.endDate),
                                          Calendars = cGroup
                                      };
    


    What is Calendars = cGroup do?
    Thursday, January 7, 2010 4:39 PM

  • cGroup is the collection of Calendar objects that make up the group.  I set the property name to "Calendars" just to make it more clear.  In your example, the result would contain two objects, and the Calendars property of the first object would contain two calendar objects (each with bindingName = 1), and the 2nd would contain one calendar object (with bindingName = 2).
    Thursday, January 7, 2010 8:56 PM

  • cGroup is the collection of Calendar objects that make up the group.  I set the property name to "Calendars" just to make it more clear.  In your example, the result would contain two objects, and the Calendars property of the first object would contain two calendar objects (each with bindingName = 1), and the 2nd would contain one calendar object (with bindingName = 2).
    I sort of see what it is doing that it is like holding all the records of the group but I am not sure what I would do with it. When I look at it through the debugger it is like a List<string,Calendar> objects.

    I am not sure what I would do with this would I use a foreach loop to extract the data or something?

    Also say I have another field in my database that I want to get. Say it is the title field. How would do the new statement for that? Like you have

      startDate = cGroup.Min(g => g.startDate),
    endDate = cGroup.Max(g => g.endDate),
    How would a string one look?

    title = cGroup.Select( g => g.Title)?

    All titles will be the same.

    So


    id = 1
    startDate = 01/01/2010  mm/dd/yyyy
    endDate = 01/02/2010
    bindingName = 1
    Title = "Number 1"

    Record 2

    id = 2
    startDate = 01/03/2010
    endDate = 01/09/2010
    bindingName = 1
    Title = "Number 1"

    Record 3
     id = 3
    startDate = 01/03/2010
    endDate = 01/05/2010
    bindingName = 2.
    Title = "Number 2"

    So the result would be

    startDate = 01/01/2010
    endDate = 01/09/2010
    bindingName = 1
    Title = "Number 1"
    Thursday, January 7, 2010 9:28 PM

  • Here is a sample loop you might use if you wanted to walk through your data:

                    foreach (var g in myCalendars)
                    {
                        var groupBindingName = g.bindingName;
                        var groupStartDate = g.startDate;
                        var groupEndDate = g.endDate;
                        foreach (Calendar c in g.Calendars)
                        {
                            var calStartDate = c.startDate;
                            var calEndDate = c.endDate;
                            // Do whatever you like here...
                        }
                    }
    

    As for Title... We'd need more information about how/where the title is stored and how you would get it.  In the above example, we defined a value of start date and end date based on all the elements of the group (min/max).  You could use a similar approach for your title but we'd need more details.
    • Marked as answer by chobo3 Saturday, January 9, 2010 11:01 PM
    Thursday, January 7, 2010 10:02 PM

  • Here is a sample loop you might use if you wanted to walk through your data:

                    foreach
     (var
     g in
     myCalendars)
                    {
                        var
     groupBindingName = g.bindingName;
                        var
     groupStartDate = g.startDate;
                        var
     groupEndDate = g.endDate;
                        foreach
     (Calendar c in
     g.Calendars)
                        {
                            var
     calStartDate = c.startDate;
                            var
     calEndDate = c.endDate;
                            // Do whatever you like here...
    
                        }
                    }
    
    

    As for Title... We'd need more information about how/where the title is stored and how you would get it.  In the above example, we defined a value of start date and end date based on all the elements of the group (min/max).  You could use a similar approach for your title but we'd need more details.
    Hi

    the title is storedi n the datbase just like bindingname, id, start and end date.  The title would be with the list and grabbed by this query

    return
     ContextDB.Calendars.Where(u => u.UserId == userId).ToList();
    So it is in the calendars object there is a string field called Title.

    I was looking at trying to do the same sort of thing like min/max but I don't see any built in method that I could just get this title.
    Thursday, January 7, 2010 10:38 PM

  • If the title comes from the Calendar object, then you need to decide what title you want to use for the group.  Do all of the Calendar objects with the same bindingName all have the same title?
    Friday, January 8, 2010 12:52 PM

  • If the title comes from the Calendar object, then you need to decide what title you want to use for the group.  Do all of the Calendar objects with the same bindingName all have the same title?

    Yes all the calendar objects that have a bindingName will have the same title.  I am not sure how to to choose from what group to use the title from. Right now I made a foreach loop within a foreach loop that grabs the first title of that group and then breaks out of that loop however I rather find a better way.
    Friday, January 8, 2010 7:01 PM
  • Just use First()


                    var myCalendars = from c in ContextDB.Calendars.Where(u => u.UserID == userId).ToList()
                                      orderby c.startDate
                                      group c by c.bindingName into cGroup
                                      select new
                                      {
                                          bindingName = cGroup.Key,
                                          startDate = cGroup.Min(g => g.startDate),
                                          endDate = cGroup.Max(g => g.endDate),
                                          title = cGroup.First().title,
                                          Calendars = cGroup
                                      };
    
    

    • Marked as answer by chobo3 Saturday, January 9, 2010 11:01 PM
    Friday, January 8, 2010 7:22 PM
  • Just use First()


                    var
     myCalendars = from
     c in
     ContextDB.Calendars.Where(u => u.UserID == userId).ToList()
                                      orderby
     c.startDate
                                      group
     c by
     c.bindingName into
     cGroup
                                      select
     new
    
                                      {
                                          bindingName = cGroup.Key,
                                          startDate = cGroup.Min(g => g.startDate),
                                          endDate = cGroup.Max(g => g.endDate),
                                          title = cGroup.First().title,
                                          Calendars = cGroup
                                      };
    
    
    

    Ah. I tried to do it cGroup.First(u => u.title) and it always wanted a bool. Had no clue it did not need any parameters.
    Friday, January 8, 2010 9:44 PM
  • One more thing what other methods can you do that. Like I would have never guess your could do that

       title = cGroup.First().title,



    Like what other things can you do .Something().Yourfield.
    Saturday, January 9, 2010 11:02 PM