LINQ throwing invalid cast exception on a bigint

Question

I have a LINQ query that looks something like this:

var clintLst = (from clntDt in ent.ClientDatas
                where clntDt.CompanyName.Substring(0,searchWord.Length).Equals(searchWord, StringComparison.CurrentCultureIgnoreCase)
                orderby clntDt.CompanyName
                select new { ClientDataID = clntDt.ClientDataID,
                    CompanyName = clntDt.CompanyName, 
                    ContactName = (clntDt.ContactFirstName + " " + clntDt.ContactLastName),
                    CompanyLocation = clntDt.Location.LocationCity.CityName + ", " + clntDt.Location.LocationState.StateCode
                } ).Distinct().Take(10);

However, it is throwing the following exception:

The specified cast from a materialized 'System.Int32' type to the 'System.Int64' type is not valid. [..] Exception Details: System.InvalidOperationException: The specified cast from a materialized 'System.Int32' type to the 'System.Int64' type is not valid.

Source File: C:\TempPersonalCode\TransportTracking\TransportTracking\TransportTracking\Controllers\AJAXController.cs Line: 35

(Line 35 is the select clause)

I'm confused because if change:

select new { ClientDataID = clntDt.ClientDataID,
    CompanyName = clntDt.CompanyName, 

to

select new { ClientDataID = (Int32)clntDt.ClientDataID,
    CompanyName = clntDt.CompanyName, 

then it works fine. Isn't an anonymous object supposed to use reflection to determine it's type? if so, why is it deciding that it's an "Int32" instead of a long? Within the EDMX I have it as an Int64.

Answer 1

In my stored procedure, I was returning row number and rowcount, I casted it to int and it works properly now.

CAST (TotalCount AS INT)TotalCount

Answer 2

The phrase "materialized value" refers to the value that was retrieved from the data store.

What's probably happening is that the database has that column configured as an int, but in your EDMX file it's a long (or Int64).

The (Int32) cast you're putting on the front is (probably) being translated to the data store (in SQL Server, this means something like CAST([columnName] AS int), and consequently, the Entity Framework is now expecting to get an int instead of a long.

Without the cast, it's expecting a long but getting an int.

The solution is to either change the EDMX file or change the column, so that the data type in the EDMX file matches the data type in the database.

(jhott)