Using LINQ to update a product list with rank

Question

I have a list of products:

IQueryable<Product> list = _ProductRepository.GetAll();

The column Product.Clicks corresponds to the number of times the product has been viewed.

I want to update this list of products and update the column Product.Rank using a ranking such as this:

SELECT
    ProductId,
    Name,
    RANK() OVER (ORDER BY Clicks DESC) [Rank],
    Clicks
FROM
    Product
WHERE
    Clicks > 0

What is the most efficient way using LINQ to do this? Is there a way to do a direct update, rather than querying the products and enumerating them? This will run as a batch job on a weekly basis.

UPDATE: It appears that many people have suggested that a SQL SP batch job would be the best way forward here. Points go to the person that suggests such a query.

UPDATE: Answer as follows:

CREATE PROCEDURE UpdateRank 
AS
BEGIN
    SET NOCOUNT ON;

    WITH RankValues AS
    (SELECT [ProductId], [Clicks], [Rank],
    RANK() OVER (ORDER BY [Clicks] DESC) AS [NewRank]
    FROM [Product])
    UPDATE [RankValues]
    SET [Rank] = [NewRank]
    WHERE Clicks > 0
END
GO

Answer 1

I'm only just getting into LINQ so apologies, but why involve LINQ?

If I was looking at this problem the most efficient way would be to wrap it up in a stored proc and leave it for the server to do for you as an automated job.

Answer 2

To answer that question one would have to know how your repository is implemented (linq to sql, entity framework, custom implementation, ...). For Ling to sql there is an ExecuteQuery method.

Depending on your repository you could also use a stored procedure to do the updating for you.

Answer 3

I'm assuming that you're using linq-to-sql. I would recommend using a stored procedure to do that job. When creating your DataContext, just drag your stored procedure to the methods window.

If you, for some reason, cannot use a stored procedure, linq-to-sql has an ExecuteQuery method which you can use if you want to do a 'direct update' and don't want to fetch all records and then update them.

Answer 4

Agreed w/ the comments thus far. It seems like a database maintenance task, frankly, which can and shoudl be done as a batch job inside the SQL Server. I wouldn't even involve Linq To SQL unless you need to explicitly call it from inside the app occasionally. If that is the case, then you can still do what I said, but then also do as others have said and add it as a method on your datacontext.

Answer 5

If you want to use a single query to update Rank field with Calculated Rank using function Rank(), I think you have to create a View like this:

CREATE VIEW [vwProductWithRank]
AS
SELECT     ProductID, Rank, Rank() Over (Order by [Clicks]) [CalculatedRank]
FROM         Product

It is an updateable View and you can update it like this in your SP:

UPDATE vwProductWithRank Set [Rank] = [CalculatedRank]