How to create a new DateTime where condition using table field with LINQ

Question

i have a win application in c# where i would obtain a linq query with a where condition containing a Data concatened by table fields. my query is :

var fatture = (from t016 in cont.T016_FATT
              where t016.ID_FATTURA = aiIdFattura
              && Convert.toDateTime("01" + t016.COD_MESE+"/"+t016.ANNO) > aiData
              select t016).ToList();

where t016.COD_MESE is a string rapresenting month value, aiIdFattura is a decimal parameter passed in function and aiData is a DateTime parameter.

but this linq query gives me this error:

LINQ to Entities does not recognize the method Convert.ToDateTime

i have found in internet a solution suggesting to use DBFunctions that is present in EntityFramework (version 6) but i have a minor version of EntityFramework so i cannot use this function.

another found solution was to format date before linq query but in my case is not possible because depending from T016_FATT so i cannot format my date first.

so have you any idea to suggest me?

thanks a lot

...it looks like you're missing the separator between month and day-of-month. I'd be concerned if that was the method's expected input. Also, converting every row like this is going to cost you performance, doing an index or possibly table scan (will depend on what indices you have, and percentage of rows matching the first condition). You don't actually care about the date-time itself per se, so why not extract year/month from aiData? (or potentially store the full date, or ping a calendar table) — Clockwork-Muse
– Clockwork-Muse, Commented Mar 31, 2016 at 14:10
i'm sorry in my code is "01/" + t016.COD_MESE+"/"+t016.ANNO, i have replied wrong — giovanna zoppi
– giovanna zoppi, Commented Apr 1, 2016 at 6:38

ocuenca · Accepted Answer · 2016-03-31 14:20:06Z

1

Remember your query is going to be translated to SQL, and in this case your Linq provider doesn't know how to translate that method call to a sql statement. To avoid that issue, you can make the switch of Linq to Entities to Linq to Objects using AsEnumerable extension method. After you call that method you can apply your date conversion:

var fatture = cont.T016_FATT.Where(e=>e.ID_FATTURA == aiIdFattura)
                            .AsEnumerable()
                            .Where(e=>Convert.toDateTime("01" +"/"+ e.COD_MESE+"/"+e.ANNO) > aiData)
                            .ToList();

If you want to see the methods that are supported by Linq to Entities, you can check this link.

edited Mar 31, 2016 at 14:20

answered Mar 31, 2016 at 13:51

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a new DateTime where condition using table field with LINQ

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related