locked
Linq - Specified cast is not valid. RRS feed

  • Question

  • User1066913796 posted

     Hello!

    I am having problems running what seems like a simple bit of LINQ!

     This is the piece of code:

    public static void AddNewGuest(string GuestName, string AssociatedLogin, int GroupId)
        {
            DataClassesDataContext DB = new DataClassesDataContext();

            DB.Guests.InsertOnSubmit(new Guest
            {
                Name = GuestName,
                AssociatedUserName = AssociatedLogin,
                FamilyGroupId = GroupId
            }
                )

            ;
            DB.SubmitChanges();
        }

     

    and I am getting a "Specified cast is not valid." Error when it tries to  submit.

    My database is set up as follows

    GuestId uniqueidentifier NOT NULL
    Name varchar(50)  NOT NULL
    FamilyGroupId int  NOT NULL
    Accepted bit NULL Allowed
    Notes varchar(1000) Null Allowed
    AssociatedUserName nvarchar(256) Null Allowed

    (associatedUserName is linked to UserName in the membership tables..)

    The full error Message is as follows:

    [InvalidCastException: Specified cast is not valid.]
       System.Data.Linq.SingleKeyManager`2.TryCreateKeyFromValues(Object[] values, V& v) +81
       System.Data.Linq.IdentityCache`2.Find(Object[] keyValues) +34
       System.Data.Linq.StandardIdentityManager.Find(MetaType type, Object[] keyValues) +21
       System.Data.Linq.CommonDataServices.GetCachedObject(MetaType type, Object[] keyValues) +41
       System.Data.Linq.ChangeProcessor.GetOtherItem(MetaAssociation assoc, Object instance) +139
       System.Data.Linq.ChangeProcessor.BuildEdgeMaps() +248
       System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) +59
       System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) +1155
       System.Data.Linq.DataContext.SubmitChanges() +82
       GuestManager.AddNewGuest(String GuestName, String AssociatedLogin, Int32 GroupId) in c:\Documents and Settings\...\Desktop\Test Projects\...App_Code\GuestManager.cs:76
       Admin_AddGuests.submitGuestsButton_Click(Object sender, EventArgs e) in c:\Documents and Settings\...\Desktop\Test Projects\...\Admin\AddGuests.aspx.cs:64
       System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
       System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
       System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

    Any ideas what's wrong?

    Thanks Bex

    Thursday, February 21, 2008 9:28 AM

Answers

  • User897471407 posted

    Glad thats working.   

    Probably why it wasn't working was that you mentioned you had a FK releationship set up on the membership table.  With that being the case then you will have the constraint check upon inserts into that table for that column.  If the username doesn't exist in the membership table it wont let you insert.

     

    Please remember to mark an answer to help improve the searches.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 21, 2008 12:03 PM

All replies

  • User897471407 posted

    What it looks like its having an issue with is passing the Guest object into the DB context object.  Keep in mind that this is just brainstorming right now.  Have you tried setting your guest object properties outside of the InsertOnSubmit and then passing the object itself in.  I'll try to code it up for you, just keep in mind that I'm a VB guy, not a C# guy.

    public static void AddNewGuest(string GuestName, string AssociatedLogin, int GroupId)
        {
            DataClassesDataContext DB = new DataClassesDataContext();
            Guest guest = new Guest;
            guest.name = GuestName;
            guest.AssociatedUserName = AssociatedLogin;
            guest.FamilyGroupID = GroupID;

            DB.Guests.InsertOnSubmit(guest) ;
            DB.SubmitChanges();
        }

    Thursday, February 21, 2008 10:33 AM
  • User1066913796 posted

    Hi Dave!

     

    Thanks for your reply!

    I was thinking that maybe my syntax was wrong or something.. as my VS isn't giving me intellisense as I have Resharper installed and its not liking it.. but I do have another function that works..

     This is as follows:

     

     public static void saveMessage(Guid giftId, string name, string message, bool displayMessage)
        {
            DataClassesDataContext DB = new DataClassesDataContext();
    
            GiftMessage msg =
                new GiftMessage { GiftId = giftId, 
                    Name = name, 
                    Message = message, 
                    DisplayMessage = displayMessage, 
                    Date=DateTime.Now };
            DB.GiftMessages.InsertOnSubmit(msg);
            DB.SubmitChanges();
       
        }

     

    I have updated my "non working function" so its almost identicle.. so it is now as follows:

      public static void AddNewGuest(string GuestName, string AssociatedLogin, int GroupId)
        {
            DataClassesDataContext DB = new DataClassesDataContext();
    
    
            Guest guest =
               new Guest
               {
                   Name = GuestName,
                   AssociatedUserName = AssociatedLogin,
                   FamilyGroupId = GroupId
               };
            DB.Guests.InsertOnSubmit(guest);
            DB.SubmitChanges();
        }

     But it still doesn;t work!

    I am thinking it must be something really obvious, but I just can't see it!

    Thanks

    Bex

    Thursday, February 21, 2008 10:44 AM
  • User897471407 posted

    have you looked at data type conversions?  In the table def from your first post everything looked fine there.  But you may need to go into the designer.cs file for your DBML file since your intelisense is giving ya hell to see what is going on there.

    your syntax is fine.

    Thursday, February 21, 2008 11:15 AM
  • User1066913796 posted

    Hello!

     

    I am not sure what you mean by data type conversions, but I have managed to turn off my resharper intellisense thing, so I have got rid of my blue sqiggles.. and it builds fine..

    In my dbml class,

    AssociatedUserName is a string
    FamilyGroupId is an int
    and Name is also a string, which is what I am passing in!

    Do you think it may be anything to do with me linking the associatedUserName to the membership tables?
    as I have had to make the datatype nvarchar(256).

    This is unbelievably frustrating, as I really can't see whay its not working!

     Thanks

    Bex

     

    Thursday, February 21, 2008 11:25 AM
  • User897471407 posted

    You got what I meant with the data type conversions.   

    There shouldn't be any problems there.

    However; what you can try to see if it is causing problems is to remove your FK and change the Datatype of the AssociatedUserName to varchar and see if the insert works then. 

    Thursday, February 21, 2008 11:38 AM
  • User1066913796 posted

    Now why didn't I think of that!

    Ok so getting rid of the relationship works.. hmm..
    I'd really like to know why, but as this is a test site, it doesn't really matter... but if I come across this in the future it may cause me problems!

    But for now.. THANK YOU VERY MUCH! [:D]

    Thursday, February 21, 2008 11:48 AM
  • User897471407 posted

    Glad thats working.   

    Probably why it wasn't working was that you mentioned you had a FK releationship set up on the membership table.  With that being the case then you will have the constraint check upon inserts into that table for that column.  If the username doesn't exist in the membership table it wont let you insert.

     

    Please remember to mark an answer to help improve the searches.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 21, 2008 12:03 PM
  • User1066913796 posted

     

    The username definitly existed in the membership table as I was using Membership.getallusers (or what ever the syntax is) to get the users to choose from..!
    Will keep my eye on it and see if I  come across it again!

     

    Thanks

    Bex

    Thursday, February 21, 2008 12:09 PM
  • User-49128286 posted

    one instance ive seen where this error can occur (and just did for me!) is if you add an association between tables which doesnt reference the key of the parent table... i have one ref'ing another column which had unique values etc but was not designated the key, so it would fail on inserts.

     

    Wednesday, April 2, 2008 8:43 PM
  • User-1986064961 posted

    one instance ive seen where this error can occur (and just did for me!) is if you add an association between tables which doesnt reference the key of the parent table... i have one ref'ing another column which had unique values etc but was not designated the key, so it would fail on inserts.

     

    I had this same problem, where a child table referenced a parent table using a foreign key that didn't key into the parent table based on it's primary key, but instead using a unique constraint on another column. The problem seemed to permeate throughout other tables as well, which was a problem.

    Take for example, two tables, Parent and Child, where they both have primary key columns named Id, and they also have columns named Code, which is a column that is not a key but has a unique constraint on it in the Parent table. Now the trick for me here is that Child has a foreign key to Parent based on Code. And that's how my LINQ objects are all linked together.

    The solution for me was to always first retrieve the Parent LINQ object and assign it to the Child when creating a new child record, like this:

    1    Dim ParentQuery = From EachParent In data.Parents _
    2                      Where EachParent.Code = TheCode
    3    Dim ThisParent = ParentQuery.Single()

    Then, when creating a new child, to use that LINQ object and assign it in the relationship:

    1    Dim NewChild as Child = new Child()
    2    NewChild.Parent = ThisParent
    3    data.Childs.InsertOnSubmit(NewChild)
    4    data.SubmitChanges()
    
      

    If, however, I had just set the Child's Code property (the foreign key between the two tables) to associate the two in a relationship, that threw the "Specified cast is not valid" exception.

    I also found that in other associations, using the LINQ object wasn't enough, *if* it hadn't yet properly been identified by its key (and I think this is only the case for an identity field in the database, where LINQ doesn't necissarily know what the ID of a newly-created row in the database is, and then tries to then go and make use of that row in another relationship). For example, if you're creating a many-to-many relationship, you need to explicitly set the id (primary key) Consider another part of my schema that had a many-to-many association, this code did not work (threw the Specified cast is not valid exception)... Remember Child's Id is an identity.

    1    Dim ThisM2M as new ManyToMany
    2    ThisM2M.Child = NewChild
    3    ThisM2M.SomeOtherTable = MySomeOtherTable
    

    BUT - this code worked...

    1    Dim ThisM2M as new ManyToMany
    2    ThisM2M.ChildId = NewChild.Id
    3    ThisM2M.SomeOtherTableId = MySomeOtherTable.Id
    

    Maybe LINQ then knows to go back to the database to get that Id, knowing that it doesn't know it yet? This is all just guesswork at this point, but hope this helps someone.

    Sunday, November 23, 2008 5:33 PM
  • User-1986064961 posted

    And so, my solution to the non-working problem from BexMed above would be (with some guesswork around the actual object structure)...

    The intent is to:

    1. Select the LINQ object uniquely (using the unique value against the parent table first using your own explicit query)
    2. Use that newly found LINQ object in the association (not the foreign key value itself)
      public static void AddNewGuest(string GuestName, string AssociatedLogin, int GroupId)
        {
            DataClassesDataContext DB = new DataClassesDataContext();
    
            // First, select the entire Login from the db, uniquely. (Step 1)
            var loginQuery = from l in DB.Logins
                             where l.UserName = AssociatedLogin;
            Login login = loginQuery.Single();
    
            // Now, create guest using the new unique object. (Step 2, guessing on the name "Login")
            Guest guest =
               new Guest
               {
                   Name = GuestName,
                   Login = login,
                   FamilyGroupId = GroupId
               };
            DB.Guests.InsertOnSubmit(guest);
            DB.SubmitChanges();
        }
     
    Sunday, November 23, 2008 6:03 PM
  • User1558069517 posted

    Hi All,

    I’m in a big problem.

    I'm using WCF service and LINQ for our Silverlight 2 application.

    In my application I'm fetching records from the SQL database using the stored procedure.

    Below is the interface of my procedure

    List<usp_getreportdataresult> GetReportData(int JobId, DateTime StartDate, DateTime EndDate) </usp_getreportdataresult>

    <usp_getreportdataresult>For some JobId I’m able to get the data successfully, but for most of the JobIds I’m getting 'Specified cast is not valid' error exception. </usp_getreportdataresult>

    <usp_getreportdataresult>Below is the Web Method to get the result using LINQ statement: </usp_getreportdataresult>

    <usp_getreportdataresult>public List<usp_getreportdataresult> GetReportData(int JobId, DateTime StartDate, DateTime EndDate) </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>{ </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>// Call to get record from the Database </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>WMDBDataContext db = new WMDBDataContext(); </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>var matchingRecords = from Report in db.Usp_GetReportData(JobId, StartDate, EndDate) </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>select Report; </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>return matchingRecords.ToList(); </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>}</usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult> Below are the fields and datatypes generated by the LINQ Designer class for Usp_GetReportData. </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>private string _Status; </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>private string _Protocol; </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>private string _Country; </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>private string _CountryID; </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>private string _LanguageName; </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>private string _Subtitles; </usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult>private System.Nullable<short> _VideoID; </short></usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult><short>private System.Nullable<system.datetime> _LastSeenDateET; </system.datetime></short></usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult><short><system.datetime>private System.Nullable<int> _DownloadCounts; </int></system.datetime></short></usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult><short><system.datetime><int>private char _AdjustedInd; </int></system.datetime></short></usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult><short><system.datetime><int>private System.Nullable<long> _FileSize; </long></int></system.datetime></short></usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult><short><system.datetime><int><long>private System.Nullable<byte> _HourNumber; </byte></long></int></system.datetime></short></usp_getreportdataresult></usp_getreportdataresult>

    <usp_getreportdataresult><usp_getreportdataresult><short><system.datetime><int><long><byte>private string _IPAddress;</byte></long></int></system.datetime></short></usp_getreportdataresult></usp_getreportdataresult>

     

    <usp_getreportdataresult><usp_getreportdataresult><short><system.datetime><int><long><byte>Thanks, Mahesh </byte></long></int></system.datetime></short></usp_getreportdataresult></usp_getreportdataresult>

    Monday, February 2, 2009 1:04 AM
  • User-1986064961 posted

    Hi Mahesh,

    Is the exception happening on the line: return matchingRecords.ToList(); ?

    It would help to see an example of the data, do you have sample data of when it does and doesn't work?

    Lastly, can you give an idea of the structure of your database and/or the code in the stored procedure? how is JobId defined? Is it a unique key? Foreign key? I suspect that the issue has something to do with the ID's that you may be using to join to other tables and the definitions of those foreign keys.

    Monday, February 2, 2009 6:36 PM
  • User1558069517 posted

    Hi,

    Thanks for you reply.

    And yes error is coming on retrun matchingRecords.ToList();

    JobId is a unique key of Title Table. But if this is the problem then why error is coming for few particular JobId only not for all.

    We used JobId and dates fields as parameter to get the records.

    where CN.JobID = @iJobID

    and d.FullDate >= @iStartDate

    and d.FullDate <= @iEndDate

     

    And type of data are:

     

    Status Protocol Country CountryID LanguageName Subtitles VideoID LastSeenDateET DownloadCounts AdjustedInd FileSize HourNumber IPAddress
    NULL eDonkey Unknown -1            NULL                   None NULL 2008-12-20 00:00:00.000 7 N 710543935 0 93.1.68.200   
    NULL eDonkey Unknown -1           NULL                   None NULL 2008-12-21 00:00:00.000 2 N 710543935 0 90.9.154.141  
    NULL eDonkey Satellite Provider A2 NULL                None NULL 2008-12-20 00:00:00.000 0 N 710543935 0 87.68.140.197 

     

    And below are the fields and datatypes generated by the LINQ Designer class for Usp_GetReportData.

    private string _Status;<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>private string _Protocol;<o:p></o:p>private string _Country;<o:p></o:p>private string _CountryID;<o:p></o:p>private string _LanguageName;<o:p></o:p>private string _Subtitles;<o:p></o:p>private System.Nullable<short> _VideoID;<o:p></o:p>private System.Nullable<System.DateTime> _LastSeenDateET;<o:p></o:p>private System.Nullable<int> _DownloadCounts;<o:p></o:p>private char _AdjustedInd;<o:p></o:p>private System.Nullable<long> _FileSize;<o:p></o:p>private System.Nullable<byte> _HourNumber;<o:p></o:p>private string _IPAddress;<o:p></o:p>

     

     

    Thanks

    Mahesh

     

    Monday, February 2, 2009 10:48 PM
  • User334174592 posted

    one instance ive seen where this error can occur (and just did for me!) is if you add an association between tables which doesnt reference the key of the parent table... i have one ref'ing another column which had unique values etc but was not designated the key, so it would fail on inserts.

    This was exactly what my problem was.

    Friday, March 13, 2009 5:14 PM
  • User400605540 posted

    Solution:

    I had the same problem. Scenario:

    Two tables one of which has a unique key that's a varchar is linked with the other table.

    After removing the foreign key, everything works fine.

    I think my foreign key relationships need to be on an integer, a long, or a uniqueidentifier for linq not to crash. 

    I just removed the relationship for now, all works fine.

    Hope this was helpful to anyone =)


    Sunday, October 4, 2009 1:49 AM
  • User-1696401752 posted

    Same issue here. Had a table with a foreign key that referenced a column which was unique but not set to a primary key. Inserting and deleting using actual MSSQL statements worked fine but LinQ continually caused the invalid cast error. Removing the foreign key worked.


    This sounds like a limitation in LinQ. Even if the practice of pointing a foreign key to a non primary key column is poor design, best practice in DB design is a suggestion at best. Some situations warrant taking advantage of a trick that is otherwise, poor design. LinQ also shouldn't be the one enforcing DB design standards. Anyway I'm rambling. Carry on!

    Monday, June 28, 2010 2:21 PM
  • User864846475 posted

    u can try to insert, delete with LINQ via Stored Procedure Smile

    Wednesday, July 27, 2011 4:59 PM