CODEWITHSUNDEEP
c#sql-serverlinqlinq-to-sql
Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34152

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?

How can I apply collation?

Upvotes: 6

Views: 14763

Answers (3)

Mielipuoli
Mielipuoli

Reputation: 1510

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"));

Upvotes: 7

Gert Arnold
Gert Arnold

Reputation: 109079

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(new CultureInfo("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.

Upvotes: 4

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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;

Upvotes: 5

Related Questions