En el post anterior os hablaba de Linq to SharePoint y la posibilidad de usarlo en Office 365. Ahora os quiero contar algunas observaciones del funcionamiento. Lógicamente, las pruebas que he hecho sólo son posibles en modo on-premise, pero estoy casi seguro de que se comporta igual en la versión 365.
Antes de nada, tiene que quedar claro que Linq to SharePoint, internamente, funciona con consultas CAML, igual que hace por ejemplo el SPQuery. Podemos ver qué CAML autogenera Linq to SP, asignando la propiedad Log del DataContext. El siguiente snippet es un ejemplo de uso de Linq2SP haciendo debug de la CAML generada:
1 |
<span id="lnum1" style="color: #606060"> 1:</span> StringBuilder stringBuilder = <span style="color: #0000ff">new</span> StringBuilder(); |
1 |
<span id="lnum2" style="color: #606060"> 2:</span> TextWriter textWriter = <span style="color: #0000ff">new</span> StringWriter(stringBuilder); |
1 |
<span id="lnum3" style="color: #606060"> 3:</span> |
1 |
<span id="lnum4" style="color: #606060"> 4:</span> |
1 |
<span id="lnum5" style="color: #606060"> 5:</span> <span style="color: #0000ff">using</span> (TeamSiteDataContext context = <span style="color: #0000ff">new</span> TeamSiteDataContext(<span style="color: #006080">"http://team.server.lab"</span>)) |
1 |
<span id="lnum6" style="color: #606060"> 6:</span> { |
1 |
<span id="lnum7" style="color: #606060"> 7:</span> context.Log = textWriter; |
1 |
<span id="lnum8" style="color: #606060"> 8:</span> var questions = from q <span style="color: #0000ff">in</span> (context.Questions).Skip(10).Take(10) |
1 |
<span id="lnum9" style="color: #606060"> 9:</span> select q; |
1 |
<span id="lnum10" style="color: #606060"> 10:</span> |
1 |
<span id="lnum11" style="color: #606060"> 11:</span> <span style="color: #0000ff">foreach</span> (var item <span style="color: #0000ff">in</span> questions) |
1 |
<span id="lnum12" style="color: #606060"> 12:</span> { |
1 |
<span id="lnum13" style="color: #606060"> 13:</span> System.Console.WriteLine(<span style="color: #006080">"{0}::{1}"</span>, item.Id, item.Title); |
1 |
<span id="lnum14" style="color: #606060"> 14:</span> } |
1 |
<span id="lnum15" style="color: #606060"> 15:</span> |
1 |
<span id="lnum16" style="color: #606060"> 16:</span> <span style="color: #0000ff">string</span> queryLog = stringBuilder.ToString(); |
1 |
<span id="lnum17" style="color: #606060"> 17:</span> System.Console.WriteLine(queryLog); |
1 |
<span id="lnum18" style="color: #606060"> 18:</span> |
1 |
<span id="lnum19" style="color: #606060"> 19:</span> } |
1 |
<span id="lnum20" style="color: #606060"> 20:</span> |
1 |
<span id="lnum21" style="color: #606060"> 21:</span> System.Console.WriteLine(<span style="color: #006080">"FIN"</span>); |
1 |
<span id="lnum22" style="color: #606060"> 22:</span> System.Console.ReadLine(); |
Además, el ejemplo anterior realiza una consulta “paginada”, es decir, nos saltamos los primeros 10 elementos, y devolvemos los siguientes 10 (vamos, dame la página 2, cuando paginamos de 10 en 10).
Si vemos la CAML generada, tenemos:
1 |
<span id="lnum1" style="color: #606060"> 1:</span> <span style="color: #0000ff"><?</span><span style="color: #800000">xml</span> <span style="color: #ff0000">version</span><span style="color: #0000ff">="1.0"</span> <span style="color: #ff0000">encoding</span><span style="color: #0000ff">="utf-8"</span> ?<span style="color: #0000ff">></span> |
1 |
<span id="lnum2" style="color: #606060"> 2:</span> <span style="color: #0000ff"><</span><span style="color: #800000">View</span><span style="color: #0000ff">></span> |
1 |
<span id="lnum3" style="color: #606060"> 3:</span> <span style="color: #0000ff"><</span><span style="color: #800000">Query</span><span style="color: #0000ff">></span> |
1 |
<span id="lnum4" style="color: #606060"> 4:</span> <span style="color: #0000ff"><</span><span style="color: #800000">Where</span><span style="color: #0000ff">></span> |
1 |
<span id="lnum5" style="color: #606060"> 5:</span> <span style="color: #0000ff"><</span><span style="color: #800000">BeginsWith</span><span style="color: #0000ff">></span> |
1 |
<span id="lnum6" style="color: #606060"> 6:</span> <span style="color: #0000ff"><</span><span style="color: #800000">FieldRef</span> <span style="color: #ff0000">Name</span><span style="color: #0000ff">="ContentTypeId"</span> <span style="color: #0000ff">/></span> |
1 |
<span id="lnum7" style="color: #606060"> 7:</span> <span style="color: #0000ff"><</span><span style="color: #800000">Value</span> <span style="color: #ff0000">Type</span><span style="color: #0000ff">="ContentTypeId"</span><span style="color: #0000ff">></span>0x0100<span style="color: #0000ff"></</span><span style="color: #800000">Value</span><span style="color: #0000ff">></span> |
1 |
<span id="lnum8" style="color: #606060"> 8:</span> <span style="color: #0000ff"></</span><span style="color: #800000">BeginsWith</span><span style="color: #0000ff">></span> |
1 |
<span id="lnum9" style="color: #606060"> 9:</span> <span style="color: #0000ff"></</span><span style="color: #800000">Where</span><span style="color: #0000ff">></span> |
1 |
<span id="lnum10" style="color: #606060"> 10:</span> <span style="color: #0000ff"></</span><span style="color: #800000">Query</span><span style="color: #0000ff">></span> |
1 |
<span id="lnum11" style="color: #606060"> 11:</span> <span style="color: #0000ff"><</span><span style="color: #800000">ViewFields</span><span style="color: #0000ff">></span> |
1 |
<span id="lnum12" style="color: #606060"> 12:</span> <span style="color: #0000ff"><</span><span style="color: #800000">FieldRef</span> <span style="color: #ff0000">Name</span><span style="color: #0000ff">="Content"</span> <span style="color: #0000ff">/></span> |
1 |
<span id="lnum13" style="color: #606060"> 13:</span> <span style="color: #0000ff"><</span><span style="color: #800000">FieldRef</span> <span style="color: #ff0000">Name</span><span style="color: #0000ff">="VisitedCount"</span> <span style="color: #0000ff">/></span> |
1 |
<span id="lnum14" style="color: #606060"> 14:</span> <span style="color: #0000ff"><</span><span style="color: #800000">FieldRef</span> <span style="color: #ff0000">Name</span><span style="color: #0000ff">="Votes"</span> <span style="color: #0000ff">/></span> |
1 |
<span id="lnum15" style="color: #606060"> 15:</span> <span style="color: #0000ff"><</span><span style="color: #800000">FieldRef</span> <span style="color: #ff0000">Name</span><span style="color: #0000ff">="ID"</span> <span style="color: #0000ff">/></span> |
1 |
<span id="lnum16" style="color: #606060"> 16:</span> <span style="color: #0000ff"><</span><span style="color: #800000">FieldRef</span> <span style="color: #ff0000">Name</span><span style="color: #0000ff">="owshiddenversion"</span> <span style="color: #0000ff">/></span> |
1 |
<span id="lnum17" style="color: #606060"> 17:</span> <span style="color: #0000ff"><</span><span style="color: #800000">FieldRef</span> <span style="color: #ff0000">Name</span><span style="color: #0000ff">="FileDirRef"</span> <span style="color: #0000ff">/></span> |
1 |
<span id="lnum18" style="color: #606060"> 18:</span> <span style="color: #0000ff"><</span><span style="color: #800000">FieldRef</span> <span style="color: #ff0000">Name</span><span style="color: #0000ff">="Title"</span> <span style="color: #0000ff">/></span> |
1 |
<span id="lnum19" style="color: #606060"> 19:</span> <span style="color: #0000ff"></</span><span style="color: #800000">ViewFields</span><span style="color: #0000ff">></span> |
1 |
<span id="lnum20" style="color: #606060"> 20:</span> <span style="color: #0000ff"><</span><span style="color: #800000">RowLimit</span> <span style="color: #ff0000">Paged</span><span style="color: #0000ff">="TRUE"</span><span style="color: #0000ff">></span>2147483647<span style="color: #0000ff"></</span><span style="color: #800000">RowLimit</span><span style="color: #0000ff">></span> |
1 |
<span id="lnum21" style="color: #606060"> 21:</span> <span style="color: #0000ff"></</span><span style="color: #800000">View</span><span style="color: #0000ff">></span> |
Fijaros en el último atributo:
1 |
<span id="lnum1" style="color: #606060"> 1:</span> <span style="color: #0000ff"><</span><span style="color: #800000">RowLimit</span> <span style="color: #ff0000">Paged</span><span style="color: #0000ff">="TRUE"</span><span style="color: #0000ff">></span>2147483647<span style="color: #0000ff"></</span><span style="color: #800000">RowLimit</span><span style="color: #0000ff">></span> |
2 mil, cien millones, 47… uff, un número muy largo, vamos
Pues sí, la cosa está clara. Cuando usamos Linq 2 SP, aunque paginemos desde LINQ, la CAML generada, y por tanto, la SQL final que llega al servidor de BD, devuelve todos los elementos de la lista, y la paginación, se hace en memoria, lo cual, desde luego, no es lo ideal para temas de rendimiento, así que tenerlo en cuenta.
Por cierto, también estuve haciendo la traza desde el SQL Profiler, y os dejo la SQL tan intuitiva que genera SharePoint:
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_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.[ntext2], UserData.[float1], UserData.[tp_ID], UserData.[tp_CopySource], UserData.[tp_Version], t1.[Id] <span style="color: #0000ff">AS</span> c4, UserData.[float2], t1.[DirName] <span style="color: #0000ff">AS</span> c6, UserData.[tp_Created], t1.[SortBehavior] <span style="color: #0000ff">AS</span> c1, UserData.[tp_HasCopyDestinations], UserData.[tp_ModerationStatus], UserData.[tp_Level], UserData.[nvarchar1], t1.[MetaInfo] <span style="color: #0000ff">AS</span> c3, t1.[ScopeId] <span style="color: #0000ff">AS</span> c5 |
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) <span style="color: #0000ff">LEFT</span> <span style="color: #0000ff">OUTER</span> LOOP <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_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_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">WHERE</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_ParentId=@DocParentIdForRF)) <span style="color: #0000ff">AND</span> (UserData.tp_RowOrdinal=0) <span style="color: #0000ff">AND</span> ((UserData.[tp_ContentTypeId] >= @L2IMG <span style="color: #0000ff">AND</span> UserData.[tp_ContentTypeId] <= 0x0100 + 0xff) <span style="color: #0000ff">AND</span> t1.SiteId=@SITEID <span style="color: #0000ff">AND</span> (t1.ParentId=@DocParentIdForRF)) <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">',N'</span>@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2IMG varbinary(8000),@FDN nvarchar(4000),@FLN nvarchar(4000),@LISTID uniqueidentifier,@NUMROWS bigint,@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<span style="color: #006080">', </span>Questions<span style="color: #006080">', </span>ADFE87E8-3F1E-46B1-B70F-B60557E3BE43<span style="color: #006080">', </span>B89AF1B8-3BAA-4C66-BD8C-322D67D655F6' |
1 |
<span id="lnum4" style="color: #606060"> 4:</span> |
1 |
<span id="lnum5" style="color: #606060"> 5:</span> @LFFP=' |
1 |
<span id="lnum6" style="color: #606060"> 6:</span> @SITEID=' |
1 |
<span id="lnum7" style="color: #606060"> 7:</span> @L2IMG=0x0100, |
1 |
<span id="lnum8" style="color: #606060"> 8:</span> @FDN=N' |
1 |
<span id="lnum9" style="color: #606060"> 9:</span> @FLN=N' |
1 |
<span id="lnum10" style="color: #606060"> 10:</span> @LISTID=' |
1 |
<span id="lnum11" style="color: #606060"> 11:</span> @NUMROWS=2147483648, |
1 |
<span id="lnum12" style="color: #606060"> 12:</span> @RequestGuid=' |
Fijaros que sí tenemos un TOP(@NUMROWS), lo que es ideal para sacar un número concreto de elementos, pero el valor de @NUMROWS, vuelve a ser ese número tan largo.
¿Pero, se puede paginar en SharePoint de forma óptima?
La respuesta es que por supuesto que sí, pero considerando que, si queremos hacer una paginación al estilo Google, vamos a tener que dedicarle algo de tiempo, ya que los mecanismos que proporciona SharePoint, nos ponen fácil la paginación “anterior-siguiente” (más aún la de ir a la siguiente página), pero no tan fácil el poder saltar a una página concreta.
Os dejo algunas referencias concretas al respecto:
- Best practices working with folders and lists
- Learning SharePoint Part VII – List Pagination
- How does SharePoint List pagination work?
Respecto a esto que os comentaba, fijaros como el propio webpart de lista de SharePoint, cuando se activa la paginación, sólo ofrece la paginación de anterior y siguiente:
Y hasta aquí puedo leer…
Saludos!!
Deja un comentario