How to get totalRecord count from object using ESQ

You can use something like this: 

var esq = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Project");
var countCol = esq.AddColumn(esq.CreateAggregationFunction(AggregationTypeStrict.Count, "Id")).Name;
 
// add any filters if needed
 
var entityCollection = esq.GetEntityCollection(UserConnection);
var entity = entityCollection[0];
 
var count = entity.GetTypedColumnValue<int>(countCol);

You need to add a column that creates an aggregate function (count). Then, when you get the results from GetEntityCollection you'll get the first item in the results to get the Count value.

Ryan

Hello,
Thank you for your question.

This line of code is the source of your concern: 

esqCount.RowCount = 0;

RowCount is a property which is responsible for the number of readable elements from your query.

Also note that UseAdminRights is a flag that determines whether access rights will be checked for performing CRUD operations with the database. When UseAdminRights is set to false, the rights check is disabled, and operations are performed without considering access rights.

Hope this helps.

Ryan Farley,

Thanks for answering , I am having hard time to fix this piece of code please see if you can help me -

var selectQuery = new Select(userConnection)
                           .Column("Project", "Id").As("ProjectRecordId")
                           .Column("Project", "UsrProjectID").As("ProjectId")
                           .Column("Project", "Name").As("ProjectName")
                           .Column("Project", "UsrProjectDescription").As("ProjectDescription")
                           .Column("UsrEntitlements", "UsrNumber").As("Entitlements")
                           .Column("ServicePact", "Number").As("ServiceContract")
                           .Column("Project", "CreatedOn").As("CreatedOn")
                           .Column("Contact", "Email").As("CreatedByEmail")
                           .Column("Project", "ModifiedOn").As("ModifiedOn")
                           .Column("Contact", "Email").As("ModifiedByEmail")
                           .From("Project")
                           .Join(JoinType.LeftOuter, "UsrEntitlements").On("Project", "UsrEntitlementsId").IsEqual("UsrEntitlements", "Id")
                           .Join(JoinType.LeftOuter, "ServicePact").On("Project", "UsrServiceContractId").IsEqual("ServicePact", "Id")
                           .Join(JoinType.LeftOuter, "Contact").On("Project", "CreatedById").IsEqual("Contact", "Id")
                           .Join(JoinType.LeftOuter, "Contact").On("Project", "ModifiedById").IsEqual("Contact", "Id")
                            as Select;

Screenshot - 

And the error I am getting is - Error: 42712: table name "Contact" specified more than once.

Thanks.

AS,

You're joining to the Contact table twice, but not aliasing them (which means they are both named "Contact")

Change the contact joins to include As

.Join(JoinType.LeftOuter, "Contact").As("CreatedContact").On("Project", "CreatedById").IsEqual("Contact", "Id")
.Join(JoinType.LeftOuter, "Contact").As("ModifiedContact").On("Project", "ModifiedById").IsEqual("Contact", "Id")

Then use them using that alias: 

 .Column("ModifiedContact", "Email").As("ModifiedByEmail")

Also, as an FYI, you can change those joins like this, which I think reads better and less wordy:

.LeftOuterJoin("Contact").As("CreatedContact").On("Project", "CreatedById").IsEqual("Contact", "Id")
.LeftOuterJoin("Contact").As("ModifiedContact").On("Project", "ModifiedById").IsEqual("Contact", "Id")

Ryan