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

问题描述

我正在查询雇员的生日(在罗斯文数据库中)，我想知道他们的年龄.我怎么做?

I'm runing a query (in northwind db) on the employees birth date and I want to get their age. How do i do that?

    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;

        }
    }

在我的最终网格中，我想要年龄列.

in my final grid i want column of age.

生日= 1969年5月29日(示例)

birthdate = 29/05/1969 (example)

推荐答案

这可能很棘手，因为您需要确保今年的生日已经过去，否则您的年龄将增加一岁.

This can be tricky because you need to make sure the birthday has passed this year, otherwise your age will be out by one.

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查询中计算使用期限.这意味着您不需要遍历客户端的结果集来计算年龄.

Doing it this way has the advantage of calculating the age in the generated SQL query. This means you don't need to iterate through the result set on the client side to calculate the age.

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

In case you're interested, it will produce the following 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]

这篇关于LINQ查询出生日期以获取年龄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！