En un post de hace unos días os comentaba cómo funciona internamente SharePoint cuando usamos Linq to SharePoint y hacemos uso de la paginación de elementos en el propio Linq (con los métodos de Skip y Take).
En este post veremos qué pasa cuando hacemos uso de la API REST de listas.
Para los que no conozcáis que podemos hacer con REST a la hora de atacar listas, os dejo un par de referencias muy sencillitas, pero donde podemos ver muy claramente cómo empezar y sus beneficios:
- Overview of using REST in SharePoint 2010
- http://msdn.microsoft.com/en-us/library/cc907912.aspx en este link, además de mucha información sobre ado.net data services, hay unas tablas resumen muy buenas con la sintaxis REST (filtros, funciones, operadores…)
Si queremos usar paginación en REST, lo haríamos de la siguiente forma:
http://team.server.lab/_vti_bin/ListData.svc/Questions?$skip=20&$top=20
Pues bien, si volvemos a usar el SQL Profiler para trazar el resultado de la SQL que genera SharePoint, nos encontramos con la misma SQL que si usamos Linq to SP:
1 |
<span id="lnum1" style="color: #606060"> 1:</span> <span style="color: #0000ff">SELECT</span> <span style="color: #0000ff">TOP</span>(@NUMROWS) t1.[Type] <span style="color: #0000ff">AS</span> c0, UserData.[tp_ContentTypeId], UserData.[ntext2], t1.[DirName] <span style="color: #0000ff">AS</span> c9, UserData.[tp_ID], UserData.[tp_CopySource], UserData.[tp_Version], t1.[Id] <span style="color: #0000ff">AS</span> c4, UserData.[float2], UserData.[tp_Editor] <span style="color: #0000ff">AS</span> c8c7, UserData.[tp_Created], t1.[SortBehavior] <span style="color: #0000ff">AS</span> c1, UserData.[tp_HasCopyDestinations], UserData.[tp_ModerationStatus], UserData.[tp_Level], UserData.[nvarchar1], UserData.[tp_UIVersionString], t1.[MetaInfo] <span style="color: #0000ff">AS</span> c3, t1.[ScopeId] <span style="color: #0000ff">AS</span> c5, UserData.[tp_Author] <span style="color: #0000ff">AS</span> c6c7, UserData.[tp_HasAttachment], UserData.[tp_Modified], <span style="color: #0000ff">CASE</span> <span style="color: #0000ff">WHEN</span> DATALENGTH(t1.DirName) = 0 <span style="color: #0000ff">THEN</span> t1.LeafName <span style="color: #0000ff">WHEN</span> DATALENGTH(t1.LeafName) = 0 <span style="color: #0000ff">THEN</span> t1.DirName <span style="color: #0000ff">ELSE</span> t1.DirName + N<span style="color: #006080">''</span>/<span style="color: #006080">''</span> + t1.LeafName <span style="color: #0000ff">END</span> <span style="color: #0000ff">AS</span> c2, UserData.[float1] |
1 |
<span id="lnum2" style="color: #606060"> 2:</span> |
1 |
<span id="lnum3" style="color: #606060"> 3:</span> <span style="color: #0000ff">FROM</span> AllUserData <span style="color: #0000ff">AS</span> UserData <span style="color: #0000ff">WITH</span>(<span style="color: #0000ff">INDEX</span>=AllUserData_PK) |
1 |
<span id="lnum4" style="color: #606060"> 4:</span> <span style="color: #0000ff">INNER</span> <span style="color: #0000ff">JOIN</span> Docs <span style="color: #0000ff">AS</span> t1 <span style="color: #0000ff">WITH</span>(NOLOCK) <span style="color: #0000ff">ON</span> (UserData.[tp_RowOrdinal] = 0) <span style="color: #0000ff">AND</span> (t1.SiteId=UserData.tp_SiteId) <span style="color: #0000ff">AND</span> (t1.SiteId = @SITEID) <span style="color: #0000ff">AND</span> (t1.ParentId = UserData.tp_ParentId) <span style="color: #0000ff">AND</span> (t1.Id = UserData.tp_DocId) <span style="color: #0000ff">AND</span> ( (UserData.tp_Level = 1) ) <span style="color: #0000ff">AND</span> (t1.<span style="color: #0000ff">Level</span> = UserData.tp_Level) <span style="color: #0000ff">AND</span> (t1.IsCurrentVersion = 1) <span style="color: #0000ff">AND</span> (t1.<span style="color: #0000ff">Level</span> = 1 <span style="color: #0000ff">OR</span> t1.<span style="color: #0000ff">Level</span> = 2) <span style="color: #0000ff">AND</span> (UserData.[tp_ListID] =@LISTID) <span style="color: #0000ff">AND</span> (UserData.[tp_CalculatedVersion] = 0 ) <span style="color: #0000ff">AND</span> (UserData.[tp_IsCurrentVersion] = <span style="color: #0000ff">CONVERT</span>(<span style="color: #0000ff">bit</span>,1) ) <span style="color: #0000ff">AND</span> (UserData.[tp_DeleteTransactionId] = 0x ) |
1 |
<span id="lnum5" style="color: #606060"> 5:</span> |
1 |
<span id="lnum6" style="color: #606060"> 6:</span> <span style="color: #0000ff">WHERE</span> (UserData.[tp_CalculatedVersion] = 0 ) <span style="color: #0000ff">AND</span> (UserData.[tp_IsCurrentVersion] = <span style="color: #0000ff">CONVERT</span>(<span style="color: #0000ff">bit</span>,1) ) <span style="color: #0000ff">AND</span> (UserData.[tp_DeleteTransactionId] = 0x ) <span style="color: #0000ff">AND</span> (UserData.tp_ListID=@LISTID) <span style="color: #0000ff">AND</span> ( (UserData.tp_Level = 1) ) <span style="color: #0000ff">AND</span> (UserData.tp_SiteId=@SITEID) <span style="color: #0000ff">AND</span> (UserData.tp_RowOrdinal=0) <span style="color: #0000ff">AND</span> (t1.SiteId=@SITEID <span style="color: #0000ff">AND</span> (t1.DirName=@DN <span style="color: #0000ff">OR</span> t1.DirName <span style="color: #0000ff">LIKE</span> @DNEL+N<span style="color: #006080">''</span>/%<span style="color: #006080">''</span>)) |
1 |
<span id="lnum7" style="color: #606060"> 7:</span> |
1 |
<span id="lnum8" style="color: #606060"> 8:</span> <span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> UserData.[tp_ID] <span style="color: #0000ff">ASC</span> <span style="color: #0000ff">OPTION</span> (FORCE <span style="color: #0000ff">ORDER</span>, MAXDOP 1)<span style="color: #006080">',</span> |
1 |
<span id="lnum9" style="color: #606060"> 9:</span> |
1 |
<span id="lnum10" style="color: #606060"> 10:</span> N' |
1 |
<span id="lnum11" style="color: #606060"> 11:</span> @LFFP uniqueidentifier, |
1 |
<span id="lnum12" style="color: #606060"> 12:</span> @SITEID uniqueidentifier, |
1 |
<span id="lnum13" style="color: #606060"> 13:</span> @DN nvarchar(4000), |
1 |
<span id="lnum14" style="color: #606060"> 14:</span> @DNEL nvarchar(4000), |
1 |
<span id="lnum15" style="color: #606060"> 15:</span> @LISTID uniqueidentifier, |
1 |
<span id="lnum16" style="color: #606060"> 16:</span> @NUMROWS bigint, |
1 |
<span id="lnum17" style="color: #606060"> 17:</span> @RequestGuid uniqueidentifier<span style="color: #006080">',</span>00000000-0000-0000-0000-000000000000<span style="color: #006080">', </span>FB8F2EA0-94D9-4848-AA89-C13AD67145B7<span style="color: #006080">', </span>Lists/Questions<span style="color: #006080">', </span>Lists/Questions<span style="color: #006080">', </span>ADFE87E8-3F1E-46B1-B70F-B60557E3BE43<span style="color: #006080">', </span>26540A30-80DD-4283-8EC0-C9AF68126EAD' |
1 |
<span id="lnum18" style="color: #606060"> 18:</span> |
1 |
<span id="lnum19" style="color: #606060"> 19:</span> @LFFP=' |
1 |
<span id="lnum20" style="color: #606060"> 20:</span> @SITEID=' |
1 |
<span id="lnum21" style="color: #606060"> 21:</span> @DN=N' |
1 |
<span id="lnum22" style="color: #606060"> 22:</span> @DNEL=N' |
1 |
<span id="lnum23" style="color: #606060"> 23:</span> @LISTID=' |
1 |
<span id="lnum24" style="color: #606060"> 24:</span> @NUMROWS=2147483648, |
1 |
<span id="lnum25" style="color: #606060"> 25:</span> @RequestGuid=' |
ahí tenemos el NUMROWS con ese número… tan largo.
Nada más, como veis, a la hora de usar REST, tampoco estamos optimizando la paginación, así que tenerlo en cuenta en vuestros desarrollos.
Saludos!!
Deja un comentario