CODEWITHSUNDEEP
vb.netlinq
KJM
KJM

Reputation: 83

LINQ and Grouping by Week

I have a string field which contains a date which I cast as date. I need to group it using LINQ by week. My eventual goal is to have it as

YEAR
WEEK
BeginDateOfWeek (week starts on Monday)

BeginDateOfWeek would actually be used in the reports. Year and week are there to feed a function (not written yet but will be once I figure this out) which would determine the date information in BeginDateOfWeek.

However when I group by this date field as YEAR and WEEK with both functions called i I get:

YEAR=2020, WEEK= generated method

I did a for each and sure enough every week is listed as WEEK=generated method.

So I tried renaming the function (just spitballing on what is happening so I tried that) in the join clause to j and I get the error:

"Error BC36621 'Equals' cannot compare a value of type 'Function (i As Object) As Integer' with a value of type 'Function (j As Object) As Integer'."

So if anyone could help I would be so grateful. This is vb.net (c# solutions are fine) and NOT Linq To Entites but regular LINQ. I am really at wits end.

Dim query = From CCM In tbl1 Group CCM _
            By Year = CType(CCM.LCDATE, Date).Year,
            Week = (Function(i) System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(i.LCDATE, System.Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Monday)) Into grp = Group
            Group Join CCT In (From CCTS In tbl2
                               Group CCTS _
                               By Year1 = CType(CCTS.CCDATE, Date).Year, Week1 = (Function(j) System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(j.CCDATE, System.Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Monday))
                               Into grp1 = Group) 
            On Year Equals CCT.Year1 And Week Equals CCT.Week1
            Into Merged = Group From CCT In Merged.DefaultIfEmpty()
            Select New With {.YEAR = Year, .WEEK = Week, .MyTest = "")}

Upvotes: 1

Views: 298

Answers (2)

jdweng
jdweng

Reputation: 34421

I tested following code which starts on first Monday of the Year.

       Dim Year As Integer = 2020
        Dim jan1 As New DateTime(Year, 1, 1)
        Dim firstMonday As DateTime = jan1.AddDays((8 - jan1.DayOfWeek) Mod 7)
        Dim firstMondayDayOfYear As Integer = firstMonday.DayOfYear

        Dim days As List(Of DateTime) = Enumerable.Range(0, 365).Select(Function(x) firstMonday.AddDays(x)).ToList()
        Dim weeks = days.GroupBy(Function(x) CType((x.DayOfYear - firstMondayDayOfYear) / 7, Integer)).ToList()

I would create a function which can be called inside a linq

    Function GetWeek(day As DateTime) As Integer
        Dim Year As Integer = day.Year
        Dim jan1 As New DateTime(Year, 1, 1)
        Dim firstMonday As DateTime = jan1.AddDays((8 - jan1.DayOfWeek) Mod 7)
        Dim firstMondayDayOfYear As Integer = firstMonday.DayOfYear

        Dim week = CType((day.DayOfYear - firstMondayDayOfYear) / 7, Integer)
        Return week
    End Function

Upvotes: 2

pwilcox
pwilcox

Reputation: 5753

Just as the error says, 

(Function(i) System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(i.LCDATE, System.Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Monday))

and its counterpart are functions.

You need to actually execute those functions:

(Function(i) System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(i.LCDATE, System.Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Monday))(CType(CCM.LCDATE, Date))

Or, preferably, define that function elsewhere and then use it in your query:

Function getWeekOfYear (str As String) As Integer

    Return System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(
        CType(str, Date), 
        System.Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Monday
    )

End Function


Dim query = 
    From CCM In tbl1 
    Group CCM By 
        Year = CType(CCM.LCDATE, Date).Year,
        Week = getWeekOfYear(CCM.LCDATE) Into grp = Group
        Group Join CCT In (
            From CCTS In tbl2
            Group CCTS By 
                Year1 = CType(CCTS.CCDATE, Date).Year, 
                Week1 = getWeekOfYear(CCTS.CCDATE)
                Into grp1 = Group
        ) 
        On Year Equals CCT.Year1 And Week Equals CCT.Week1
        Into Merged = Group 
    From CCT In Merged.DefaultIfEmpty()
    Select New With {.YEAR = Year, .WEEK = Week, .MyTest = ""}

Upvotes: 1

Related Questions