How to GroupBy a part of DateTime using System.Dynamic.LINQ library

Question

I have tables that contain a lot of rows, each record is excellent by whom and on what date was added. I want to group all records, based on AddedBy and AddedOn fields.

The problem is that the dates are in full including the miliseconds and I only want to group by day, month or year.

I hereby noted that at Compilation time I do not know which table it is and Therefore I use with System.Dynamic.LINQ library.

Below I demonstrate how did a grouping by datetime using System.Dynamic.LINQ library:

Dim groupColl= myColl.GroupBy("new(AddedName, AddedOn), it").Select("new(Key.AddedName, Key.AddedOn, Count() AS Count)")

But the problem is that I need to grouping by day, month or year.

In sql server I found how to do it, by day or by month or by year. In Lambda Expression also found the way how to do it, but only by day.

But through System.Dynamic.LINQ not find any way how to do it.

Below I demonstrate how I'm doing this in sql server and Lambda Expression:

Using SQL SERVER:

SELECT AddedBy, DATEADD(DAY, DATEDIFF(DAY, 0, AddedOn), 0) as AddedOn, count(*)
FROM myTable
GROUP BY AddedBy, DATEADD(DAY, DATEDIFF(DAY, 0, AddedOn), 0)

Using Lambda Expression vb.net code

Dim groupColl = myCollection.GroupBy(Function(x) New With {Key x.AddedBy, Key .AddedeOn_Day = DbFunctions.TruncateTime(x.AddedOn)}).Select(Function(y) New With {y.Key.AddedBy, y.Key.AddedeOn_Day, y.Count})

I would be grateful to anyone who would help me how to do it using System.Dynamic.LINQ Library

Thanks

Can't you use DatePart(DateInterval.Month, YourDate) or any DateInteveal to do so? — Minus, Aug 04 '15 at 18:55
Thank you for your response. It is not a solution for me because the DatePart(DateInterval.Month, YourDate), returns Integer, and I need Date. — Haim Tabak, Aug 05 '15 at 09:16

score 0 · Answer 1 · edited May 23 '17 at 12:29

I found a solution! My solution basis found here:

Using DateDiff with Linq.Dynamic library for fetching today records

Using Lambda Expression

    Dim groupColl = myColl.GroupBy(Function(x) New With {Key x.AddedBy, Key .AddedeOn_Month = DbFunctions.CreateDateTime(x.AddedOn.Year, x.AddedOn.Year, x.AddedOn.Month, 1, 0, 0.0)}).Select(Function(y) New With {y.Key.AddedBy, y.Key.AddedeOn_Month , y.Count})

I added the following lines to the System.Dynamic.Linq recognize the DbFunctions:

    Dim type = GetType(DynamicQueryable).Assembly.[GetType]("System.Linq.Dynamic.ExpressionParser")
    Dim field = type.GetField("predefinedTypes", BindingFlags.[Static] Or BindingFlags.NonPublic)
    Dim predefinedTypes = DirectCast(field.GetValue(Nothing), Type())
    Array.Resize(predefinedTypes, predefinedTypes.Length + 1)
    predefinedTypes(predefinedTypes.Length - 1) = GetType(DbFunctions)
    field.SetValue(Nothing, predefinedTypes)

Then I added:

Dim groupColl = myColl.GroupBy("new(AddedName,  DbFunctions.CreateDateTime(AddedOn.Year, AddedOn.Month, 1, 0, 0, 0.0) AS AddedOn_Month), it").Select("new(Key.AddedName, Key.AddedOn_Month, Count() AS Count)")

Now it works great.

How to GroupBy a part of DateTime using System.Dynamic.LINQ library

1 Answers1