How to convert a string to decimal within an IQueryable

c# entity-framework-6 iqueryable


Well, this sounds easier than it is! I have a table that contains amount stored as string values. But my code needs them as decimal values and it needs them to be casted to decimal as part of the query that IQueryable is making! Thus, the use of .ToList() is not possible. The cast needs to be done by the database behind it.
This IQueryable is basically part of a chain on IQueryables so a next query might want to add a filter on this amount to make a smaller selection, or do other kinds of math with the amount. The query that goes in might already be a subselection of all data.
Edit: Let me explain what I am working on here: I have something like this method to write:

public static IQueryable<PriceList> GetPriceList(this IQueryable<PriceData> query) => query.Select(d => new PriceList{Name = d.Item.Name, Price = decimal.Parse(d.Value)}).AsQueryable();

And PriceData is a record containing a few fields and the prices as string values. But the PriceList record needs them as Decimal.
This method could then be used by another extension method for further selections, math and whatever. It's just that decimal.parse and other options don't work within an IQueryable...

12/7/2017 4:56:26 AM

Accepted Answer

As already commented, what you are trying to do is not supported.

  • Your best bet is to change your database field type to what it actualy represents: Money/Decimal
  • If that is not possible, talk to the person, which has the ability to make it possible

If that is not possible, the closest workaround I can imagine is:

public class PriceList
    public string Price { get; set; }
    public decimal ParsedPrice => decimal.Parse(Price);

And then:

.Select(d=>new PriceList
    Price = d.Price

However, you can't execute any DbOperations on ParsedPrice then. Also note that you can't use a Constructor with parameters in EF6. That would be the next problem you would encounter.

EF works best for CRUD. For everything more you probably should have a Service, which is fetching the data only you need, materialize them and make a DTO or Businessobject out of it.

Something like that:

public class MyService
    public IEnumerable<PriceList> GetPriceList(Expression<Func<MyEntity, bool>> predicate)
        var data = _context.MyEntity.Where(predicate).ToList();
        foreach (var item in data)
            var dto = new PriceList {...}
            yield return dto;
12/7/2017 5:17:53 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow