Using Collation In Linq To Sql

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?

Solution 1:

There is no direct way. Workaround:

Create function in Sql Server

CREATEFUNCTION [dbo].[fnsConvert]
    (
      @p NVARCHAR(2000) ,
      @c NVARCHAR(2000)
    )
RETURNS NVARCHAR(2000)
ASBEGIN
        IF ( @c='Persian_100_CI_AI' )
            SET@p=@pCOLLATE Persian_100_CI_AI
        IF ( @c='Persian_100_CS_AI' )
            SET@p=@pCOLLATE Persian_100_CS_AI

        RETURN@pEND

Import it in model and use:

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

Solution 2:

You can't change the collation through a LINQ statement. You better do the sorting in memory by applying a StringComparer that is initialized with the correct culture (at least... I hope it's correct) and ignores case (true).

DB.Products.AsEnumerable()
  .OrderBy (x => x, StringComparer.Create(newCultureInfo("fa-IR"), true))

edit

Since people (understandably) don't seem to read comments let me add that this is answered using the exact code of the question, in which there is no Where or Select. Of course I'm aware of the possibly huge data overhead when doing something like...

DB.Products.AsEnumerable().Where(...).Select(...).OrderBy(...)

...which first pulls the entire table contents into memory and then does the filtering and projection the database itself could have done by moving AsEnumerable():

DB.Products.Where(...).Select(...).AsEnumerable().OrderBy(...)

The point is that if the database doesn't support ordering by some desired character set/collation the only option using EF's DbSet is to do the ordering in memory.

The alternative is to run a SQL query having an ORDER BY with explicit collation. If paging is used, this is the only option.

Solution 3:

This is now possible with EF Core 5.0 using the collate function.

In your example the code would be:

product = DB.Products.OrderBy(p => EF.Functions.Collate(p.name, "Persian_100_CI_AI"));