BusinessFoundation and counting entities

Thomas Schmidt

Vote:

It seems there is no way of counting entities with business foundation, meaning there doesn't seem to be a way of doing SELECT COUNT(*) FROM cls_MyEntity. The only way to do this so far is basically listing all entities with BusinessManager.List(..) (potientially with some filters) and then doing a count on that, but that also means loading all the entities meaning goodbye performance.

I am on a project that will end up with around 400.000+ contacts so this is a deal breaker for me.

Am i missing something or is this true? Looking for a BusinessManager.Count(some list of FilterElements) or similar and would hate having to do raw sql as that kills of refactoring.

If no such method exists then what is the suggested workaround for this? Guessing we can create an extension method for BusinessManager that could pull out table name of the entity object being queried and use that in some raw sql call but that seems extremely hacky.

#90009

Sep 01, 2014 9:16

Michael Jensen

Vote:

Does this help? Remember that you need to add your own caching layer if needed on BF.

SortingElementCollection sortCollection = new SortingElementCollection();
sortCollection.Add(SortingElement.Empty);

MetaClass m = DataContext.Current.MetaModel.MetaClasses["YourBusinessFoundationClassName"];
EntityObject[] sortedList = BusinessManager.List(m.Name, filterCollection.ToArray(), sortCollection.ToArray(), 0, int.MaxValue);

if (sortedList.Length > 0)

#90303

Sep 05, 2014 8:31

Thomas Schmidt

Vote:

Hi Michael,

that code still load all entities, what I am looking for is a count of entities, so I do not, in the worst case, load 400.000+ contacts just to count how many there are.

#90345

Sep 08, 2014 9:25

Quan Mai

Vote:

As far as I know we don't have any built-in function to do that count. But you can implement it by a stored procedure. Is there any difficulty with that?

Regards.

#90360

Sep 08, 2014 11:44

Michael Jensen

Vote:

I haven´t looked into this function before beacuse I never needed this But maybe this one will work even if it´s not recommended. Not sure about performance and what´s going on under the hood in this case.

http://world.episerver.com/Documentation/Items/Developers-Guide/EPiServer-Commerce/75/Business-Foundation/Working-with-SQL-Records/

Getting the Total Records Count
Call the CustomTableRow.GetTotalCount statement to return the total record count.

#90362

Sep 08, 2014 11:56

Thomas Schmidt

Vote:

@Quan

implementing a stored procedure would be doable, it just messes with the deployment routines we use, because then we would have to also deploy some sql scripts when we move code from dev -> staging -> prod. i guess we will have to do some raw sql as it seems like we can always rely on the meta class names to get the proper table names we need to count on and then work a bit with the metafield classes to get the proper column when we need to do filtering when counting.

@Michael

I just did some quick tests with this and it seems to do the trick, and I can also reuse my FilterElements. Will go with this solution, thanks :)

#90363

Sep 08, 2014 12:03

Michael Jensen

Vote:

Great, I also took a quick test and this is what I did

Database database = SqlContext.Current.Database;
Mediachase.BusinessFoundation.Data.Sql.Management.Table contactTable = database.Tables["cls_Contact"];
FilterElementCollection fc = new FilterElementCollection();
int contactCount = CustomTableRow.GetTotalCount(contactTable, fc.ToArray());

Will generate the following query against the database

SELECT COUNT(*)

FROM [cls_Contact] AS [t01]

#90371

Sep 08, 2014 14:33

Thomas Schmidt

Vote:

I did it a bit different(removed null checks for shorter example code) to prevent magic strings with table names:

MetaClass mc = DataContext.Current.GetMetaClass(ContactEntity.ClassName);

TableConfig contactConfig = mc.GetTableConfig();

int totalCount = CustomTableRow.GetTotalCount(contactConfig, new[] {filterelementOne, filterelementTwo});

but either way works.

#90376

Sep 08, 2014 14:53