Expected a SQL group by clause, got a client-side group by instead

Given the following code

                    var query =
                        (
                            from c in unitOfWork.Documents
                            where channelsGuids.Contains(c.CmsChannelGuid)
                            group c.LastModified by c.CmsChannelGuid
                            into g
                            select new
                            {
                                ChannelGuid = g.Key,
                                DateTime = g.Max()
                            }
                        )

I would expect the SQL to be

                        SELECT ChannelGuid, MAX(LastModifed) FROM Document 
                        WHERE ChannelGuid in (@p0, @p1)
                        GROUP BY ChannelGuid

however, it is

                        SELECT ChannelGuid, LastModifed FROM Document 
                        WHERE ChannelGuid in (@p0, @p1) AND ChannelGuid in (@p0, @p1) 

The double condition on the actual SQL looks suspicious... (Especially given there are two values in the original array)

Yes that does look odd - presumably there is a grouping query prior to the query above though? If not then the grouping will be being performed client side which would indicate there is a client side component to your query so check if that might be the case.

Ive created a comparable query against a test model here but it is executing as I would expect - is it possible for you to send through a small repro example of this and I can have more of a look into what might be going on and if we need to make a fix for this.

Thanks!
Jeremy

Here is the reproduction.

You were right, there are two queries generated.

SELECT
  g1.Guid
FROM
  (
    SELECT
      E.Guid AS [Guid]
    FROM
      E E
    WHERE
      E.Guid IN ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000001')
    GROUP BY
      E.Guid
  )
  g1

SELECT
  E.Guid AS [E.Guid],
  E.Date AS [E.Date]
FROM
  E
WHERE
  E.Guid IN ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000001')

So that output is what I would expect and doesn't contain the duplicate criteria you saw originally - any thoughts on how to reproduce that?

The way the translation for a LINQ group by occurs is that we have to issue a grouping query to fetch the grouping keys and then issue a query to fetch the entities associated with those keys so you will see 2 queries as above. It would be nicer to perform this as a single query but currently this is just how it works because we have to perform the queries using the underlying querying API.

Jeremy

Oh dear, that was the problem I was having. That table has a large number of rows in it so fetching each row is not ideal.

Any idea on what the best work around is? Both stored procedure and inline SQL seem a bit tacky. I guess inline sql will allow the work around to be kept local to the code executing the query.

Cheers Andy

You should definitely try handling this using the querying API directly where you can be a bit smarter than our translation engine about constructing the query since you know you dont actually need to deal with entities for handling the projection so you should be able to write this as a single query.

Try something like:

var query = new Query(typeof(Document));

query.Group = Group.By(Entity.Attribute("CmsChannelGuid"));
query.Projection.Add(Entity.Attribute("CmsChannelGuid"));
query.Projection.Add(Entity.Attribute("LastModified").Function("MAX"));
query.QueryExpression = Entity.Attribute("CmsChannelGuid").In(channelGuids.ToArray());

var results = unitOfWork.Project(query);

Hopefully that should do the trick.

Jeremy

Excellent, that will do the trick.

Thanks