CODEWITHSUNDEEP
c#sqllinq
Konrad Viltersten
Konrad Viltersten

Reputation: 39088

Is LINQ always preferred before sorting in an SQL-statement?

We need to retrieve a sorted list of items. The sort order is to be related to an arbitrary list of items' guids. I.e. it might be 1, 2, 3, 4 as well as 3, 7, 2, 4.

We're not using LINQ-to-SQL nor stored procedures.

I suggested that we fetch the list in the order SQL Server provides it and sort it using LINQ in the C# code.

The alternative to that is to create a string like this (following this tip).

select * from publishers
Order by (CASE City
 WHEN 'Paris'    THEN 1
 WHEN 'Chicago'  THEN 2
 WHEN 'Boston'   THEN 3
 WHEN 'New York' THEN 4
 ELSE 100 END) ASC, City DESC

I must admit that there's a certain neatness to this approach but I really prefer to play with data operations in C#. I can't come up with a good shoot-down to the other approach, so I'll have to admit that it's a working one.

What are the drawbacks here? (Except for the obvious of injections, of course, which isnät a problem for us. We need not to worry about security issues, luckily.)

Upvotes: 2

Views: 146

Answers (1)

Tigran
Tigran

Reputation: 62246

I would personally go for database centric solution. If I need sort or page a data, I almost always try to do it on DB server side (usually using SQL and/or StoredProcedures).

Benefits are evident:

  1. Server is usually is the most powerful machine in the your app system, so let's give the hard work to it. Also because it's tuned to do the hard work for us.

  2. One time data prepared on the server, sometimes it means that it also reduced its size too (filtered or fetched concrete page), so the data amount transmitted to the client is drastically reduced, which benefit to a user in faster response time.

If you're fetching all at once (as you mentioned), at least, the first point remain something that you can consider.

Upvotes: 4

Related Questions