To start out I will tell you how I want my solutions and in particular my SharePoint “applications” (see later) to be structured, and then I will show you a very simple way of creating classes that will enable you to easily write search code for your solution implementation. The target audience for this post is of course SharePoint developers, who, as me, want a more structured way of writing code for searching in SharePoint.
Since I’ve lately been writing a lot of code with some advanced techniques for searching I also want to create a baseline before I jump into these techniques (and I thought that this was an easy start). I have a small point I want to emphasize in the end (and I think you should read that no matter what) about when searching using “OR” – particular in large lists and how to get around the problem using my query pattern.
Developing solutions for SharePoint often falls into two categories – either you’re extending the standard usage scenarios with some component, that provides a tool *inside* the standard SharePoint context, that could be a “Facebook commenting look-a-like” feature placed on all articles with possibility for “liking” the article and the comments, searching for particular documents in a fashion closer related to the customers’ business etc.
The other category is when customers tell you they want a whole new full flown application X and they want it running on SharePoint – and presumably with SharePoint as the “database”.
I usually call these two categories “tools” and “applications”. We all know that these two categories could afford two distinctly different development strategies. Creating a web based “application” requires some additional thoughts into software architecture and patterns, which is hinted by the sheer amount of literature on this subject.
| I can easily give you a massive amount of examples of isolated “tools” that I personally have created, so toolmaking is an important aspect of being a SharePoint consultant. I also know that sometimes there is a crossover from “tools” into the “application” category. Additionally “tools” have a tendency to grow, and before you know it, you’re swamped in developing an application. You should always *try* to think of even the smallest “tool” as an “application” – it’s just too expensive to correct afterwards. |
The “application” development for SharePoint is where I find that the SharePoint object model has its shortcomings, since I really would like to build my application in a “layered” way – and I’m just not sure where the SharePoint Object Model fits this software architecture: It has objects that are clearly related to building the correct UI controls, it also have objects that could be seen as more Business Layer related, and it clearly has some Data layer objects fetching and storing objects (I *do* think that the SharePoint Client Object Model is a step in the right direction, the object here being mostly concerned about fetching and storing).
Since it often makes more sense to actually store application data in a database – but circumstances right now requires you to store it in SharePoint, doing layering theoretically would enable you to *easily* switch data source at a later point.
| The top “UI” layer isn’t the final layer in our layered application, since what we really want to create is something closer to a Single-Page-Application (SPA) type, which is the type of web based solutions our customers have become accustomed to. This of course calls for a “Presentation” layer on top of the “UI layer. Presentation of course goes on in the user’s browser – and “classic application” entities is luckily something we can serialize into JavaScript objects, unlike a SPListItem.In a later post I will tell you a good deal more about what the problems with using SPListItems directly in your application are, and why using them is a really bad idea – particularly when dealing with an installation with large lists. |
So with the above thoughts in mind, I now want to search for something in my new application – in the example I’ll be using, I have some Cases each having a number of Activities and each activity being of a particular configurable ActivityType type.
I’ll start by creating these entities, and creating them as plain old CLR objects (POCOs). I, of course, also have a SharePoint list model, where I can store these object, though this unimportant for our example: we want the ability to replace “database” (SharePoint) without having to redo anything
| This is also the reason I’m not creating my entities through SPMETAL, thus making them queryable through Linq, since I feel that this would bind my objects too close to the platform.I’m also unsure how entities created through SPMetal would look like, when serializing these into JSON, to be used by the presentation – and if this is at all possible (something to be tried out at some point). |
Now I want to search for my object, but I want to search with the following requirement:
1) I want to search without knowing which datasource holds my objects
2) I want to search without having to know anything about CAML
| I always find creating CAML queries to prune to errors and mistakes, when doing them by hand, and replacing values within your CAML text. In the end you always end up using CAML query builder (excellent tool though), copying your CAML text into Visual Studio, and replacing the “dynamic” search values.I’ve seen examples on queries that, though they seemed fine, and never threw an exception, never returned what we actually wanted, until I noticed that the CAML where missing a “<Where></Where>” tag construct, in which case you will just get everything.I also would prefer, if possible, never to have to leave Visual Studio, when querying. I want to query through some object model, and have my code automatically converted into the appropriate CAML Xml. |
3) I want my queries to return my entities, and not SPListItems
In the end, I want to write something like this, from my code, when I need cases that match a given criteria:
|
CaseRepository rep=new CaseRepository();
Query q=new Query([Some Typed Query Object Model Here]); List<Case> |
Or from inside the CaseRepository
public List<Case> SearchByCaseHandler(string caseHandlerName) { return _caseDataInterface.GetCases([Some Typed Query Object Model with caseHandlerName]); } |
So I can call:
|
CaseRepository rep=new CaseRepository();
List<Case>
|
| I’ve bolded the “interface” in the name “_caseDataInterface”, to imply, that I’m talking to an interface where the “current” instance of that interface could be a SharePoint Data Access Object, but needn’t necessarily be so – and we don’t need to know.I won’t go into details about the inversion of control and dependency injection – and haven’t done so in the example provided either. I have thought it into the example, that we will need some kind of data access factory – it’s just not completely implemented. |
So let’s get started. First we’ll start with the entities – in example code I have made Case, Activities and ActivityTypes, but they are really simple, so I’ll just show the Case entity:
namespace SimpleQueryPattern.Model.Entities { public classCase { public Case() { Activities = new List<Activity>(); } public Case(Guid id) : this() { _ID = id; } private string _name = ""; public string Name { get { return _name; } set { _name = value; } } private string _cpr = ""; public string Cpr { get { return _cpr; } set { _cpr = value; } } private Guid _ID = Guid.Empty; public Guid ID { get { return _ID; } internal set { _ID = value; } }
public virtual IList<Activity> Activities { get; protected set; } }
|
For querying I first create a query Interface: We require a Query object, and then a mapping function that can take us from the “DataSource” (in our case SharePoint SPListItem), to one of our Entities, so lets start with defining a generic interface for this:
namespace SimpleQueryPattern.Model.Query {
public delegate Target ToEntityFunction<Target,Source>(Source item); public interface IQuerySource<Target, Source> where Target : class where Source : class { List<Target> Search(DataQuery q, ToEntityFunction<Target, Source> func); } } |
“ToEntityFunction” the definition of a function that takes us from the Source type to the Target type. When using a IQuerySource, you should specify how you go from source to target. In our case this will be from SPListItem to a Case.
Let’s define the mapping function, which we’ll do through an extension method on SPListItem (a nifty little trick):
public static class CaseExtensions { public static Case ToCase(this SPListItem ListItem) { return ListItem.ToCase(true); } public static Case ToCase(thisSPListItem ListItem, bool LazyLoad) { if (LazyLoad) { return new LazyLoadCase(ListItem.UniqueId) { Cpr = ListItem.Fields.ContainsField("CPR") && ListItem["CPR"] != null ? ListItem["CPR"].ToString() : "", Name = ListItem.Name }; } else { return new Case(ListItem.UniqueId) { Cpr = ListItem.Fields.ContainsField("CPR") && ListItem["CPR"] != null ? ListItem["CPR"].ToString() : "", Name = ListItem.Name }; } } public static void FromCase(thisSPListItem ListItem, Case EntityCase) { if (ListItem.Fields.ContainsField("CPR")) { ListItem["CPR"] = EntityCase.Cpr; } ListItem["Name"] = EntityCase.Name; ListItem["Title"] = EntityCase.Name; } } |
Ok, then we’re ready to start defining the Query classes. I’ll keep it simple, and only implement searching for string values – the using developer will just have to convert integers, bools etc. to the string equivalent for now.
Actually the Query class should, if searching for more than one criteria, start with a Combine operator, but if it doesn’t, I will just assume an AND operator between the BaseCriterias. The Query/Criteria objects, of course, does not know anything how we’re really searching – their concert is purely describing what we want to search for.
public class DataQuery { public const int DefaultMaxItems = 500; public List<CriteriaBase> Criterias = newList<CriteriaBase>(); public List<SortOrder> SortBy = newList<SortOrder>(); publicint MaxItems { get; set; } public static DataQuery CreateQuery(int maxItems) { DataQuery q = newDataQuery() { MaxItems = maxItems }; return q; } public DataQuery Where(CriteriaBase criteria) { criteria.Parent = this; Criterias.Add(criteria); return this; } public DataQuery Where(string field, OperatorEnum anOperator, string value) { return Where(newCriteria(field, anOperator, value)); } public DataQuery OrderBy(SortOrder sortOrder) { sortOrder.Parent = this; SortBy.Add(sortOrder); return this; } public DataQuery OrderBy(string field, SortDirection direction) { return OrderBy(newSimpleSortOrder(field, direction)); } public static Criteria Criteria(string field, OperatorEnum anOperator, string value) { return new Criteria(field, anOperator, value); } public static AndOperator And() { return new AndOperator(); } public static OrOperator Or() { return new OrOperator(); } public stati cSimpleSortOrder OrderByClause(string field, SortDirection direction) { return new SimpleSortOrder(field, direction); } } public enum OperatorEnum { Like, BeginsWith, Equals, BiggerThan, LessThan, BiggerThanOrEqual, LessThanOrEqual } public enum SortDirection { ascending, descending } public class SortOrder { public virtual DataQuery Parent { get; set; } } public class SimpleSortOrder : SortOrder { public virtual string Field { get; set; } public virtual SortDirection SortOrder { get; set; } public SimpleSortOrder(string field, SortDirection direction) { Field = field; SortOrder = direction; } }
public abstract class CriteriaBase { public virtual DataQuery Parent { get; set; } } public class Criteria : CriteriaBase { public virtual string Field { get; set; } public virtual OperatorEnum Operator { get; set; } public virtual string Value { get; set; } public Criteria(string field, OperatorEnum anOperator, string value) { Field = field; Operator = anOperator; Value = value; } }
public abstract class CombineOperator : CriteriaBase { private List<CriteriaBase> _subCriterias = new List<CriteriaBase>(); publicoverrideDataQuery Parent { get { return base.Parent; } set { base.Parent = value; foreach (Criteria c in _subCriterias) { c.Parent = value; } } } protected virtual CombineOperator Where(CriteriaBase criteria) { criteria.Parent = base.Parent; _subCriterias.Add(criteria); return this; } public abstract string OperatorName { get; } public List<CriteriaBase> Items { get { return _subCriterias; } } } public class AndOperator : CombineOperator { public virtual AndOperator And(CriteriaBase criteria) { return Where(criteria) as AndOperator; } public virtual AndOperator And(string field, OperatorEnum anOperator, string value) { return And(DataQuery.Criteria(field, anOperator, value)); } public override string OperatorName { get { return "And"; } } } public class OrOperator : CombineOperator { public virtual OrOperator Or(CriteriaBase criteria) { return Where(criteria) asOrOperator; } public virtual OrOperator Or(string field, OperatorEnum,anOperator, string value) { return Or(DataQuery.Criteria(field, anOperator, value)); } public override string OperatorName { get { return "Or"; } } } |
As you can see, I’ve aimed for a kind of fluent API for my query objects.
So now we can implement our SharePoint searching “engine”, which will read the Query objects, and return entity objects. As you probably remember, we had two generic types: Source and Target, and we actually do know the source type, since we’re searching in SharePoint: that should be a SPListItem:
public class SPQuerySource<Target>:IQuerySource<Target,SPListItem> where Target:class { privateSPWeb _web = null; privatestring _listName = string.Empty; public SPQuerySource(SPWeb web,string TargetList) { _web = web; _listName = TargetList; } private SPWeb Web { get { return _web; } } public List<Target> Search(DataQuery q, ToEntityFunction<Target, SPListItem> func) { string qstr = ""; var returns = new List<Target>(); for (int i = 0; i < q.Criterias.Count; i++) { if (i == q.Criterias.Count - 1) { qstr += getCriteria(_listName, q.Criterias[i]); } else { qstr += "<And>" + getCriteria(_listName, q.Criterias[i]); } } for (int i = 0; i < q.Criterias.Count - 1; i++) { qstr += "</And>"; } string sortStr = ""; if (q.SortBy.Count > 0) { sortStr += "<OrderBy>"; foreach (SortOrder so in q.SortBy) { sortStr += getSortField(_listName, so); } sortStr += "</OrderBy>"; } SPQuery query = newSPQuery(); query.Query = "<Where>" + qstr + "</Where>" + sortStr; query.RowLimit = (uint)q.MaxItems; SPList l = Web.Lists.TryGetList(_listName); if (l != null) { SPListItemCollection col = l.GetItems(query); foreach (SPListItem item in col) { if (func != null) { Target Entity = func(item); returns.Add(Entity); } } } return returns; } private string getSortField(string listName, SortOrder so) { if (so is SimpleSortOrder) { return getSimpleSortField(listName, so asSimpleSortOrder); } return""; }
private string getSimpleSortField(string listName, SimpleSortOrder simpleSortOrder) { SPList l = Web.Lists.TryGetList(listName); if (l != null) { try { SPField f = l.Fields[simpleSortOrder.Field]; string asc = simpleSortOrder.SortOrder == SortDirection.ascending ? "True" : "False"; return"<FieldRef Name='" + simpleSortOrder.Field + "' Ascending='" + asc + "'/>"; } catch { } } return""; } privatestring getSimpleCriteria(string listName, Criteria sq) { SPList l = Web.Lists.TryGetList(listName); if (l != null) { try { SPField f = l.Fields[sq.Field]; string op = ""; switch (sq.Operator) { case OperatorEnum.BeginsWith: { op = "BeginsWith"; break; } case OperatorEnum.BiggerThan: { op = "Bt"; break; } caseeOperatorEnum.BiggerThanOrEqual: { op = "Beq"; break; } case OperatorEnum.Equals: { op = "Eq"; break; } case OperatorEnum.LessThan: { op = "Lt"; break; } case OperatorEnum.LessThanOrEqual: { op = "Leq"; break; } case OperatorEnum.Like: { op = "Contains"; break; } } return"<" + op + "><FieldRef Name='" + sq.Field + "'/><Value Type='" + f.FieldTypeDefinition.TypeName + "'>" + sq.Value + "</Value></" + op + ">"; } catch { } } return""; } private string getCriteria(string listName, CriteriaBase criteria) { if (criteria isCriteria) { Criteria sq = criteria as Criteria; return getSimpleCriteria(listName, sq); } else if (criteria is CombineOperator) { CombineOperator combine=criteria asCombineOperator; if (combine != null) { string qstr = ""; for (int i = 0; i < combine.Items.Count; i++) { if (i == combine.Items.Count - 1) { qstr += getCriteria(listName, combine.Items[i]); } else { qstr += "<" + combine.OperatorName + ">" + getCriteria(listName, combine.Items[i]); } } for (int i = 0; i < combine.Items.Count - 1; i++) { qstr += "</" + combine.OperatorName + ">"; } return qstr; } } return""; } }
|
With the Query “engine” in place, I can start creating my SharePoint Case Data Access module, which is very simple. Note that in the Query I knew the source, and in this know the Target, namely Case, and I will supply a datamapping function for the Query module, which is the extension method shown earlier:
public class SPCaseData:ICaseData<Entities.Case> { private SPWeb _web = null; private IQuerySource<Entities.Case,SPListItem> _searchSource = null; public SPCaseData() { // THIS WOULD NORMALLY BE FACTORED OUT _web = SPContext.Current.Web; _searchSource = new SPQuerySource<Entities.Case>(_web, "Case"); } public SPCaseData(SPWeb web) { // THIS WOULD NORMALLY BE FACTORED OUT _web = web; _searchSource = new SPQuerySource<Entities.Case>(_web, "Case"); }
public List<Entities.Case> GetItems(DataQuery q) { return _searchSource.Search(q, ListItem => ListItem.ToCase()); } } |
The final thing is to actually use my query solution, and I’ve made a couple of usages in the CaseRepository:
public class CaseRepository { private readonly ICaseData<Entities.Case> _caseDataInterface = null; public CaseRepository() { // Would normally be factored out _caseDataInterface = new DataAccess.SharePoint.SPCaseData(SPContext.Current.Web); } public CaseRepository(ICaseData<Entities.Case> dataContext) { _caseDataInterface = dataContext; } public List<Entities.Case> GetCases(DataQuery q) { return _caseDataInterface.GetItems(q); } public List<Entities.Case> SearchByCaseHandler(string CaseHandlerName) { Query.DataQuery q = Query.DataQuery.CreateQuery(500) .Where(newCriteria("CaseHandler", OperatorEnum.Equals, CaseHandlerName)); return _caseDataInterface.GetItems(q); } public List<Entities.Case> SearchByBeginStringFirstTry(string beginsWith) { return _caseDataInterface.GetItems( Query.DataQuery.CreateQuery(30) .Where( Query.DataQuery.Or() .Or("Name", Query.OperatorEnum.BeginsWith, beginsWith) .Or("CPR", Query.OperatorEnum.BeginsWith, beginsWith) .Or("Title", Query.OperatorEnum.BeginsWith, beginsWith) ) .OrderBy("Name", Query.SortDirection.ascending) ); } public List<Entities.Case> SearchByBeginStringBetterIndexWise(string beginsWith) { List<Entities.Case> returns = _caseDataInterface.GetItems( Query.DataQuery.CreateQuery(10) .Where(Query.DataQuery.Criteria("Name", Query.OperatorEnum.BeginsWith, beginsWith)) .OrderBy(new Query.SimpleSortOrder("Title",Query.SortDirection.ascending))); returns.Union(_caseDataInterface.GetItems( Query.DataQuery.CreateQuery(10) .Where(Query.DataQuery.Criteria("CPR", Query.OperatorEnum.BeginsWith, beginsWith)) .OrderBy(new Query.SimpleSortOrder("Title",Query.SortDirection.ascending))), newCaseComparer());
returns.Union(_caseDataInterface.GetItems( Query.DataQuery.CreateQuery(10) .Where(new Query.Criteria("Title", Query.OperatorEnum.BeginsWith, beginsWith)) .OrderBy(new Query.SimpleSortOrder("Title",Query.SortDirection.ascending))), newCaseComparer()); return returns; } private class CaseComparer : IEqualityComparer<Entities.Case> { public bool Equals(Entities.Case x, Entities.Case y) { return x.ID == y.ID; } public int GetHashCode(Entities.Case obj) { return obj.ID.GetHashCode(); } } } |
Please note the two functions SearchByBeginStringFirstTry And SearchByBeginStringBetterIndexWise.When searching in big lists, it is impossible to search for anything not indexed – and even though all of the 3 fields “Name”, “CPR” and “Title” would be indexed, when using the “Or” operator in your CAML query, you will fail, since the SharePoint “query optimizer” will not be able to use your indexes, when combining with an OR operator. Performance wise I was actually not able to measure any differences between the two Query methods, even though I had a massive amount of data.
| In my test, I bypassed the query throttling by running as Farm Admin, and performance wise the later method actually was better, since it was able to utilize the SharePoint field indexes. |
The version I’ve written here, is taken from a real world example, showing how you can get around such problems, but I will dedicate a complete post exclusively for CAML query optimization and list throttling on large lists.
In the next posting, I will use this query pattern implementation, and “Linq-enable” it, so you can write Linq expressions, that will be converted into this Query pattern implementation.
I have a piece of code you can get to test for yourself. Send me an email on [email protected] and I’ll send it to you.
