Beware with BCS Filter on non-nullable types

Application scenario:

• ‘query’ operation in external system with one or more import parameters; with default values
  • Examples: SQL dynamic query or stored procedure, SAP Remote Function Call, SAP Gateway Service, WCF web service;
• ‘query’ operation exposed via a BCS::Finder method; each of the import parameters mapped to BCS Filter parameters
• SharePoint view on the ‘query’ result; e.g. via ExternalList, Business Data List, custom webpart

In such an application context you might experience that when you (on purpose or by accident) do not enter concrete values for the Filter parameters, SharePoint retrieves + renders a different set of external items as expected. You would expect the native ‘query’ operation to apply its default value(s). However, this is only true when the types of the import/filter parameters are nullable. If not nullable, SharePoint is required to submit a concrete value when invoking the Finder method. If missing from front-end invocation, SharePoint will submit the default value for the non-nullable type. E.g. for a DateTime, this means the value ‘1-1-1900’.

You have 2 options to alter this SharePoint BCS invocation behavior:

1. In the BCS Model, declare the non-nullable types as nullable.
   <TypeDescriptor TypeName="System.Nullable`1[[System.DateTime, mscorlib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" Name="IV_SEARCH_BEGDA" AssociatedFilter="IV_SEARCH_BEGDA">
2. In the BCS Model, search for a method parameter with name "<filter-import-parameter>Specified". If present, make sure it’s default value is set to ‘false’.
   <TypeDescriptor TypeName="System.Boolean" Name="IV_SEARCH_BEGDASpecified">
       <DefaultValues>
           <DefaultValue MethodInstanceName2="Find_ExternalItems" Type="System.Boolean">false</DefaultValue>
       </DefaultValues>
   </TypeDescriptor>