How to perform case-insensitive string comparison in Creatio server-side C# Select query?
Hi Team,
I’m trying to implement a server-side utility method in Creatio to fetch the Id from a table by matching the Name field.
The challenge is that the value comparison should be case-insensitive (e.g., "Test", "test", "TEST" should all match the same record).
I have tried with several ways to resolve it but could not.
Query:
What’s the recommended way in Creatio server-side C# to do a case-insensitive comparison when querying lookups (via Select or EntitySchemaQuery)?
Here’s my current code:
public static Guid GetValueIdCached(string tableName, string name, Dictionary<string, Guid> cache) { if (string.IsNullOrWhiteSpace(name)) return Guid.Empty; if (cache.TryGetValue(name, out Guid cachedId)) return cachedId; name = name.Trim().ToLower(); var selectQuery = new Select(_userConnection) .Top(1) .Column("Id") .From(tableName) .Where("Name").IsEqual(Column.Parameter(name)) as Select; Guid resultId = selectQuery.ExecuteScalar<Guid>(); cache[name] = resultId != Guid.Empty ? resultId : Guid.Empty; return resultId; }
Like
Hi Team,
This is quite urgent since I need to migrate a large volume of data and this function match value (from DWH) in Creatio Lookup & return respective ID. I would appreciate any guidance or clarification at the earliest.
I've not tested this out, but could the following work (after converting `name` to uppercase instead of lowercase, since there doesn't seem to be a "Lower" equivalent in Func!):
.Where(Func.Upper("Name")).IsEqual(Column.Parameter(uppercaseName))Hello,
To implement a case-insensitive lookup in Creatio when fetching the record Id by its Name field, you can normalize both sides of the comparison using the SQL LOWER function. This ensures that "Test", "test", and "TEST" will all match the same record.
Here’s an example implementation using Select in server-side C#:
name = name.Trim().ToLower(); var selectQuery = new Select(_userConnection) .Top(1) .Column("Id") .From(tableName) .Where() .Func("LOWER", Column.Column("Name")) .IsEqual(Column.Parameter(name)) as Select; Guid resultId = selectQuery.ExecuteScalar<Guid>();