LINQ to Entities string to decimal a column

Question

I have researched this question to death. Everyone and their brother wants to know how to convert an int or decimal to string, but I can't find any example of doing the opposite with EF.

My data source has an order total_amt column in the database that is of type varchar. The reason is because the source data was encrypted. The decryptor does so in place. I could rewrite that to decrypt to a temp table and then insert those results to a properly typed table but that would require allot more work, both now and during DB updates as we expand the app.

I'd love to be able to cast the columns but I can't figure out how to do that with Linq and EF.

public ActionResult Orders_Read([DataSourceRequest]DataSourceRequest request) {
var db = new Ecommerce_DecryptedEntities();
var orders = from s in db.Orders
                where s.Order_Complete == true
                select new { 
                    s.Order_Id, 
                    s.MySEL_Name, 
                    s.MySEL_EMail, 
                    s.MySEL_Bus_Name,
                    s.Total_Amt,
                    s.Order_Complete_DateTime
                };
DataSourceResult result = orders.ToDataSourceResult(request);
return Json(result, JsonRequestBehavior.AllowGet);
}

Note: the result needs to be iQueryable. I really don't want to ToList this as that would pull all the data from the DB. This is being bound to a Telerik KendoUI Grid which is passing in paging, sorting and other params (notice it's being cast to KendoUI's ToDataSourceResult using the passed in request which hold the above mentioned paging, sorting, etc. data.

possible duplicate of [Problem with converting int to string in Linq to entities](http://stackoverflow.com/questions/1066760/problem-with-converting-int-to-string-in-linq-to-entities) — Win, Mar 05 '13 at 16:15
Does the Total_Amt need to be an int or a decimal for a certain amount of time? How often are you using it? — DJ Burb, Mar 05 '13 at 16:16
@Win, that's a negative, as I clearly stated I want to do the opposite, convert a string to another format, in this case decimal. — David L. Sargent, Mar 05 '13 at 16:22
@DJBurb. I just need it to be a Decimal in the resulting orders var, so idealy I'd like to do something like "cast(s.Total_Amt as decimal)" — David L. Sargent, Mar 05 '13 at 16:25
@Win My apologies, I reread my comment and realized it came across rather jerk like, totally didn't mean it that way. Any help is appreciated. — David L. Sargent, Mar 05 '13 at 16:45

Jan P. · Answer 1 · 2013-03-05T16:26:24.317

1

You have to get the data to a List<Order> first and after that you can cast whatever you want.

var orders = 
    from s in
        ((from o in db.Orders
        where o.Order_Complete
        select o).ToList())
    select new { 
        s.Order_Id, 
        s.MySEL_Name, 
        s.MySEL_EMail, 
        s.MySEL_Bus_Name,
        Double.Parse(s.Total_Amt),
        s.Order_Complete_DateTime
    };

I think the EMS way would look much better in your code ;)

var orders =
    db.Orders.Where(s => s.Order_Complete).ToList().Select(s => new { /*...*/ }

After casting ToList() you have got the data object based and can modify it, if you don't you can use Double.Parse because EF is trying to find e.g. a stored procedure on the database and will throw an exception.

edited Mar 05 '13 at 16:26

answered Mar 05 '13 at 16:20

Jan P.

3,261
19
26

I thought of that, the problem is that it would get all of the data from the table. This is being bound to a Telerik Kendo UI grid that is passing in sort, grouping and paging data and requires iQueryable. I'll edit the question to elaborate. – David L. Sargent Mar 05 '13 at 16:29
.AsQueryable() :p i do not see a way to archieve your goal like you want it... but anyways the grid will also get the data from the DB – Jan P. Mar 05 '13 at 22:28

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

Have you tried model-defined functions?

<Function Name="ConvertToDecimal" ReturnType="Edm.Decimal">
     <Parameter Name="myStr" Type="Edm.String" />
     <DefiningExpression>
          CAST(myStr AS Edm.Decimal(12, 2))
     </DefiningExpression>
 </Function>

Check this answer: Convert string to decimal in group join linq query

edited May 23 '17 at 12:21

Community

1
1

answered Mar 05 '13 at 21:12

Davor Zlotrg

6,020
2
33
51

LINQ to Entities string to decimal a column

2 Answers2

Linked

Related