I have a column in my database (SQL Server) storing averages as varchar.
Values are not always valid numbers, it can be something like : 0
, 12
, 5.5
, .4
, 4.0
, 6.
, 5.21428571428571428571428571428571428571
, X
, fail
, or simply empty text.
When assigning variable with linq, I want to display :
success
if average is valid number and >= 4failed
if average is valid number and < 4The code :
myresults = myDataContext.myDbFunction()
double note;
from a in myresults
select new MainReportModel()
{
Id = a.id,
Lastname = a.nom,
Firstname = a.prenom,
Average = a.moyenne,
Result = double.TryParse(a.moyenne.Trim(), NumberStyles.Number, CultureInfo.InvariantCulture, out note) ? (double.Parse(a.moyenne.Trim(), NumberStyles.Number, CultureInfo.InvariantCulture) < 4 ? "failed" : "success") : a.moyenne
}
But Parse fails.
When replacing double.Parse(a.moyenne.Trim()
with any hardcoded value (for example double.Parse("3.3"
) it works fine.
So it seems one of the values of a.moyenne.Trim()
is causing Parse to fail.
How can it fail if TryParse returned true?
Edit : Here is the error message :
Could not translate expression 'value(MyProjectWeb.Models.MyProjectDataContext).myDbFunction().Select(a => new MainReportModel() {Id = a.id, Lastname = a.nom, Firstname = a.prenom, Average = a.moyenne, Result = IIF(TryParse(a.moyenne.Trim(), Number, Invoke(value(System.Func`1[System.Globalization.CultureInfo])), Invoke(value(System.Func`1[System.Double]))), IIF((Parse(a.moyenne.Trim(), Number, Invoke(value(System.Func`1[System.Globalization.CultureInfo]))) < 4), Invoke(value(System.Func`1[System.String])), "success"), ((a.moyenne) + "</span>"))})' into SQL and could not treat it as a local expression.
You can't perform TryParse on the Sql server, but if you are not doing further selection criteria, then you can try pulling the data from Sql as it is and then parsing on the client.
eg
myresults = myDataContext.myDbFunction()
double note;
var list =(from a in myresults
select new
{
a.id,
a.nom,
a.prenom,
a.moyenne,
}).ToList();
var finalResults = (from a in list
select new MainReportModel()
{
Id = a.id,
Lastname = a.nom,
Firstname = a.prenom,
Average = a.moyenne,
Result = double.TryParse(a.moyenne.Trim(), NumberStyles.Number, CultureInfo.InvariantCulture, out note) ? (double.Parse(a.moyenne.Trim(), NumberStyles.Number, CultureInfo.InvariantCulture) < 4 ? "failed" : "success") : a.moyenne
}