LINQ查询出生日期以获取年龄

本文关键字：获取 查询 出生日期 LINQ | 更新日期: 2023-09-27 17:59:15

我正在运行一个关于员工出生日期的查询（在northwind数据库中），我想得到他们的年龄。我该怎么做？

    private void Form1_Load(object sender, EventArgs e)
    {
        using (NorthWindDataContext dataContext = new NorthWindDataContext())
        {
            DateTime today = DateTime.Today;
            var q1 = from z in dataContext.Employees
                     select new
                     {
                         z.FirstName,
                       today-z.BirthDate <---- problem  here
                     };
            dataGridView1.DataSource = q1;
        }
    }

在我的最后一个格子里，我想要年龄栏。

出生日期=1969年5月29日（示例）

LINQ查询出生日期以获取年龄

这可能很棘手，因为你需要确保今年的生日已经过了，否则你的年龄会相差一岁。

var query = from e in dataContext.Employees
            //get the difference in years since the birthdate
            let years = DateTime.Now.Year - e.BirthDate.Year
            //get the date of the birthday this year
            let birthdayThisYear = e.BirthDate.AddYears(years)
            select new
            {
                //if the birthday hasn't passed yet this year we need years - 1
                Age = birthdayThisYear > DateTime.Now ? years - 1 : years
            };

这样做的优点是可以在生成的SQL查询中计算年龄。这意味着您不需要迭代客户端的结果集来计算年龄。

如果您感兴趣，它将生成以下SQL:

-- Region Parameters
DECLARE @p0 Int = 2013
DECLARE @p1 DateTime = '2013-02-14 09:08:46.413'
DECLARE @p2 Int = 1
-- EndRegion
SELECT [t2].[value] AS [years], [t2].[value2] AS [birthdayThisYear], 
    (CASE 
        WHEN [t2].[value2] > @p1 THEN [t2].[value] - @p2
        ELSE [t2].[value]
     END) AS [Age]
FROM (
    SELECT [t1].[value], DATEADD(YEAR, [t1].[value], [t1].[BirthDate]) AS [value2]
    FROM (
        SELECT [t0].[BirthDate], @p0 - DATEPART(Year, [t0].[BirthDate]) AS [value]
        FROM [Employees] AS [t0]
        ) AS [t1]
    ) AS [t2]

创建具有3个属性的匿名对象：FirstName、Age和BirthDate（这是检查员工今年是否过生日所必需的）。

var q1 = (from z in dataContext.Employees
    select new
    {
         FirstName = z.FirstName,
         Age = today.Year - z.BirthDate.Year,
         BirthDate = z.BirthDate
    }).ToList();
q1.ForEach(x => if (x.BirthDate > today.AddYears(-x.Age)) { x.Age--; });

根据注释（正确的），您还应该检查BirthDate是否已经通过，所以在选择数据后，您必须运行检查。

您需要一个命名属性，类似于以下内容：

private void Form1_Load(object sender, EventArgs e)
{
    using (NorthWindDataContext dataContext = new NorthWindDataContext())
    {
        DateTime today = DateTime.Today;
        var q1 = from z in dataContext.Employees
                 select new
                 {
                     Name = z.FirstName,
                     Age = today - z.BirthDate
                 };
        dataGridView1.DataSource = q1;
    }
}

var temp = from o in dataContext.Employees
    select new
    {
         FirstName = o.FirstName,
         Age = DateTime.Now.Year - z.BirthDate.Year -1
    };

 var q1 = from z in dataContext.Employees
          select new
         {
            z.FirstName,
            z.BirthDate,
            Age = DateTime.Now.Year - z.BirthDate.Year
         };
相关文章：