LINQ throwing invalid cast exception on a bigint

linqlinq-to-entities

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,

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.

Best Answer

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)

Related Solutions

C# – LINQ query on a DataTable

You can't query against the DataTable's Rows collection, since DataRowCollection doesn't implement IEnumerable<T>. You need to use the AsEnumerable() extension for DataTable. Like so:

var results = from myRow in myDataTable.AsEnumerable()
where myRow.Field<int>("RowNo") == 1
select myRow;

And as @Keith says, you'll need to add a reference to System.Data.DataSetExtensions

AsEnumerable() returns IEnumerable<DataRow>. If you need to convert IEnumerable<DataRow> to a DataTable, use the CopyToDataTable() extension.

Below is query with Lambda Expression,

var result = myDataTable
    .AsEnumerable()
    .Where(myRow => myRow.Field<int>("RowNo") == 1);

LINQ equivalent of foreach for IEnumerable

There is no ForEach extension for IEnumerable; only for List<T>. So you could do

items.ToList().ForEach(i => i.DoStuff());

Alternatively, write your own ForEach extension method:

public static void ForEach<T>(this IEnumerable<T> enumeration, Action<T> action)
{
    foreach(T item in enumeration)
    {
        action(item);
    }
}

Best Answer

Related Solutions

C# – LINQ query on a DataTable

LINQ equivalent of foreach for IEnumerable

Related Topic