Solved

Invalid cast exception with LINQ to SQL for a column

Posted on 2008-10-24
12
3,251 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 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility

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
Comment Utility
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
 

Author Comment

by:abrimberry
Comment Utility

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 62

Accepted Solution

by:
Fernando Soto earned 500 total points
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility

"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
Comment Utility

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 62

Expert Comment

by:Fernando Soto
Comment Utility
Very good. Have a great day.
0
 

Author Comment

by:abrimberry
Comment Utility
We tested it and it worked successfully.  I will accept the solution.
0
 
LVL 62

Expert Comment

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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now