使用C#进行比较(年龄)的LINQ查询DateTime字段的计算(或年)[英] calculateAge (or year) from LINQ Query DateTime field using C# for comparision (Age From)

本文是小编为大家收集整理的关于使用C#进行比较(年龄)的LINQ查询DateTime字段的计算(或年)的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

请参阅计算方法,以将生日转换为LINQ查询年的当前年龄(它适用于其他键,例如:Eyecolor,但年龄转换失败),我想与存储在数组中的值进行比较.我尝试在链接上方的DateTime Temp变量中存储该值,就在" case" ageto":"但是我不能在Linq查询中允许使用x.profile.birthdate的值x.profile.birthdate.似乎LINQ查询在构建过程中没有给出错误,但是,它未能解决

错误:

linq到实体无法识别方法'int32 calculateage(system.datetime)'方法,此方法不能为 翻译成商店表达式.

任何帮助如何处理?

代码和计算方法:

if (searchList.Count > 0)
{
    foreach (KeyValuePair<String, String> kvp in searchList)
    {
        switch (kvp.Key)
        {
            case "AgeFrom":
                photosquery = photosquery.Where(x => calculateAge(Convert.ToDateTime(x.profile.BirthDate)) >= Convert.ToInt32(kvp.Value));
                break;
            case "AgeTo":
                photosquery = photosquery.Where(x => calculateAge(Convert.ToDateTime(x.profile.BirthDate)) <= Convert.ToInt32(kvp.Value));
                break;
            case "EyeColor":
                photosquery = photosquery.Where(x => x.physical.EyesColor.Contains(kvp.Value));
                break;
        }
    }
}

//My code for calculateAge:

public static int calculateAge(DateTime birthDay)
{
    int years = DateTime.Now.Year - birthDay.Year;

    if ((birthDay.Month > DateTime.Now.Month) || (birthDay.Month == DateTime.Now.Month && birthDay.Day > DateTime.Now.Day))
        years--;

    return years;
}

推荐答案

有一个更轻松的解决方案.而不是比较年龄,而是比较日期本身.

case "AgeFrom":
    var fromAge = Convert.ToInt32(kvp.Value);
    var maxDate = DateTime.Today.AddYears(-fromAge);
    photosquery = photosquery.Where(x => x.profile.BirthDate <= maxDate);
    break;
case "AgeTo":
    var toAge = Convert.ToInt32(kvp.Value);
    var minDate = DateTime.Today.AddYears(-toAge-1).AddDays(1);
    photosquery = photosquery.Where(x => x.profile.BirthDate >= minDate);
    break;

这具有制作查询 sargable 假设您已经在BirthDate字段上创建了索引.

,可以使用索引.

其他推荐答案

您遇到此错误的原因是,您的Where prause正在您的实体查询中调用,并且不知道如何将C#代码转换为数据库语言的等效内容.

如果您能够将该函数的内部重写为没有基于.NET的变量存储或仅引用数据库中可用的CLR函数的内容,则可以在数据库中使用它,就好像您没有使用它有函数调用:

var now = DateTime.Now;
...    
case "AgeFrom":
    photosquery = photosquery.Where(x => DbFunctions.DiffYears(x.profile.BirthDate,now) >= Convert.ToInt32(kvp.Value));
    break;

如果要使用所拥有的代码,则需要在过滤之前检索所有数据,这通常不建议.您仍然可以通过在您的.there(..)子句之前调用.tolist()来实现这一目标.

其他推荐答案

您正在使用IQueryable,因此您不能在实现之前将其与任意C#代码混合.

现在您有两个选择:

  1. 使用ToList或ToArray加载所有数据并应用您的过滤方法
  2. 重写您的查询以使用一些sql函数(DATEPART为这种情况)

对于第二种方式,您可以编写此查询(switch之前):

var newQuery = photosquery.Select(x => new
{
    AllQuery = x,
    yearsDiff = (x.profile.BirthDate.Month > now.Month) ||
                (x.profile.BirthDate.Month == now.Month 
                   && x.profile.BirthDate.Day > now.Day)
        ? now.Year - x.profile.BirthDate.Year - 1
        : now.Year - x.profile.BirthDate.Year
});

然后,当您输入switch时,您可以写作:

case "AgeFrom":
  photosquery = newQuery
      .Where(x => x.yearsDiff >= Convert.ToInt32(kvp.Value))
      .Select(x => x.AllQuery );
  break;

本文地址:https://www.itbaoku.cn/post/1556950.html

问题描述

please see calculateAge to convert the Birthdate to current age in years in LINQ Query (it works for other keys, ex: EyeColor, but age conversion failing), I would like to compare with the value that was stored in Array. I tried storing the value in DateTime temp variable above the link, right below "case "AgeTo":" but I can not take the value x.profile.BirthDate as it's allowed only within LINQ Query. It appears that LINQ query is not giving error during build, however, it's failing to con

Error:

LINQ to Entities does not recognize the method 'Int32 calculateAge(System.DateTime)' method, and this method cannot be translated into a store expression.

Any help how to handle?

Code and calculateAge method:

if (searchList.Count > 0)
{
    foreach (KeyValuePair<String, String> kvp in searchList)
    {
        switch (kvp.Key)
        {
            case "AgeFrom":
                photosquery = photosquery.Where(x => calculateAge(Convert.ToDateTime(x.profile.BirthDate)) >= Convert.ToInt32(kvp.Value));
                break;
            case "AgeTo":
                photosquery = photosquery.Where(x => calculateAge(Convert.ToDateTime(x.profile.BirthDate)) <= Convert.ToInt32(kvp.Value));
                break;
            case "EyeColor":
                photosquery = photosquery.Where(x => x.physical.EyesColor.Contains(kvp.Value));
                break;
        }
    }
}

//My code for calculateAge:

public static int calculateAge(DateTime birthDay)
{
    int years = DateTime.Now.Year - birthDay.Year;

    if ((birthDay.Month > DateTime.Now.Month) || (birthDay.Month == DateTime.Now.Month && birthDay.Day > DateTime.Now.Day))
        years--;

    return years;
}

推荐答案

There's an easier solution. Rather than comparing the age, compare the dates themselves.

case "AgeFrom":
    var fromAge = Convert.ToInt32(kvp.Value);
    var maxDate = DateTime.Today.AddYears(-fromAge);
    photosquery = photosquery.Where(x => x.profile.BirthDate <= maxDate);
    break;
case "AgeTo":
    var toAge = Convert.ToInt32(kvp.Value);
    var minDate = DateTime.Today.AddYears(-toAge-1).AddDays(1);
    photosquery = photosquery.Where(x => x.profile.BirthDate >= minDate);
    break;

This has the added benefit of making the query sargable, so it can make use of an index, assuming you've created one on the BirthDate field.

其他推荐答案

The reason you're getting this error is that your Where-clause is being invoked on your Entity queryable, and it doesn't know how to translate the C# code into the equivalent for your database language.

If you were able to rewrite the internals of that function into something that didn't have .NET-based variable storage or referenced only CLR functions that are available in the database you could use it inline, as if you didn't have the function call:

var now = DateTime.Now;
...    
case "AgeFrom":
    photosquery = photosquery.Where(x => DbFunctions.DiffYears(x.profile.BirthDate,now) >= Convert.ToInt32(kvp.Value));
    break;

If you want to use the code you have though, you'll need to retrieve all of the data before filtering, which is not generally recommended. You could still achieve this by calling .ToList() before your .Where(..) clause.

其他推荐答案

You are working with IQueryable, so you can't mix it with arbitrary C# code before materialization.

Now you have two options:

  1. Load all your data into app memory with ToList or ToArray and apply your filtering method
  2. Rewrite your query to use some sql functions (DATEPART for this case)

For second way you can write this query (before switch):

var newQuery = photosquery.Select(x => new
{
    AllQuery = x,
    yearsDiff = (x.profile.BirthDate.Month > now.Month) ||
                (x.profile.BirthDate.Month == now.Month 
                   && x.profile.BirthDate.Day > now.Day)
        ? now.Year - x.profile.BirthDate.Year - 1
        : now.Year - x.profile.BirthDate.Year
});

Then when you enter your switch you can write:

case "AgeFrom":
  photosquery = newQuery
      .Where(x => x.yearsDiff >= Convert.ToInt32(kvp.Value))
      .Select(x => x.AllQuery );
  break;