CODEWITHSUNDEEP
c#vb.netlinqlinq-to-sql
Narazana
Narazana

Reputation: 1950

LINQ to SQL group people by age

Sample in C# and VB.NET are OK.

I have a table "People" with the following columns:

-FullName (nvarchar not null) -DOB (datetime null)

I want to write a LINQ to SQL to group the people by age, like following result:

Age 19: 4 ppl

Age 20: 5 ppl

Age 21: 6 ppl

and so on...

Here's my try:

Dim query = From ppl In db.People _
         Select New With {.Age = DateTime.Now.Year - CDate(ppl.DOB).Year, .CountAge = ppl.Count}

Notice that there are no DOB record for some people in the tables, so these shall not be included. The DOB column has record like this 1982-10-24 10:12:45 AM because it's a DateTime column.

Upvotes: 1

Views: 3055

Answers (6)

Lorena72
Lorena72

Reputation: 13

If you're using EF Core 3.1 DbFunctions has been replaced with EF.Functions. As another alternative you could use this:

var query = await db.People
                 .GroupBy(x.DateOfBirth.HasValue ? (int)(EF.Functions.DateDiffDay(x.DateOfBirth.Value, DateTime.UtcNow) / 365.25) : (int?)null)
                  .Select(x => new
                  {
                      Age = x.Key,
                      Count = x.Count(),
                  })
                .ToListAsync(cancellationToken);

It takes the 'Birth day' into consideration, not the time but is more accurate than using only the 'Birth year'

Upvotes: 1

Mustafa Abdel-Halim Ahmed
Mustafa Abdel-Halim Ahmed

Reputation: 39

More accurate solution:

db.People.Where(p => p.DOB != null).GroupBy(p => ((DbFunctions.DiffDays(p.DOB, DateTime.Today) / 365)))
.Select(g => new {Age=g.Key, Count = g.Count()})

to group into intervals:

var interval = 5; //years
db.People.Where(p => p.DOB != null).GroupBy(p => ((DbFunctions.DiffDays(p.DOB, DateTime.Today) / 365) / interval))
.Select(g => new {GroupIndex=g.Key, Count = g.Count()})

Upvotes: 1

Sedat Kapanoglu
Sedat Kapanoglu

Reputation: 47680

I would rather:

var result = db.People
  .Where(p => p.DOB != null) // exclude null DOB's from stats
  .GroupBy(p => p.DOB.Value.Year)
  .Select(g => new { Age = DateTime.Now.Year - g.Key, Count = g.Count() });

We are grouping against year of birth here. This should translate to GROUP BY YEAR(dob) in SQL which could have slightly better performance or optimization compared to GROUP BY YEAR(GETDATE()) - YEAR(dob). It's almost impossible to map row expressions into indexes but certain constructs like YEAR() alone could be optimized to use a datetime index partially. I make many assumptions here though, like that datetime structures start with year, SQL server cares about YEAR(x) for optimizations etc. It's still good to keep in mind such situations when constructing a LINQ query.

Upvotes: 0

dariom
dariom

Reputation: 4578

This should work (and should calculate ages a little more accurately):

var query = from person in db.People
            where person.DOB.HasValue
            let age = (DateTime.Now - username.UpdateDateTime.Value).Days % 365
            group person by age into ages
            select new { Age =ages.Key, People = ages.Count() }

Upvotes: 0

edvaldig
edvaldig

Reputation: 2299

Assuming DOB is a nullable DateTime (DateTime?), so no DOB record would mean null there:

  from ppl in db.People
  where ppl.DOB.HasValue
  let age = DateTime.Today.Year - ppl.DOB.Value.Year 
  group ppl by age into ages
  select new { Age=ages.Key, Persons = ages  }

This is in C# but should translate well to VB since the syntax is similar.

Upvotes: 0

Technology Digestified
Technology Digestified

Reputation: 79

From ppl In db.People
Select New With {.Age = DateTime.Now.Year - CDate(ppl.DOB).Year, 
                 .CountAge = ppl.Count()}
Group By (DateTime.Now.Year - CDate(ppl.DOB).Year)

I think this query will serve your purpose.

Upvotes: 0

Related Questions