none
System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'

    Question

  • I'm getting the following error message when submitting changes.  Unfortunately the exception is being thrown in the System.Data.Linq code so I can't do much in terms of debugging the error.

     

    Code Snippet

    {System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'.
       at System.Data.Linq.IdentityManager.StandardIdentityManager.SingleKeyManager`2.TryCreateKeyFromValues(Object[] values, V& v)
       at System.Data.Linq.IdentityManager.StandardIdentityManager.IdentityCache`2.Find(Object[] keyValues)
       at System.Data.Linq.IdentityManager.StandardIdentityManager.Find(MetaType type, Object[] keyValues)
       at System.Data.Linq.CommonDataServices.GetCachedObject(MetaType type, Object[] keyValues)
       at System.Data.Linq.ChangeProcessor.GetOtherItem(MetaAssociation assoc, Object instance)
       at System.Data.Linq.ChangeProcessor.BuildEdgeMaps()
       at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
       at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
       at System.Data.Linq.DataContext.SubmitChanges()
       at MSEC.Apex.DataAccess.ApexMembership.UpdateUser(ApexUser User, String cs) in C:\Dev\MSEC Apex\Source\MSEC.Apex.DataAccess\ApexMembership.cs:line 664}

     

     

    In terms of what I'm doing here is the Linq code:

     

    Code Snippet

    ApexDataContext apex = new ApexDataContext(cs);

    apex.Log = new DebuggerWriter();

     

    var userEntities = from e in apex.entities

    where e.entity_id == User.Entity_Id

    select e;

     

    entity anEntity = userEntities.FirstOrDefault<entity>();

     

    anEntity.entity_u36 = User.UserName;

    anEntity.create_dt = User.CreationDate;

     

    // Method One

    //phone aPhone = anEntity.phones.Where<phone>(phone => phone.phone_type == "EMAIL").FirstOrDefault<phone>();

     

    // Method Two

    var phoneQuery = from p in apex.phones

    where p.phone_type == "EMAIL"

    where p.phone_id == anEntity.phone_id

    select p;

     

    phone aPhone = phoneQuery.FirstOrDefault<phone>();

    aPhone.phone_no = User.Email;

    try

    {

    apex.SubmitChanges();

    return true;

    }

    catch (Exception ex)

    {

    throw ex;

    }

     

     

    If you look at the comments, you'll see that I've tried two different methods of updating items in the "phones" entity set.

    The first method uses the entity set object graph.  The second method uses a second query.  Both methods generate the same error.

     

    Any suggestions?

     

    Monday, May 05, 2008 4:01 PM

Answers

  •  

    I actually ended up resolving the issue by modifying the table so that the PK was on the identity column and switching the name to a UNIQUE constraint (though the unique constraint would likely be irrelevant to the problem):

     

    CREATE TABLE [dbo].[SystemSettingsCategoryList](

    [Category_ID] [smallint] IDENTITY(0,1) NOT NULL,

    [CategoryName] [varchar](50) NOT NULL,

    [CategoryDescription] [varchar](200) NULL,

    CONSTRAINT [PK_SystemSettingsCategoryList] PRIMARY KEY CLUSTERED

    [Category_ID] ASC

    CONSTRAINT [UQ_SystemSettingsCategoryList_CategoryName] UNIQUE NONCLUSTERED

    [CategoryName] ASC

     

    Why LINQ did not like having the primary key on the name table eludes me, as well as to why it was trying to link the SystemSettingsCategoryList table when I was only modifying data in the SystemSettings table. Maybe LINQ will only work with foreign key links to primary keys? Unfortunately I don't have time to do more testing to see what the true problem is, but I'm sure someone with a deeper understanding of the LINQ system has already found this issue.

     

    Good luck with resolving your problem if you feel like tackling it.

     

     

    Tuesday, May 27, 2008 7:37 PM

All replies

  • It seems like there is a problem in the mapping.  LINQ to SQL is trying to build up an edge map the defines the dependency graph between known objects.  To do this it is following associations.  It is attempting to follow an association by value (the reference must not be assigned or loaded at this time), by looking the object on the other side of the association in the identity cache. It is doing this by reading the foreign key value out of one object and using it as a key to lookup the other object in the cache.  The data types are not matching.  Somehow, one side of the association is stored as a string and the other side is an integer.  If you review you association mappings you should find this to be true.
    Monday, May 05, 2008 6:03 PM
  • Thanks for the reply.

     

    I too thought that the problem might be the association between the tables and the data types on both sides.  On the parent side the field is an Int (System.Int32).  On the child side the field is also an Int (System.Int32).  The association is a one to many relationship.  I am able to use the association to retrieve values from the child table.

     

    So as far as I can see, the association is valid.  Any other ideas?

     

    -Tim

    Monday, May 05, 2008 8:05 PM
  • I was curious if you found a resolution to this problem?

     

    I have run into what appears to be the exact same problem. I have a table which has a foreign key to an identity in another table and getting an InvalidCastException when trying to update a column in the table. Below is the database schema for the two tables and the code I'm executing to get the error:

     

    [dbo].[SystemSettings]

    [SettingName] [varchar](50) NOT NULL,

    [SettingDescription] [varchar](200) NULL,

    [SettingValue] [varchar](100) NULL,

    [SettingValueDatatype] [varchar](50) NOT NULL,

    [SettingCategory_ID] [smallint] NOT NULL,

    [LastUpdate] [smalldatetime] NOT NULL DEFAULT (getdate()),

    [DependantSettingName] [varchar](50) NULL

     

    [dbo].[SystemSettingsCategoryList]

    [Category_ID] [smallint] IDENTITY(0,1) NOT NULL,

    [CategoryName] [varchar](50) NOT NULL,

    [CategoryDescription] [varchar](200) NULL,

    PRIMARY KEY CLUSTERED

    [CategoryName] ASC

    UNIQUE NONCLUSTERED

    [Category_ID] ASC

     

    ALTER TABLE [dbo].[SystemSettings] ADD CONSTRAINT [FK_SystemSettings_SystemSettingsCategoryList] FOREIGN KEY([SettingCategory_ID])

    REFERENCES [dbo].[SystemSettingsCategoryList] ([Category_ID])

     

    ALTER TABLE [dbo].[SystemSettings] ADD CONSTRAINT [FK_SystemSettings_Dependant] FOREIGN KEY([DependantSettingName])

    REFERENCES [dbo].[SystemSettings] ([SettingName])

     

     

     

    O/R Designer shows "SystemSettingsCategoryList.Category_ID -> SystemSettings.SettingCategory_ID" under the Participating Properties of the FK line.

     

    Code:

     

    Code Snippet

    public static void SetValue(string settingName, object settingValue)

    {

    DataClassesDataContext db = new DataClassesDataContext();

    SystemSettings setting = db.SystemSettings.Single(s => s.SettingName == settingName);

    if (Type.GetType(setting.SettingValueDatatype).Equals(settingValue.GetType()))

    setting.SettingValue = Convert.ToString(settingValue);

    else

    throw new ArgumentException("SettingValue type does not match SettingValueDatatype");

     

    db.SubmitChanges();

    }

     

     

     

     

    If I delete the FK link in the O/R Designer, the code above works fine. But with the link in, I get the following:

     

    System.InvalidCastException: Unable to cast object of type 'System.Int16' to type 'System.String'.
       at System.Data.Linq.IdentityManager.StandardIdentityManager.SingleKeyManager`2.TryCreateKeyFromValues(Object[] values, V& v)
       at System.Data.Linq.IdentityManager.StandardIdentityManager.IdentityCache`2.Find(Object[] keyValues)
       at System.Data.Linq.IdentityManager.StandardIdentityManager.Find(MetaType type, Object[] keyValues)
       at System.Data.Linq.CommonDataServices.GetCachedObject(MetaType type, Object[] keyValues)
       at System.Data.Linq.ChangeProcessor.GetOtherItem(MetaAssociation assoc, Object instance)
       at System.Data.Linq.ChangeProcessor.BuildEdgeMaps()
       at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
       at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
       at System.Data.Linq.DataContext.SubmitChanges()


     

    Obviously the columns in both tables are smallint, so I'm not entirely sure where LINQ is getting the string from. The only thing fishy that I can think of is that the PK on the SystemSettingscategoryList is the CategoryName (which indeed is a varchar/string).

     

    [edit] I can leave the FK link in and delete the PK on the SystemSettingsCategoryList table in the O/R designer and the code will succeed as well, so it's definately related to the poor database design of the table. But still... in it's simplist form, I'm just attempting to do a UPDATE SystemSettings SET SettingValue = <value> WHERE SettingName = <settingname>. LINQ seems to be adding a lot more overhead then is nessessary.

    Monday, May 26, 2008 8:24 PM
  • Unfortunately after spending several days on this issue I was never able to resolve the problem.  I finally had to implement a workaround so that I could move forward on the project.  For the one, problem relationship, I use a pair of stored procedures to insert/update records in the child table.  I've added the stored procs to the model so they are reflected as methods on the data context object.

     

    Although this approach works, it's not very satisfactory.  It would be be good to find out what the core problem is.  Since the error is being thrown in the Linq code, there isn't much we can do to troubleshoot this further.

     

    -Tim

    Tuesday, May 27, 2008 7:20 PM
  •  

    I actually ended up resolving the issue by modifying the table so that the PK was on the identity column and switching the name to a UNIQUE constraint (though the unique constraint would likely be irrelevant to the problem):

     

    CREATE TABLE [dbo].[SystemSettingsCategoryList](

    [Category_ID] [smallint] IDENTITY(0,1) NOT NULL,

    [CategoryName] [varchar](50) NOT NULL,

    [CategoryDescription] [varchar](200) NULL,

    CONSTRAINT [PK_SystemSettingsCategoryList] PRIMARY KEY CLUSTERED

    [Category_ID] ASC

    CONSTRAINT [UQ_SystemSettingsCategoryList_CategoryName] UNIQUE NONCLUSTERED

    [CategoryName] ASC

     

    Why LINQ did not like having the primary key on the name table eludes me, as well as to why it was trying to link the SystemSettingsCategoryList table when I was only modifying data in the SystemSettings table. Maybe LINQ will only work with foreign key links to primary keys? Unfortunately I don't have time to do more testing to see what the true problem is, but I'm sure someone with a deeper understanding of the LINQ system has already found this issue.

     

    Good luck with resolving your problem if you feel like tackling it.

     

     

    Tuesday, May 27, 2008 7:37 PM
  • I had a similar issue, described it and filed a bug at

    https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=351358
    Monday, June 16, 2008 8:31 PM
  • Hi, I just commented on the bug report you made.

    It has been marked as resolved so i made sure all my service packs were up to date etc but I am still able to reproduce it (when deleting records but not inserting them).

    Did you ever hear back from them, the only workaround i can think of is using stored procs for delete but I surely shouldnt have to.

    Thanks
    Dan
    Monday, January 05, 2009 6:55 PM
  • Hi all,

    I know this question is already solved, but even then i would like to share my case.

    My platform is ASP.NET 3.5, MSSQL.

    Uploaded on Azure, and Azure SQL

    I was getting the same error "Unable to cast object of type 'System.Int32' to type 'System.String' ". and moreover, i was not getting this error on my development server, but when i was uploading this on azure. I am using a lots of table, and many of them referencing each other through foreign keys. So, after trying a lot, i ended up deleting some of the foreign keys, and then things started working fine. I know this is not the right approach, but i was forced to do this. I moved all the FK logic to my code.

     

    Thanks. It might help someone.

    Tuesday, June 15, 2010 12:21 PM
  • hi...

    facing the same problem.

    try this workaround:

    reader["columnName"].ToString();

     

    Thanks,

    Friday, January 21, 2011 8:40 AM