c# - Using collation in Linq to Sql

Question

Imagine this sql query

select * from products order by name collate Persian_100_CI_AI asc

Now using Linq:

product = DB.Products.OrderBy(p => p.name); // what should I do here?

How can I apply collation?

Answer 1

There is no direct way. Workaround:

Create function in Sql Server

CREATE FUNCTION [dbo].[fnsConvert]
    (
      @p NVARCHAR(2000) ,
      @c NVARCHAR(2000)
    )
RETURNS NVARCHAR(2000)
AS
    BEGIN
        IF ( @c = 'Persian_100_CI_AI' )
            SET @p = @p COLLATE Persian_100_CI_AI
        IF ( @c = 'Persian_100_CS_AI' )
            SET @p = @p COLLATE Persian_100_CS_AI

        RETURN @p    
    END

Import it in model and use:

from o in DB.Products
orderby DB.fnsConvert(s.Description, "Persian_100_CI_AI")
select o;