Over Partition Sql in LINQ

Posted by Avmp2208 under ASP.NET on 6/19/2013 | Points: 10 | Views : 2144 | Status : [Member] | Replies : 1
Hi, i'm here and i want to learn more about LINQ,

I have written code in sql server which I placed a column with a number of identity with OVER PARTITION BY formulation and dive in groups of 6 records like the example below
i j rn color
0 2 1 AMARILLO
0 4 2 AZUL
0 6 3 MARRON
0 8 4 NEGRO
0 10 5 ROJO
1 2 6 VERDE
1 4 1 AMARILLO
1 6 2 AZUL
1 8 3 MARRON
1 10 4 NEGRO
2 2 5 ROJO
2 4 6 VERDE
2 6 1 AMARILLO
2 8 2 AZUL
2 10 3 MARRON

so i want to translate this code made it in sql to LINQ. how can i do that?

here's my sql

WITH T1 AS (
SELECT [i],[j],ROW_NUMBER() OVER(ORDER BY [i],[j]) AS RANGO
FROM (
SELECT [Row]
, [Col]
FROM [ROAMING].[dbo].[Filas_Columnas]
) AS T([i],[j])),
T2 AS (
SELECT color,Id
FROM (
SELECT COLOR,Id FROM COLORES)
AS T(color,Id))
SELECT
A.[i],A.[j],A.rn--,B.color--,B.rn
FROM
(
SELECT [i],[j],RANGO, ROW_NUMBER() OVER(PARTITION BY ((RANGO - 1) / 6) ORDER BY RANGO) AS rn
FROM T1
) AS A
INNER JOIN
(
SELECT color, Id AS rn
FROM T2
) AS B
ON A.rn = B.rn
ORDER BY
A.[i],A.[j]


I was trying to do this example but I did not get the result
http://www.dotnetfunda.com/articles/article1995-rownumber-simulation-in-linq.aspx




Responses

Posted by: Niladri.Biswas on: 6/21/2013 [Member] Platinum | Points: 25

Up
0
Down
Once I wrote an article Row_Number simulation in LINQ (http://www.dotnetfunda.com/articles/article1995-rownumber-simulation-in-linq.aspx).
hope you can do the needed changes.

Best Regards,
Niladri Biswas

Avmp2208, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response