Solved

Invalid cast exception with LINQ to SQL for a column

Posted on 2008-10-24
12
3,273 Views
Last Modified: 2013-12-17
Im getting an invalid cast exception.

As I step through the code, the last function that works is this:


namespace WallHomes.Enterprise.Model
{
      using System.Data.Linq;
      . . .       
      
      [System.Data.Linq.Mapping.DatabaseAttribute(Name="Operation")]
      public partial class OperationDatabaseDataContext : System.Data.Linq.DataContext
      {
            . . .

            public BudgetItem()
            {
                  this._MasterItems = new EntitySet<MasterItem>(new Action<MasterItem>(this.attach_MasterItems), new Action<MasterItem>(this.detach_MasterItems));
                  this._Budget = default(EntityRef<Budget>);
                  this._AddressBook = default(EntityRef<AddressBook>);
                  OnCreated();
            }

            . . .
      }
}

The code above was autogenerated by .NET
Right after the code above, the exception occurs.

The function that runs just before the above code is as follows:

namespace WallHomes.Enterprise.Model
{
    public partial class OperationDatabaseDataContext : IBudgetRepository
    {
      . . .

public IList<BudgetItem> GetBudgetItems(BudgetItemFilter budgetItemFilter)
        {
            setBudgetItemLoadOptions();

            var query = from item in BudgetItems
                        select item;

            if (budgetItemFilter.BudgetId > 0)
            {
                query = from item in query
                        where item.DetailHeaderID == budgetItemFilter.BudgetId
                        select item;
            }

            if (budgetItemFilter.StatusCode.Length > 0)
            {
                query = from item in query
                        where item.DetailStatus == Convert.ToChar(budgetItemFilter.StatusCode)
                        select item;
            }

            if (budgetItemFilter.BudgetTypeId > 0)
            {
                query = from item in query
                        where item.Budget.TypeID == budgetItemFilter.BudgetTypeId
                        select item;
            }

            if (budgetItemFilter.BudgetTypes.Count > 0)
            {
                query = from item in query
                        where budgetItemFilter.BudgetTypes.ToArray().Contains(item.Budget.TypeID)
                        select item;
            }

            if (budgetItemFilter.CostType.Trim().Length > 0)
            {
                query = from item in query
                        where item.CostType.Trim() == budgetItemFilter.CostType
                        select item;
            }

            if (budgetItemFilter.RegionCodes.Count > 0)
            {
                query = from item in query
                        where budgetItemFilter.RegionCodes.ToArray().Contains("0" + item.Budget.LocationCode.Substring(0, 4))
                        select item;
            }

            if (budgetItemFilter.CommunityId.Trim().Length > 0)
            {
                query = from item in query
                        where item.Budget.LocationCode.Substring(0, 8) + "0000" == budgetItemFilter.CommunityId
                        select item;
            }

            if (budgetItemFilter.PlanNumber.Trim().Length > 0)
            {
                query = from item in query
                        where item.Budget.PlanNumber == budgetItemFilter.PlanNumber
                        select item;
            }

            if (budgetItemFilter.Elevation.Trim().Length > 0)
            {
                query = from item in query
                        where item.Budget.Elevation == budgetItemFilter.Elevation
                        select item;
            }

            if (budgetItemFilter.CostCode.Trim().Length > 0)
            {
                query = from item in query
                        where item.CostCode == budgetItemFilter.CostCode
                        select item;
            }

            if (budgetItemFilter.ItemNumber.Trim().Length > 0)
            {
                query = from item in query
                        where item.ItemNumber == budgetItemFilter.ItemNumber
                        select item;
            }

            return query.ToList();
        }

      . . .
}

The query.ToList() creates the following SQL :

SELECT [t0].[DetailID], [t0].[DetailHeaderID], [t0].[VendorID], [t0].[ItemNumber], [t0].[CostCode], [t0].[CostType], [t0].[Amount], [t0].[OptionType], [t0].[DetailStatus], [t0].[AddedBy], [t0].[AddedDate], [t0].[ModifiedBy], [t0].[ModifiedDate], [t1].[ID], [t1].[TypeID], [t1].[ParentID], [t1].[LocationCode], [t1].[Description], [t1].[PlanNumber], [t1].[Elevation], [t1].[HeaderStatus], [t1].[AddedBy] AS [AddedBy2], [t1].[AddedDate] AS [AddedDate2], [t1].[ModifiedBy] AS [ModifiedBy2], [t1].[ModifiedDate] AS [ModifiedDate2], [t1].[UploadedBy], [t1].[UploadedDate], [t2].[TypeID] AS [Id2], [t2].[TypeDescription] AS [Description2], [t2].[TypeStatus] AS [Status], [t3].[ID] AS [ID3], [t3].[Region], [t3].[LocationDescription]
FROM [dbo].[eBudgetDetail] AS [t0]
INNER JOIN ([dbo].[eBudgetHeader] AS [t1]
    INNER JOIN [dbo].[eBudgetType] AS [t2] ON [t2].[TypeID] = [t1].[TypeID]
    INNER JOIN [dbo].[vwBudgetLocation] AS [t3] ON [t3].[ID] = [t1].[ID]) ON [t1].[ID] = [t0].[DetailHeaderID]
WHERE [t0].[DetailID] IN (@p0)
ORDER BY [t0].[DetailID]


What happens is that we have a vendorId column in an eBudgetDetail table.

When the vendorId is set to null, then the SQL above works fine. When the vendorId is set to anything, then we get the invalid cast exception.

The vendorId column in the SQL Server table was set as nvarchar(50). I changed it to an int, but received the same error.

Specifically, the exception message in the catch statement states, Specified Cast is not valid
System.Collections.ListDictionaryInternal

The stack trace is as follows:
at System.Data.SqlClient.SqlBuffer.get_Double()
   at System.Data.SqlClient.SqlDataReader.GetDouble(Int32 i)
   at Read_BudgetItem(ObjectMaterializer`1 )
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at WallHomes.Enterprise.Model.OperationDatabaseDataContext.GetBudgetItemsById(IEnumerable`1 budgetItemIdList) in C:\foundation01\Enterprise\Main\WallHomes\EBudget\Source\WallHomes.Enterprise.Model\Data\InterfaceImplementations\Repository\OperationDatabaseDataContext.IBudgetRepository.cs:line 352
   at WallHomes.Enterprise.Web.EBudget.Application.Commands.SaveBudgetItemsCommand.Execute(NameValueCollection nameValueCollection, ApplicationUser currentUser) in C:\Documents and Settings\babak.sekandari.WALLHOMES\My Documents\Visual Studio 2008\Projects\EBudget\WallHomes.Enterprise.Web.EBudget.Application\Commands\SaveBudgetItemsCommand.cs:line 31
   at EBudgetService.ProcessRequest(HttpContext httpContext) in c:\Documents and Settings\babak.sekandari.WALLHOMES\My Documents\Visual Studio 2008\Projects\EBudget\WallHomes.Enterprise.Web.EBudget\App_Code\EBudgetService.cs:line 15


Invalid-Cast-Exception-on-Vendor.doc
0
Comment
Question by:abrimberry
  • 7
  • 5
12 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 22800372
Whenever you make changes to the Database you need to refresh the DataContect that you created from the DB to reflect the new changes.
0
 

Author Comment

by:abrimberry
ID: 22800437

Thank you Fernando Soto,

Is refreshing the DataContext done in the .dbml file?
I'm new to LINQ and the ORM gui.
0
 

Author Comment

by:abrimberry
ID: 22800675
I just did a little research on the DataContext.Refresh()
Is that what you were referring to?
I wasn't sure if you meant the DataContext.Refresh method, or if you meant refreshing the .dbml page.

Is it your theory that LINQ to SQL is keeping the result set object from the database table for an entity in cache?

I am running in debug mode. After I changed the column in the database, I ran the debug in VS 2008 again.  I infer that would clear the cache since debug will rebuild the solution each time, right?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:abrimberry
ID: 22800758

I  just tried to put a DataContext.Refresh() method into the code.

Here is what I infer is the code that creates a DataContext object in my code:
IBudgetRepository budgetRepository = new OperationDatabaseDataContext(ConfigurationManager.ConnectionStrings["OperationDatabase"].ConnectionString);

Then my DataContext object is budgetRepository, right?

So then I tried to make a budgetRepository.Refresh();
I also tried to make a budgetRepository.ObjectTrackingEnabled = False;

However, intellisense showed neither the Refresh() or the ObjectTrackingEnabled property.

0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 22801596
Hi abrimberry;

Sorry, what I mean is that any time you change the structure of the Database such as adding a new column or change a data type of a table element as you stated in your question that you have created a DataContext/dbml file of you need to delete that table from the dbml/ORM and re-add it to the dbml. Changes made to the database structure are not automatically reflected in the ORM file in your project you need to delete and then re-add it to recreate the correct mapping to the database.

Fernando
0
 

Author Comment

by:abrimberry
ID: 22804427
Ok, I'm going to give that a shot and see how it works.
The problem is that the .dbml file has many tables in it. It has something like 19 tables. I guess I could draw the relations between the tables and then delete it.
Or maybe I can copy and paste it all into another dbml page, and then back into this one.
Let me know if there is an easy way to do it without having to re-add everything one by one.
In the meantime, I will give that a try and let you know how it went.
I appreciate your assistance greatly.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 22805003
Hi abrimberry;

To your statement, "The problem is that the .dbml file has many tables in it.", If the table in the database have the relationships already set up then dragging the table from the Server Explorer will automatically set up the relationships in the dbml.

To your statement, "It has something like 19 tables.", You only need to delete and add back only those table that have changed, if a table has no changes you do not need to delete it from the dbml.

To your statement, "Or maybe I can copy and paste it all into another dbml page,", this will most likely not work because copy and past will not update it from the database but copy and past what is already their.

To that part of your statement, "It has something like 19 tables.", please have a look at tip number 2 in the following link.

10 Tips to Improve your LINQ to SQL Application Performance
http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html

Fernando
0
 

Author Comment

by:abrimberry
ID: 22805260

"You only need to delete and add back only those table that have changed, "

That helps tremendously.
I was about to recreate all 19 tables.
Now, I'll just replace that one table.
I'll try that and let you know how it works.
Thanks very much.
0
 

Author Comment

by:abrimberry
ID: 22807916

Hi FernandoSoto.
I tried it, and that does seem to be working.
On Monday, I'm going to have our DBA help me test it some more, and if it is still working, then I'll accept your solution and close the question.
I thank you very much for your assistance. You have been most helpful.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 22807975
Very good. Have a great day.
0
 

Author Comment

by:abrimberry
ID: 22815970
We tested it and it worked successfully.  I will accept the solution.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 22818731
Not a problem, glad I was able to help.  ;=)
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Server Error 11 58
Advice on Xojo as a development tool over VB. 4 55
Sending receiving text messages in vb.net 15 37
Release Dynamically Allocated Memory in C# 3 26
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question