Implementing a simple secure store provider

In my recent post I described two alternatives for accessing external data from the OWS process. One of the options – the RevertToSelf authentication – is not the best choice for a production environment. The other options – the Impersonate Windows Identity authentication mode – requires Secure Store Service (SSS) service, that is a SharePoint Server 2010 feature. It means you might have no ideal solution if you have a SharePoint Foundation 2010 environment.

Although the out-of-the-box Secure Store Implementation is the Microsoft.Office.SecureStoreService.Server.SecureStoreProvider class (Microsoft.Office.SecureStoreService assembly), that is really SharePoint Server only, the main interfaces located in the Microsoft.BusinessData.Infrastructure.SecureStore namespace(Microsoft.BusinessData assembly), that is available in SharePoint Foundation as well.

It means we can implement a custom Secure Store Implementation for SharePoint Foundation.

Below I show you a very basic sample for that. Of course, you can create more sophisticated versions based on the same concepts.

In Visual Studio 2010 we should create a new Class Library project. The target framework should be set to .NET 3.5 and platform target as x64. Since we have to deploy our assembly to GAC, we need a key file as well, to sign the strong-named assembly.

First I include the code for the helper classes.

The SimpleTargetApplicationDefinition class holds information about our target application.

1. namespace SimpleSecureStoreProvider
2. {
3.     public class SimpleTargetApplicationDefinition : ITargetApplicationDefinition
4.     {
5.         public string ContactEmail { get; internal set; }
6.  
7.         public Uri CredentialManagementUrl { get; internal set; }
8.  
9.         public string FriendlyName { get; internal set; }
10.  
11.         public string Name { get; internal set; }
12.  
13.         public TargetApplicationType Type { get; internal set; }
14.     }
15. }

The SimpleTargetApplicationField describes a field of the target application.

1. namespace SimpleSecureStoreProvider
2. {
3.     public class SimpleTargetApplicationField : ITargetApplicationField
4.     {
5.         public SecureStoreCredentialType CredentialType { get; internal set; }
6.  
7.         public bool IsMasked { get; internal set; }
8.  
9.         public string Name { get; internal set; }
10.     }
11. }

The SimpleSecureStoreCredential class contains a piece of the credential information.

1. namespace SimpleSecureStoreProvider
2. {
3.     public class SimpleSecureStoreCredential : ISecureStoreCredential
4.     {
5.         bool disposed;
6.  
7.         public SecureString Credential { get; internal set; }
8.  
9.         public SecureStoreCredentialType CredentialType { get; internal set; }
10.  
11.         public void Dispose()
12.         {
13.             this.Dispose(true);
14.         }
15.  
16.         private void Dispose(bool disposing)
17.         {
18.             if (!disposed)
19.             {
20.                 if (Credential != null)
21.                 {
22.                     Credential.Dispose();
23.                 }
24.                 disposed = true;
25.             }
26.         }
27.  
28.     }
29. }

The main part of the code is the provider itself, that is SimpleSecureStoreProvider class. The skeleton of the class is like this:

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Text;
5. using System.Collections.ObjectModel;
6. using Microsoft.BusinessData.Infrastructure.SecureStore;
7. using System.Diagnostics;
8. using System.Security;
9.  
10. namespace SimpleSecureStoreProvider
11. {
12.     public class SimpleSecureStoreProvider : ISecureStoreProviderExtended, ISecureStoreProvider
13.     {
14.     
15.         …
16.  
17.     }
18. }

We should return the application definition in the GetTargetApplication method. In this sample we return always the same data, regardless of the appId parameter.  As you can see, the sample is an example for a Group Target Application Type. In the GetTargetApplications method we return a single target application. I’ve included Trace commands to help us to monitor which methods are invoked in runtime.

1. public ITargetApplicationDefinition GetTargetApplication(string appId)
2. {
3.     Trace.TraceInformation("GetTargetApplication {0} ({1})", appId, Process.GetCurrentProcess().Id);
4.     return new SimpleTargetApplicationDefinition
5.     {
6.         ContactEmail = "user@company.com",
7.         CredentialManagementUrl = null,
8.         FriendlyName = "TargetApp",
9.         Name = "AppDef",
10.         Type = TargetApplicationType.Group
11.     };
12. }
13.  
14. public ReadOnlyCollection<ITargetApplicationDefinition> GetTargetApplications()
15. {
16.     Trace.TraceInformation("GetTargetApplications ({1})", Process.GetCurrentProcess().Id);
17.     return new ReadOnlyCollection<ITargetApplicationDefinition>(new List<ITargetApplicationDefinition>() { GetTargetApplication("app") });
18. }

In this application we have two fields, one for the user name and one for the password, as shown in the GetTargetApplicationFields method.

1. public ReadOnlyCollection<ITargetApplicationField> GetTargetApplicationFields(string appId)
2. {
3.     Trace.TraceInformation("GetTargetApplicationFields {0} ({1})", appId, Process.GetCurrentProcess().Id);
4.     List<ITargetApplicationField> fields = new List<ITargetApplicationField>();
5.     fields.Add(new SimpleTargetApplicationField
6.     {
7.         CredentialType = SecureStoreCredentialType.WindowsUserName,
8.         IsMasked = false,
9.         Name = "UserName"
10.     });
11.     fields.Add(new SimpleTargetApplicationField
12.     {
13.         CredentialType = SecureStoreCredentialType.WindowsPassword,
14.         IsMasked = false,
15.         Name = "Password"
16.     });
17.  
18.     return new ReadOnlyCollection<ITargetApplicationField>(fields);
19. }

In the GetCredentials and GetRestrictedCredentials methods we should return credentials for the application. In this case I have a user name and password hardcoded, but these should be stored in a bit more secure and configurable way.

1. public SecureStoreCredentialCollection GetCredentials(string appId)
2. {
3.     Trace.TraceInformation("GetCredentials {0} ({1})", appId, Process.GetCurrentProcess().Id);
4.  
5.     List<ISecureStoreCredential> creds = new List<ISecureStoreCredential>();
6.  
7.     creds.Add(new SimpleSecureStoreCredential
8.     {
9.         Credential = GetSecureString(@"domain\user"),
10.         CredentialType = SecureStoreCredentialType.UserName
11.     });
12.     creds.Add(new SimpleSecureStoreCredential
13.     {
14.         Credential = GetSecureString("password"),
15.         CredentialType = SecureStoreCredentialType.Password
16.     });
17.     return new SecureStoreCredentialCollection(creds);
18. }
19.  
20. public SecureStoreCredentialCollection GetRestrictedCredentials(string appId)
21. {
22.     Trace.TraceInformation("GetRestrictedCredentials {0} ({1})", appId, Process.GetCurrentProcess().Id);
23.  
24.     return GetCredentials(appId);
25. }

The GetSecureString method helps to convert a string into SecureString.

1. private SecureString GetSecureString(string value)
2. {
3.     SecureString result = new SecureString();
4.     if (value != null)
5.     {
6.         value.ToCharArray().ToList().ForEach(c => result.AppendChar(c));
7.     }
8.  
9.     return result;
10. }

The methods below are not implemented in this sample:

1. public void DeleteCredentials(string appId)
2. {
3.     Trace.TraceInformation("DeleteCredentials {0} ({1})", appId, Process.GetCurrentProcess().Id);
4.  
5.     throw new NotImplementedException();
6. }
7.  
8. public SecureStoreCredentialCollection GetCredentialsUsingTicket(string ticket, string appId)
9. {
10.     Trace.TraceInformation("GetCredentialsUsingTicket {0} ({1})", appId, Process.GetCurrentProcess().Id);
11.  
12.     throw new NotImplementedException();
13. }
14.  
15. public string IssueTicket()
16. {
17.     Trace.TraceInformation("IssueTicket ({1})", Process.GetCurrentProcess().Id);
18.  
19.     throw new NotImplementedException();
20. }
21.  
22. public ISecureStoreProviderInformation ProviderInformation
23. {
24.     get
25.     {
26.         Trace.TraceInformation("ProviderInformation ({1})", Process.GetCurrentProcess().Id);
27.        
28.         throw new NotImplementedException();
29.     }
30. }

After building the assembly and deploying it to the GAC, we should configure our BCS external system instance. Most important is to set the right Secure Store Implementation value (in my case it is SimpleSecureStoreProvider.SimpleSecureStoreProvider, simplesecurestoreprovider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9fc83bb193b5ea3b). The value of the Secure Store Target Application Id is irrelevant in this version.

If everything works as expected, we should be able to access the external data (in this case the Northwind database through an external list) using the credentials returned by our custom secure store implementation.

Solving the external data access security issue in the case of the OWS process

In the recent two posts I wrote about a security related problem we found when tried to access an external data source from the SharePoint 2010 Timer service process.

After presenting a workaround in the first part, last time I promised a real solution for the issue.

The key to the solution seems to be the information one can found on this MSDN page.

As it states, “The user security token is not available in every context. Without the security token, PassThrough security will not work.”

As you may know, PassThrough is the standard and usually recommended authentication method to an external data source, but in this case we should switch to an alternative one.

The trivial solution would be to use the RevertToSelf authentication. Since this type of authentication is not recommended in a production environment, it is disabled by default. Before using it, you should enable it, for example with the help of PowerShell (see an example here).

After you enabled RevertToSelf, you can find the equivalent BDC Identity option in the list of the available authentication modes:

(To access the settings above, you should select the External Systems view at the administration of the Business Data Connectivity Service, then click the name of the external system you would like to manage, and then click the name of the external system instance.)

After you selected BDC Identity authentication mode, you can use this code to access the external system:

1. using (SPSite site = new SPSite("http://sp2010&quot;))
2. {
3.     using (SPWeb web = site.OpenWeb())
4.     {
5.         Guid siteId = site.ID;
6.         Guid webId = web.ID;
7.  
8.         SPSecurity.RunWithElevatedPrivileges(delegate()
9.         {
10.             using (SPSite siteImp = new SPSite(siteId))
11.             {
12.                 // access external list here
13.             }
14.         });
15.     }
16. }

The other, and recommended option for authentication is to use the Secure Store Service (SSS).

Note: Secure Store Service is not included in SharePoint Foundation 2010, so this option is unfortunately limited for SharePoint Server 2010 Standard and Enterprise versions.

Create a target application in SSS,

then set the credentials of an account with permissions for the external system.

Next, configure your external system to Impersonate Windows Identity, set the name of the Secure Store Target Application Id as created in the previous step, and set  Secure Store Implementation as Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c.

After you configured your external system instance as described above, you can use this code to access the external data from the timer process job / event receiver:

1. using (SPServiceContextScope scope = new SPServiceContextScope(SPServiceContext.GetContext(web.Site)))
2. {
3.     // access external list within this block
4. }

Important to note, that based on the MSDN article mentioned at the beginning of this post, workflows and sandboxed solutions might suffer from the same security problem, so if you have such issues accessing an external data source, the solutions described above might help you in these cases as well.

Efforts taken to fool (or fix?) external data access security from the OWS timer process code

Based on the feedback I received to my last post, I think it would be useful to provide a bit more details about the different impersonation / context change methods I’ve tried to applied in my code when experimenting with external data access from OWS timer process. I will include a few helper methods in this post as well, for example one that can be used to trace out the current process context and identity details:

1. private void TraceUserNames(string msg, SPWeb web)
2. {
3.     string httpContextUser = ((HttpContext.Current != null) && (HttpContext.Current.User != null) && (HttpContext.Current.User.Identity != null)) ?
4.         HttpContext.Current.User.Identity.Name : string.Empty;
5.     string windowsIdentity = (WindowsIdentity.GetCurrent() != null) ? WindowsIdentity.GetCurrent().Name : string.Empty;
6.     string spUserName = ((web != null)) ? web.CurrentUser.LoginName : string.Empty;
7.     Trace.TraceInformation("MailTestEventReceiver.ItemAdded, {3}. httpContextName: '{0}', windowsIdentity: '{1}', spUserName: '{2}'",
8.        httpContextUser, windowsIdentity, spUserName, msg);
9. }

You should include

using System.Diagnostics;

for the Trace commands. Using a method like this one is always suggested when you need to get information about what context your the process is running in.

The following code access the external list using the default context of the process:

1. SPWeb web = properties.Web;
2. HttpContext originalContext = HttpContext.Current;
3.  
4. TraceUserNames("Before impersonation", web);
5. TestExtList(web);

In this case, TestExtList was a simple method to test external list access from code:

1. private void TestExtList(SPWeb web)
2. {
3.     try
4.     {
5.         SPList extList = web.Lists[Global.ExtList];
6.         SPListItemCollection items = extList.GetItems(extList.DefaultView);
7.         Trace.TraceInformation("MailTestEventReceiver.TestExtList item count: {0}", items.Count);
8.     }
9.     catch (Exception ex)
10.     {
11.         Trace.TraceInformation("ERROR in MailTestEventReceiver.TestExtList: '{0}', '{1}'", ex.Message, ex.StackTrace);
12.     }
13. }

When uploading documents from the UI, the result of the TraceUserNames method:

Before impersonation. httpContextName: ”, windowsIdentity: ‘domain\user’, spUserName: ‘domain\user’

However, when the code is triggered by an incoming mail, the result is pretty different:

Before impersonation. httpContextName: ”, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘SHAREPOINT\system’

That is true, even if the E-mail security policy at Incoming e-mail settings is set to Accept e-mail messages based on document library permissions (A).

So this setting seems to have no effect on the context identity.

The differences I found between this one and Accept e-mail messages from any sender (B).

1. In the first case (A) a user is looked up based on the e-mail address, and if no one is found or this user has no write permission, the mail won’t be delivered to the document library. In the case (B) the mail is delivered without this kind of verification.
2. In the case (A) Created By / Modified By fields are set to the user determined by the e-mail address. In the case (B) theses fields are set to SHAREPOINT\system. This latter one correlates with the SharePoint user identity I’ve found for the incoming mail process (see above).

Note: It means that no really security check / authorization happens here. The SMTP mails required no authentication, and it is not so complex to fake a mail with a sender mail address that has write access to the library.

As you may remember from the former post, accessing the external data from code was successful when the document was uploaded from the browser, however it failed with the exception below, when sent as a mail attachment to the library.

Access denied by Business Data Connectivity.
at Microsoft.SharePoint.SPListDataSource.GetEntityInstanceEnumerator(XmlNode xnMethodAndFilters)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstancesInternal(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()

To fix this, I first tried to use “simple” elevated permissions:

1. Guid siteId = properties.SiteId;
2. Guid webId = properties.Web.ID;
3.  
4. SPSecurity.RunWithElevatedPrivileges(delegate()
5.     {
6.         using (SPSite siteImp = new SPSite(siteId))
7.         {
8.             using (SPWeb webImp = siteImp.OpenWeb(webId))
9.             {
10.                 TraceUserNames("Using elevated privileges", webImp);
11.                 TestExtList(webImp);
12.             }
13.         }
14.     });

The output of the TraceUserNames method was:

Using elevated privileges. httpContextName: ”, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘SHAREPOINT\system’

The exception on external data access was in this case:

Attempted to perform an unauthorized operation.
at Microsoft.SharePoint.SPListDataSource.CheckUserIsAuthorized(SPBasePermissions perms)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()

My next thought was that I should probably apply some kind of impersonation to reproduce the context I found when uploading the document from the browser (see the first TraceUserNames output above).

To get the user I should to impersonate, I read the value of the Modified By field (as discussed earlier, this field contains the sender in scenario A).

1. SPListItem item = properties.ListItem;
2. Object editorFieldValueRaw = item[SPBuiltInFieldId.Editor];
3. SPFieldUserValue editorFieldValue = (editorFieldValueRaw is String) ?
4.     new SPFieldUserValue(web, (String)editorFieldValueRaw) :
5.     (SPFieldUserValue)editorFieldValueRaw;
6. SPUser editor = editorFieldValue.User;
7. Trace.TraceInformation("User to impersonate: '{0}'", editor.LoginName);

The following code used to impersonate the SharePoint user:

1. using (SPSite siteImp = new SPSite(siteId, editor.UserToken))
2. {
3.     using (SPWeb webImp = siteImp.OpenWeb(webId))
4.     {
5.         TraceUserNames("After impersonation", webImp);
6.         TestExtList(webImp);
7.     }
8. }

The output of the TraceUserNames method was:

After impersonation. httpContextName: ”, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘domain\user’

I’ve received the original Access Denied exception when tried to get the number of item in the external list.

Next, I’ve injected a HttpContext as described in this and this posts.

1. IPrincipal impersonationPrincipal = new WindowsPrincipal(new WindowsIdentity(GetUpn(editor)));
2. HttpRequest request =
3. new HttpRequest(string.Empty, properties.WebUrl, string.Empty);
4.  
5. HttpContext originalContext = HttpContext.Current;
6.  
7. HttpResponse response = new HttpResponse(
8.      new System.IO.StreamWriter(new System.IO.MemoryStream()));
9.  
10. HttpContext impersonatedContext = new HttpContext(request, response);
11. // these lines required to inject SPContext as well
12. // if you don't need that it can be deleted
13. impersonatedContext.User = impersonationPrincipal;
14. if (web != null)
15. {
16.     impersonatedContext.Items["HttpHandlerSPWeb"] = web;
17. }
18. HttpContext.Current = impersonatedContext;
19.  
20. TraceUserNames("Dummy HTTP context", web);
21. TestExtList(web);
22.  
23. HttpContext.Current = originalContext;

The output of the TraceUserNames method was:

Dummy HTTP context. httpContextName: ‘domain\user’, windowsIdentity: ‘domain\farmAdmin’, spUserName: ‘SHAREPOINT\system’

Again, I’ve received the original Access Denied exception.

Finally, I’ve applied Windows impersonation (after granting the Act as part of the operating system user right to the SharePoint 2010 Timer service identity):

1. WindowsImpersonationContext impersonationContext = null;
2. try
3. {
4.     WindowsIdentity userIdentity = new WindowsIdentity(GetUpn(editor));
5.     impersonationContext = userIdentity.Impersonate();
6.  
7.     using (SPSite siteImp = new SPSite(siteId))
8.     {
9.         using (SPWeb webImp = siteImp.OpenWeb(webId))
10.         {
11.             TraceUserNames("After Windows impersonation", webImp);
12.             TestExtList(webImp);
13.         }
14.     }
15.  
16. }
17. catch (Exception ex)
18. {
19.     Trace.TraceInformation("ERROR in MailTestEventReceiver.ItemAdded impersonation: '{0}', '{1}'", ex.Message, ex.StackTrace);
20. }
21. finally
22. {
23.     if (impersonationContext != null)
24.     {
25.         impersonationContext.Undo();
26.     }
27. }

The GetUpn method used in the former code (using System.DirectoryServices.ActiveDirectory namespace is required):

1. private static string GetUpn(SPUser spUser)
2. {
3.     string[] userName = spUser.LoginName.Split('\\');
4.     Domain domain = Domain.GetCurrentDomain();
5.     string upn = userName[1] + "@" + domain.Name;
6.     Trace.TraceInformation("MailTestEventReceiver.GetUpn result: '{0}'", upn);
7.     return upn;
8. }

The output of the TraceUserNames method was:

After Windows impersonation. httpContextName: ”, windowsIdentity: ‘domain\user’, spUserName: ‘SHAREPOINT\system’

Again, I’ve received the original Access Denied exception.

As you can see, none of this methods resulted the same output as the one from the user interface-based upload, however mixing the methods (Windows + SharePoint impersonation) the output was the same. However, it did not help to avoid the exception, so the problem seemed to be a little more complex.

After introducing a workaround in the last post, in the next post I will show you a solution for this issue.

Accessing external data from SharePoint timer jobs or from event receivers triggered by incoming mail

Recently we had an issue with accessing external list data. One of my colleagues wrote an ItemAdded event receiver for a document library, that updates the document metadata based on a CAML query run against an external list. The source of the external list is a simple database table. For this post assume it is the Meetings table in the Northwind database.

Everything was OK while we uploaded the documents “manually” – that means from the web UI. However, enabling incoming mail for the document library and configuring it to save mail attachments to the library caused us headache.

Documents sent as e-mail attachments were not handled as expected, instead, generated an exception on the external list query.

We received the following error message on a system configured for Kerberos:

Access Denied for User ”, which may be an impersonation by ‘domain\owsTimerAccount’.

In another configuration without Kerberos, using “simple” Windows authentication:

Access denied by Business Data Connectivity.

The stack trace was the same for the both cases:

Microsoft.SharePoint.SPListDataSource.GetEntityInstanceEnumerator(XmlNode xnMethodAndFilters)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstancesInternal(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()

Of course, the owsTimerAccount user has permissions for BCS, the external list and for the external database.

I’ve tried the same code with elevated permissions, SharePoint and Windows impersonation, but that has no effect, except elevated permissions, where I’ve received just another exception:

Attempted to perform an unauthorized operation.

and the stack trace is a bit different as well:

at Microsoft.SharePoint.SPListDataSource.CheckUserIsAuthorized(SPBasePermissions perms)
at Microsoft.SharePoint.SPListDataSource.GetFilteredEntityInstances(XmlDocument xdQueryView, Boolean fFormatDates, Boolean fUTCToLocal, String firstRowId, Boolean fBackwardsPaging, String& bdcidFirstRow, String& bdcidNextPageRow, List`1& lstColumnNames, Dictionary`2& dictColumnsUsed, List`1& mapRowOrdering, List`1& lstEntityData)
at Microsoft.SharePoint.SPListItemCollection.EnsureEntityDataViewAndOrdering(String& bdcidFirstRow, String& bdcidNextPageFirstRow)
at Microsoft.SharePoint.SPListItemCollection.EnsureListItemsData()
at Microsoft.SharePoint.SPListItemCollection.get_Count()

Note: If you would like to apply Windows impersonation, the original identity your process running with (in this case the account configured for the SharePoint 2010 Timer service) should have the Act as part of the operating system user right.

At this point we started to suspect that the source of the issue is somehow related with the SPTimerV4 context. I’ve created a timer job to access our external list just to validate this theory and found the very same results.

Fortunately, this forum thread led us to the solution: instead of running a CAML query against an external list, we should access and filter the external data directly using the BCS API.

The code examples below show the implementation steps for a timer job. If you need the solution for an event receiver, simply replace the name and signature of the Execute method to the one of your event receiver method.

Trace methods in code are included simply to verify the flow of the process.

First, we should add a reference to the Microsoft.BusinessData.dll assembly located at GAC.

Next, add the following namespaces to the code of your SPJobDefinition class:

1. using System.Diagnostics;
2. using Microsoft.SharePoint;
3. using Microsoft.SharePoint.Administration;
4. using Microsoft.SharePoint.BusinessData.SharedService;
5. using Microsoft.BusinessData.MetadataModel;
6. using Microsoft.BusinessData.MetadataModel.Collections;
7. using Microsoft.BusinessData.Runtime;

I’ve defined the following “constants” in my code. These values should be altered to reflect the properties of your business data.

1. private static readonly String _nameSpace = "http://sp2010&quot;;
2. private static readonly String _externalList = "ExtCalendar";
3. private static readonly String _externalCT = "Nortwind Calendar";
4. private static readonly String _finderView = "Read List";
5. private static readonly String _textFieldName = "cLocation";
6. private static readonly String _dateFieldName = "dStart";

In the Execute method the test method is started using elevated permissions (site URL should be replaced):

1. public override void Execute(Guid targetInstanceId)
2. {
3.     Trace.TraceInformation("Starting BcsTestJob execution");
4.  
5.     try
6.     {
7.         using (SPSite site = new SPSite("http://sp2010&quot;))
8.         {
9.             using (SPWeb web = site.OpenWeb())
10.             {
11.                 Guid siteId = site.ID;
12.                 Guid webId = web.ID;
13.  
14.                 SPSecurity.RunWithElevatedPrivileges(delegate()
15.                 {
16.                     using (SPSite siteImp = new SPSite(siteId))
17.                     {
18.                         TestExtList(site, "New York", new DateTime(2010, 9, 21));
19.                     }
20.                 });
21.             }
22.         }
23.     }
24.     catch (Exception ex)
25.     {
26.         Trace.TraceInformation("ERROR in BcsTestJob.Execute: '{0}', '{1}'", ex.Message, ex.StackTrace);
27.     }
28.     Trace.TraceInformation("BcsTestJob finished");
29. }

In the TestExtList method we access the business data and filter the results. Of course, in this case we can’t use CAML.

1. private void TestExtList(SPSite site, string textValue, DateTime dateValue)
2. {
3.     try
4.     {
5.  
6.         using (SPServiceContextScope scope = new Microsoft.SharePoint.SPServiceContextScope(SPServiceContext.GetContext(site)))
7.         {
8.             BdcService service = SPFarm.Local.Services.GetValue<BdcService>(String.Empty);
9.             IMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
10.             IEntity entity = catalog.GetEntity(_nameSpace, _externalCT);
11.             ILobSystemInstance LobSysteminstance = entity.GetLobSystem().GetLobSystemInstances()[0].Value;
12.  
13.             IFieldCollection fieldCollection = entity.GetFinderView(_finderView).Fields;
14.  
15.             IMethodInstance methodInstance = entity.GetMethodInstance(_finderView, MethodInstanceType.Finder);
16.             IEntityInstanceEnumerator ientityInstanceEnumerator = entity.FindFiltered(methodInstance.GetFilters(), LobSysteminstance);
17.  
18.             int counter = 0;
19.             while (ientityInstanceEnumerator.MoveNext())
20.             {
21.                 IEntityInstance entityInstance = ientityInstanceEnumerator.Current;
22.                 if (((String)entityInstance[_textFieldName] == textValue) && ((DateTime)entityInstance[_dateFieldName] == dateValue))
23.                 {
24.                     counter++;
25.                 }
26.             }
27.  
28.             Trace.TraceInformation("BcsTestJob.TestExtList item count: {0}", counter);
29.         }
30.  
31.     }
32.     catch (Exception ex)
33.     {
34.         Trace.TraceInformation("ERROR in BcsTestJob.TestExtList: '{0}', '{1}'", ex.Message, ex.StackTrace);
35.     }
36. }

Applying this approach no exception is thrown. The external data can be accessed from the event receiver as well from the timer job, both of them running in the OWS Timer process context.

A more developer-friendly way of programmatically creating SharePoint 2010 external content types

Todd Baginski held a presentation (SPC 405 – Business Connectivity Services Runtime and Object Model Deep Dive) on 2009 Microsoft SharePoint Conference about creating ECTs from code. You can read more about it and find the original code on his blog.

The code in Todd’s post is really valuable when learning the object model, but I found that creating ECTs this way is very error-prone. If you need to alter the model, you have serious chance to make something wrong.

In this post I show you the main issues with that approach and try to offer you an alternative way to achieve the same result in a more developer-friendly manner.

First, I don’t like specifying type descriptor parameters as string values. It is longer than necessary and easy to mistype.

1. TypeDescriptor returnRootCollectionTypeDescriptor2 =
2.     customersParameter.CreateRootTypeDescriptor("Customers", true,
3.     "System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
4.     "Customers", null, null, TypeDescriptorFlags.IsCollection, null, catalog);

As a first step, one could compute the string from the original type and store it in string variables as shown here:

1. String int32TypeName = typeof(Int32).ToString(); // will be "System.Int32"
2. String stringTypeName = typeof(String).ToString(); // will be "System.String"
3. String iDataReaderTypeName = typeof(IDataReader).AssemblyQualifiedName; // will be "System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
4. String iDataRecordTypeName = typeof(IDataRecord).AssemblyQualifiedName; // will be "System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

Later, these string values could be used as parameters of type descriptor creation.

The ideal solution would be to use the Type itself as the parameter of these methods. We will see how to achieve that soon.

Second, the parameters of the methods are rather redundant (see the name and lobName parameters that are usually the same value), contain several unspecified (null) parameter values, and the calls to create different type descriptors share most of their parameter values.

1. returnRootElementTypeDescriptor.ChildTypeDescriptors.Create("FirstName", true,
2.     "System.String", "FirstName", null, null, TypeDescriptorFlags.None, null);
3. returnRootElementTypeDescriptor.ChildTypeDescriptors.Create("LastName", true,
4.     "System.String", "LastName", null, null, TypeDescriptorFlags.None, null);
5. returnRootElementTypeDescriptor.ChildTypeDescriptors.Create("Phone", true,
6.     "System.String", "Phone", null, null, TypeDescriptorFlags.None, null);

It would be nice to create a base parameter set, without the need to specify default (null) values, and specify only the unique values on method calls.

Third, the calls to create type descriptors are sometimes rather complex and contains redundant parameter values again.

1. customerIDParameter.CreateRootTypeDescriptor("CustomerId", true, "System.Int32", "CustomerId",
2.     new IdentifierReference("CustomerId",
3.     new EntityReference("AdventureWorks", "Customer", catalog), catalog),
4.     null, TypeDescriptorFlags.None, null, catalog);

Wouldn’t it be great to get the entity and identifier references automatically from the entities and identifiers of the model already specified?

I’ve tried to achieve the above goals by applying a few extension methods and creating a custom type to hold type descriptor creation parameters.

The following two extension methods show how to get the IdentifierReference and the EntityReference by specifying the Identifier and the Entity:

1. public static IdentifierReference GetReference(this Identifier identifier)
2. {
3.     // to protect method against possible direct call with null value
4.     // should not happen when called like a "standard" extension method
5.     if (identifier == null)
6.     {
7.         throw new ArgumentNullException("identifier");
8.     }
9.  
10.     Entity entity = identifier.Entity;
11.     EntityReference entityReference = entity.GetReference();
12.     IdentifierReference result = new IdentifierReference(identifier.Name, entityReference, entity.GetCatalog());
13.  
14.     return result;
15. }
16.  
17. public static EntityReference GetReference(this Entity entity)
18. {
19.     // to protect method against possible direct call with null value
20.     // should not happen when called like a "standard" extension method
21.     if (entity == null)
22.     {
23.         throw new ArgumentNullException("entity");
24.     }
25.  
26.     // IMPORTANT! name and namespace parameters are in opposite order
27.     // in the case of Entity.Create method and EntityReference constructor
28.     EntityReference result = new EntityReference(entity.Namespace, entity.Name, entity.GetCatalog());
29.  
30.     return result;
31. }

Just a side note. As you can read in the comments, name and namespace parameters are in opposite order in the case of Entity.Create method and EntityReference constructor. I find this design to be quite misleading.

Both methods above need a reference to the parent AdministrationMetadataCatalog. Instead of passing this as a parameter, I applied a Reflection call to get its value from the Entity that contains it as a non-public field. This extension method might be nicer as an extension property, but unfortunately there is not yet such thing in .NET.

1. // unfortunately, there is no (yet) "extension property" in .NET
2. // so we must create a "get" extension method instead
3. public static AdministrationMetadataCatalog GetCatalog(this Entity entity)
4. {
5.     // to protect method against possible direct call with null value
6.     // should not happen when called like a "standard" extension method
7.     if (entity == null)
8.     {
9.         throw new ArgumentNullException("entity");
10.     }
11.  
12.     AdministrationMetadataCatalog result = null;
13.  
14.     MethodCollection methods = entity.Methods;
15.  
16.     // a new MethodColletion is created on each Entity creation (see internal Entity constructor)
17.     // so it shouldn't be null
18.     if (methods != null)
19.     {
20.         Type methodCollectionType = typeof(MethodCollection);
21.         FieldInfo fi_metadataCatalog = methodCollectionType.GetField("metadataCatalog", BindingFlags.NonPublic | BindingFlags.Instance);
22.         result = (AdministrationMetadataCatalog)fi_metadataCatalog.GetValue(methods);
23.     }
24.  
25.     return result;
26. }

You can use these methods as illustrated here:

1. Entity customerEntity = Entity.Create("Customer", "AdventureWorks", true,
2.     new Version("1.0.0.0"), 1000, CacheUsage.Default, lobSystem, customerModel, catalog);
3. EntityReference customerEntityRef = customerEntity.GetReference();
4.  
5. // create the identifier
6. Identifier customerIdentifier = customerEntity.CreateIdentifier("CustomerID", true, typeof(Int32));
7. IdentifierReference customerIdentifierRef = customerIdentifier.GetReference();

Next, I’ve created a class to hold type descriptor creation parameters. I’ve planned this class to support inheriting values from another instance of the class through its constructor. Only values explicitly set must be inherited, but default values must not. It requires the class to remember which values were specified explicitly. (Remark: in this version of code this behavior has not too much importance. I planned a merge functionality either that needs this feature in a possible later version.) The class is able to resolve name and lobName parameter values from each other so one should only specify one of them if they are the same.

1. public class TypeDescriptorParams
2. {
3.     private String _name;
4.     private bool _isNameSpecified = false;
5.     public String Name { get
6.     {
7.         return _name;
8.     }
9.         set
10.         {
11.             _isNameSpecified = true;
12.             _name = value;
13.         }
14.     }
15.  
16.     private bool _isCached;
17.     private bool _isIsCachedSpecified = false;
18.     public bool IsCached
19.     {
20.         get
21.         {
22.             return _isCached;
23.         }
24.         set
25.         {
26.             _isIsCachedSpecified = true;
27.             _isCached = value;
28.         }
29.     }
30.  
31.     private Type _type;
32.     private bool _isTypeSpecified = false;
33.     public Type Type
34.     {
35.         get
36.         {
37.             return _type;
38.         }
39.         set
40.         {
41.             _isTypeSpecified = true;
42.             _type = value;
43.         }
44.     }
45.  
46.     private String _lobName;
47.     private bool _isLobNameSpecified = false;
48.     public String LobName
49.     {
50.         get
51.         {
52.             return _lobName;
53.         }
54.         set
55.         {
56.             _isLobNameSpecified = true;
57.             _lobName = value;
58.         }
59.     }
60.  
61.     private IdentifierReference _identifierReference;
62.     private bool _isIdentifierReferenceSpecified = false;
63.     public IdentifierReference IdentifierReference
64.     {
65.         get
66.         {
67.             return _identifierReference;
68.         }
69.         set
70.         {
71.             _isIdentifierReferenceSpecified = true;
72.             _identifierReference = value;
73.         }
74.     }
75.  
76.     private FilterDescriptor _filterDescriptor;
77.     private bool _isFilterDescriptorSpecified = false;
78.     public FilterDescriptor FilterDescriptor
79.     {
80.         get
81.         {
82.             return _filterDescriptor;
83.         }
84.         set
85.         {
86.             _isFilterDescriptorSpecified = true;
87.             _filterDescriptor = value;
88.         }
89.     }
90.  
91.     private TypeDescriptorFlags _flags;
92.     private bool _isFlagsSpecified = false;
93.     public TypeDescriptorFlags Flags
94.     {
95.         get
96.         {
97.             return _flags;
98.         }
99.         set
100.         {
101.             _isFlagsSpecified = true;
102.             _flags = value;
103.         }
104.     }        
105.  
106.     private AssociationReference _associationReference;
107.     private bool _isAssociationReferenceSpecified = false;
108.     public AssociationReference AssociationReference
109.     {
110.         get
111.         {
112.             return _associationReference;
113.         }
114.         set
115.         {
116.             _isAssociationReferenceSpecified = true;
117.             _associationReference = value;
118.         }
119.     }
120.  
121.     private AdministrationMetadataCatalog _metadataCatalog;
122.     private bool _isMetadataCatalogSpecified = false;
123.     public AdministrationMetadataCatalog MetadataCatalog
124.     {
125.         get
126.         {
127.             return _metadataCatalog;
128.         }
129.         set
130.         {
131.             _isMetadataCatalogSpecified = true;
132.             _metadataCatalog = value;
133.         }
134.     }
135.  
136.     public TypeDescriptorParams()
137.     {
138.     }
139.  
140.     public TypeDescriptorParams(TypeDescriptorParams explicitParams)
141.     {
142.         if (explicitParams._isNameSpecified) this.Name = explicitParams.Name;
143.         if (explicitParams._isIsCachedSpecified) this.IsCached = explicitParams.IsCached;
144.         if (explicitParams._isTypeSpecified) this.Type = explicitParams.Type;
145.         if (explicitParams._isLobNameSpecified) this.LobName = explicitParams.LobName;
146.         if (explicitParams._isIdentifierReferenceSpecified) this.IdentifierReference = explicitParams.IdentifierReference;
147.         if (explicitParams._isFilterDescriptorSpecified) this.FilterDescriptor = explicitParams.FilterDescriptor;
148.         if (explicitParams._isFlagsSpecified) this.Flags = explicitParams.Flags;
149.         if (explicitParams._isAssociationReferenceSpecified) this.AssociationReference = explicitParams.AssociationReference;
150.         if (explicitParams._isMetadataCatalogSpecified) this.MetadataCatalog = explicitParams.MetadataCatalog;
151.     }
152.  
153.     public void ResolveNames()
154.     {
155.         // names default to the other name type
156.         if ((this._isNameSpecified) && (!this._isLobNameSpecified))
157.         {
158.             this.LobName = this.Name;
159.         }
160.  
161.         if ((!this._isNameSpecified) && (this._isLobNameSpecified))
162.         {
163.             this.Name = this.LobName;
164.         }
165.     }
166. }

You can see that we specify the Type and not the String-based type name of the descriptor. It will be resolved later to the name.

Having this class, I created the following extension methods to bridge my code to the standard BCS API calls:

1. public static TypeDescriptor CreateChildTypeDescriptor(this TypeDescriptor typeDescriptor, TypeDescriptorParams typeDescrParams)
2. {
3.     // to protect method against possible direct call with null value
4.     // should not happen when called like a "standard" extension method
5.     if (typeDescriptor == null)
6.     {
7.         throw new ArgumentNullException("typeDescriptor");
8.     }
9.  
10.     typeDescrParams.ResolveNames();
11.  
12.     TypeDescriptor result = typeDescriptor.ChildTypeDescriptors.Create(
13.         typeDescrParams.Name,
14.         typeDescrParams.IsCached,
15.         ResolveForBcs(typeDescrParams.Type),
16.         typeDescrParams.LobName,
17.         typeDescrParams.IdentifierReference,
18.         typeDescrParams.FilterDescriptor,
19.         typeDescrParams.Flags,
20.         typeDescrParams.AssociationReference);
21.  
22.     return result;
23. }
24.  
25. public static TypeDescriptor CreateRootTypeDescriptor(this Parameter parameter, TypeDescriptorParams typeDescrParams)
26. {
27.     // to protect method against possible direct call with null value
28.     // should not happen when called like a "standard" extension method
29.     if (parameter == null)
30.     {
31.         throw new ArgumentNullException("parameter");
32.     }
33.  
34.     typeDescrParams.ResolveNames();
35.  
36.     TypeDescriptor result = parameter.CreateRootTypeDescriptor(
37.         typeDescrParams.Name,
38.         typeDescrParams.IsCached,
39.         ResolveForBcs(typeDescrParams.Type),
40.         typeDescrParams.LobName,
41.         typeDescrParams.IdentifierReference,
42.         typeDescrParams.FilterDescriptor,
43.         typeDescrParams.Flags,
44.         typeDescrParams.AssociationReference,
45.         typeDescrParams.MetadataCatalog);
46.  
47.     return result;
48. }
49.  
50. public static Identifier CreateIdentifier(this Entity entity, String name, bool isCached, Type type)
51. {
52.     // to protect method against possible direct call with null value
53.     // should not happen when called like a "standard" extension method
54.     if (entity == null)
55.     {
56.         throw new ArgumentNullException("entity");
57.     }
58.  
59.     Identifier result =
60.         entity.Identifiers.Create(name, isCached, ResolveForBcs(type));
61.  
62.     return result;
63. }

The following helper method is used by the above methods to resolve the Type to the type name BCS calls require. For CLR library classes we need only the short name of the type, otherwise the qualified name.

1. private static string ResolveForBcs(Type type)
2. {
3.     String typeName = null;
4.     
5.     if (type != null)
6.     {
7.         typeName = (type.Module.ScopeName == "CommonLanguageRuntimeLibrary") ? type.ToString() : type.AssemblyQualifiedName;
8.     }
9.  
10.     return typeName;
11. }

Using these concepts, one can write more compact code, no need to specify null and common values. The calls to create type descriptors are easier to read and maintain.

1. TypeDescriptorParams standardStringType =
2.     new TypeDescriptorParams
3.     {
4.         IsCached = true,
5.         Type = typeof(Int32),
6.         Flags = TypeDescriptorFlags.None,
7.     };
8.  
9. TypeDescriptorParams standardIntType =
10.     new TypeDescriptorParams(standardStringType)
11.     {
12.         Type = typeof(Int32),
13.     };
14.  
15. // create the TypeDescriptor for the CustomerID parameter
16. CustomerIDParameter.CreateRootTypeDescriptor(new TypeDescriptorParams(standardIntType)
17. {
18.     Name = "CustomerID",
19.     IdentifierReference = customerIdentifierRef,
20.     MetadataCatalog = catalog
21. });

Repetitive code blocks requires only specifying unique parameter values:

1. returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
2. {
3.     Name = "FirstName"
4. });
5.  
6. returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
7. {
8.     Name = "LastName"
9. });
10.  
11. returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
12. {
13.     Name = "Phone"
14. });

This methods help you to write and alter the code easier, but there is still chance to have errors. To find these errors before activating your model, you can use the Validate method of your Entity, and check the returning ActivationError[] array if necessary.

1. ActivationError[] errors = customerEntity.Validate();
2.  
3. result = (errors.Length == 0);
4.  
5. if (result)
6. {
7.     customerEntity.Activate();
8.     Console.WriteLine("Model created");
9. }
10. else
11. {
12.     Console.WriteLine("Validation errors:");
13.     Array.ForEach(errors,
14.         error => Console.WriteLine(error));
15. }

Of course, these examples only provide you some idea about what can be done to create a more maintainable code. There are a lot of things to do yet, for example, BCS connection properties are still string based, that I think not really safe as well:

1. // set the connection properties
2. lobSystemInstance.Properties.Add("AuthenticationMode", "PassThrough");
3. lobSystemInstance.Properties.Add("DatabaseAccessProvider", "SqlServer");
4. lobSystemInstance.Properties.Add("RdbConnection Data Source", "sp2010");
5. lobSystemInstance.Properties.Add("RdbConnection Initial Catalog", "AdventureWorksLT");
6. lobSystemInstance.Properties.Add("RdbConnection Integrated Security", "SSPI");
7. lobSystemInstance.Properties.Add("RdbConnection Pooling", "true");

You can find the complete solution here, including code from my former posts about checking existence of a list from managed client object model and creating external list from code client side code.

How to query external lists on the server side using CAML and alter the external data?

In my former post I’ve wrote about how to query SharePoint 2010 BCS external lists using client side code. Now I try to show how to do it on the server side.

For this example I used the external links sample demonstrated in a this post. Although the download for that post contains only a single sample link having SharePoint in its title, you need to create more such links to see the real effect of the code below. I used that code in a server side console application, but after minor modifications it can be used in web context as well.

1. String siteUrl = "http://sp2010&quot;;
2.  
3. using (SPSite site = new SPSite(siteUrl))
4. {
5.     using (SPWeb web = site.OpenWeb())
6.     {
7.  
8.         SPList list = web.Lists["External Links"];
9.  
10.         // query items
11.         SPQuery query = new SPQuery();
12.         query.Query =
13.             @"<Where>
14.                 <Contains>
15.                     <FieldRef Name='Title' />
16.                     <Value Type='Text'>SharePoint</Value>
17.                 </Contains>
18.               </Where>
19.               <OrderBy>
20.                    <FieldRef Name='Title'/>
21.               </OrderBy>";
22.         query.ViewFields =
23.             @"<ViewFields>
24.                    <FieldRef Name='Title'/>
25.               </ViewFields>";
26.         // RowLimit seems to have no effect
27.         query.RowLimit = 1;
28.  
29.         SPListItemCollection items = list.GetItems(query);
30.  
31.         foreach (SPListItem item in items)
32.         {
33.             Console.WriteLine("Title: '{0}'", item["Title"]);
34.         }
35.  
36.     }
37. }

As you can see, the code is just like for a standard SharePoint list. You have to include the fields you use in the code in the ViewFields node of the CAML query as FieldRef. Similar to the client side example, specifying the RowLimit seems to have no effect, that is very sad, but true.

If your model allows that (it is not read-only and has the necessary methods), you can use code to alter (create, update, delete) your external data through the external list as well. In our case, the following code snippet will create a new link in the file system:

1. newItem["Link"] = new SPFieldUrlValue { Url = "http://www.company.com&quot;, Description = "Link item from code" };
2. newItem.Update();
3. list.Update();

In this case we had to fill only the Link field with the link title and URL, as external list item Title field will be determined in our .NET assembly BCS connector class based on the link title (see the original post for details). It means that the fields you have to fill depend on the nature of the connector and the properties of the external system.

How to query external lists on the client side using CAML?

One of the great features of SharePoint 2010 BCS External List concept is that it enables you to publish and access external data similar way as in the case of standard SharePoint list (I wrote similar, because there are significant exceptions as well). It includes the programmatic access either.

Despite of the similarities, I have found forums questions that show it is not always trivial to achieve your goals. In this post I show you a code example that illustrates how one can filter the external data using managed client object model through a  CAML query. For this example I’ve created an external list on my server that connects to the Customers table of the well-known Northwind database. You can find several posts about how to create the external content type (ECT) for you external list, for example here.

Once you have your external list created (called Northwind Customers in my case), you can try this code from your client project:

1. String siteUrl = "http://sp2010&quot;;
2.  
3. ClientContext clientContext = new ClientContext(siteUrl);
4.  
5. List list = clientContext.Web.Lists
6.     .GetByTitle("Northwind Customers");
7. CamlQuery camlQuery = new CamlQuery();
8. // When using a CAML query for external list
9. // one should include all referenced fields in ViewFields
10. // otherwise a "The given key was not present in the dictionary" exception is thrown
11. // RowLimit seems to have no effect            
12. camlQuery.ViewXml =
13.     @"<View>
14.         <Query>
15.           <Where>
16.             <Eq>
17.               <FieldRef Name='City'/>
18.               <Value Type='Text'>London</Value>
19.             </Eq>
20.           </Where>
21.           <OrderBy>
22.             <FieldRef Name='ContactName'/>
23.           </OrderBy>
24.         </Query>
25.         <ViewFields>
26.           <FieldRef Name='CustomerID'/>
27.           <FieldRef Name='ContactName'/>
28.           <FieldRef Name='CompanyName'/>
29.           <FieldRef Name='City'/>
30.         </ViewFields>
31.         <RowLimit>1</RowLimit>
32.     </View>";
33.  
34. // include referenced field here as well
35. ListItemCollection listItems = list.GetItems(camlQuery);
36. clientContext.Load(
37.      listItems,
38.      items => items
39.          .Include(
40.             item => item["CustomerID"],
41.             item => item["CompanyName"],
42.             item => item["ContactName"],
43.             item => item["City"]));
44. clientContext.ExecuteQuery();
45.  
46. // display the result
47. foreach (ListItem listItem in listItems)
48. {
49.     Console.WriteLine("CustomerID: '{0}', CompanyName: '{1}', ContactName: '{2}'",
50.         listItem.FieldValues["CustomerID"],
51.         listItem.FieldValues["CompanyName"],
52.         listItem.FieldValues["ContactName"]);
53. }
54.  
55. Console.Write("Press Enter to continue!");
56. Console.ReadLine();

The code filters customers that have London specified in the City field and order them by the value of the ContactName field.

Important things to note:

• You must include all fields you use either to filter, order in CAML query, or to display later in your code into both the ViewFields node of CAML query as FieldRef and load into the ClientContext as illustrated above. Otherwise you will receive a "The given key was not present in the dictionary" exception when referencing the field later.
• Specifying the RowLimit seems to have no effect, all of the matching items are returned by the query (I found similar info in this forum thread). That is not very nice when working with external lists having large item count.

How to get a BCS metadata catalog without specifying a URL?

It was the original question in this MSDN forum thread.

As you probably know, we can get a reference for a BCS catalog from client side code (Microsoft.SharePoint.BusinessData.Administration.Client) like this:

AdministrationMetadataCatalog catalog = AdministrationMetadataCatalog.GetCatalog(siteUrl);

On the server side (Microsoft.SharePoint.BusinessData.Administration) there are two different options. If the process runs in the SharePoint context, you can use a code that does not require the URL:

BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
AdministrationMetadataCatalog catalog = service.GetAdministrationMetadataCatalog(SPServiceContext.Current);

However if your code runs out of SharePoint context, you need a URL again to get the SPServiceContext reference:

SPServiceContext.GetContext(siteUrl)

In this specific question we had to solve the puzzle for the server side, out of SharePoint server context variation, to be able to use the code from an STSADM command.

Although my first reply (one has to provide some kind of context for the BdcSerivce to be able to return a catalog to work with) remained true, it turned out one can get a service context without specifying a site URL, and it can be done using the following code snippet:

1. private AdministrationMetadataCatalog GetBdcCatalog()
2. {
3.     AdministrationMetadataCatalog catalog = null;
4.  
5.     try
6.     {
7.         BdcService bdcService = SPFarm.Local.Services.GetValue<BdcService>();
8.         SPServiceApplication serviceApp = bdcService.Applications.FirstOrDefault();
9.  
10.         if (serviceApp != null)
11.         {
12.             SPServiceContext context = SPServiceContext.GetContext(serviceApp.ServiceApplicationProxyGroup, SPSiteSubscriptionIdentifier.Default);
13.             catalog = bdcService.GetAdministrationMetadataCatalog(context);
14.         }
15.     }
16.     catch (Exception ex)
17.     {
18.         // do error handling
19.     }
20.  
21.     return catalog;
22.  
23. }

A bit tricky, but seems to work as expected.

Creating external lists using the managed client object model

Last year I wrote a post about how to create external lists in SharePoint 2010 using server side code. Now I show you how to achieve the same result from the client side.

The solution does not differ too much, there are only two minor differences:

1. External data source property names are not exposed as public fields (see the members of the SPListDataSource.BDCProperties class on the server side).
2. When adding the new external list to the SPListCollection, you have to use the Add method that has three String parameters (title, description and url) and a dataSource parameter of SPListDataSource type. The result of the call is the ID (Guid) of the new list.
   On the client side we have use the Add method of the ListCollection class that has a single parameters parameter of type ListCreationInformation. The ListCreationInformation class represents all the information (list title, description, URL, data source properties, and a few more info) we used on the server side. The return type of the Add method is List. Another Important difference is that in this case the data source properties are not represented as a SharePoint-specific type, like SPListDataSource on the server side, but they are collected into a DataSourceProperties property that is a “standard” .NET  IDictionary<string, string> type. The additional information in ListCreationInformation contains for example a QuickLaunchOption property of enum type  QuickLaunchOption (possible values Off, On and DefaultValue).

After this introduction let’s see the code.

First, I’ve created an extension method in a static helper class to make my life easier and to avoid mistyping parameter names:

1. // the listDataSource parameter is only a fake one to enable attaching the static method to ListDataSource type
2. public static IDictionary<String, String> Initialize(this ListDataSource listDataSource, String entity, String entityNamespace, String lobSystemInstance, String specificFinder)
3. {
4.     // BDCProperties (SPListDataSource internal class) are not available from client code, we should use string literals
5.     Dictionary<String, String> result = new Dictionary<String, String>();
6.     result.Add("Entity", entity);
7.     result.Add("EntityNamespace", entityNamespace);
8.     result.Add("LobSystemInstance", lobSystemInstance);
9.     result.Add("SpecificFinder", specificFinder);
10.  
11.     return result;
12. }

As you can see, the listDataSource parameter is not used in the class. My purpose was only to bind the functionality to the ListDataSource class and keep the working of the server-side and client-side codes as parallel as it is possible.

The following code demonstrates the creation of the external list from the managed client API using the extension method we created above.

1. ClientContext clientContext = new ClientContext(siteUrl);
2. Web web = clientContext.Web;
3.  
4. ListCollection lists = web.Lists;
5.  
6. ListDataSource listDataSource = new ListDataSource();
7. // set up the list data source properties using the extension method (see Extensions.cs)
8. IDictionary<String, String> listDataSourceProp = listDataSource.Initialize("YourBdcEntity", "YourBdc.EntityNamespace", "YourLobSystemInstancece", "ReadItem");
9.  
10. // initialize create list info
11. // IMPORTANT! The DataSourceProperties is not of type ListDataSource,
12. // as one may expect, but a simple IDictionary<String, String>
13. ListCreationInformation listCreateInfo = new ListCreationInformation
14. {
15.     Title = listTitle,
16.     Description = "List description",
17.     DataSourceProperties = listDataSourceProp,
18.     Url = "listurl",
19.     QuickLaunchOption = QuickLaunchOptions.On
20. };
21.  
22. // create list
23. List newList = lists.Add(listCreateInfo);
24.  
25. clientContext.ExecuteQuery();

I made some test if I can to change the data source binding of a list after creation.

The List class contains a DataSource property of type ListDataSource, but it is read-only so you cannot make a standard list to  an external one, or change the data source parameters simply like:

newList.DataSource = listDataSource;

The above code lines produces a compile time error.

I’ve also tested what happens if I change the properties of the data source one-by-one like shown here:

1. clientContext.Load(newList, list => list.DataSource);
2. clientContext.ExecuteQuery();
3.  
4. newList.DataSource.Properties["SpecificFinder"] = "AnotherSpecificFinder";
5. clientContext.ExecuteQuery();

Using this code I’ve experienced neither compile-time nor run-time error, but when I checked the value of the property later using this code:

1. clientContext.Load(existingList, list => list.DataSource);
2. clientContext.ExecuteQuery();
3. Console.WriteLine(existingList.DataSource.Properties["SpecificFinder"]);

I found that the value of the property has not been changed. So it seems to be a good idea to plan and set the properties of your external lists in advance.

Validation issues with external lists (or have I missed something?)

Probably most of you have already seen one of those compelling demos of the new external list feature of SharePoint 2010 when it took only a few mouse click for the presenter to display business data stored for example in SQL database integrated into SharePoint as a list.

I should admit I became very enthusiastic over this exciting new possibility when first saw that on the SharePoint Conference last year. One year has elapsed since then and – although I’m still interested in this feature – I had to learn there are some rather annoying issues if you scrape the surface a bit.

One of the most serious issues I found is the question of validation. It’s usually trivial in the case of a traditional business application development, but it will give you a hard time in the case of external list.

In this post I illustrate this issue only with a few examples and the error stack traces belonging to them.

First, let’s see the well known Northwind Customers example. In this case the customers from the Northwind database are displayed in a SharePoint external list.

The image below shows the fields of the list. As you can see, all of the fields are Single line of text (the corresponding type in BDC is System.String), but you have no more information about them than whether they are optional of mandatory fields.

In contrast with System.String database fields are usually has length limitation. So let’s see what happens if we type content longer than the corresponding field in database allows that.

I would expect some user friendly warning message but instead of that we receive an unhandled exception.

Full stack trace is displayed below, having the most important text parts bolded.

Server Error in ‘/’ Application.
——————————————————————————–

String or binary data would be truncated.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[SqlException (0x80131904): String or binary data would be truncated.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +212
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +245
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2811
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +58
   System.Data.SqlClient.SqlDataReader.get_MetaData() +112
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6281668
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +6282737
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +424
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +28
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +211
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +19
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +19
   Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbSystemUtility.ExecuteStaticInternal(DbAccessProviderObjectFactory accessProviderObjectFactory, IConnectionManager connectionManager, IMethodInstance methodInstance, ILobSystemInstance lobSystemInstance, INamedPropertyDictionary lobSystemInstanceProperties, Object[]& args, IDbConnection connection, Boolean connectionExplicitlyManaged) +14939845
   Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbSystemUtility.ExecuteStatic(IMethodInstance methodInstance, ILobSystemInstance lobSystemInstance, Object[] args, IExecutionContext context) +577

[RuntimeException: The query against the database caused an error.]
   Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbSystemUtility.ExecuteStatic(IMethodInstance methodInstance, ILobSystemInstance lobSystemInstance, Object[] args, IExecutionContext context) +14941379
   Microsoft.SharePoint.BusinessData.Runtime.DataClassRuntime.ExecuteInternalWithAuthNFailureRetry(ISystemUtility systemUtility, IMethodInstance methodInstanceToExecute, IMethod methodToExecute, ILobSystemInstance lobSystemInstance, ILobSystem lobSystem, IParameterCollection nonReturnParameters, Object[] overrideArgs) +377
   Microsoft.SharePoint.BusinessData.Runtime.DataClassRuntime.ExecuteInternal(IDataClass thisDataClass, ILobSystemInstance lobSystemInstance, ILobSystem lobSystem, IMethodInstance methodInstanceToExecute, IMethod methodToExecute, IParameterCollection nonReturnParameters, Object[]& overrideArgs) +19307891
   Microsoft.SharePoint.BusinessData.Runtime.EntityRuntime.ExecuteInternal(IDataClass thisDataClass, ILobSystemInstance lobSystemInstance, ILobSystem lobSystem, IMethodInstance methodInstanceToExecute, IMethod methodToExecute, IParameterCollection nonReturnParameters, Object[]& overrideArgs, IFilterCollection filters) +122
   Microsoft.SharePoint.BusinessData.Runtime.AbstractEntityInstance.UpdateInternal(IMethodInstance updater, IEntityInstance instanceToUpdate, IDictionary`2 preValues) +316
   Microsoft.SharePoint.BusinessData.Runtime.AbstractEntityInstance.Update(Boolean compareHash) +1684
   Microsoft.SharePoint.SPListDataSource.UpdateEntityInstance(String bdcid, SPListItem itemToUpdate, IDictionary dictFieldValues) +212

[SPException: Failed to update a list item for this external list based on the Entity (External Content Type) 'Customer' in EntityNamespace 'http://sp2010/offline'. Details: The query against the database caused an error.]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.UpdateCallback(Int32 affectedRecords, Exception ex) +23690873
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +4052212
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +312
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

You will get similar exceptions if you database table has unique key constraints of foreign key references and the user input contains a value that does not fulfill these constraints.

Now let’s see some examples where we work with Visual Studio and edit the BDC model file.

If you read the page BDC Custom Properties there are some promising properties.

Reference type fields (like String) are optional by default and value type fields (like int, double) are mandatory.

You can change this behavior and set for example a text as mandatory and a number to optional via the RequiredInForms property.

The following XML fragments of the DBC model file illustrate that concept.

1. <TypeDescriptor TypeName="System.String" Name="Message">
2.   <Properties>
3.     <Property Name="RequiredInForms" Type="System.Boolean">true</Property>
4.   </Properties>
5. </TypeDescriptor>

and similarly

1. <TypeDescriptor TypeName="System.Int32" Name="Number">
2.   <Properties>
3.     <Property Name="RequiredInForms" Type="System.Boolean">false</Property>
4.   </Properties>
5. </TypeDescriptor>

If you prefer, you can use the BDC designer as well and set the property there in the Custom Properties.

If you want your value type to be optional, don’t forget to declare that accordingly (e.g. it should be nullable to accept empty values):

public int? Number { get; set; }

I’ve also played with the DecimalDigits property to limit the allowed number of decimal digits for a number and with the Size property to set the maximum length of a text, but without success.

Back to the validation issues.

If you have a field in your model that stores its value as int, then the input field control is smart enough to detect if you type a text into it. In this case you get the following validation error:

The value of this field is not a valid number.

But it is not smart enough to limit you only typing integers. So if you enter a non-integer number (like 22.22) or the text “NaN” (!) then you get different exceptions based on the method type. In the case of the edit item form (in the Updater method):

Server Error in ‘/’ Application.
——————————————————————————–

The data source control failed to execute the update command. 0616f1c3-a17b-4218-90af-932d82d54a85
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Microsoft.SharePoint.WebPartPages.DataFormWebPartException: The data source control failed to execute the update command. 0616f1c3-a17b-4218-90af-932d82d54a85

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[DataFormWebPartException: The data source control failed to execute the update command. 0616f1c3-a17b-4218-90af-932d82d54a85]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.UpdateCallback(Int32 affectedRecords, Exception ex) +23690775
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +4052212
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +312
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

In the case of the new item form (in the Creator method):

Server Error in ‘/’ Application.
——————————————————————————–

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[FormatException: Input string was not in a correct format.]
   System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +10161267
   System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +207
   System.String.System.IConvertible.ToInt32(IFormatProvider provider) +61
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +307
   Microsoft.SharePoint.BdcClientUtil.ChangeType(Object value, Type destinationType, IFormatProvider culture) +478
   Microsoft.SharePoint.SPListDataSource.FillOneFieldValue(SPField fld, IView view, String bdcfldnm, Object objRawFieldValue, IFieldValueDictionary dictFields) +536

[SPException: Failed to convert value '22.22' to type 'System.Int32' for field 'Number' in Entity (External Content Type) 'Entity1', EntityNamespace 'BDCValidationTest.BDCModel'. Details: Input string was not in a correct format.]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.InsertCallback(Int32 affectedRecords, Exception ex) +23690584
   System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +4052324
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +378
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

Another example, if you type a value that does not fit into the size of the .NET type you used in you model and entity. In this case I declared the type as byte, but entered the number 256:

Server Error in ‘/’ Application.
——————————————————————————–

Value was either too large or too small for an unsigned byte.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.OverflowException: Value was either too large or too small for an unsigned byte.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Stack Trace:

[OverflowException: Value was either too large or too small for an unsigned byte.]
   System.Byte.Parse(String s, NumberStyles style, NumberFormatInfo info) +4386590
   System.String.System.IConvertible.ToByte(IFormatProvider provider) +55
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +10246009
   Microsoft.SharePoint.BdcClientUtil.ChangeType(Object value, Type destinationType, IFormatProvider culture) +478
   Microsoft.SharePoint.SPListDataSource.FillOneFieldValue(SPField fld, IView view, String bdcfldnm, Object objRawFieldValue, IFieldValueDictionary dictFields) +536

[SPException: Failed to convert value '256' to type 'System.Byte' for field 'Byte' in Entity (External Content Type) 'Entity1', EntityNamespace 'BDCValidationTest.BDCModel'. Details: Value was either too large or too small for an unsigned byte.]
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.UpdateCallback(Int32 affectedRecords, Exception ex) +23690873
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +4052212
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.FlatCommit() +312
   Microsoft.SharePoint.WebPartPages.DataFormWebPart.HandleOnSave(Object sender, EventArgs e) +24
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +687
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

Finally, another strange behavior in SharePoint lists, not limited to the external lists only. If you have to specify the value of a numerical field, you can type a number containing any number of digit grouping symbols in any order (like “3,,,,2” that will be interpreted as 32). The only exception is that placing it into the first position (like “,32”) is not allowed.

After this experience, I’m thinking about how validation issues can be handled in the case of external lists to make them a reliable tool that can be used even for business applications, not only in presentation rooms.