Cannot convert type string to decimal in linq query

Question

I have my linq query where I'm joining 3 tables. grouping them into another so I can sum values in table 3

var roles = from p in db.Properties 
                    join od in db.tblOrderDetails on p.pID equals od.odpID 
                    join s in db.tblServices on od.odsID equals s.sID                        
                    where p.poID == 0 && p.pActive == true   

                    group s by new {p.pID, p.pAddress} into g
                    select new
                    {
                        ID = g.Key.pID,
                        Address = g.Key.pAddress,
                        SubTotal = g.Sum(s => s.sPrice)
                    };

My problem comes in at the SubtTotal. I get "Cannot implicitly convert type 'string' to 'decimal?'" I tried using convert.todecimal but I get

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

sqlfunctions.stringconvert doesn't help either. Any thoughts?

Answer 1

I think this conversion from LINQ to Entitites to LINQ to Objects could be used if the data volume is considerably small:

  var roles = (from p in db.Properties 
         join od in db.tblOrderDetails on p.pID equals od.odpID 
         join s in db.tblServices on od.odsID equals s.sID                        
         where p.poID == 0 && p.pActive == true   

         group s by new {p.pID, p.pAddress} into g)
           .AsEnumerable().Select(p => new
                {
                    ID = p.Key.pID,
                    Address = p.Key.pAddress,
                    SubTotal = p.Sum(s => s.sPrice)
                });