How to convert two columns to one row column with dynamic linq?
I am using System.Linq.Dynamic to dynamically query multiple database sources (SQL Server) and it all works very well, but now I am caught in a trap. I need to be able to take two numeric columns and format them into one line. Basically, it boils down to me doing something like this (note: this is not exactly what I am doing, because it is all dynamic, and in fact the string passed to Select
is dynamically created):
var mystuff = data.Select("year, qtr, string.Format(\"{0}Q{1}\",year,qtr) as yearQtr");
Surprisingly, it System.Linq.Dynamic
really does interpret it the way I wanted it, but unfortunately it throws an error System.ArgumentException
:
Expression of type 'System.Int16' cannot be used for parameter of type 'System.Object'
of method 'System.String Format(System.String, System.Object, System.Object)'
I think the problem is that it doesn't box fact.year
(which is of type System.Int16
) automatically like you would normally use string.Format
. So, is there a way to tell about this in the field year
and qtr
?
I tried just casting on object
, but it doesn't work:
var mystuff = data.Select("year, qtr, string.Format(\"{0}Q{1}\",(object)year,(object)qtr) as yearQtr");
An exception of type 'System.Linq.Dynamic.ParseException' occurred in JobsEQ.Web.dll but was not handled in user code
Additional information: '.' or '(' expected
Apparently dynamic linq doesn't understand casting.
Update : only ToString()
these came to my mind :
var mystuff = data.Select("year, qtr, string.Format(\"{0}Q{1}\",year.ToString(),qtr.ToString()) as yearQtr");
But now I am getting another Exception ( System.NotSupportedException
) which makes me think that I am going down the wrong path:
LINQ to Entities does not recognize the method 'System.String Format(System.String, System.Object, System.Object)' method, and this method cannot be translated into a store expression.
So, is there any other way to do this converting those two columns to one row column?
source to share
Just do string manipulation on the application side, rather than trying to do it on the database side. As you can see, although dynamic LINQ can create an expression tree to represent the call Format
, EF doesn't know how to translate it to SQL.
Instead, just ask the database about the columns you need, and then string convert those values on the application side using LINQ on objects.
source to share