Hi,

I'm wondering what the best way to retrieve distinct items from a database table would be when we are only interested in returning a single column and having those results as distinct.

Eg. A Post code table has the state, postcode and suburb. From a postal code table retrieving all available distinct States to populate a State combo.

What's the best way to retrieve these details and bind them to the combo box?

I'm using LLBLGen 2.6 with self servicing.

Using Linq to objects previousy to query distinct data and bind it to the UI I would have done something like:

Dim lCustomerOrderList As System.Linq.IQueryable(Of CustomerOrderPointer)


'Create Distinct Customer Order Pointers for each Open order
lCustomerOrderList = From co In DbContext.CustomerOrders _
                                Where co.Closed = False _
                                 Select New CustomerOrderPointer With {.PK_OrderID = co.PK_OrderID, .CustomerOrderNo = co.CustomerOrderNo, .FK_CustomerID = co.FK_CustomerID} Distinct
    
 'Bind the Customer Order Pointers to the Order dropdown
cmbOrderNo.DisplayMember = "CustomerOrderNo"
cmbOrderNo.ValueMember = "PK_OrderID"
cmbOrderNo.DataSource = lCustomerOrderList.OrderBy(Function(lcop As CustomerOrderPointer) lcop.CustomerOrderNo)

Just wondering what the equivalent in LLBLGen would be?

If I was to grab a distinct list with linq and push this out into a custom class I could do the following:

    

      Dim lStateList As System.Linq.IQueryable(Of CommonBinder)

        Dim lStateList
        lStateList = From pc In mPostalCodeList _
                      Select New CommonBinder With {.DisplayMember = pc.State, .ValueMember = pc.State} Distinct

is the shortest way to achieve this with LLBLGen this:

 Dim mPostalCodeList As New CollectionClasses.PostcodeCollection

        'Retrieve the Postcode List
        mPostalCodeList.GetMulti(Nothing)   
Dim lStateList As New List(Of CommonBinder)()

        Dim allPostcodesView As EntityView(Of EntityClasses.PostcodeEntity) = mPostalCodeList.DefaultView

        Dim customClassProjector As New DataProjectorToCustomClass(Of CommonBinder)(lStateList)
        Dim propertyProjectors As New List(Of IEntityPropertyProjector)()
        propertyProjectors.Add(New EntityPropertyProjector(HelperClasses.PostcodeFields.State, "DisplayMember"))
        propertyProjectors.Add(New EntityPropertyProjector(HelperClasses.PostcodeFields.State, "ValueMember"))

        ' create the projection
        allPostcodesView.CreateProjection(propertyProjectors, customClassProjector, False)

Is there any easier way to perform this operation and any way I can get around having to enter names as strings?

Use a DynamicList with only one field (the state field), and set allowDuplicates parameter to false.