How can I shorten this linq to sql query?

Question

Hi

I am am making a calendar and to make it easier on myself I break up appointments that span over multiple weeks.

For instance Jan 1st to Jan 31st spans like 6 weeks(my calendar is always 42 cells - 6 by 7). So I would basically have 6 rows stored in my database.

However somethings I do require to me to put all these rows back together into one row. For instance if I want to export my calendar in Ical format.

I have a field in my database called bindingClassName all these rows get the same unquie id to that group of tasks so I am able to get all the weeks easily.

// get all of the task rows by binding class name.
            var found = plannerDb.Calendars.Where(u => u.UserId == userId && u.BindingClassName == bindingClassName)
                .GroupBy(u => u.BindingClassName);



            List<Calendar> allAppoingments = new List<Calendar>();

            // go through each of the results and add it to a list of calendars
            foreach (var group in found)
            {

                foreach (var row in group)
                {

                    Calendar appointment = new Calendar();
                    appointment.AppointmentId = row.AppointmentId;
                    appointment.AllDay = row.AllDay;
                    appointment.BindingClassName = row.BindingClassName;
                    appointment.Description = row.Description;
                    appointment.EndDate = row.EndDate;
                    appointment.StartDate = row.StartDate;
                    appointment.Title = row.Title;
                    appointment.Where = row.Where;
                    appointment.UserId = row.UserId;

                    allAppoingments.Add(appointment);
                }
            }
            // order 
           var test = allAppoingments.OrderBy(u => u.StartDate);

           var firstAppointment = test.First();
           var LastAppointment = test.Last();

           Calendar newAppointment = new Calendar();
           newAppointment.UserId = firstAppointment.UserId;
           newAppointment.Description = firstAppointment.Description;
           newAppointment.AllDay = firstAppointment.AllDay;
           newAppointment.StartDate = firstAppointment.StartDate;
           newAppointment.Title = firstAppointment.Title;
           newAppointment.Where = firstAppointment.Where;
           newAppointment.BindingClassName = firstAppointment.BindingClassName;
           newAppointment.EndDate = LastAppointment.EndDate;

            return newAppointment;

So basically that big blob finds all the appointments with the same binding name. Then I go through each one and make it into a Calendar object then finally once it is all made I get the first and last record to get the startDate and endDate.

So I am not good with linq but I am not sure if I can just add something after the groupBy to do what I want.

Edit.

I am trying group all my appointments together once I get all of them from the user.

So I have this so far

I tried something like this.

    var allApointments = calendar.GetAllAppointments(userId);
    var group = allApointments.GroupBy(u => u.BindingClassName).Select(u => new Calendar()).ToList

I was hoping that it would fill each group automatically but it does not. So I am not sure if don't need groupby again.

Edit @ admin

Hi thanks for explaining sorting and grouping. How you explained it though it seems either one would work.

Like the code you have for getting the first and last date works great and does what I wanted it to.

I think grouping might have worked because in the end though I am looking just to have one row that has the startdate of the first record and the end date of the last record all the other information would be the same.

So I don't know if it would harder to write that instead or what but like I said your query does what I want.

However that query is used on a single basis. Like I use that query only when a user clicks to view that appointment on my calendar. By clicking on the appointment I get all the information about that appointment and thats where I need to look at if that task spans over multiple days and figure out when the appointment started and when it is going to end.

Now I need another query and I think it would be better if I could actually group them as how I understand it from your explanation it will make one row. the reason I think this is because I want to export all the records in the table from that user.

So if I order them into one continues block by binding name I still going to need some loops that goes through all the records and gets the first and start date. So if I could just group it in one go and the final result would be just one record for each group of binding names and it would have the first start date and the last end date from the first and last record would be better.

Answer 1

Why are you grouping the appointments if you aren't actually using the group? It looks like you're just using them individually. In any case, you're already filtering the rows on a single value for BindingClassName in the Where clause, so you would only end up with 1 (or 0) group(s) anyway.

You can rewrite that series of foreach loops into a Select and ToList() like this:

var allAppointments = 
    plannerDb.Calendars.Where(
    row => row.UserId == userId && 
           row.BindingClassName == bindingClassName).OrderBy(
    row => row.StartDate).Select(
    row => new Calendar()
    {
        AppointmentId = row.AppointmentId,
        AllDay = row.AllDay,
        BindingClassName = row.BindingClassName,
        Description = row.Description,
        EndDate = row.EndDate,
        StartDate = row.StartDate,
        Title = row.Title,
        Where = row.Where,
        UserId = row.UserId
    }).ToList();

This will give you back the full list in the order you wanted. However, I'm curious why you're retrieving the whole list when it looks like you're only interested in the first and last appointment. You could instead do this:

var baseQuery = 
        plannerDb.Calendars.Where(
        row => row.UserId == userId && 
               row.BindingClassName == bindingClassName);

var first = baseQuery.OrderBy(row => row.StartDate).First();
var last = baseQuery.OrderByDescending(row => row.StartDate).Select(
           row => row.EndDate).First();

return new Calendar()
{
    AppointmentId = first.AppointmentId,
    AllDay = first.AllDay,
    BindingClassName = first.BindingClassName,
    Description = first.Description,
    EndDate = last,
    StartDate = first.StartDate,
    Title = first.Title,
    Where = first.Where,
    UserId = first.UserId
});

This should produce outputs that are the same as what you have now. I would question, however, if this is exactly what you want. Say you have two appointments:

• Appointment 1 starts January 5 and ends on January 10
• Appointment 2 starts January 6 and ends on January 7

Using this (and your) logic, you would get the end date as January 7, since Appointment 2 has the larger start date, but Appointment 1 actually ends later. I would recommend changing the second query to this:

var last = baseQuery.OrderByDescending(row => row.EndDate).Select(
           row => row.EndDate).First();

This will give you the largest end date, which I think is what you're actually after.

EDIT

I think you're making the (very common) mistake of confusing grouping with sorting. When you say you want to "group the appointments by the binding name", it sounds like you want a full, complete list of appointments, and you want those appointments arranged in such a way as all appointments with a particular binding name form a contiguous block. If that's the case, you want to order the list by the binding name, not group them. Grouping takes the whole list and produces one row per grouping clause and allows you to perform aggregation functions on the remaining columns. For example, let's say I group the appointments on the binding name. This means that my result set will contain one row per binding name, and I can then do things like find the maximum start or end date or something like that; more formally, you can specify aggregation operations, which are operations that take a set of data (i.e. a list of start dates) and return a single piece of data (i.e. the maximum start date).

Unless I'm misunderstanding, it sounds like you still want to retrieve all of the individual assignments, you just want them arranged by binding name. If this is the case, just OrderBy(row => row.BindingName) and it will do the trick. In addition, you may want to avoid using the word "group", as people will think you mean the sort of grouping that I described above.

Answer 2

Just as a side point not concerning the linq, have you looked at AutoMapper? I am currently using this for populating data objects from linq and I've found it really useful for getting rid of the large sections of code where you just map to dtos. It wouldn't make the query parts of your code any shorter but would reduce:

return new Calendar()
{
    AppointmentId = first.AppointmentId,
    AllDay = first.AllDay,
    BindingClassName = first.BindingClassName,
    Description = first.Description,
    EndDate = last,
    StartDate = first.StartDate,
    Title = first.Title,
    Where = first.Where,
    UserId = first.UserId
});

to:

return Mapper.Map(first,new Calendar{EndDate = last});