Client side grouping in linq not handled correctly

Lightspeed nightly build of 12th march 2014 Sql Server 2008 provider

Hi,

I have a problem with the client side grouping when using Linq to Lightspeed.

I have a query where I link to the same table twice, grouping on a column of the first and aggregating on a column of the second. The aggregates are performed on the values of the first linked table. I will attach a test case illustrating the problem.

var y1 = from rec in uow.WgiImportRecords
         join field1 in uow.WgiImportFields on rec.Id equals field1.WgiImportRecordId
         join field2 in uow.WgiImportFields on rec.Id equals field2.WgiImportRecordId
         where rec.WgiImportKopId == maxKopId && field1.Name == "BurgerServiceNummer" && field2.Name == "JaarMaand"
         group field2 by field1.ValueNumber into g
         select new { Bsn = g.Key, Aantal = g.Count(), MaxDate = g.Max(jmf => jmf.ValueDateTime), MaxNumber = g.Max(jmf => jmf.ValueNumber) };

In this example the Bsn property and the MaxNumber propert are the same, while the MaxNumber should be null and MaxDate should contain a value.

The values in the aggregates are from field1 and not of field2 as I would expect. It would appear that the client side grouping algorithm is confused of which table to use for aggregating the values.

I also included a database and sql statement for testing. The results from the sql statement is what I would expect to see. Don't forget to alter the database file path in the connectionstring in the app.config.

thnx in advance,

Arjan

Any aggregate function will be handled client side, our LINQ provider translates a grouping query into several queries, one for the underlying group by query to fetch the grouping keys and then one per key to fetch the underlying entities in that set. Subsequent operations are handled client side as they need to be performed over sets of entities.

Unfortunately combining grouping and joins is not supported (see: http://www.mindscapehq.com/documentation/lightspeed/Appendices/LINQ-Support-Limitations under the Joining, Grouping and Combining section) so you may need to look at some alternative to achieve the above query (e.g. use of a stored proc or view if thats possible).

Jeremy

Hi Jeremy,

I'm sorry to hear that the scenario is unsupported. In the appendix you mention, it states that the some scenario's will be supported in a future release:

Some complex joining and grouping scenarios are not supported. Support for some scenarios will be added in future builds; please contact Mindscape if there is a particular pattern you would like us to support.

Well, I would very much like to see the the scenario described in my initial post supported.

Besides that I'm wondering if the behavior is likely to change that aggregate functions are performed client side. Wouldn't it be better performance wise to let the database handle those functions? With large datasets the amount of data fetched will grow and thus memory consumption.

Regards, Arjan