sort string as number in linq to entity

Question

I have som strings like "1","2","3","10" and etc and when use orderby sorted list is "1","10","2","3". I want to sort them as number like 1,2,3,...,10. I use below code to sort the list.

var model = (from c in General.db.GlbTbComboBases
             where c.ClassCode.Equals(classCode)
             select new ReturnData { id = c.BaseCode, name = c.FAName }).OrderBy(c => c.id,
             new SemiNumericComparer());
             if (model.Any())
            {
                CacheManager.cache.GetOrAdd<List<ReturnData>>(key, () => 
                model.ToList<ReturnData>());
                return model.ToList<ReturnData>();
            }

public class SemiNumericComparer : IComparer<string>
        {
            public int Compare(string s1, string s2)
            {
                if (IsNumeric(s1) && IsNumeric(s2))
                {
                    if (Convert.ToInt32(s1) > Convert.ToInt32(s2)) return 1;
                    if (Convert.ToInt32(s1) < Convert.ToInt32(s2)) return -1;
                    if (Convert.ToInt32(s1) == Convert.ToInt32(s2)) return 0;
                }

                if (IsNumeric(s1) && !IsNumeric(s2))
                    return -1;

                if (!IsNumeric(s1) && IsNumeric(s2))
                    return 1;

                return string.Compare(s1, s2, true);
            }

            public static bool IsNumeric(object value)
            {
                try
                {
                    int i = Convert.ToInt32(value.ToString());
                    return true;
                }
                catch (FormatException)
                {
                    return false;
                }
            }
        }

when I run the code I get this error :

LINQ to Entities does not recognize the method 'System.Linq.IOrderedQueryable`1[Salary.Classes.ReturnData] OrderBy[ReturnData,String](System.Linq.IQueryable`1[Salary.Classes.ReturnData], System.Linq.Expressions.Expression`1[System.Func`2[Salary.Classes.ReturnData,System.String]], System.Collections.Generic.IComparer`1[System.String])' method, and this method cannot be translated into a store expression.

It's a legacy database and I can't change any data type because may raise error on other applications.

Answer 1

You have two problems here:

1. You are storing numbers as strings in your database and
2. Youre trying to execute C# code on Sql Server

The exception you are receiving is due to the fact that the compiler cannot translate the comparison logic from SemiNumericComparer class into a sql query.

In order to achieve the desired result you could:

a) Load all data in memory and perform the comparison using SemiNumericComparer in memory by iterating through the selected results and ordering them after that like this:

var model = (from c in General.db.GlbTbComboBases
             where c.ClassCode.Equals(classCode)
             select new ReturnData { id = c.BaseCode, name = c.FAName })
            .ToList() // this will load data into memory
            .OrderBy(c => c.id, new SemiNumericComparer());

This, however is not a good approach because it will add a lot of useless memory consumption if the dataset is quite small and will crash your application if your dataset is larger than the available memory at a given time.

Edit As pointed out by @gsubiran this approach is not valid.

b) Convert your strings into numbers on Sql Server using SqlFunctions and order them as numbers using the ordering provided by Sql Server:

var model = (from c in General.db.GlbTbComboBases
             where c.ClassCode.Equals(classCode)
             select new ReturnData { id = c.BaseCode, name = c.FAName })
            .OrderBy(c => SqlFunctions.IsNumeric(c.id));

Answer 2

There is no a built in way to convert string to int on linq to entities. SqlFunctions class has many useful functions but no one of them have support to convert/parse/cast string to int.

You could use your own custom sql function and then add it to your EF model and use it as you need. I tested it with table value function, I don't know if it also work with scalar value functions.

CREATE FUNCTION [dbo].[StringToInt](
    @strInt AS VARCHAR(30)
)
RETURNS TABLE WITH SCHEMABINDING
RETURN
    SELECT TRY_CAST (@strInt AS INT) [IntValue];

Then you add it to your EF model (YourModel.context.cs)

[DbFunction("YourContext", "StringToInt")]
public virtual IQueryable<Nullable<int>> StringToInt(string strInt)
{
    var strIntParameter = strInt != null ?
        new ObjectParameter("strInt", strInt) :
        new ObjectParameter("strInt", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<Nullable<int>>("[YourContext].[StringToInt](@strInt)", strIntParameter);
}

Finally you just could use it in a sorting expression in this way:

var qry = context.SomeEntityYouHave
            .Where(x => x.Category == "Numbers")
            .OrderBy(
                x => ctx.StringToInt(x.StringPropertyContainingNumbersToSort)
                .FirstOrDefault()//Don't forget to call FirstOrDefault
            );

With this approach you are sorting data on SQL side avoiding the need to do an early call to .ToList() and then sort all the data on application memory. Of course is a better approach to have int values stored as int instead of strings but some times for different reasons it couldn't be an option.

Answer 3

If you want a numerical order you have to sort numberical data types:

    string[] str = {"1", "10", "2","011"};

    List<string> ordered = str.OrderBy(x => int.Parse(x)).ToList();

Answer 4

Try something like this:

var model = (from c in General.db.GlbTbComboBases
         where c.ClassCode.Equals(classCode)
         select new { Id = Convert.ToInt32(c.BaseCode), Name = c.FAName })
         .OrderBy(c => c.Id)
        .Select(x => new ReturnData { id = x.Id, name = x.Name });

Simply added an anonymous type to sort and then converted to the required type. Of course it takes more memory.