CODEWITHSUNDEEP
c#sqlentity-frameworklinq
Scott Becker
Scott Becker

Reputation: 83

In LINQ to Entities, How can you order like follows?

I have a queryable of entities with a "Code" field against them

1.1.1,
1.1.2,
1.1.3,
1.1.4,
...,
1.1.10,
1.1.11

Unfortunately, When I do an .OrderBy(x=> x.Code) on the query, it returns in the following order

1.1.1,
1.1.10,
1.1.11,
1.1.2,
1.1.3,
...

How can I make it that the object list is ordered by the code field, split by the "."s and as an integer between each part?

This is customer data, so I can't just put a "0" in front of the 1 number ones. Also it is any number of "."s in this Code field.

Let me know if you require further information.

Upvotes: 4

Views: 632

Answers (4)

Serkan Arslan
Serkan Arslan

Reputation: 13393

If you can make some assumption like every node can have max n letter. you can use this code. 

.OrderBy(x => String.Concat( x.Code.Split('.')
                                   .Select(ss => ss.PadLeft(3, '0'))) )

Upvotes: 5

fubo
fubo

Reputation: 45947

The Version class should be a good approach for that. Unfortunately Version.Parse fails at Linq2Entity so you have to fetch the data first from your sql-server and sort it afterwards.

var result = input.AsEnumerable<string>().OrderBy(x => Version.Parse(x.Code));

Upvotes: 5

Evk
Evk

Reputation: 101443

If you have non-trivial amount of data in your table - you cannot really use proposed solutions which require to pull all data from server and then sort on client. To do that on server and still using Entity Framework you need to either create SQL function or computable column and sort by that, unless you can figure out how to convert your code to sortable form using only functions translatable to SQL by Entity Framework. If you are using SQL Server, example of SQL function would be:

CREATE FUNCTION dbo.DotSeparatedSort(@input varchar(max))
RETURNS hierarchyid
AS
BEGIN
    return CAST(N'/' + replace(@input, '.', '/') + N'/' AS hierarchyid)
END

Which will convert dot separated numbers to hierarchyid type which has sorting semantics you need.

The same can be done with computed column (just put CAST(N'/' + replace(Code, '.', '/') + N'/' AS hierarchyid) as formula).

Then you need to import function to EF model (details depend on which EF version you use and if that is code-first or database-first) and sort by that:

ctx.Entities.OrderBy(c => ctx.DotSeparatedSort(c.Code))

Which will be translated to select * from Entities order by dbo.DotSeparatedSort(Code). In case of computed column you obviously just sort by that.

If you use computed column - you can define index on it, so if you have really large amount of data - I'd prefer computed column because of that.

Upvotes: 1

thmshd
thmshd

Reputation: 5847

It's problematic to apply the correct sorting (OrderBy) when executing as a Query (e.g. LINQ to Entities), because the expression is directly converted to a ORDER BY SQL Expression, and there is no such SQL clause.

What you CAN do is execute the SQL without a particular sorting to materialize the data (e.g. run .ToList() on it) and then sort the Data in Memory using this specific Sort Override.

This is how your custom Comparer implementation could look like (copied from above page and updated, left comments as is etc.):

public class CodeComparer : IComparer<MyEntity>
{
    public int Compare(MyEntity x, MyEntity y)
    {
        if (x == null)
        {
            if (y == null)
            {
                // If x is null and y is null, they're
                // equal. 
                return 0;
            }
            else
            {
                // If x is null and y is not null, y
                // is greater. 
                return -1;
            }
        }
        else
        {
            // If x is not null...
            //
            if (y == null)
                // ...and y is null, x is greater.
            {
                return 1;
            }
            else
            {
                return ToComparableString(x.Code).CompareTo(ToComparableString(y.Code));

            }
        }
    }

    private string ToComparableString(string input)
    {
        var split = input.Split(new [] {'.'});
        return string.Join(".", split.Select(x => x.PadLeft(5, '0')));
    }
}

And then, you apply the Sorting:

var query = (...your Query or data source...).ToList();
var sortedList = query.Sort(new CodeComparer());

It will convert a string like "1.15.141" to "00001.00015.0141", making it comparable. (you could also remove the dot ., also you can adjust the padding length to the expected maximum number, e.g. make it 2 instead of 5 if you have a maximum of 99 per "node".

Upvotes: 0

Related Questions