Distinct must be used with Select to identify the field whose distinct values are to be found - exception

[woaksie]

This bit of code

                var realSessionCount = _appliances.CreateQuery()

                    .Where(a => a.Date > DateTime.UtcNow.Subtract(TimeSpan.FromDays(7)))

                    .Select(a => a.ApplianceId)

                    .Distinct()

                    .Count();

throws this exception

    System.NotSupportedException: Distinct must be used with Select to identify the field whose distinct values are to be found.

   at MongoDB.Driver.Linq.SelectQuery.ExecuteDistinct(IMongoQuery query) in C:\source\mongo-csharp-driver\Driver\Linq\Translators\SelectQuery.cs:line 302

   at MongoDB.Driver.Linq.SelectQuery.Execute() in C:\source\mongo-csharp-driver\Driver\Linq\Translators\SelectQuery.cs:line 137

   at MongoDB.Driver.Linq.MongoQueryProvider.Execute(Expression expression) in C:\source\mongo-csharp-driver\Driver\Linq\MongoQueryProvider.cs:line 146

   at MongoDB.Driver.Linq.MongoQueryProvider.Execute[TResult](Expression expression) in C:\source\mongo-csharp-driver\Driver\Linq\MongoQueryProvider.cs:line 130

   at System.Linq.Queryable.Count[TSource](IQueryable`1 source)

   at IQ.IR.Web.API.Handlers.MetricsModule.GetActiveDisplaysCount() in C:\Dev\XQ.Service\IQ.IR.Web\API\Handlers\MetricsModule.cs:line 24

this code works as expected;

                var realSessionCount = _appliances.CreateQuery()

                    .Where(a => a.Date > DateTime.UtcNow.Subtract(TimeSpan.FromDays(7)))

                    .Select(a => a.ApplianceId)

                    .Distinct()

                    .ToArray()

                    .Count();

I think there is a bug here but if there isn't the exception message is wrong.

[Robert Stam]

The reason this doesn't work is that there is no equivalent native MongoDB query that will return just the count of distinct values. The alternative is to count the distinct values client side, which can be accomplished by inserting a call to AsEnumerable() at the point in the chain where you want to switch from server side execution to client side execution:

var count = collection.AsQueryable()

    .Where(c => c.Date > DateTime.UtcNow.Subtract(TimeSpan.FromDays(7)))

    .Select(a => a.ApplianceId)

    .Distinct()

    .AsEnumerable()

    .Count();

Note that all the distinct values will be transmitted over the wire to be counted client side, so this is not necessarily optimal.

One of the design principles of the LINQ implementation in the C# driver is that we assure you that the LINQ queries will be translated to closely equivalent native MongoDB queries. We won't do any weird black magic under the covers that might hide unpleasant performance surprises (like doing part of the work client side without your knowledge). That's why we want you to be explicit and insert a call to AsEnumerable so that you know exactly what is being done server side and what is being done client side.

The error message is definitely wrong, and I've created a JIRA ticket to improve it.

https://jira.mongodb.org/browse/CSHARP-524