Hi Tom,
There is no way to optimize that search using Row, Entities, or Services - your stuck having to write OSQL. The magic is in the ExecuteReader parameters.
private void SelectTopContacts(int topCount, string startsWith)
{
int contactId = 0;
ContactTableInfo contactTable = TablesInfo.GetContactTableInfo();
Select select = S.NewSelect();
SoCommand command = null;
SoConnection conn = null;
SoDataReader reader = null;
select.ReturnFields.Add(contactTable.ContactId);
select.Restriction = contactTable.Name.Like(startsWith + "%");
try
{
using (conn = ConnectionFactory.GetConnection())
{
using (command = conn.CreateCommand())
{
command.SqlCommand = select;
conn.Open();
//uses paging - every page consists of topCountN
//entries and we only want the first page
using (reader = command.ExecuteReader(topCount, 1))
{
while (reader.Read())
{
//do stuff
contactId = (int)reader[contactTable.ContactId];
}
}
}
}
}
catch (Exception)
{
//errormessage
}
}
Hope this helps.