Group By Statement in LightSpeed 2

Hi Gents, 

I have been trying to find a way to find every duplicate in a table using lightspeed 2.2 however i can't use the group by statement (as it's not supported), is there a way to do this with out loading up the whole list and iterating through it?

Josh

I don't think it's directly possible in LightSpeed 2.2.

Later nightly builds supported a FindBySql method which could be used to execute arbitrary SQL, including GROUP BY statements.  However note that FindBySql must return an entity type so you'd need to hack in a "fake" entity type to receive the grouping results.  Also, we have not tested FindBySql with GROUP BY so there might be a bit of trial and error in shaping the result set and pseudo-entity definition so they match up!

Hey Guys,

I have just upgraded to version 3.0, and all seems to be working with no code changes (good job guys) but i still can't use the group by statement:

                var participantQuery =
                    from participants in unitOfWork.CampaignParticipants
                    where participants.IsRegistered == Constants.YES
                          && participants.RequiresUpdate == Constants.YES
                    group participants by new { participants.BpsCampaignId, participants.AccountNo }
                        into jtApps
                        where jtApps.Count() >= 2
                        select new { jtApps.Key, jtApps };
cheers,

Josh

I'm getting the following error:

System.NotSupportedException: Specified method is not supported.

Hi Josh,

Yes, the NotSupportedException you are seeing there is due to the use of a subexpression criteria (e.g. Count()) on the grouping query. We currently dont support this type of translation through the LINQ provider since it doesn't resolve to anything sensible within the core API.

In the query above, it is this part of it:

group participants by new { participants.BpsCampaignId, participants.AccountNo }

into jtApps

 where jtApps.Count() >= 2

You could look at applying a client side filter or looking at stored proc for this type of query if the set is not going to be efficiently parse client side.

Jeremy

and apparently 'Grouping by multiple grouping keys is not currently supported'.  Do u have plans on supporting this functionality, seem the group by function is fairly limited.

Hi Josh,

Indeed - looks like there is a bug in the LINQ provider relating to the handling of multiple grouping keys post querying. We will aim to get that fixed up fairly quickly and get it out in a nightly soon. Will keep you posted.

In relation to the original issue, as mentioned above this will require enhancements to the core API first before we can translate those types of queries in the LINQ provider, so this will be something we will have to look at for a future minor version release Im afraid.

Jeremy

Hi Jeremy,

Thanks for your reply, I'm trying to get around the group by having sql statement by the following:

                var participantQuery = (from participants1 in unitOfWork.CampaignParticipants
                                        join participants2 in unitOfWork.CampaignParticipants
                                        on participants1.AccountNo equals participants2.AccountNo into blah
                                        from participants in blah.DefaultIfEmpty()
                                        where participants1.Id != participants.Id
                                        && participants1.AccountNo == participants.AccountNo
                                        && participants1.BpsCampaignId == participants.BpsCampaignId
                                        && participants1.AccountNo == participants.AccountNo
                                        select participants).Distinct();

The code does an left inner join as hoped but the where statement accours look wrong:

SELECT
  COUNT(*)
FROM
  BPS_CAMPAIGN_PERSON t0
LEFT OUTER JOIN
  BPS_CAMPAIGN_PERSON t1
ON
  t0.ACCOUNT_NO = t1.ACCOUNT_NO
WHERE
  ((t0.campid = t1.campid
  AND t0.pkey <> t1_NO IS NOT NULL)

Is this a prablem with the query or a bug in lightspeed...

Hi Josh,

We have resolved the outstanding issue relating to multiple grouping keys so this should be resolved for you in the latest nightly build. Let us know how you get on.

In relation to the query you posted, its hard to say where the issue is since I am not entirely sure from the query what it should be expressing. Are you able to post some more details about this? e.g. What would be the SQL you would be expecting it to translate to?

Jeremy