Get DISTINCT count

6 posts, 1 answers
  1. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 17 Oct 2011 Link to this post

    Hi there guys, i have done much searching and found lots of LINQ examples for this, but none that fit my code that i have here:

    ...              
    using (MyDBContext dbContext = new MyDBContext())
    {
         Users user = dbContext.Users.Single(u => u.Credentials.Email == email);
     
         count = user.UserGroups.Triggers.Where(t => t.TriggerTypeID == 1 && t.TriggerDateTime >= startdate && t.TriggerDateTime <= enddate).Count();
    }
    ...

    This works fine, but i now need to get a Count by the DISTINCT values of the "IP" column (t.IP)...

    How do i do this?

    Thanks,
    Duncan
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 18 Oct 2011 Link to this post

    Hi Duncan,

    I think there are two solutions: (My code uses the Northwind model)

    (A) Use the Distinct() method:
         
    var order = Scope.Extent<Order>().Single(u => u.Id == x);
    var count = order.OrderDetails.Where(d => d.Discount > 0.2).Select(d => d.UnitPrice).Distinct().Count();
          Issue with this way is, that there will actually be two queries: one for the order, the other one for the OrderDetails; the rest is done in memory.

    (B) Use the distinctness of GroupBy:
    var diffIp = Scope.Extent<OrderDetail>().Where(od => od.OrderID == x).GroupBy(od => od.UnitPrice).Count();
          Here we can combine the filter for the Order into the query, and the group by will produce distinct keys, which can then be counted easily; only one query needed.


    Regards,
    Thomas
    the Telerik team

    Check out the latest stable build of Telerik OpenAccess ORM. Download it and benefit from our new Project Templates.

  3. DevCraft banner
  4. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 18 Oct 2011 Link to this post

    Is it just me or was this a whole lot more straight forward with SQL?

    So the GroupBy(od => od.UnitPrice) is basically the DISTINCT(UnitPrice). The GroupBy works beacuse the Count() is counting the groups, not the records?
  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 19 Oct 2011 Link to this post

    Hello Duncan,

    yes, sometimes SQL is more direct, but generating SQL is sometimes not direct :-) .
    Yes, the GroupBy/Count approach uses the grouping to generate distinct keys, and then counts the groups in order to count the distinct keys.

    Kind regards,
    Thomas
    the Telerik team

    Check out the latest stable build of Telerik OpenAccess ORM. Download it and benefit from our new Project Templates.

  6. Duncan
    Duncan avatar
    93 posts
    Member since:
    Jul 2012

    Posted 09 Nov 2011 Link to this post

    Thomas,

    Sorry to revisit this topic but i need to pull distinct records based on a particular column. But instead of returning a count, i need to return the distinct rows... What would be the correct approach to accomplish this?

    Thanks,

    Duncan
  7. Answer
    Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 10 Nov 2011 Link to this post

    Hello Duncan,

    I'm not sure what you want to accomplish: the relational model does not allow for a single distinct column only, only completely distinct rows are permitted. This means, your result set can be distinct if it solely includes only the one distinct column (ipaddress).
    To retrieve the complete data for a given ipaddress value you would need to use a separate query then. This is why I proposed the grouping , because grouping makes it distinct, and enumerating the group gives you all the row information.
    Or do you need to know how many distinct values are in the database?
    count = user.UserGroups.Triggers.Where(t => t.TriggerTypeID == 1 && t.TriggerDateTime >= startdate && t.TriggerDateTime <= enddate).Select(x => x.IPAddress).Distinct().Count();

    Best wishes,
    Thomas
    the Telerik team

    NEW and UPDATED OpenAccess ORM Resources. Check them out!

Back to Top
DevCraft banner