CODEWITHSUNDEEP
t-sqllinq-to-sql
Coco
Coco

Reputation: 23

T-SQL (Order by Case) to LinQ

I have the folowwing statement and I would like to have a LINQ equivalent:

SELECT *

  FROM People

  where Name like '%something%'

  ORDER BY CASE 

  WHEN Name LIKE 'something%' then 1

  WHEN Name LIKE '%something%' then 2

  ELSE 3 END

Basically, I'm retrieving all the rows which contains a value (in this case 'something') and I'm ordering them: first the ones starting with that value, and then the remaining.

Any idea on how to do that in LinQ?

Upvotes: 1

Views: 1208

Answers (2)

Justin Pihony
Justin Pihony

Reputation: 67075

I am not able to verify this, but something like this might work. The code can definitely be optimized/cleaned up, but in theory this just might work :)

The only question is whether the contains in the comparable delegate will translate the way it does in the Where. So, you might need to use an IndexOf or similar (as Oybek implemented)

var queryResult = 
     people
    .Where(person=>person.name.Contains(@"/something/"))
    .OrderBy(person=>person.Name, 
        delegate(string name1, string name2)
        {
            int result1, result2;
            if(name1.Contains(@"something/")) result1 = 1;
            else if(name1.Contains(@"/something/")) result1 = 2;
            else result1 = 3;

            if(name2.Contains(@"something/")) result2 = 1;
            else if(name2.Contains(@"/something/")) result2 = 2;
            else result2 = 3;

            return result1.CompareTo(result2);
        })

Upvotes: 0

Oybek
Oybek

Reputation: 7243

I've came out with the following solution. 

var dc = new EntityContext();

var result = dc 
// Condition part
.People.Where(x => x.Name.IndexOf("Foo") > -1) // This part is translated to like
// projection part
.Select(x => new { Person = x, Weight = x.Name.IndexOf("Bar") > -1 ? 1 : (x.Name.IndexOf("Baz") ? 2 : 0)})
// Order
.OrderBy(x => x.Weight)
// Final projection
.Select(x => x.Person);

I guess everything is self explanatory. First you select under your condition, then create a new object with weights necessary, then order it and finally take the necessary people.

Upvotes: 3

Related Questions