Int.Parse in Linq Expression

Question

I have the following LINQ expression. I want calculate the sum of numeric values in an nvarchar field. I'm using following code to do this, but I get an error when I try to run this.

var m = new MaterialModelContainer();

var list = 
    (from x in
        (
            from inv in m.INVs
            join l in m.LIBs on inv.MESC equals l.MESC
            join o in m.OUTs on inv.MESC equals o.MESC
            join t in m.TRANs on inv.MESC equals t.MESC
            where t.TYPE == "60"
            select new
            {
                l.MESC,
                l.LINE_NO,
                l.UNIT_LINE,
                Description = l.DES + " " + l.PART_NO,
                inv.NEW_QTY,
                o.PJ,
                o.DATE,
                o.QTY,
                o.QTY_REC,
                TranQty = t.QTY,
                tranDate = t.DATE
            }
        )
        group x by
            new
            {
                x.MESC,
                x.LINE_NO,
                x.UNIT_LINE,
                x.Description,
                x.NEW_QTY,
                x.PJ,
                x.DATE,
                x.QTY,
                x.QTY_REC
            }
        into g
        select new
        {
            QTY_Consum_1 = g.Where(c => int.Parse(c.tranDate) >= cuDate && int.Parse(c.tranDate) <= endDate).Sum(d => int.Parse(d.TranQty))
        }
    ).ToList();

Error Description:

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

How can I solve this problem and write this code better than this?

I changed the code to this:

select new
{
    QTY_Consum_1 = g.Where(c => SqlFunctions.StringConvert(c.tranDate) >= cuDate && SqlFunctions.StringConvert(c.tranDate) <= endDate).Sum(d => SqlFunctions.StringConvert(d.TranQty)),
   g.Key.MESC
}
).ToList();

but got this error:

Answer 1

EF 5:

Instead of int.Pasrse use Convert.ToInt32. Entity Framework will generate proper CAST functions in SQL.

EF 6:

Short answer:

youEntity.Where(c=>SqlFunctions.StringConvert((decimal?)c.INTFIELD).Trim() == STRINGVALUE)

Long answer:

in EF 6 you have to convert numeric value to string with SqlFunctions.StringConvert. but it has a problem. It will add unnecessary spaces to the result. so the comparison will fail. That's why I have put Trim() there. I have tested it with EF 6.1.1.

Answer 2

Entity Framework can't translate that type of conversion to SQL.

Is there any chance you could alter your data structure to use proper data types such as actual DateTime types? For large data volumes conversions like that will affect performance.

I would recommend either changing your data model types to avoid these conversions, or if the amount of data will always be small, then get the data first, and later use Linq to Objects.

Answer 3

In your where clause, you can't call int.Parse. Entity Framework doesn't know how to convert that to SQL. Consider revising your Where.