BDC Web Part - Column filtering

When a BDC web part is mapped with an instance of BDC Application Definition file ( that uses SQL database ), sometimes the column filtering doesn't work. On filtering of the column, it doesn't return any data.

The reason being the issue integration of SQL Server with SharePoint when it comes to data types. Basically, if the data type in the SQL table is CHAR, we don't seem to be able to use the filter feature in Business Data List web part. This behavior is not seen if the data type is nvarchar/varchar. Changing the data types from SQL enterprise manager also does not seem to help. Pumping the SQL server table data to a new table schema (with nvarchar/varchar instead of char) works. In the BDC application definition file for the typedescriptor, the typename is of System.String(<TypeDescriptor TypeName="System.String ">). System.String maps only with varchar/nvarchar. It doesn't map with char datatypes. Changing the tyename to System.Char also doesn't work. Changing the sql query to cast / convert to varchar datatype also doesn't work.

The workaround to this problem is to change the datatype in SQL table to Varchar/nvarchar instead of char. Just by changing the datatypes in the table won't work. We need to write out the SQL server data to a CSV/TXT file and populate them back into the new table schema (with nvarchar instead of char) and that will help fixing this filtering issue.