Search code examples
c#sqllinqlinq-to-sqllinq-to-entities

Linq to SQL get distinct records grouped by date

I am trying to get a list of distinct "customer type id" records for each each 24 hour period and add them to a list.

Say my data structure is like

DATE        TYPEOF CUSTOMER    CUSTOMER TYPE ID
31-10-2013  GUEST              3
31-10-2013  REGISTERED         4
31-10-2013  MANAGER            2
30-10-2013  GUEST              3
30-10-2013  REGISTERED         4
30-10-2013  MANAGER            3

In linq to sql I want to get all the records for each day (24 hour period) then get a distinct count of each "customer type id"

I have made start but do not know how to proceed. How can I do this?

var results = _customerTable.GroupBy(c => new {Date = c.Date.Date}).....
Solution

  • you can try the following sample code:

    var results = _customerTable.GroupBy(c => new {c.Date, c.CUSTOMER_TYPE_ID}).Select(g => new {g.Key.Date, g.Key.CUSTOMER_TYPE_ID, MyCount = g.Count()});

    using this way you can get number of recors per Date and CUSTOMER_TYPE_ID.