How to order by numericaly on a char column using entity framework

So I had this request where a user need to order a char column as int, because it contains equipment numbers and in some rare case letters.

EF does not implement any DbFunctions that would allow conversion from String to int, heres the

fastest solution i could come up with (this is a large table) , is the following.

ALTER TABLE schema.Table ADD CodeAsInt AS (cast(Code as int));

select * from schema.Table order by CodeAsInt desc

Then add the computed column to your code first config.

Property(x => x.CodeAsInt).HasColumnName(@”CodeAsInt”).IsOptional().HasColumnType(“int”).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Computed);

Now  just use LINQ OrderBy on CodeAsInt.

 

Submit a Comment

Your email address will not be published. Required fields are marked *

Share This