Distinct count with group fails though count is working fine

Question

I'm trying to convert a sql server query to Mongo C# driver LINQ. I have a table with multiple entries for each guid and each entry has different code value. I need get a distinct count of guids for each code. I'm able to do Count()in LINQ but when I do Distinct().Count() I get the following exception:

Specified method is not supported

Following is the T-SQL I'm trying to convert:

SELECT a.code, substring(convert(varchar, a.create_Date, 107), 1, 6) as create_Date,count(distinct  a.guid) as mycount
FROM table1 a (nolock) 
WHERE a.code = 0 
AND a.create_Date BETWEEN DateAdd(day,   -2  , @startDate) AND DateAdd(day, 1, @startDate)
GROUP BY a.code, substring(convert(varchar, a.create_Date, 107), 1, 6)
Order by 1

Following is my LINQ :

    var result = from tb in collection3.AsQueryable()
                 where
                 tb.code == 0
                 && tb.create_Date > rptGte
                 && tb.create_Date < rptLt
                 group tb by new
                 {
                     tb.code,
                     create_Date = tb.create_Date.DayOfYear
                 } into g
                 select new
                 {
                     code = g.Key.code,
                     create_Date = g.Key.create_Date,
                     mycount = g.Select(x => x.guid).Distinct().Count() //distinct not working here
                 };

    var resultList = rpt.ToList();

Please tell what am I doing wrong here.

I would suggest looking into using Aggregation to accomplish this: https://docs.mongodb.com/manual/aggregation/ — Pete Garafano, Dec 03 '19 at 22:43

Carlo Bos · Answer 1 · 2019-12-03T15:04:49.177

The key is that some Linq-to-Object constructs are not supported in Linq-to-SQL/EF. One approach is to grab the data with your WHERE using Linq-to-SQL/EF and then do the rest of the operations on the objects that come back in memory with Linq-to-Objects.

Without your actual data source it's impossible to test, but the following code illustrates how this can be done. Notice the first .ToList() call, which will fetch the results from the database, then the rest of the Linq query is done in memory on the returned objects:

public List<Result> GetGroupedResults(DateTime date)
{
    return table1
        .Select(x => new {x.code, x.create_Date, x.guid })
        .Where(x => 
            x.code == 0 && 
            x.create_Date > date.AddDays(-2) && 
            x.create_Date < date.AddDays(1))
        .ToList() // Fetch from database
        .GroupBy(x => new
        {
            Code = x.code, 
            CreateDateStr = x.create_Date.ToString("MMM dd")
        })
        .OrderBy(g => g.Key.Code)
        .Select(g => new Result
        {
            Code = g.Key.Code, 
            DateStr = g.Key.CreateDateStr, 
            Count = g.Select(x => x.guid).Distinct().Count()
        })
        .ToList();
}

public class Result
{
    public int Code { get; set; }
    public string DateStr { get; set; }
    public int Count { get; set; }
}

I know right! This is exactly what I wanted to avoid. The pain here is that there is so much data that I wanted to avoid doing operations at the application end. But if there's no other way I guess I'll have to go this way. Thanks :) — gatsby, Dec 04 '19 at 04:31
Yes, you will need to bring in the un-grouped data, however if you are worried about your cloud egress costs, it will be filtered by the 3 columns and your `where` clause. — Carlo Bos, Dec 04 '19 at 20:09
@gatsby if my answer helped, please accept and optionally up-vote it. — Carlo Bos, Dec 06 '19 at 15:30
I'm waiting if there's another answer that can help me handle this at the database side itself. Thanks! I would deifinitely upvoted your answer though but I don't have enough reputation for that sorry! — gatsby, Dec 09 '19 at 05:36

score 0 · Answer 2 · answered Dec 10 '19 at 09:51

i believe this will give the result you want. nothing is done on the clientside as ToList is called at the very end to materialize the results. also, since you're already fitering by code == 0 grouping is done on the guid field instead.

using MongoDB.Entities;
using MongoDB.Entities.Core;
using System;
using System.Linq;

namespace StackOverflow
{
    public class TeeBee : Entity
    {
        public int code { get; set; }
        public DateTime create_Date { get; set; }
        public string guid { get; set; }
    }

    public class Program
    {
        private static void Main(string[] args)
        {
            new DB("test", "localhost");

            (new[] {
                new TeeBee { code = 0, create_Date = DateTime.UtcNow, guid = "xxx"},
                new TeeBee { code = 0, create_Date = DateTime.UtcNow, guid = "xxx"},
                new TeeBee { code = 0, create_Date = DateTime.UtcNow, guid = "yyy"},
                new TeeBee { code = 1, create_Date = DateTime.UtcNow, guid = "xxx"},
            }).Save();

            var result = DB.Queryable<TeeBee>() // for official driver use: collection.AsQueryable()

                           .Where(tb =>
                                  tb.code == 0 &&
                                  tb.create_Date <= DateTime.UtcNow.AddDays(1) &&
                                  tb.create_Date >= DateTime.UtcNow.AddDays(-1))

                           .GroupBy(tb => tb.guid,
                                   (g, tbs) => new
                                   {
                                       tbs.First().code,
                                       tbs.First().create_Date,
                                       uniqueGuids = tbs.Select(tb => tb.guid).Distinct()
                                   })

                           .Select(x => new
                           {
                               Code = x.code,
                               DateStr = x.create_Date.DayOfYear,
                               Count = x.uniqueGuids.Count()
                           })

                           .ToList();
        }
    }
}

thanks for the answer but I'm facing two issues here : 1.) you have grouped by create_Date which is a datetime field, i want to group by the day part of that field. 2.) I'm getting only 1 item in the result list when I apply your code. — gatsby, Dec 10 '19 at 10:28
@gatsby it would help if you could add some sample data from mongodb as well as the output you need. that way i could insert that test data in to mongodb and try to come up with the needed end result. — Dĵ ΝιΓΞΗΛψΚ, Dec 10 '19 at 13:49

Distinct count with group fails though count is working fine

2 Answers2