Question

Server ESQ - Select records with list of ids

Shivani Lakshman

13:02 Jan 05, 2022

Hello community,

I have a case where I need to get details of records which match the list of Ids. Ie

select * from table where id in [list of ids];

How do I implement this using server esq?

The following

var fileIdFilter = fileLinkESQ.CreateFilterWithParameters(FilterComparisonType.Equal,

"UsrFileId", FileIds.ToArray()); //FileIds is a list of Guid

throws the error : System.FormatException: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

2 comments

Bhoobalan Palanivelu

20:16 Jan 05, 2022

Shivani,

Here is a sample scenario,

Step 1: Take the complete table into a collection

var entitySchemaManager = UserConnection.GetSchemaManager("EntitySchemaManager") as EntitySchemaManager;
var contactESQ = new EntitySchemaQuery(entitySchemaManager,"Contact");
contactESQ.JoinRightState = Core.DB.QueryJoinRightLevel.Disabled;
contactESQ.AddAllSchemaColumns();
EntityCollection entities = contactESQ.GetEntityCollection(UserConnection);

Step 2: Parse through the collection via Linq

var count = (from elements in entities
             where elementsGetTypedColumValue&lt;string&gt;("FirstName") == "RequiredText"
             select elements).count();

Shivani Lakshman

06:30 Jan 06, 2022

Bhoobalan Palanivelu,

Thank you, but the table in question can hold 1000s of records at any given time. Bringing the whole table into memory and then performing LINQ may not be efficient.

Also, I already know the list of records I need to fetch. Instead of

select * from table where id = A

select * from table where id = B

select * from table where id = C

I am looking for a query using "In" operator which will reduce the number of DB hits to 1 through

select * from table where id in (A,B,C).

This way I will have just the records I want to process in memory instead of whole table.

Show all comments