Friday, April 13, 2007 4:09 PM bart

Coming soon - The return of IQueryable<T> - LINQ to SharePoint

Earlier this week, "The IQueryable Tales" were published on my blog, with great success. This series covered the end-to-end creation of a custom query provider for LINQ, focusing on the LINQ to LDAP case. For your reference, here are a few links to the episodes:

As the matter in fact, this implementation is primarily meant for "academic purposes", showing how to go ahead in creating your own query provider using IQueryable<T>. Other samples around the blogosphere include LINQ to Amazon and LINQ to NHibernate. The LINQ to LDAP provider was written in a quick-n-dirty fashion in about one afternoon but has serious restrictions because of the underlying query language (LDAP filters) being quite simplistic, limiting our expressiveness. Nevertheless, it's both a good sample annex tutorial and useful for people dealing with Active Directory or ADAM who don't like to mess around with LDAP filters themselves.

Today, I'm happy to announce another fun project of mine, of which an alpha is to be released on CodePlex in the next few weeks: LINQ to SharePoint. Again, it's meant as a (good) sample of implementing query providers and to serve developers when consuming SharePoint data from lists. Essentially, the implementation provides a translation of LINQ queries to CAML, together with a "list schema to entity mapping tool" called SpMetal.

CAML, which stands for Collaborative Application Markup Language, is the language used by SharePoint for various purposes, including querying. LINQ to SharePoint translates LINQ queries like the following:

1 DateTime startDate = new DateTime(2007, 01, 01); 2 string firstNameStartsWith = "B"; 3 int maximumAge1 = 24; 4 string lastNameContains = "Smet"; 5 int minimumAge2 = 50; 6 var res = from usr in users 7 where ( (usr.FirstName.StartsWith(firstNameStartsWith) && !(usr.Age > maximumAge1)) 8 || (usr.LastName.Contains(lastNameContains) && usr.Age >= minimumAge2)) 9 && usr.Member == true 10 && usr.MemberSince >= startDate 11 && usr.ShortBio != null 12 orderby usr.MemberSince descending 13 orderby usr.Age ascending 14 select new 15 { 16 Name = usr.FirstName + " " + usr.LastName, 17 usr.Age, 18 usr.Homepage, 19 usr.ShortBio, 20 usr.Activities, 21 MembershipInfo = new { usr.MembershipType, usr.MemberSince }, 22 FoodPreferences = new { usr.FavoriteFood, usr.FavoriteFoodOtherValue } 23 };

into the corresponding CAML query, like this:

<Query> <Where> <And> <And> <And> <Or> <And> <BeginsWith> <FieldRef Name="First_x0020_name" /> <Value Type="Text">B</Value> </BeginsWith> <Leq> <Value Type="Number">24</Value> <FieldRef Name="Age" /> </Leq> </And> <And> <Contains> <FieldRef Name="Last_x0020_name" /> <Value Type="Text">Smet</Value> </Contains> <Geq> <Value Type="Number">50</Value> <FieldRef Name="Age" /> </Geq> </And> </Or> <Eq> <Value Type="Boolean">1</Value> <FieldRef Name="Member" /> </Eq> </And> <Geq> <Value Type="DateTime">2007-01-01T00:00:00Z</Value> <FieldRef Name="Member_x0020_since" /> </Geq> </And> <IsNotNull> <FieldRef Name="Short_x0020_Bio" /> </IsNotNull> </And> </Where> <OrderBy> <FieldRef Name="Member_x0020_since" Ascending="FALSE" /> <FieldRef Name="Age" /> </OrderBy> </Query>

Finally it's sent to the server using either the SharePoint Object Model (SPList) or the SharePoint web services (lists.asmx). The query provider is "intelligent" on several fields:

  • The CAML Query schema doesn't support a boolean negation operator, so the query provider applies De Morgan's laws to invert boolean conditions if needed.
  • It queries only for the fields required in the projection.
  • Mappings can be done to make the "entity object" feeling natural, while queries can be much more complex under the covers.
  • Restricting the results using a Take(n) call is supported and will only query for the desired number of rows, reducing traffic.
  • Support for most of the SharePoint types (including choices and calculated columns) is provided.
  • On a longer term, applying updates to retrieved data via entities is considered too.

In order to be able to write a query like the one above, one uses SpMetal to export the schema of the list:

The underlying list looks like this in SharePoint:

During the export, SpMetal creates a class like this:

1 using System; 2 using BdsSoft.SharePoint.Linq; 3 4 /// <summary> 5 /// Demo 6 /// </summary> 7 [List("Demo", ID = "34c90895-fbf3-4da7-a260-4b3ddc67146d", Version = 36, Path = "/Lists/Demo")] 8 class Demo 9 { 10 /// <summary> 11 /// First name 12 /// </summary> 13 [Field("First name", Type = "Text", ID = "f5164cc0-8a1b-423d-8669-e86bb65a3118")] 14 public string FirstName { get; set; } 15 16 /// <summary> 17 /// Last name 18 /// </summary> 19 [Field("Last name", Type = "Text", ID = "42de3b10-2186-4db1-9345-0ec91fb8a62d")] 20 public string LastName { get; set; } 21 22 /// <summary> 23 /// Age 24 /// </summary> 25 [Field("Age", Type = "Number", ID = "95feebc1-5719-440d-8c95-eccfc2c88f4d")] 26 public double? Age { get; set; } 27 28 /// <summary> 29 /// Member 30 /// </summary> 31 [Field("Member", Type = "Boolean", ID = "32c5c166-6890-4685-acd4-c8d3ad78031c")] 32 public bool? Member { get; set; } 33 34 /// <summary> 35 /// Member since 36 /// </summary> 37 [Field("Member since", Type = "DateTime", ID = "3320e4c8-b7d2-4a7f-b828-d995167bbecc")] 38 public System.DateTime MemberSince { get; set; } 39 40 /// <summary> 41 /// Homepage 42 /// </summary> 43 [Field("Homepage", Type = "URL", ID = "72e5a0be-72cf-4be1-8e38-f234e25a05c5")] 44 public System.Uri Homepage { get; set; } 45 46 /// <summary> 47 /// Account balance 48 /// </summary> 49 [Field("Account balance", Type = "Currency", ID = "f8a1efb7-3bac-444a-a122-6245754b47da")] 50 public double? AccountBalance { get; set; } 51 52 /// <summary> 53 /// Short biography 54 /// </summary> 55 [Field("Short Bio", Type = "Note", ID = "0460ce4a-1282-4ba7-8235-30cf74cb80ab")] 56 public string ShortBio { get; set; } 57 58 /// <summary> 59 /// Membership Type 60 /// </summary> 61 [Field("Membership Type", Type = "Choice", ID = "f51ebc87-6402-4bb8-a70e-f11ee5428b43")] 62 public MembershipType? MembershipType { get; set; } 63 64 /// <summary> 65 /// Activities 66 /// </summary> 67 [Field("Activities", Type = "MultiChoice", ID = "c311147f-efec-4938-af6b-374b23706bf9")] 68 public Activities? Activities { get; set; } 69 70 /// <summary> 71 /// Favorite food 72 /// </summary> 73 [Field("Favorite food", Type = "Choice", ID = "bdf129e3-b899-4aa0-badb-6529a630a01e", OtherChoice = "FavoriteFoodOtherValue")] 74 public FavoriteFood? FavoriteFood { get; set; } 75 76 /// <summary> 77 /// Favorite food helper for value FavoriteFood.Other 78 /// </summary> 79 public string FavoriteFoodOtherValue { get; set; } 80 81 /// <summary> 82 /// DoubleAge 83 /// </summary> 84 [Field("DoubleAge", Type = "Number", ID = "0acee3ef-a076-44ad-9818-531d6e6b626e", ReadOnly = true, Calculated = true)] 85 public double? DoubleAge { get; set; } 86 87 /// <summary> 88 /// Full name 89 /// </summary> 90 [Field("Full name", Type = "Text", ID = "1f062f0a-9783-4638-ba2b-2400e8a04602", ReadOnly = true, Calculated = true)] 91 public string FullName { get; set; } 92 93 /// <summary> 94 /// Test 95 /// </summary> 96 [Field("Test", Type = "MultiChoice", ID = "a65b9acc-2513-4f0c-8198-adf78658a13c", OtherChoice = "TestOtherValue")] 97 public Test? Test { get; set; } 98 99 /// <summary> 100 /// Test helper for value Test.Other 101 /// </summary> 102 public string TestOtherValue { get; set; } 103 } 104 105 enum MembershipType { Platinum, Gold, Silver, Bronze } 106 107 [Flags] enum Activities { Quiz = 1, Adventure = 2, Culture = 4 } 108 109 enum FavoriteFood { Pizza, Lasagna, Hamburger, Other } 110 111 [Flags] enum Test { A = 1, B = 2, C = 4, Other = 8 }

This allows writing strongly-typed queries and contains all of the information required at runtime to translate the query expression into the corresponding CAML query. Notice that choices with custom values are supported (line 73, 74) and that non-required values are indicated as nullables (? syntax in C#). Also calculated columns and read-only ones are indicated in the Field attributes, as in lines 84 and 90. Also, multi-choice fields are translated into "Flags" enums.

Another reason to keep an eye on my blog ... you'll be the first to know when the first alpha is available!

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

Filed under: ,

Comments

# &raquo; Coming soon - The return of IQueryable - LINQ to SharePoint

# &raquo; Coming soon - The return of IQueryable - LINQ to SharePoint

# &raquo; Coming soon - The return of IQueryable - LINQ to SharePoint

# &raquo; Coming soon - The return of IQueryable - LINQ to SharePoint

# re: Coming soon - The return of IQueryable<T> - LINQ to SharePoint

Wednesday, April 18, 2007 12:57 AM by Ivo Vink

Hi,

I see you're busing running CAML queries to Sharepoint lists as well.

Any experience regarding performance? We are trying to search for items in large lists but are experiencing perofmance trouble as soon as we reach the 100.000 items count in our list. Is you're implementation facing the same problems?

# re: Coming soon - The return of IQueryable<T> - LINQ to SharePoint

Wednesday, April 18, 2007 1:27 AM by bart

Hi Ivo,

LINQ to SharePoint is a layer on top of the regular SharePoint object model (SPSite, SPList) and the web services stack (lists.asmx). Any performance issues you might see without the LINQ stuff, will most likely also apply to LINQ to SharePoint. This being said, the LINQ to SharePoint implementation aims to generate the most efficient query possible, especially concerning the "view fields" in order to limit network traffic.

Feel free to drop me a mail if you have further questions regarding this. I'm not a SharePoint expert though, but I could forward your questions to others who do know more about SharePoint.

-Bart

# re: Coming soon - The return of IQueryable<T> - LINQ to SharePoint

Wednesday, April 18, 2007 4:21 AM by John

What a really cool and aggressive project.  I think it is awesome that project actually serves a business need (isn't a toy) and that your pushing the envelope of newer technologies.  As an early adopter of MOSS 2007, WCF and WPF, I think it awesome that you're actually going past just using the technolgies, and actually making them better for other developers.

Cudos for putting it up on CodePlex.  As of recently, I've seen more and more MVPs commercializing their code rather that helping out the community.

# re: Coming soon - The return of IQueryable<T> - LINQ to SharePoint

Wednesday, April 18, 2007 8:12 PM by Ed

Fully agree with John, kudos from down-under too. Looking forward to having a play with this.

# Building Custom LINQ Enabled Data Providers using IQueryable<T>

Friday, April 20, 2007 6:16 AM by Tom's MSDN Belux Corner

Bart De Smet wrote a hands-on tutorial that explains quite in-depth how one can build a custom data provider

# Building Custom LINQ Enabled Data Providers using IQueryable<T>

Friday, April 20, 2007 6:19 AM by Tom's MSDN Belux Corner

Bart De Smet wrote a hands-on tutorial that explains quite in-depth how one can build a custom data provider

# LINQ to SharePoint - 0.1.2.0 Alpha release available now

Sunday, April 22, 2007 4:43 PM by B# .NET Blog

It's been a long ten days since the first announcement of the LINQ to SharePoint project, with lots of

# Community Convergence XXVI

Sunday, May 06, 2007 6:42 PM by Charlie Calvert's Community Blog

The biggest news in the C# community is the official announcement of Silverlight at the MIX conference.

# Community Convergence XXVII

Sunday, May 13, 2007 11:13 PM by Charlie Calvert's Community Blog

Welcome to the 27th Community Convergence. I use this column to keep you informed of events in the C#

# Community Convergence XXVII

Sunday, May 13, 2007 11:19 PM by Charlie Calvert's Community Blog

Welcome to the 27th Community Convergence. I use this column to keep you informed of events in the C#

# Monday Useful Bits and Pieces

Friday, August 17, 2007 1:04 PM by Mirrored Blogs

Quite often (and on a Monday in particular after a weekend&#39;s postings) I find a list of useful things