Infor CRM Summing a Property of an Entity Collection Using Linq

With the collections available in the entity model it makes it very easy to navigate between entities to get related objects. For instance it is very easy to access all opportunities tied to an account via the Opportunities collection.

By looping through the collection you can do things like sum up the sales potential of all opportunities:

var account = this.BindingSource.Current as Sage.Entity.Interfaces.IAccount;
double amt = 0;
foreach(var opp in account.Opportunities)
{
    amt += opp.SalesPotential;
}

Console.WriteLine(amt.ToString());

You could also do this via a direct call to SQL using OLEDB calls (but why would you?):

var account = this.BindingSource.Current as Sage.Entity.Interfaces.IAccount;
double amt = 0;
var datasvc = Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Data.IDataService>();
using (var conn = new System.Data.OleDb.OleDbConnection(datasvc.GetConnectionString()))
{
    conn.Open();
    string sql = "select sum(salespotential) Amt from opportunity where ACCOUNTID = '{0}'";
    using (var cmd = new System.Data.OleDb.OleDbCommand(string.Format(sql, account.Id.ToString()), conn))
    {
        using (var reader = cmd.ExecuteReader())
        {
            if (reader.HasRows)
            {
                reader.Read();
                amt = reader.GetDouble(0);
            }
            reader.Close();
        }
    }
}
Console.WriteLine(amt.ToString());

However, there is a far quicker way of doing this using Linq (be sure to add using System.Linq to your using directives):

    var account = this.BindingSource.Current as Sage.Entity.Interfaces.IAccount;
    double amt = account.Opportunities.Sum(x => x.SalesPotential.HasValue?x.SalesPotential.Value:0);    
    Console.WriteLine(amt.ToString());

There you go. Options galore!

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) news and product updates!

You have Successfully Subscribed!