I am working on generating report for showing customer using LINQ in C#. I want to show no. of customers of each type.
There are 3 types of customer registered, guest and manager. I want to group by customers by registered date and then by type of customer. i.e If today 3 guest, 4 registered and 2 manager are inserted. and tomorrow 4,5 and 6 are registered resp. then report should show Number of customers registerd on the day . separate row for each type.
DATE TYPEOF CUSTOMER COUNT
31-10-2013 GUEST 3
31-10-2013 REGISTERED 4
31-10-2013 MANAGER 2
30-10-2013 GUEST 5
30-10-2013 REGISTERED 10
30-10-2013 MANAGER 3
LIKE THIS .
var subquery = from eat in _customerRepo.Table
group eat by new { yy = eat.CreatedOnUTC.Value.Year, mm = eat.CreatedOnUTC.Value.Month, dd = eat.CreatedOnUTC.Value.Day } into g
select new { Id = g.Min(x => x.Id) };
var query = from c in _customerRepo.Table
join cin in subquery.Distinct() on c.Id equals cin.Id
select c;
By above query I get minimum cutomers registerd on that day Thanks in advance
var query = _customerRepo.Table
.GroupBy(c => new {Date = c.Date.Date, Type = c.TypeOfCustomer})
.Select(g => new
{
Date = g.Key.Date,
Type = g.Key.Type,
Count = g.Count
}
)
.OrderByDescending (r = r.Date);
var query = from c in _customerRepo.Table
group c by new { c.TypeOfCustomer, c.Date } into g
orderby g.Key.Date descending,
g.Key.TypeOfCustomer == "GUEST" ? 1 :
g.Key.TypeOfCustomer == "REGISTERED" ? 2 : 3
select new {
g.Key.Date,
g.Key.TypeOfCustomer,
Count = g.Count()
};
You can remove second ordering if it's not required for daily results to be in order guest > registered > manager:
var query = from c in _customerRepo.Table
group c by new { c.TypeOfCustomer, c.Date } into g
orderby g.Key.Date descending
select new {
g.Key.Date,
g.Key.TypeOfCustomer,
Count = g.Count()
};
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With