Split string and use in Select calculation

Question

I'm trying to split a string field and use the values in a calculation in a Select new so I can display the info in a report. The calculation is for the cubic meters of a package. The following code is what I'm using now but when I try to multiply the values from the split string I get an error:

Could not translate expression 'Parse(grp.Key.Length)' into SQL and could not treat it as a local expression

 var res = (from packs in data
            where packs.DispatchDate != null || (packs.DispatchDate >= DateTime.Parse(_dateFrom).Date &&
            packs.DispatchDate <= DateTime.Parse(_dateTo).Date)
            orderby packs.Production.DimensionMetric
            group packs by
            new
            {
                 packs.DispatchDate,
                 packs.ProductType.TypeDetails,
                 packs.PackNo,
                 packs.Thickness,
                 Width = packs.Production.DimensionMetric.Substring(0,packs.Production.DimensionMetric.IndexOf('x')),
                 Length = packs.Production.DimensionMetric.Substring(packs.Production.DimensionMetric.IndexOf('x')+1),
                          packs.Sheets,
            }
            into grp
            select new
            {
                 grp.Key.DispatchDate,
                 grp.Key.TypeDetails,
                 grp.Key.PackNo,
                 grp.Key.Thickness,
                 grp.Key.Width,
                 grp.Key.Length,
                 grp.Key.Sheets,
                 CBM = ((int.Parse(grp.Key.Length)) * (int.Parse(grp.Key.Length))).ToString(),
             }).ToList();

If I just assign or concat the values from Length and width to CBM there is no error, its only when I perform a math calculation on the 2 values. Can someone explain what needs to be done to make this work or do I have to come at it from a different angle?

In order to understand this error message, I suggest you look [here](http://stackoverflow.com/a/3234510/111794) or [here](http://stackoverflow.com/questions/3635418/custom-method-in-linq-to-sql-query) as a starting point. — Zev Spitz, May 29 '16 at 13:05
Note that the problem is not in splitting the string; it's in trying to call the `int.Parse` method. — Zev Spitz, May 29 '16 at 13:16
Are you sure this is not Entity Framework? L2S should be able to run this. LINQ support in EF is notoriously lacking. — usr, May 29 '16 at 13:36
@usr It does seem to be a LINQ to SQL exception message, but it puzzles me too why it isn't executed. LINQ to SQL should auto-switch to LINQ to objects to do the parsing. LINQ to Entities wouldn't even swallow the IndexOf. If only OP's would respond to comments... — Gert Arnold, May 29 '16 at 21:57

Robert McKee · Answer 1 · 2016-05-29T20:28:46.923

 var res = (from packs in data
        where packs.DispatchDate != null || (packs.DispatchDate >= DateTime.Parse(_dateFrom).Date &&
        packs.DispatchDate <= DateTime.Parse(_dateTo).Date)
        orderby packs.Production.DimensionMetric
        group packs by
        new
        {
             packs.DispatchDate,
             packs.ProductType.TypeDetails,
             packs.PackNo,
             packs.Thickness,
             Width = packs.Production.DimensionMetric.Substring(0,packs.Production.DimensionMetric.IndexOf('x')),
             Length = packs.Production.DimensionMetric.Substring(packs.Production.DimensionMetric.IndexOf('x')+1),
                      packs.Sheets,
        }
        into grp
        select grp)
         .AsEnumerable()
         .Select(x=>new {
             x.Key.DispatchDate,
             x.Key.TypeDetails,
             x.Key.PackNo,
             x.Key.Thickness,
             x.Key.Width,
             x.Key.Length,
             x.Key.Sheets,
             CBM = ((int.Parse(x.Key.Length)) * (int.Parse(x.Key.Length))).ToString(),
   })
   .ToList();

Split string and use in Select calculation

1 Answers1