calculateAge (or year) from LINQ Query DateTime field using C# for comparision (Age From)

Question

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;
}

You do realise that if you perform your code near midnight if might fail? Better ask only once for the current time, to ensure that all items in your search list are compared using the same time. Also easier to write a decent specification: "This function calculates the age of all items in the collection at the moment the function is called". The result will be predictable, regardless of how fast your procedure performs. Furthermore consider using DateTime.Today, instead of DateTime.Now — Harald Coppoolse, Oct 16 '17 at 06:43
Good point, I’ll change the code as get back to you on my success. — dotNETEngineer, Oct 16 '17 at 20:38

Rob G · Answer 1 · 2017-10-15T22:50:53.520

3

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.

edited Oct 15 '17 at 22:50

answered Oct 15 '17 at 22:38

Rob G

3,496
1
20
29

Thanks Rob, looks like I missed your comment. I’ll try it. – dotNETEngineer Oct 16 '17 at 20:10
1

Rob, I got it worked with Matt's response, thanks for the help. DbFunctions class is fairly usable as well. I'll keep a note for future calculations. – dotNETEngineer Oct 17 '17 at 02:40
Hi, can someone review the SQL syntax and help me - https://stackoverflow.com/questions/47849473/select-statement-has-case-that-provies-select-value-sql-server – dotNETEngineer Dec 16 '17 at 20:05

score 1 · Accepted Answer · answered Oct 16 '17 at 21:33

1

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.

answered Oct 16 '17 at 21:33

Matt Johnson-Pint

230,703
74
448
575

THank you for the help Matt JOhnson, this worked for me. BirthDate field is DateTime field and it can be compared with converted maxDate or minDate variables.. I appreciate the help. – dotNETEngineer Oct 17 '17 at 02:38
Can someone respond to the below (similar) question? This time I ned to compare with numeric value. https://stackoverflow.com/questions/46855132/convert-to-int-from-string-from-linq-query-field-using-c-sharp-for-comparison-h – dotNETEngineer Oct 20 '17 at 18:25

score 0 · Answer 3 · edited Nov 16 '18 at 18:09

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

Now you have two options:

Load all your data into app memory with ToList or ToArray and apply your filtering method
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;

calculateAge (or year) from LINQ Query DateTime field using C# for comparision (Age From)

3 Answers3

Linked