CrmAdo applies .ToLower() to the column names in a Select statement

[lornemcintosh]

CrmAdo applies .ToLower() to the column names in a Select statement, but MSCRM/Xrm has case-sensitive attribute names.

E.g. query:

SELECT leadid, emailaddress1, CFPAbsolute_Industry, CFPState_Province, CFPRelationship_Type
FROM lead

The result is an exception like:

System.ServiceModel.FaultException`1 was unhandled _message='Lead' entity doesn't contain attribute with Name = 'cfpabsolute_industry'.

Note that the query requests the proper CamelCased name ‘CFPAbsolute_Industry’, but the error indicates that a lower cased version was actually requested of Xrm. If I do the SOAP request to Xrm manually, with the CamelCased name, it works fine.

The attribute in question is an 'Option Set' type, and has the following names, (read directly from the MetadataSchema.Attribute table): Name: CFPAbsolute_Industry PhysicalName: CFPAbsolute_Industry LogicalName: CFPAbsolute_Industry TableColumnName: CFPAbsolute_Industry

[dazinator]

Thanks for reporting this issue. I was under the illusion that CRM automatically created logical attribute names that were lower case.. I will investigate further

[lornemcintosh]

Yeah, it usually creates lower case logical names. Somehow (don't ask me how!) our system has a bunch of attributes with CamelCased logical names though. We've had similar trouble with another tool that also makes the lowercase assumption as a result of this.

[dazinator]

Have done some investigation and here are my thoughts so far!

The assumption that attribute logical names are always lower case fits with the documentation that I have read - so it seems a safe assumption to make really - assuming there isn't a bug in CRM (haven't seen one) that would lead to non lower case logical names, and also assuming you don't bypass the SDK and run sql against the Dynamics Database directly in order to create attributes etc (which is not supported)

Could these attributes with non lower case logical names have been created by someone directly doing it in the database do you think rather than through the UI or via the SDK?

I am going to investigate making CrmAdo preserve the casing in terms the column names you give it, but really I wan't to keep it as close to SQL as possible which isn't case sensitive, i.e these are identical:

SELECT FIRSTNAME, LASTNAME FROM CONTACT

and

select firstname, lastname from contact

If you were querying the database directly, either of those two queries would work - and I want the same to hold true for CrmAdo.

So If do decide to make any changes around this, I am thinking I would have to expose some setting (perhaps in the connection string?), whereby you could turn this case sensitivity on or off. By default it would be off, so things work the way I have described above by default, but then in your case you would be able to override this behaviour and turn case sensitive column names on.

That will take a bit more time to implement, but it would then atleast allow you to use CrmAdo with these wierd columns!

[dazinator]

To enable case of columns to be preserved, you can now (as of next NuGet release) add this to your connection string: CaseSensitiveColumnNames=True

Example connection string:

<add name="CrmOrganisation" connectionString="Url=https://someorg.crm4.dynamics.com/; Username=someuser@someorg.onmicrosoft.com; Password=somepassword; DeviceID=some-deviceid; DevicePassword=somepassword;CaseSensitiveColumnNames=True" providerName="System.Data.DynamicsCrm.CrmAdo"/>

Or if you don't like that, you can do it in code.

After you have an instance of your connection, do this before doing any queries:

CrmDbConnection connection = GetConnection();
connection.Settings.CaseSensitiveColumnNames = true;

Please give it a go and let me know if it works - as I have only Unit tested this feature by mocking up a CRM instance, not actually done any tests of this against a "real" CRM.