Is there a way to fetch a list of entities based on a distinct field?

i.e

Customers
    CustomerID
    CustomerName
    Phone
    OtherField

i.e. SELECT DISTINCT(CustomerName), CustomerID from Customers

I'm using LLBLGen Pro 2.0 w/ Self-Servicing.

Thanks!

use a typedlist, dynamiclist or project the results onto an entity collection/custom collection.

your query will not work. distinct applies to the entire select portion of the query. it would look like this

select distinct CustomerName, CustomerId from Customers

jmeckley wrote:

use a typedlist, dynamiclist or project the results onto an entity collection/custom collection.

your query will not work. distinct applies to the entire select portion of the query. it would look like this

select distinct CustomerName, CustomerId from Customers

Weird, I tried it in query analyzer and it seemed to work ok.

If you have a unique constraint on the field, you can use FetchUsingUCXYZ. SELECT DISTINCT will return only the distinct combination of all columns on the query. It may have seemed to work because you only have unique values in your table.

If you actually need a field to be unique/distinct, you need to use a constraint.

True, I could use the Fetch UsingUCXYZ but it will only return 1 value, not ALL the unique values in the list?

Basically I want to return all unique entities based on a field.

But I guess the TypedList will do for now.

by defination an entity is unique (see help docs). you can use projection to populate an entitycollection with a distinct list of customers.

this way you get the required result contained within the model you need.

Basically I want to return all unique entities based on a field.

DISTINCT is applied on all the fields in the select list, not on one field. I don't understand what you are trying to do. Please post an example. post a table design schema, and a sample of rows data, and last but not least the query that you want to execute.

sqlserver doesn't support this, only oracle does. LLBLGen Pro only supports DISTINCT per row, not per field.

Thanks for the information Otis!