Get DISTINCT count

[Duncan]

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

[Thomas]

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

[Duncan]

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?

[Thomas]

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

[Duncan]

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

[Thomas]

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