Discrepancy between Mindscape and LINQ - whole table loaded

Hey guys,

I've found something a bit surprising when working with group bys.  Specifically, using the following query:

from g in Grants

group g by g.FiscalYearEnd into h

select h.Key

In vanilla LINQ, this generates the following SQL:

SELECT [t0].[FiscalYearEnd]

FROM [Grants] AS [t0]

GROUP BY [t0].[FiscalYearEnd]

So... er... what? Why is this happening?  I really would prefer to use Lightspeed for this project.

Thanks very much in advance - and hope things are good with you guys over there in NZ - I used to live down in Queenstown, but I'm in Vancouver now!

Cheers,

Mike

Hi Mike,

The reason for the two queries is that when we translate this from a LINQ query to our underlying native querying engine, we first need to select out the results of the grouping statement. Then the second query is needed to satisfy that you might have further client side projections where you can talk about things in terms of entire entity so the entities themselves need to be loaded. Because there is no other criteria in your LINQ query the whole table gets loaded to create the grouped sets.

There is a backlog item to optimize this in the future for cases like yours where the original server side projection is enough to satisfy the following projections (you are just selecting the key) but thats currently parked for now.

If you wanted to execute just the single query you can do this by writing this using the native API (effectively doing just the first part of what the LINQ provider does). An example would be:

struct MyGroupingResult

{

    public DateTime FiscalYearEnd { get; set; }

}

var query = new Query(typeof(Grant)) { Group = Group.BySelection() };

query.Projection.Add("FiscalYearEnd");

var results = unitOfWork.Project<MyGroupingResult>(query);

Jeremy