Business Data Catalog and Guids as table primary keys

This one had me puzzled for a little while today. People have been asking us to get BDC Meta Man generating application definition files for tables with Guid primary key columns rather than integers for a while and so this weekend that was the task.

The first thing you have to get your head round is that you'll need a seperate finder and specific finder method for guid tables. With integer primary keys you can get away with just one method that has two method instances.

Second is more tricky! If you define your identifier and type descriptors as System.Guid your profile page will return the following error when executing the specific finder method:

Exception in BusinessDataWebPart.OnPreRender: System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.

What you need to do however is define these as type System.String. Also do not put apostraphies around the parameter in the SQL statement as I did to start with, you'll get errors about key type conversion if you do that. Here's an example application definition file for a very simple table that has a guid as it's primary key:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance " xsi:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.XSD" xmlns=" http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog" Type="Database" Version="1.0.0.0" Name="GuidTestLOBSystem">
<Properties>
  <Property Name="WildcardCharacter" Type="System.String">%</Property>
</Properties>
<LobSystemInstances>
  <LobSystemInstance Name="GuidTestInstance">
   <Properties>
    <Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
    <Property Name="AuthenticationMode" Type="System.String ">PassThrough</Property>
    <Property Name="RdbConnection Data Source" Type="System.String">localhost</Property>
    <Property Name="RdbConnection Initial Catalog" Type=" System.String">GuidTest</Property>
    <Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
    <Property Name="RdbConnection Pooling" Type=" System.String">false</Property>
   </Properties>
  </LobSystemInstance>
</LobSystemInstances>
<Entities>
  <Entity EstimatedInstanceCount="0" Name=" dbo.Customers">
   <Identifiers>
    <Identifier Name="CustomerId" TypeName="System.String" />
   </Identifiers>
   <Methods>
    <Method Name="Getdbo.Customers ">
     <Properties>
      <Property Name="RdbCommandText" Type="System.String">Select CustomerId,CustomerName From dbo.Customers</Property>
      <Property Name="RdbCommandType" Type=" System.Data.CommandType">Text</Property>
     </Properties>
     <Parameters>
      <Parameter Direction="Return" Name="dbo.Customers">
       <TypeDescriptor TypeName=" System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.CustomersDataReader" IsCollection="true">
        <TypeDescriptors>
         <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.CustomersDataRecord">
          <TypeDescriptors>
           <TypeDescriptor TypeName="System.String" IdentifierName="CustomerId" Name="CustomerId" />
           <TypeDescriptor TypeName="System.String" Name="CustomerName" />
          </TypeDescriptors>
         </TypeDescriptor>
        </TypeDescriptors>
       </TypeDescriptor>
      </Parameter>
     </Parameters>
     <MethodInstances>
      <MethodInstance Name="dbo.CustomersFinder" Type="Finder" ReturnParameterName="dbo.Customers" ReturnTypeDescriptorName="dbo.CustomersDataReader" ReturnTypeDescriptorLevel="0" />
     </MethodInstances>
    </Method>
    <Method Name="dbo.CustomersSpecificFinder">
     <Properties>
      <Property Name="RdbCommandText" Type="System.String ">Select CustomerId,CustomerName From dbo.Customers Where (CustomerId=@CustomerId)</Property>
      <Property Name="RdbCommandType" Type=" System.Data.CommandType">Text</Property>
     </Properties>
     <FilterDescriptors>
      <FilterDescriptor Type="Comparison" Name="CustomerId" />
     </FilterDescriptors>
     <Parameters>
      <Parameter Direction="In" Name="@CustomerId">
       <TypeDescriptor TypeName="System.String" IdentifierName="CustomerId" AssociatedFilter="CustomerId" Name="CustomerId" />
      </Parameter>
      <Parameter Direction="Return" Name="dbo.Customers">
       <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name=" dbo.CustomersDataReader" IsCollection="true">
        <TypeDescriptors>
         <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name=" dbo.CustomersDataRecord">
          <TypeDescriptors>
           <TypeDescriptor TypeName="System.String" IdentifierName="CustomerId" Name="CustomerId" />
           <TypeDescriptor TypeName=" System.String" Name="CustomerName" />
          </TypeDescriptors>
         </TypeDescriptor>
        </TypeDescriptors>
       </TypeDescriptor>
      </Parameter>
     </Parameters>
     <MethodInstances>
      <MethodInstance Name="dbo.CustomersSpecificFinder" Type="SpecificFinder" ReturnParameterName="dbo.Customers" ReturnTypeDescriptorName=" dbo.CustomersDataReader" ReturnTypeDescriptorLevel="0" />
     </MethodInstances>
    </Method>
   </Methods>
  </Entity>
</Entities>
</LobSystem>