Linq to SQL combination of aggregate functions

Question

Is there a way to simplify this and make it go faster? In SQL I use count(*) and count(distinct) in same query which gives me a faster result

Thanks a bunch.

int numberOfUsers = (from u in db.UseLogs
                     where u.DocID == docID 
                        && u.LogDate >= fromDate 
                        && u.LogDate <= to
                     select u.UserID).Distinct().Count();

int numberOfVisits = (from u in db.UseLogs
                      where u.DocID == docID 
                         && u.LogDate >= fromDate 
                         && u.LogDate <= to
                      select u).Count();

Conclusion

There is no Count( Distinct) in LINQ. Not EVERY SQL statement can be translated into LINQ.

Thanks for the help guys!

You may want to simplify your question. Everything but the first two queries is unnecessary to explain the question. A lot of users will probably be turned off by having to parse through your code to find the relevant parts (which aren't many). — Ocelot20, Nov 14 '12 at 13:46
Thanks, didn't see this one until now. Did a make up on it now — Hawk, Nov 14 '12 at 14:15
Short answer is Linq2Sql doesn't have direct support for count(distinct) — Bob Vale, Nov 14 '12 at 14:50

score 0 · Answer 1 · answered Nov 14 '12 at 13:57

0

Is this what you're looking for?

from u in db.UseLogs
where u.DocId == docId && u.LogDate >= fromDate && u.LogDate <= to
group u by true into g
select new
{
    Count = g.Count(),
    DistinctUserCount = g.Select(x => x.UserId).Distinct().Count()
}

You will want to check if it's actually faster than running two queries, or if the performance gain is worth the loss of readability.

answered Nov 14 '12 at 13:57

Ocelot20

10,510
11
55
96

Hey, thanks for answering. I also tried that one which "looks" to me as would be faster, but it's sameToSlower than having two separate. In SQL I can use count(*) and count(distinct) in same query. My sql and individual linq queries require 7 seconds each, whereas this suggested ones and others I've tried tend to use 14+. Is every SQL statement possible in Linq? Thanks again – Hawk Nov 14 '12 at 14:07
No, not every SQL statement is possible in LINQ. You have to remember that LINQ itself doesn't just apply to SQL. It can also be used for XML for example. There are plenty of concepts that don't overlap between SQL and XML querying, and LINQ has to remain generic enough to apply to both. – Ocelot20 Nov 14 '12 at 15:01
Thank you. That's very good to know. I got the impression that LINQ can do everything and more, but now I know. – Hawk Nov 15 '12 at 08:47

D Stanley · Answer 2 · 2012-11-14T14:48:23.297

0

Not exactly what you're trying to acheive but may be just as fast:

var query = 
    from u in db.UseLogs
    where u.DocId == docId && u.LogDate >= fromDate && u.LogDate <= to
    group u by u.UserID into g
    select new
    {
        Visits = g.Count()
    };

    int numberOfUsers  = query.Count();
    int numberOfVisits = query.Sum(q=>q.Visits);

It executes the SQL only once and does the SUMming in memory.

edited Nov 14 '12 at 14:48

answered Nov 14 '12 at 14:36

D Stanley

149,601
11
178
240

Hey. Thanks for answering! I tried it out and at my current computer it requires same amount of time as the two queries, so it's down to preference of readability now I guess. Unless one of the ways is the preferred way of doing it. – Hawk Nov 15 '12 at 08:46

Linq to SQL combination of aggregate functions

2 Answers2