Character (string) encoded number to number comparison in Linq

Question

I have this query.

  levelnumber = (from b in bv.baLevels
                 where b.ba_Level_Code == ("0" + SqlFunctions.StringConvert((double)cl.Level_Num.Value)) && b.isActive == 1
                 select (b.ba_Level_Code + " - " + b.ba_Level_Desc)).FirstOrDefault(),

My Problem is b.ba_Level_Code is string.

cl.Level_Num is Int.

baLevels table I have this ba_Level_code value has 008

but cl is the BaCodeLibrary table this cl.Level_Num has 8

if I do the query with hardcoded value

 levelnumber = (from b in bv.baLevels
                where b.ba_Level_Code == "008" && b.isActive == 1
                select (b.ba_Level_Code + " - " + b.ba_Level_Desc)).FirstOrDefault(),

how to display 008? cl.Level_Num I am getting has 8. I need to change that value to 008.

Thanks

score 2 · Answer 1 · answered Oct 16 '11 at 14:01

I can't see a good way to do this in LINQ to Enties. Assuming that you cannot modify the data type of the ba_Level_Code column, I think your best option is to add a persisted computed column to that table, index it, and use that column in your query.

For example, if you defined a column

ba_Level_Code_Int as (case when isnumeric(ba_Level_Code)= 1 then cast(ba_Level_Code as int) end) persisted

then your LINQ query could be

levelnumber = (from b in bv.baLevels   
               where b.ba_Level_Code_Int == cl.Level_Num.Value && b.isActive == 1   
               select (b.ba_Level_Code + " - " + b.ba_Level_Desc)).FirstOrDefault();

score 1 · Accepted Answer · answered Oct 16 '11 at 02:09

1

You could use string.Format to specify that your `cl.Level_Num' is converted to a string with a minimum number of digits:

string.Format("{0:D3}", cl.Level_Num.Value) // would ouput "008" value 8

Thus:

levelnumber = (from b in bv.baLevels
               where b.ba_Level_Code == string.Format("{0:D3}", cl.Level_Num.Value) && b.isActive == 1
               select (b.ba_Level_Code + " - " + b.ba_Level_Desc)).FirstOrDefault()

answered Oct 16 '11 at 02:09

jeffora

4,009
2
25
38

Thanks Jeffora, but I have some other level Number in the table has 046,023,056.....etc.. I think I will get problem that time. – user957178 Oct 16 '11 at 02:12
I am getting this error.LINQ to Entities does not recognize the method 'System.String Format(System.String, System.Object)' method, and this method cannot be translated into a store expression. – user957178 Oct 16 '11 at 02:14
@user957178 looks like you have to cast the 2nd parameter to a type. – Hogan Oct 16 '11 at 14:04
Just FYI the String.Format call would work in those situations too (in terms of formatting - D3 means minimum 3 digits so will put in leading zeros if required). As for the Linq To Entities problem, it sounds like a pain to get around and I agree with adrift's recommendation – jeffora Oct 16 '11 at 14:18

Character (string) encoded number to number comparison in Linq

2 Answers2