0

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...

Wim ten Brink
  • 25,901
  • 20
  • 83
  • 149
  • 1
    How about `dbContext.someTable.SqlQuery("SELECT CONVERT(amount, DECIMAL) FROM someTable").AsIQueryable()`? – Isaac Kleinman Dec 07 '17 at 03:23
  • If only I could do that, yeah. But no. I get an IQueryable as input and must kick out an IQueryable as output as it is all part of a larger expression tree... – Wim ten Brink Dec 07 '17 at 03:52
  • 1
    Why is price in first instance a string? This seems wrong. (And should probably be Money, Decimal). That said, *probably* a duplicate of https://stackoverflow.com/questions/12118258/int-parse-in-linq-expression – Christian Gollhardt Dec 07 '17 at 04:26
  • Yes, I know. It is wrong and I have no control over it. It's just part of the chain of expressions. – Wim ten Brink Dec 07 '17 at 04:37
  • As for duplicate? Nope, as the other question is over 5 years old as the answer there suggests the use of .ToList(). I can't use that as that means the IQueryable loses its link to the database and will actually execute the query. That's not allowed in my situation! – Wim ten Brink Dec 07 '17 at 04:38
  • This is the annoying part: "Unhandled Exception: System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Decimal ToDecimal(System.String)' method, and this method cannot be translated into a store expression." And again, I can't use ToList(). – Wim ten Brink Dec 07 '17 at 04:41
  • One answer suggesting `ToList`. The correct answer says it is not possible. Also note: new object without a parameterless constructor was not supported the last time I tried it. I am sorry to say, but what you are trying to do is not supported. – Christian Gollhardt Dec 07 '17 at 04:43
  • So, after 5 years this is still not supported? As for the parameterless thing, let me edit that... – Wim ten Brink Dec 07 '17 at 04:55
  • As for the price being a string... The data is retrieved from a simple key/value table which contains just two string fields linked to an item. It has various different properties for an item but I just get the records where the key value is "Price". Other keys are size, color and a few other things. – Wim ten Brink Dec 07 '17 at 04:58

1 Answers1

1

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;
        }        
    }
}
Christian Gollhardt
  • 16,510
  • 17
  • 74
  • 111
  • Yeah, it is annoying as EF does have the SqlFunctions static class which can convert numerical values to strings. So why no similar things to convert in the other direction? I found https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/clr-method-to-canonical-function-mapping which doesn't list any string-tonumber conversions so it seems it's not possible. For now... – Wim ten Brink Dec 07 '17 at 05:40
  • 1
    Probably because every int can be string, but not every string can be a int. But I don't want to make any assumptions, I am not 100 % sure about. – Christian Gollhardt Dec 07 '17 at 05:45
  • True, but if conversion fails, you could return NAN as result. Or an exception as many databases will just as easily return errors. But both DBFunctions and SqlFunctions seem to suggest that it should be possible to map C# functions to their SQL counterpart. Then again, the need to convert strings to numbers tends to be considered a design flaw. One I can't fix in the current system. – Wim ten Brink Dec 07 '17 at 07:42