Geek asks and answers

Getting character values ​​in Linq for objects

I have a table Assessmentthat has (among others) two columns, CurrentGradeand PreviousGrade, both of which are defined as char(1). A class is a symbol from A to Z.

Now I want to write a query to get the difference between the current class and the previous level of all entries in Assessment, to be expressed as a number, for example. if the previous A, and the current is D, then the difference is 3.

I would do something like this:

var q = db.Assessments.Select(a => new { a.ID, Diff = a.CurrentGrade[0] - a.PreviousGrade[0] });

... except that this does not work, because for String.getCharsthere is no SQL translation.

And I don't want to do this in local memory, because in my real code this is just part of the big where clause, which includes other expressions that must be executed in Linq to Entities.

Any recommendations how to do this?

+3
source share
1 answer

In Linq-To-Entities you can use SqlFunctions.Ascii:

using System.Data.Objects.SqlClient;

var result = db.Assessments.Select(a => new { a.id, 
    Diff = SqlFunctions.Ascii(a.CurrentGrade).Value - SqlFunctions.Ascii(a.PreviousGrade).Value });

Please note that if the CurrentGradeabove (A), than PreviousGrade(D), you get an answer -3that is the opposite of what you may want to ... So you can multiply by -1to get the true direction of the difference in the classroom.

var result = db.Assessments.Select(a => new { a.id, 
    Diff = (SqlFunctions.Ascii(a.CurrentGrade).Value - SqlFunctions.Ascii(a.PreviousGrade).Value) * -1 });
+3
source

More articles:


All Articles