Linq GroupBy


(Matteo) #1

I created a simple WPF project using Relativity Server and PCTrade database.

Using the statement below:

dataGrid.ItemsSource = new DataModule().DataAdapter.GetTable().GroupBy(it => it.Birthdate.Value.Month).Select(it => new { it.Key, Cnt = it.Count() });

the output data was not grouped on month (it seems that grouping occurs on the full date and not only on the month). I tried the same linq query on another project based on entity framework and it works as expected.

What am I missing?

Thank you,

(antonk) #2

Unfortunately DateTime methods and properties like .Month, .Day, etc are calculated client-side. They cannot be used as a part of where or groupby conditions.

(Matteo) #3

You mean that Data Abstract/Relativity Server is not able to perform that or it’s unlikely to do that?
For example, entity framework can translate that tree in a sql query with correct “group by” clause.

Also string Substring has the same behaviour.

(marc hoffman) #4


we should either make that fail cleanly, or do the growing client side, then? it’d not good if it just groups by the wrong value, silently…

(antonk) #5

You mean ‘string Substring’ in a group operation?

Actually, I can provide you a sample with workaround for this ‘Group By Month’ thing. Also I’ll take a deeper look to check if it is possible to add support for datepart methods in group conditions in a reasonable time. Will that work for you?

(Matteo) #6

Yes, for example:
dgr.ItemsSource = context.GetTable< Workers >().GroupBy(it => it.WorkerName.Substring(0,1)).Select(it => new { it.Key, Cnt = it.Count() }).ToList();

Yes, that would be great to understand a common way that I can use to achieve that goal waiting for DA/RS’s code upgrade.

Thank you

(RemObjects) #7

Thanks, logged as bugs://79576

(antonk) #8

Take a look at this sample: (116.2 KB)
It uses MS SQL database with a table defined as

CREATE TABLE [dbo].[Orders](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ObjectId] [int] NOT NULL,
	[CustmerId] [int] NOT NULL,
	[Date] [datetime] NOT NULL,
	[Id] ASC

Main points of interest are

  • How the statement of the OrdersEx table is defined
  • Flags set for the calculated field DateMonth

(Matteo) #9

Thank you for the workaround