none
Case Sensitive Foreign Key In Database With Case Insensitive Collation

    Question

  • I have two tables that have a foreign key that is based on a field that is VARCHAR(200).  They reside in a database that has a case insensitive collation.  However, despite this fact, when I perform a LINQ query, nothing is returned for the navigation property for a particular record since the casing doesn't match.  Is there somewhere in the ADO.NET Entity Data Model settings that I can change this to be case insensitive for foreign keys?  Any help would greatly appreciated.
    Monday, August 23, 2010 6:04 PM

Answers

  • Jason,

    unfortunately, currently there is no simple way to accomplish that. The way things work now, you would need to fix the data or use views that convert the columns to values that will compare correctly in the CLR (i.e. convert all to upper case).

    thanks,

    Julia


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, August 23, 2010 11:02 PM

All replies

  • Jason,

    unfortunately, currently there is no simple way to accomplish that. The way things work now, you would need to fix the data or use views that convert the columns to values that will compare correctly in the CLR (i.e. convert all to upper case).

    thanks,

    Julia


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, August 23, 2010 11:02 PM
  • Hi Jason,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, August 27, 2010 1:32 AM
  • I also have the same problem.

    I try to use the following code (use partial class)
          partial void OnForeignKeyChanged()
            {
                this._foreignKey = this._foreignKey .ToUpper();
            }

    If foreignKey is not the Key for this EntityObject,It can work.Otherwise, have an error(because the Entity key has been changed).
    There is a way to solve this problem?

    Or a way to convert the case before the ObjectContext?

    Saturday, January 22, 2011 8:26 AM