CODEWITHSUNDEEP
vb.netlinqlambda
user1477388
user1477388

Reputation: 21430

Advanced LINQ/ Lambda Expression

I have this model:

Public Class Tbl_Exercise

    <Key()> Public Property Exercise_ID() As Integer
    Public Property Exercise_Employee_ID() As Integer
    Public Property Exercise_Create_Date() As Date
    <ForeignKey("Tbl_Exercise_Type")> _
    Public Property Exercise_Type_ID() As Integer
    Public Property Exercise_Duration() As Integer

    Public Overridable Property Tbl_Exercise_Type As Tbl_Exercise_Type

End Class

I need to get the sum of the Exercise_Duration for each week of the year. I need to then check if the sum for the week is greater than or equal to 150. If it is, I need to +1 another variable (a count). The goal is to display this:

# of weeks you've reached 150: X out of Z

(Where X is the count of weeks greater than or equal to 150 and Z is equal to the total number of weeks in the current year.)

Final

    ' get number of weeks the exercise goal was reached (greater than or equal to the goal)
    Dim exerciseDb = New ExerciseDbContext
    Dim exercise = exerciseDb.Tbl_Exercises.Where(Function(x) x.Exercise_Employee_ID = empId)


    Dim weeks = exercise.ToList.GroupBy(Function(x) CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(x.Exercise_Create_Date, CalendarWeekRule.FirstDay, DayOfWeek.Sunday))

    Dim totalWeeks = 0

    For Each week In weeks

        Dim sum = week.Sum(Function(x) x.Exercise_Duration)

        If sum > 150 Then

            totalWeeks += 1

        End If

    Next

    Debug.Print("over150: " + totalWeeks.ToString)

Upvotes: 0

Views: 1593

Answers (2)

Wasp
Wasp

Reputation: 3425

using System.Globalization;

DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
Calendar cal = dfi.Calendar;

var recap = 
    (from e in exercises
     group e by cal.GetWeekOfYear(e.Exercise_Create_Date, 
                                  dfi.CalendarWeekRule, 
                                  dfi.FirstDayOfWeek) 
     into g
     select new 
     { 
        g.Key, 
        Total = g.Sum(x => x.Exercise_Duration) 
     } 
     into p 
     where p.Total > 150
     select p)
    .Count();

Upvotes: 2

Joey Gennari
Joey Gennari

Reputation: 2361

Here is an example in C#:

public class Exercise
{
    public DateTime CreateDate { get; set; }
    public int Duration { get; set; }
}

class Program
{
    static void Main()
    {
        Exercise[] ex = new Exercise[]
        {
            new Exercise { CreateDate = DateTime.Parse("1/1/2012"), Duration = 160 },
            new Exercise { CreateDate = DateTime.Parse("1/8/2012"), Duration = 160 },
            new Exercise { CreateDate = DateTime.Parse("1/15/2012"), Duration = 160 },
            new Exercise { CreateDate = DateTime.Parse("2/1/2012"), Duration = 100 },
            new Exercise { CreateDate = DateTime.Parse("3/1/2012"), Duration = 75 },
            new Exercise { CreateDate = DateTime.Parse("3/1/2012"), Duration = 80 }
        };

        var weeks = ex.GroupBy(x => CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(x.CreateDate, CalendarWeekRule.FirstDay, DayOfWeek.Sunday));
        int currentweek = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(DateTime.Now, CalendarWeekRule.FirstDay, DayOfWeek.Sunday);
        int over150 = weeks.Where(group => group.Sum(item => item.Duration) > 150).Count();
        Console.WriteLine(String.Format("# of weeks you've reached 150: {0} out of {1}", over150, currentweek));
    }

}

Upvotes: 1

Related Questions