1

I'm using EF6 code first with System.Dynamic.Linq. I'm trying to figure out how to cast string to decimal. Basically the same as was requested here: Dynamic LINQ Cast issue

My system parses files that generate Name/Value pairs. These are stored in a table as varchar columns. The values could represent any type (string, int, decimal, datetime, etc.) so I can't really save to the actual data type, hence the reason everything is stored as varchar.

I have a textbox in the application that lets the user enter a dynamic linq query. For instance, a user might be attempting to do this:

Name == "Test Value" && Value > 5.5

or

Name == "Other Value" && Value == "My String Value"

The problem is I can't seem to do the cast necessary on the Value column. I'm mainly interested in converting to decimal for the moment since that's what most of the data is. I've tried a number of suggestions I've found out there:

  1. Convert.ToDecimal(Value): Linq to Entities does not recognize the method
  2. Decimal.Parse(Value): Linq to Entities does not recognize the method
  3. Tried implementing a DbFunction (Example). Converted it to be decimal instead of int. ParseDecimal(Value): No applicable method exists. And apparently it's looking for that method on the business object itself.

Seems like a simple and common thing to need to do. What am I missing?

Dan
  • 533
  • 8
  • 29

1 Answers1

0

One solution might be to materialize the list (so you select your Value as a String) then again use LINQ Dynamic to perform another select, but this time, it will be on the application side so you will no longer be limited by EF6 limitations

var list = context.MyEntities.Select("[the dynamic select without conversion]")
                             .ToDynamicList()
                             .Select("[the dynamic select with conversion]")
                             .ToDynamicList();
Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60
  • So, in other words, do the conversion in memory. But that would pull all the data over to the client side first. I'm not sure I really want to do that. I was hoping for some way to just implement a custom function that works both with EF6 and dynamic linq. – Dan Sep 25 '20 at 14:43
  • No, you can select only the field you want first, just without the convert that you will do after on the application side. – Jonathan Magnan Sep 28 '20 at 12:08
  • That really sucks. Seems like a major oversight. Imagine having to pull gigabytes worth of data to find one record simply because you can't filter correctly :-/ I guess my case isn't that bad thankfully. So, on something like Convert.ToDecimal, it seems dynamic linq understands this, it's just that EF6 doesn't. Is there any way to extend EF6 to understand that and generate CAST/CONVERT SQL? – Dan Sep 28 '20 at 14:04