How can I convert this sql query that reorders items in a database to linq?

I currently have a table where an item has a new order based on a column it’s being sorted by. The goal of the sql statement is to reorder the table based on that sort column. The SQL Command currently works, but I have a lot of hard coded strings and I’m hoping to convert it to linq so it will be easier to test. The sql I have is below:

sql = string.Format(CultureInfo.InvariantCulture, "UPDATE x SET
x.[{1}] = x.[RowNum] FROM (SELECT [{1}], ROW_NUMBER() OVER (ORDER BY
t.[{1}] ASC, t.[Modified] DESC) AS RowNum FROM {0} t INNER JOIN
tableTwo i ON t.TableTwoId = i.Id INNER JOIN {4} p ON t.{4}Id
= p.Id WHERE i.{2} = {3} and {3} <> '' ", tableName, sortColumn, tableTwoColumn, "{0}", tableThreeName);

I’m familiar with basic Linq, but I’m not sure how to convert things like ROW_NUMBER() and OVER to linq. I changed the table names for sample names, in case it’s not perfect. Any help would be appreciated!

Answer

Linq is for querying, not updating. You could possibly use EF or some other framework to update data but, honestly, raw SQL is best for bulk updating.

I would say that having a “sort” column that’s based on existing data (and thus needs to be updated periodically) seems superfluous. Why not just order the data by that column in your select queries?

Advertisement

Answer