0

I have IQueryable table. How to get query like this?

select COUNT(DISTINCT id1), COUNT(DISTINCT id2)
FROM table

UPDATE 1:

I wrote my expression like this:

var counts = table.GroupBy(
  k => 1,
  g => g,
  (k, g) => new {
    id1Count = g.Select(x => x.id1).Distinct().Count(),
    id2Count = g.Select(x => x.id2).Distinct().Count()
});

, where table is IQueryable. However, this gives me quite different sql query from the one I want. For example, new query makes 3 table scans instead of 1.

user2820173
  • 308
  • 2
  • 13
  • Check [this question](https://stackoverflow.com/questions/489258/linqs-distinct-on-a-particular-property) out. – StackLloyd Jul 08 '19 at 15:09
  • @dai, it's different question. It would be the same, if id1 and id2 would have a relationship. (like id1 = id2 + 3 or smth). – user2820173 Jul 08 '19 at 15:52
  • @StackLloyd, I need that linq translated into sql. That question is about linq to objects, if I'm not confused. – user2820173 Jul 08 '19 at 15:53
  • The referenced question almost completely answers this question. The only missing piece how to express the distinct count in LINQ, which can be something like: `db.Products.GroupBy(p => p.LocationId).Count()` – David Browne - Microsoft Jul 08 '19 at 16:18
  • @DavidBrowne-Microsoft, it's very similar indeed. It gives same results, but different query, though. I wrote details in update 1. – user2820173 Jul 11 '19 at 07:21

0 Answers0