Filtering/Querying External Lists

Issue

Assume you have an External Content Type defined against a Data Source that contains a total of 3,000 items, but that has a limit filter of 1,000 items. You expose it as an external list in your SharePoint site. You want to query that external list, but using a standard SPQuery object to retrieve only seems to query your list against the first 1,000 items returned by the External Content Type. What you really want is for the query to go against the entire list of 3,000 items, but to return a total 1,000 max.
For example, assume your data source represents an inventory of cars, with information about the make and the color. If you were to query your external list for all green car from Honda using an SPQuery object, you may only get 30 records back, even if your inventory really contains 75. The reason for this is that the querying process first gets the first 1,000 items from the external list, then queries this 1,000 item dataset for all green cars.

Solution

Starting in SharePoint 2010, the object model introduced a new property on the SPQuery object named “Method”. This allows you dynamically the filters defined on any method defined by the External Content Type. In our case, we want to dynamically set the filter parameters of our Read List method.
In order achieve this, we will go and add a new Wildcard filter to the External Content Type on both the Make and Color columns of our data source. This will allow us to specify the values for these fields in our SPQuery object. Assuming our Read List operation on the External Content Type is named ReadCars and that our wildcard filters are respectively named CarColor and CarMake, our SPQuery object will be defined as follow:
SPQuery spQuery = new SPQuery();
spQuery.Method = @“<Method Name=’ReadCar’>
<Parameter Name=’CarColor’ Value=’Green’ />
<Parameter Name=’CarMake’ Value=’Honda’ />
</Method>”
Then we go and query our External List just like we would for an internal one using the query property:
SPList list = […];
SPListItemCollection greenCars = list.GetItems(spQuery);

 

What this really does in the background, is dynamically set the External Content Type filters and querying the entire data source using the values specified. Using the example above, would return all 75 items, and not the 30 items as if we would have used the query property instead.

The Surface Challenge – Part 2: Setup

In the second article of the Surface Challenge Blog Series, I will be discussing how I setup my work environment to be able to do my day to day development work from my office desk.

Display

I will have to agree with many, the display on the surface is a little too small to be able to work an entire day on without getting one hell of a headache. Fortunately for us, the device has a Mini display port that allows us to connect it to external display device. The first thing I did when I got the device, was buy a Mini-Display to DVI adapter to hook up my surface onto my 22 inches monitor. The adapter was bought brand new for about 45$. Having been patient, I should have probably buy it online for way cheaper.

Mouse

The hands-on experience surface is incredible. The combination of the touch screen, touch pen, and track pad on the keyboard is extremely powerful. However, when sitting at my desk, I like have the same experience as if I was using my laptop on a docking station, so I use the only available port to plug in a wireless mouse.

Keyboard

For a keyboard, I use magnetic Type Keyboard made for the surface. I tried to use the touch keyboard instead, but I had a hard time inputting the capital letters in my password when using it. I could never tell if I had the Shift key pressed correctly or not. So I made the switch to the type keyboard. When using my surface in the “docked state”, the device sits with the kickstand opened in front of me on what used to be a full sized keyboard tray. Another option, could be to use a Bluetooth keyboard. Remember that my only USB port is already used by my mouse, I really don’t want to for and plug in a USB hub.

Storage

The device has a 64Gb Solid State Drive in it, as mentioned in the previous post. With all the recovery data, I believe I was left with just over 10Gb of free space. Add to this Visual Studio 2012, all of the Office 2013 clients, and some default apps, there’s not much left. I went and bought a 16Gb Mini Sandisk drive for 14$ and inserted it as internal storage.

Battery

The Surface Pro, by default has around 4 hours of battery life. I very often take the device with me in meetings, so while I’m sitting at my desk, I always make sure the magnetic power adapter is connected to its side. This adds an additional to my docking experience, but helps me ensure the device won’t shut down right in the middle of meeting;
The next post in this article will describe the development process I followed to develop SharePoint 2013 apps using a Surface Device.

IMG-20130501-00168.jpg
IMG-20130501-00169.jpg

 

 

 

 

The Surface Challenge – Part 1: Introduction

This is a new blog series I’ve been planning to write for a while now. Throughout these several posts, I’ll be going over the details of how I decided to trade in my main development machine for a Microsoft Surface Pro. These blog posts are actually all written using this exact device.

Background Information

On the day I joined my new organization, I was given a Dell Precision M6500 laptop as my main development machine. Even though being very powerful, this device is also extremely heavy. It has 16 Gb of RAM which, in my opinion, is more than enough to do real SharePoint development, and 1Tb of SATA Hard Drive. However, some staff in the organization started to request 32 Gb devices, due to slow performance of their virtual machines hosted on their current laptop. No need to mention that these 32 Gb devices are very expensive. So I went and started thinking about alternative options.

Then came the new SharePoint app model that no longer required developers to install all the SharePoint Server bits on their devices to do development. I remember joking with a friend over a few brews, that this new model would open the door to a whole new development paradigm for SharePoint developers. That SharePoint developers would now be able to do extensive development using low-end devices, pushing and testing their code remotely. But he didn’t buy into that, he had read somewhere that SharePoint 2013 required over 20 Gb of RAM for a developer’s machine. I pushed the envelope and told him that he was totally wrong and that with this, I could now even do development on a tablet, that I’d be willing to stop using my normal development laptop and do strict tablet development for a month. And the Surface Challenge was born!

What Am I Trying to Prove?

The main goal of this self-imposed challenge, is to prove that one doesn’t need to have a high-end machine to do development anymore. I’m not trying to prove that the surface is the device everybody should be using to do SharePoint development from now on. I’m simply trying to prove that if you can do development on a low-end device such as a tablet, then any other low-end machine should be more than enough. However, this blog series will be focusing on using the Microsoft Surface Pro machine.

Prerequisite

Before being able to start doing your SharePoint 2013 app development, you first need a developer’s site somewhere to push and test out your code. In my case, I had two options, the first one was to ask our Administrators team to create a new Developer Site on the intranet server for each developer. This tends to be the preferred option, as the code stays inside the boundaries of your organization. My second option, was to use Office 365. As an MSDN Premium subscriber, I am given one year free of Office 365 subscription. I like to live dangerously, so I decided to go with option #2. Please note that using this option also allows me to create “Auto-Hosted” apps for my own training purposes. Our organization doesn’t have any formal plans to move over to Office 365, so we will most likely never be using Auto-Hosted apps in Azure.

The Surface Pro

The device I was given to do my challenge, was a Microsoft Surface Pro 64 Gb. The machine’s specs are:

Data Point Value
Hard Drive 64 Gb Solid State Drive
RAM 4 Ghz
CPU Intel i5 Processor 1.7Ghz Dual Core
OS Windows 8 Pro x64

 

Software

On the software side now, the first thing my surface pro will need, is everyone’s favorite IDE, Visual Studio 2012. In order for one to do SharePoint 2013 app development in visual studio, they will need several Updates/Add-ons installed first. Here’s a list of the updates I had to install:

  • Visual Studio 2012 Update 1
  • Visual Studio 2012 Office Developers Tools
  • Visual Studio 2012 Update 2

Something important to note, is that I could also have decided to use Napa as my development IDE to remove the Visual Studio dependency all together. I need to be connected at all times anyway, and all I will ever do are SharePoint-Hosted apps. Napa for those who don’t know, is Visual Studio in the cloud for Office 365. It is a Provider-Hosted app that you install on your Office 365 site. It allows you to create and publish SharePoint and Office apps straight from the browser. No need to have Visual Studio installed on your machine at all. Possibly, one could register for an Office 365 30 day free trial, and create an app using Napa. Note however, that you need a valid Office 365 account in order for you to market and sell your app in the Office Store. I said I like to live dangerously, but I didn’t say I loved to suffer, so no Napa for me. If I really wanted to give myself a lot of self-inflicted pain, I would have used Napa on an iPad J

 

Next

The next article in this series will talk about what my work setup/process looks like while developing SharePoint 2013 apps on the Microsoft Surface.