score:1

Accepted answer

you get the exception because you work with iqueryable. entity framework will try to translate the predicate in the where clause into sql but ef doesn't know how to translate your method.

if you don't want to (or can't) change the database you still have a few options:

  1. pull all rows to the client side and do the filtering on the client. you do this by changing from iqueryable to ienumerable:

    return getallproducts()
  .asenumerable()
  .where(m => equals(dateutilities.extractyear(m.productcreationdate), year))
  .asqueryable();

  2. to be more efficient you can use your own sql (this requires ef 4.1 and only avoid pulling all products to the client, not the scan on the server):

    return context
  .products
  .sqlquery("select * from products where substring(productcreationdate, 1, 4) = '2012'")
  .asqueryable();

    note that i was lazy and hardcoded the sql. you should probably parametrize it and make sure you avoid any sql injection attacks.

score:1

you could do the extraction inline, something like this:

public iqueryable<products> getcreatedproducts(int year)
{
        string syear = year.tostring();
        return getallproducts().where(m => m.productcreationdate.substring(0,4) == syear);
}

score:2

i don't think this is really a bug so much as a limitation -- there's no sql version of extractyear, so it's not possible to execute this query.

you'll have to rewrite the query to either use only sql-compatible commands (like some other answers have shown) or pull lots of data out of the database and do the filtering with linq-to-objects (this could potentially be very expensive, depending on what you're querying and how many of the records match).

score:4

my first question would be why are you storing a date in the database as a string? store it as a date and use data comparison against it through linq.

the issue that you're coming up against is linq doesn't understand what the method extractyear is when it tries to compile your linq statement to raw sql

edit another alternative would be to create a view in the database and query against the view with a computed column that represents the string value as a datetime.

score:4

in this case you could go the other way:

getallproducts().where(m => m.productcreationdate.startswith(year.tostring()));

rather than extract the year from the string, find all strings beginning with the year you're after.

i'm not sure if these will work for the month and day searches:

var monthstring = string.format("{0:00}", month);
getallproducts().where(m => m.productcreationdate.substring(4,2) == monthstring);  

var daystring = string.format("{0:00}", day);
getallproducts().where(m => m.productcreationdate.substring(6,2) == daystring);

Related Query

More Query from same tag