When using IQueryable to look for records within an Entity collection you will likely run into an error at some point when trying to add a condition to search for when that condition is a related entity property, or is a calculated entity property.
For instance, lets say we have the Sales Order entity that has had a calculated entity property added to it that shows the sum of the products tied to the order, represented as Sage.Entity.Interfaces.ISalesOrder.CalculatedTotal.
If we try to find orders that have more than $10,000 worth of orders using IQueryable like so:
Sage.Platform.RepositoryHelper<ISalesOrder> repository =
Sage.Platform.EntityFactory.GetRepositoryHelper<ISalesOrder>();
Sage.Platform.Repository.ICriteria criteria = repository.CreateCriteria();
criteria.Add(repository.EF.Eq(“Account.Id”, account.Id.ToString()));
criteria.Add(repository.EF.Gt(“CalculatedTotal”, “Invoice”));
criteria.AddOrder(repository.EF.Desc(“CreateDate”));
System.Collections.Generic.IList<Sage.Entity.Interfaces.ISalesOrder> orders = criteria.List<Sage.Entity.Interfaces.ISalesOrder>();
It is likely that you will get an error stating that the ISalesOrder interface does not implement a property called CalculatedTotal. This is because during the query the entities involved are not unwrapped, meaning that child entity properties, relationships, and calculated fields are generally not available.
There is a way of getting around this, using a bit of a double query. You can utilize IQueryable in conjunction with the Sage.Platform.EntityFactory assembly. The EntityFactory.GetById class does provide an unwrapped version of the entity object to you. So in the above case we can change it slightly to do this
System.Collections.Generic.IList<Sage.Entity.Interfaces.ISalesOrder> myOrders = new System.Collections.Generic.List<Sage.Entity.Interfaces.ISalesOrder>();
Sage.Platform.RepositoryHelper<ISalesOrder> repository =
Sage.Platform.EntityFactory.GetRepositoryHelper<ISalesOrder>();
Sage.Platform.Repository.ICriteria criteria = repository.CreateCriteria();
criteria.Add(repository.EF.Eq(“Account.Id”, account.Id.ToString()));
criteria.AddOrder(repository.EF.Desc(“CreateDate”));
System.Collections.Generic.IList<Sage.Entity.Interfaces.ISalesOrder> orders = criteria.List<Sage.Entity.Interfaces.ISalesOrder>();
foreach(Sage.Entity.Interfaces.ISalesOrder order in orders)
{
Sage.Entity.Interfaces.ISalesOrder unwrappedorder = Sage.Platform.EntityFactory.GetById<Sage.Entity.Interfaces.ISalesOrder>(order.Id.ToString());
if (unwrappedorder.CalculatedTotal > 10000) myOrders.Add(unwrappedorder);
}
Above, for each order at the account we instantiate a fully unwrapped Sales Order entity using the EntityFactory.GetById. From there we can check to see if the Calculated Field meets are conditions and if it does we add it to a new IList.
