Select Distinct Options

[Robert_Love]

We have an existing table with no relationships.

One row in the table is email address

We have a need to list all the email addresses in that table.

in SQL we would do the following...

select distinct email from tablename

How might  you do this in Aurelius without having to loop through all the rows in memory.

we are using Oracle and thought that it might work to create a view of the select distinct and then map an entity to the view.    

But that seems like a lot of work and was hoping for a better solution.

[White_Dave]

Take a look at "Removing Duplicated Objects" in the help, this might do what you need

http://www.tmssoftware.com/business/aurelius/doc/web/index.html?removing_duplicated_objects.htm

[Bishop_Greg]

Robert:

Did you ever work out how to do a "SELECT DISTINCT" for a single field with Aurelius? I've tested with .RemovingDuplicatedObjects, but if more than one object/row has the same field value (email in your example), then multiple objects are still returned, presumably since other fields are unique.

My current query is:

var
    Results:TObjectList<TCriteriaResult>;
...
  Results := ObjManager.Find<TContact>
    .Select(Linq['EMail'])
    .OrderBy('EMail')
    .RemovingDuplicatedEntities
    .ListValues;

Greg

[wlandgraf (Wagner Landgraf)]

RemovingDuplicatedEntities only work for entities, not projected values.
One workaround is to use SQL projection:

var
    Results:TObjectList<TCriteriaResult>;
...
  Results := ObjManager.Find<TContact>
    .Select(Linq['EMail'])
    .Select((TProjections.Sql<string>('Distinct {Email}').As_('EMail'))
    .OrderBy('EMail')
    .ListValues;

[Bishop_Greg]

Thank you, Wagner. This works well.