Example in LINQPad against adventureworks selfservicing

SalesOrderDetail.Sum (sod => sod.SalesOrderDetailID)

and

var orderDetails = new SalesOrderDetailCollection();
var orderPrice = orderDetails.GetScalar(SalesOrderDetailFieldIndex.SalesOrderID, AggregateFunction.Sum);

both produce Arithmetic overflow error as sum goes above max int

ORMQueryExecutionException
An exception was caught during the execution of a retrieval query: Arithmetic overflow error converting expression to data type int.. Check InnerExcep... 

Message
An exception was caught during the execution of a retrieval query: Arithmetic overflow error converting expression to data type int.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. 

InnerException

SqlException 
Arithmetic overflow error converting expression to data type int. 

TargetSite

RuntimeMethodInfo
RetrievalQuery.ExecuteScalar () 

StackTrace
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.ExecuteScalar()
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteScalarQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction)
   at AW.Data.DaoClasses.CommonDaoBase.ExecuteScalarQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetScalar(IEntityFields fields, ITransaction containingTransaction, IPredicate filter, IRelationCollection relations, IGroupByCollection groupByClause, Boolean allowDuplicates)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetScalar(IEntityFields fields, ITransaction containingTransaction, IPredicate filter, IRelationCollection relations, IGroupByCollection groupByClause)
   at AW.Data.CollectionClasses.SalesOrderDetailCollection.GetScalar(SalesOrderDetailFieldIndex fieldIndex, IExpression expressionToExecute, AggregateFunction aggregateToApply, IPredicate filter, IRelationCollection relations, IGroupByCollection groupByClause)
   at AW.Data.CollectionClasses.SalesOrderDetailCollection.GetScalar(SalesOrderDetailFieldIndex fieldIndex, AggregateFunction aggregateToApply) 

Source
SD.LLBLGen.Pro.ORMSupportClasses 

HResult
-2146232832 

RuntimeVersion
4.2.0.0 

RuntimeBuild
10232014 

QueryExecuted

  Query: SELECT TOP(@p2) SUM([AdventureWorks].[Sales].[SalesOrderDetail].[SalesOrderID]) AS [SalesOrderID] FROM [AdventureWorks].[Sales].[SalesOrderDetail]  
  Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

None       Int64 0 @p2 0 0 BigInt 1  1 Input False 0 0   False Current 

ErrorCode -2146232060 
Message Arithmetic overflow error converting expression to data type int. 
HelpLink null 
ErrorNumber 8115 

.Net SqlClient Data Provider 8115 2 16 (localdb)\MSSQLLocalDB Arithmetic overflow error converting expression to data type int.   1 

SD.LLBLGen.Pro.ORMSupportClasses.dll V4.2.14.1023

What's the SQL DataType of the SalesOrderID?

Walaa wrote:

What's the SQL DataType of the SalesOrderID?

Int(10)

IMHO, this is unavoidable, even if you do it directly on an SQL editor (ref...). You should use other technique, like casting the field to a bigger data type. Example:

var orderPrice = new SalesOrderDetailCollection().GetScalar(
    SalesOrderDetailFieldIndex.SalesOrderId,
    new DbFunctionCall("CAST({0} AS BIGINT)", new object[] {SalesOrderDetailFields.SalesOrderId} ), 
    AggregateFunction.Sum);

daelmo wrote:

IMHO, this is unavoidable, even if you do it directly on an SQL editor (ref...). You should use other technique, like casting the field to a bigger data type. Example:

var orderPrice = new SalesOrderDetailCollection().GetScalar(
    SalesOrderDetailFieldIndex.SalesOrderId,
    new DbFunctionCall("CAST({0} AS BIGINT)", new object[] {SalesOrderDetailFields.SalesOrderId} ), 
    AggregateFunction.Sum);

Thanks, that worked. And what about for the Linq version?

(not tested) SalesOrderDetail.Sum (sod => Convert.ToInt64(sod.SalesOrderDetailID))

should wrap the field in a cast to the 64bit type of the db.

Otis wrote:

SalesOrderDetail.Sum (sod => Convert.ToInt64(sod.SalesOrderDetailID))

Yup that worked, produced:

SELECT TOP(@p2) SUM([LPA_L1].[LPAV_]) AS [LPAV_]
FROM   (SELECT CONVERT(BIGINT, [LPLA_1].[SalesOrderDetailID]) AS [LPAV_]
        FROM   [AdventureWorks].[Sales].[SalesOrderDetail] [LPLA_1]) [LPA_L1] 

Many thanks.

BIGINT is SQL server specific, is there a way to call GetScalar so it will work with any DB target like the Linq version does?

TomDog wrote:

BIGINT is SQL server specific, is there a way to call GetScalar so it will work with any DB target like the Linq version does?

No built-in support for that AFAIK. You must create your own abstraction for that.

Not needed, as Convert.ToInt64() has its own mapping for every DB supported, so it has a different mapping on Oracle than e.g. on SQL Server.

Otis wrote:

Not needed, as Convert.ToInt64() has its own mapping for every DB supported, so it has a different mapping on Oracle than e.g. on SQL Server.

How can I use Convert.ToInt64() with GetScalar?

That's for Linq. use the Linq alternative, why fall back to the GetScalar() method? Otherwise use the DBFunctionCall on the field, but that's DB specific.

Walaa wrote:

use the Linq alternative, why fall back to the GetScalar() method?

I'm doing some dynamic query generation which isn't so easy with Linq. I have a number of entities with the same field name that I want to sum. FTR this is what I'm using:

    public static long GetSum(IDataAccessAdapter dataAccessAdapter, IEntityField2 field, IPredicate filter = null)
    {
      var expressionToExecute = new DbFunctionCall(string.Format("CAST({{0}} AS {0})", EntityPersistence.DBIsOracle(dataAccessAdapter) ? "NUMBER(18,0)" : "BIGINT"), new object[] { field.Clone() });
      var scalar = dataAccessAdapter.GetScalar(field, expressionToExecute, AggregateFunction.Sum, filter);
      return Convert.IsDBNull(scalar) ? 0 : Convert.ToInt64(scalar);
    }

The routine that calls this is passed an EntityType enum and a field name and generates the field of that.

A little surprised that the Low-level API doesn't provide an abstraction for this while Linq does.

TomDog wrote:

Walaa wrote:

use the Linq alternative, why fall back to the GetScalar() method?

I'm doing some dynamic query generation which isn't so easy with Linq. I have a number of entities with the same field name that I want to sum. FTR this is what I'm using:

    public static long GetSum(IDataAccessAdapter dataAccessAdapter, IEntityField2 field, IPredicate filter = null)
    {
      var expressionToExecute = new DbFunctionCall(string.Format("CAST({{0}} AS {0})", EntityPersistence.DBIsOracle(dataAccessAdapter) ? "NUMBER(18,0)" : "BIGINT"), new object[] { field.Clone() });
      var scalar = dataAccessAdapter.GetScalar(field, expressionToExecute, AggregateFunction.Sum, filter);
      return Convert.IsDBNull(scalar) ? 0 : Convert.ToInt64(scalar);
    }

The routine that calls this is passed an EntityType enum and a field name and generates the field of that.

A little surprised that the Low-level API doesn't provide an abstraction for this while Linq does.

The low level api works with strict objects, converts have been done above that, so linq and queryspec translate to the low-level API, and create DBFunctionCall instances from mappings in the DQEs.

This means that what you want can be accomplished with queryspec as well:

var qf = new QueryFactory();
var sq = qf.Create().Select(ConvertFunctions.ToInt64(SalesOrderDetailFields.SalesOrderId).Sum());
var v = new DataAccessAdapter().FetchScalar<long?>(sq);

The advantage of queryspec is that it does support lowlevel predicates if you want to and thus offers more flexibility with respect to dynamic query construction than linq does. So in your case your method becomes:

public static long GetSum(IDataAccessAdapter dataAccessAdapter, IEntityField2 field, IPredicate filter = null)
{
    var qf = new QueryFactory();
    var sq = qf.Create().Select(ConvertFunctions.ToInt64(field).Sum());
    if(filter!=null)
    {
        sq.Where(filter);
    }
    return dataAccessAdapter().FetchScalar<long?>(sq) ?? 0;
 }

Otis wrote:

public static long GetSum(IDataAccessAdapter dataAccessAdapter, IEntityField2 field, IPredicate filter = null)
{
    var qf = new QueryFactory();
    var sq = qf.Create().Select(ConvertFunctions.ToInt64(field).Sum());
    if(filter!=null)
    {
        sq.Where(filter);
    }
    return dataAccessAdapter().FetchScalar<long?>(sq) ?? 0;
 }

Nice! using that now - thanks.