What causes "Specified cast is not valid" in Linq to SQL?

Question

I'm honestly baffled by this! I have very similar code on another table that works just fine.

I'm inserting a row in a table using Linq to Sql and it will only work when either

1. A part of the trigger is disabled.
2. I set the total cost column (SQL money, C# decimal) to 0.

The trigger sums up the total cost of a set of rows, then updates the total cost on a different table.

Trigger Code

    ;WITH CalculatedTotals AS
    (
        SELECT A.ID, SUM(B.TotalCost) AS TotalCost
        FROM dbo.TableA A
        INNER JOIN dbo.TableB B ON B.GroupID = A.ID
        GROUP BY A.ID
    )

    UPDATE A
    SET A.TotalCost = CT.TotalCost
    FROM dbo.TableA A
    INNER JOIN CalculatedTotals CT ON A.ID = CT.ID

In this code, the CalculatedTotals.TotalCost is a nullable int, while the TableA.TotalCost is non-nullable money. I've tried using IsNull, Convert, etc. I always get the same exception when using Linq to Sql. However, inserting the row directly using SQL works fine. And again, I have similar code with another table that works. I'm using .NET 4.

What's wrong?

Update

InvalidCastException stack trace:

   at System.Data.SqlClient.SqlBuffer.get_Int64()
   at System.Data.SqlClient.SqlDataReader.GetInt64(Int32 i)
   at Read_Object(ObjectMaterializer`1 )
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicInsert(TrackedObject item)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.Insert(TrackedObject item)
   at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
   at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
   at System.Data.Linq.DataContext.SubmitChanges()
   at Repository`2.Save() in C:\...\Repository.cs:line 104
   at TableBManager.CreateTableB(TableB tableB) in C:\...\TableBManager.cs:line 45
   at MyService.SetTableB(TableB tableB) in C:\...\MyService.cs:line 371
   at SyncInvokeSetExtraFormItem(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)

It happens when I try I call SubmitChanges on my data context.

** Update **

This is the generic repo class I use.

        /// <summary>
        /// Creates the item.
        /// </summary>
        /// <param name="entity">The entity.</param>
        public void CreateItem(T entity)
        {
            _db.GetTable<T>().InsertOnSubmit(entity);
        }

        /// <summary>
        /// Saves this instance.
        /// </summary>
        public void Save()
        {
            _db.SubmitChanges();
        }

Update

Ok so my other code isn't working anymore either. The only thing I've changed is the trigger.

Update

Found this SO question with useful links to 2 articles: Linq-To-SQL Invalid Cast on insert, not on update

It may be a bug.

Answer 1

There was a select statement in my trigger from when I was testing it. So the trigger was returning an entity set, but the Linq to Sql create was expecting a 64 bit int. I removed the select statement and the insert is working again.