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?

Answer 1

 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();