0

SQL Server CE 3.5
SQL Server 2008

DataLenSumQuery:

SELECT SUM(DATALENGTH(Data)) FROM ...

On SQL Server CE the type of Data column is IMAGE

On SQL Server Standard the type of Data is varbinary(MAX), because Microsoft says IMAGE type for standard edition is deprecated and will be removed.

This code works fine with Standard edition:

using (var container = new DatabaseModelContainer())
{
var result = container.ExecuteStoreQuery<long?>(DataLenSumQuery).FirstOrDefault();
            long test= result.HasValue ? result.Value : 0L;
}

But throws exception on CE:

System.InvalidOperationException: The specified cast from a materialized 'System.Int32' type to a nullable 'System.Int64' type is not valid.

And vice versa this works on CE:

using (var container = new DatabaseModelContainer())
{
var result = container.ExecuteStoreQuery<int?>(DataLenSumQuery).FirstOrDefault();
            long test= result.HasValue ? result.Value : 0L;
}

But throws exception on Standard:

The specified cast from a materialized 'System.Int64' type to a nullable 'System.Int32' type is not valid.

Works fine if Data colum on both providers is IMAGE and <int?> for ExecuteStoreQuery is used.

Microsoft suggests to move to varbinary(MAX) on Standard, but Compact edition does not even support it.

ethorn10
  • 1,889
  • 1
  • 18
  • 29
Didar_Uranov
  • 1,230
  • 11
  • 26
  • If you want to use database specific queries then you need to send queries that work on the specific DB. The differences between Sql CE and Sql Server you hit are not related to entity framework. What you could do however is to either use a stored procedure that would hide the differences from your code or just cast the result in your query so that it always returns bigint and therefore EF will not throw when using long as the generic type. – Pawel Nov 27 '12 at 06:55

1 Answers1

0

SELECT CAST(SUM(DATALENGTH(Data)) AS BIGINT) FROM

container.ExecuteStoreQuery<long?>()
Didar_Uranov
  • 1,230
  • 11
  • 26