LINQ-to-Object, Distnct records

In this code, line 31 asks for distinct states to fill cmbState, but it displays repeat states.

Question: How can I make this code to display distinct states?

Do I need to add a new method like StateGetDistinct() to CustomerDB class? That will deninately work, but here the focus is on using LINQ-to-Object. How can LINQ query the CustomerData data source or the customerList array to solve the problem?
Public Class Form1

    Dim CustomerData As New CustomerDB()

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ' POPULATE customerList ARRAY LIST
        Dim customerList As List(Of Customer) = CustomerData.CustomerGetAll()

        ' CLEAR OLD DATA
        lstCustomers.Items.Clear()
        cmbState.Items.Clear()

        ' INITIALIZE THE LIST BOX WITH ALL CUSTOMERS
        Dim customers = From customer In customerList _
                              Select customer.CustomerID, customer.FirstName, customer.LastName, customer.City, customer.State

        ' ADD CUSTOMERS TO lstCustomers
        For Each customer In customers
            lstCustomers.Items.Add(customer.CustomerID & ControlChars.Tab & _
                                                      customer.FirstName & ControlChars.Tab & _
                                                      customer.LastName & ControlChars.Tab & _
                                                      customer.State & ControlChars.Tab & _
                                                      customer.City)
        Next

        ' POPULATE COMBO BOX
        Dim states = From state In customerList _
                             Order By state.State _
                             Select state.State
                             Distinct

        cmbState.Items.Add("<All>")

        ' ADD CUSTOMERS TO lstCustomers
        For Each state In customers
            cmbState.Items.Add(state.State)
        Next
        cmbState.SelectedIndex = 0
    End Sub

End Class

Open in new window

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
I think your problem is actually here:
' ADD CUSTOMERS TO lstCustomers
For Each state In customers
	cmbState.Items.Add(state.State)
Next

Open in new window


You need to add the combobox items from the states enumeration:
' ADD CUSTOMERS TO lstCustomers
For Each state In states
	cmbState.Items.Add(state)
Next

Open in new window


-saige-
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Saig,

lstCustomers is working fine. I need to have states distinct in the cboState.

I have made some progress using the following code, but it still shows multiple:

       ' POPULATE COMBO BOX
        Dim states = From state In customerList _
                             Select state.State.Distinct()

Open in new window

0
Fernando SotoRetiredCommented:
Can you try it this way

Dim states = (From state In customerList _
              Order By state.State _
              Select state.State).Distinct

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Fernando,

It didn't make any difference. I am also open to use Group By but could not figure out its syntax in LINQ.

The following is my CustomerDB class. I am trying to use the return value from CustomerGetAll(), see line 7, in this class to get this done without having to make yet another StateGetDistinct() for example in this class to shift what I want to do from LINQ to SQL:
Imports System.Data.SqlClient

Public Class CustomerDB
    Inherits WSCGSoftwareDBAccess

    ' GET ALL CUSTOMERS WITH THE SPECIFIED FirstName,  LastName, AND/ OR City
    Public Function CustomerGetAll() As List(Of Customer)
        ' Public Function CustomerGetAll(ByVal FirstName As String, ByVal LastName As String, ByVal City As String) As List(Of Customer)
        Dim cnn As SqlConnection = GetConnection()
        Dim customers As New List(Of Customer)
        Dim sql As String
        Dim cmdSelectCustomer As New SqlCommand
        Dim reader As SqlDataReader

        ' cnn = GetConnection()
        cnn.Open()
        sql = "SELECT CustomerID, FirstName, LastName, Address, City, State," & _
                                     " ZipCode, Phone, Email FROM tblCustomers" ' WHERE FirstName" '& _
        ' " LIKE @FirstName + '%' AND LastName LIKE @LastName + '%'" & _
        '" AND City LIKE @City + '%'"

        With cmdSelectCustomer
            .CommandText = sql
            .CommandType = CommandType.Text
            .Connection = cnn
            '.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = FirstName
            '.Parameters.Add("@LastName", SqlDbType.VarChar, 30).Value = LastName
            '.Parameters.Add("@City", SqlDbType.VarChar, 30).Value = City
        End With

        reader = cmdSelectCustomer.ExecuteReader(CommandBehavior.CloseConnection)
        While reader.Read()
            customers.Add(CustomerMapData(reader))
        End While
        cnn.Close()

        Return customers

    End Function

    ' GET CUSTOMER BY CustomerID
    Public Function CustomerGetByCustomerID(ByVal CustomerID As Int32) As Customer

        Dim cust As Customer
        Dim strSelectCustomer As String
        Dim cmdSelectCustomer As New SqlCommand
        Dim rdrCustomer As SqlDataReader
        Dim cnn As SqlConnection
        'Dim CustID As Int32 = CustomerID
        cnn = GetConnection()
        cnn.Open()
        strSelectCustomer = "SELECT CustomerID, FirstName, LastName, Address, City, State," & _
             " ZipCode, Phone, Email FROM tblCustomers WHERE tblCustomers.CustomerID = @CustomerID"
        ' Try
        With cmdSelectCustomer
            .CommandText = strSelectCustomer
            .CommandType = CommandType.Text
            .Connection = cnn
            .Parameters.Add("@CustomerID", SqlDbType.Int, 4).Value = CustomerID
        End With

        rdrCustomer = cmdSelectCustomer.ExecuteReader(CommandBehavior.SingleRow)
        If rdrCustomer.Read Then
            cust = CustomerMapData(rdrCustomer)
        Else
            cust = Nothing
        End If
        ' Catch ex As SqlException
        'MessageBox.Show(ex.Errors.ToString)
        ' Finally
        cnn.Close()
        ' End Try


        Return cust

    End Function

    ' FILL CUST OBJECT USING DATA-READER CONTENT FROM THE DATABASE
    Private Function CustomerMapData(ByVal reader As SqlDataReader) As Customer

        Dim cust As New Customer

        With cust
            .CustomerID = reader("CustomerID").ToString
            .FirstName = reader("FirstName").ToString
            .LastName = reader("LastName").ToString
            .Address = reader("Address").ToString
            .City = reader("City").ToString
            .State = reader("State").ToString
            .ZipCode = reader("ZipCode").ToString
            .Phone = reader("Phone").ToString
            .Email = reader("Email").ToString
        End With

        Return cust

    End Function

    ' INSERT CUSTOMER
    Public Function InsertCustomer(ByVal cust As Customer) As Int32

        Dim recordsAffected As Int32 = 0
        Dim insertCustomerSQL As String
        insertCustomerSQL = "Insert Into tblCustomers(FirstName, LastName, [Address], City, [State], ZipCode, Phone, Email)" & _
                                                            " Values(@FirstName, @LastName, @Address, @City, @State, @ZipCode, @Phone, @Email)"

        Dim cmdInsertCust As SqlCommand
        Dim cnn As SqlConnection = GetConnection()
        cmdInsertCust = CreateCustomerCommand(insertCustomerSQL, cust, cnn)

        cnn.Open()
        recordsAffected = cmdInsertCust.ExecuteNonQuery()
        cnn.Close()

        Return recordsAffected

    End Function

    Public Function UpdateCustomer(ByVal cust As Customer) As Int32

        Dim recordsAffected As Int32 = 0
        Dim strSQLUpdateCust As String
        Dim cmdUpdateCust As New SqlCommand
        Dim cnn As SqlConnection

        strSQLUpdateCust = "UPDATE tblCustomers SET " _
        & "FirstName = @FirstName, " _
        & "LastName = @LastName, " _
        & "Address = @Address, " _
        & "Address = @Address, " _
        & "City = @City, " _
        & "State = @State, " _
        & "ZipCode = @ZipCode, " _
        & "Phone = @Phone, " _
        & "Email = @Email " _
        & "WHERE CustomerID= CustomerID;"

        cnn = GetConnection()

        cmdUpdateCust = CreateCustomerCommand(strSQLUpdateCust, cust, cnn)

        cnn.Open()
        recordsAffected = cmdUpdateCust.ExecuteNonQuery
        cnn.Close()

        Return recordsAffected

    End Function

    Public Function DeleteCustomer(ByVal cust As Customer) As Int32

        Dim recordsAffected As Int32 = 0
        Dim cnn As SqlConnection = GetConnection()
        Dim cmd As New SqlCommand("spDeleteCustomerAndOrders", cnn)
        Dim prmCustID As New SqlParameter("@CustomerID", SqlDbType.Int, 4)
        prmCustID.Direction = ParameterDirection.Input
        prmCustID.Value = cust.CustomerID
        cmd.Parameters.Add(prmCustID)
        cmd.CommandType = CommandType.StoredProcedure

        cnn.Open()
        recordsAffected = cmd.ExecuteNonQuery()
        cnn.Close()

        Return recordsAffected

    End Function

    Private Function CreateCustomerCommand(ByVal sql As String, _
                                           ByVal cust As Customer, _
                                           ByVal cnn As SqlConnection) As SqlCommand

        Dim cmdCustomer As New SqlCommand
        With cmdCustomer
            .CommandType = CommandType.Text
            .CommandText = sql
            .Connection = cnn
        End With

        '*** Customer Parameters
        With cust

            cmdCustomer.Parameters.Add("@CustomerID", SqlDbType.Int, 4).Value = .CustomerID
            cmdCustomer.Parameters.Add("@FirstName", SqlDbType.VarChar, 30).Value = .FirstName
            cmdCustomer.Parameters.Add("@lastName", SqlDbType.VarChar, 30).Value = .LastName
            cmdCustomer.Parameters.Add("@Address", SqlDbType.VarChar, 50).Value = .Address
            cmdCustomer.Parameters.Add("@City", SqlDbType.VarChar, 30).Value = .City
            cmdCustomer.Parameters.Add("@State", SqlDbType.VarChar, 2).Value = .State
            cmdCustomer.Parameters.Add("@ZipCode", SqlDbType.NVarChar, 10).Value = .ZipCode
            cmdCustomer.Parameters.Add("@Phone", SqlDbType.NVarChar, 12).Value = .Phone
            cmdCustomer.Parameters.Add("@Email", SqlDbType.VarChar, 100).Value = .Email

        End With

        Return cmdCustomer

    End Function
    ' DOES CustomerID ALREADY EXISTS
    Public Shared Function Check_New_CustomerID(ByVal newCustomerID As String) As Boolean

        Dim strSelectCustomer As String
        Dim cmdSelectCustomer As New SqlCommand
        Dim intCustCount As Integer
        Dim cnn As SqlConnection

        cnn = GetConnection()
        cnn.Open()
        strSelectCustomer = "SELECT COUNT(c.CustomerID)" _
        & " FROM tblCustomers c" _
        & " WHERE c.CustomerID = @CustomerID"

        With cmdSelectCustomer
            .CommandText = strSelectCustomer
            .CommandType = CommandType.Text
            .Connection = cnn
            .Parameters.Add("@CustomerID", SqlDbType.Int, 4).Value = newCustomerID
        End With

        intCustCount = Convert.ToInt32(cmdSelectCustomer.ExecuteScalar())
        cnn.Close()

        If intCustCount = 0 Then
            Return True
        Else
            Return False
        End If

    End Function

End Class

Open in new window

0
Fernando SotoRetiredCommented:
You state, "It didn't make any difference.", same exact error on the same line in the code?
0
käµfm³d 👽Commented:
What is the type of state.State?
0
it_saigeDeveloperCommented:
Try this:
Dim states = From state in customerList Group state By state Into stateGroup = Group Select stateGroup.FirstOrDefault

Open in new window


Example -
Class LinqToObjectExample
	Shared Sub Main()
		Try
			Dim lStates As List(Of String) = New List(Of String) From {"AZ", "AL", "AZ", "AK", "AZ"}
			Dim states = From state In lStates Group state By state Into stateGroup = Group Select stateGroup.FirstOrDefault
			For Each state In states
				Console.WriteLine(String.Format("State: {0}", state))
			Next
		Catch e As Exception
			Console.WriteLine(e.ToString())
		End Try
		Console.ReadLine()
	End Sub
End Class

Open in new window


Produces the following output:Capture.JPG
-saige-
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Fernando,

The was no errors to begin with. The problem was and is it shows CA (for California) multiple times. What is expected to have one of each.
'---------
kaufmed,

It is varchar(2).
'-------------
Saige,

I see that you have a good example and it works. I just borrowed:
Dim states = From state In customerList Group state By state Into stateGroup = Group Select stateGroup.FirstOrDefault

Open in new window



It is not giving me distinct states.

Mike
0
it_saigeDeveloperCommented:
Correction to my Linq:
Dim states = From state in customerList Group state By state.State Into stateGroup = Group Select stateGroup.FirstOrDefault().State

Open in new window


Example -
Class LinqToObjectExample
	Public Class Customer
		Public Property Name() As String
		Public Property State() As String
	End Class

	Shared Sub Main()
		Try
			Dim lCustomers As List(Of Customer) = New List(Of Customer) From _
			{ _
			 New Customer() With {.Name = "Paul", .State = "AZ"}, _
			 New Customer() With {.Name = "Peter", .State = "AL"}, _
			 New Customer() With {.Name = "John", .State = "AZ"}, _
			 New Customer() With {.Name = "Ralph", .State = "AK"}, _
			 New Customer() With {.Name = "Joyce", .State = "AZ"} _
			}
			Dim states = From state In lCustomers Group state By state.State Into stateGroup = Group Select stateGroup.FirstOrDefault().State
			For Each state In states
				Console.WriteLine(String.Format("State: {0}", state))
			Next
		Catch e As Exception
			Console.WriteLine(e.ToString())
		End Try
		Console.ReadLine()
	End Sub
End Class

Open in new window


Produces the same output as above.

-saige-
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Sage,

My apology to you for not paying attention to your first post stating:" You need to add the combobox items from the states enumeration:"

All along, I have been using "For Each state In customers" not "For Each state In states". I guess I was doing this because With:

        For Each state In states
            cmbState.Items.Add(state.State)
        Next

I was getting this error --> 'State' is not a member of 'String'.

Is this because my field name [State] is mixed up with some property or something else?

Mike
0
it_saigeDeveloperCommented:
Ok eghtebas.  I got a question, is cmbState your state combo-box?  Is this where you are seeing multiple states?  If the answer to these questions is yes, the I pointed out your error earlier.

This add's all of the states in the customers list to your combo-box
For Each state In customers
	cmbState.Items.Add(state.State)
Next

Open in new window


Whereas, this add's all of the states in the states list to your combo-box
For Each state In states
	cmbState.Items.Add(state.State)
Next

Open in new window


Logically, Distinct and Group.. By do the same thing:
Module Module1
	Public Class Customer
		Public Property Name() As String
		Public Property State() As String
	End Class

	Sub Main()
		Try
			Dim lCustomers As List(Of Customer) = New List(Of Customer) From _
			 { _
			  New Customer() With {.Name = "Paul", .State = "AZ"}, _
			  New Customer() With {.Name = "Peter", .State = "AL"}, _
			  New Customer() With {.Name = "John", .State = "AZ"}, _
			  New Customer() With {.Name = "Ralph", .State = "AK"}, _
			  New Customer() With {.Name = "Joyce", .State = "AZ"} _
			 }
			Console.WriteLine("Using Group By")
			For Each st In (From item In lCustomers Group item By item.State Into stateGroup = Group Select stateGroup.FirstOrDefault().State)
				Console.WriteLine(String.Format("State: {0}", st))
			Next

			Console.WriteLine()

			Console.WriteLine("Using Distinct")
			For Each st In (From item In lCustomers Select item.State Distinct)
				Console.WriteLine(String.Format("State: {0}", st))
			Next

			Console.WriteLine()

			Console.WriteLine("Now lets really have fun")
			lCustomers = New List(Of Customer) From _
			 { _
			  New Customer() With {.Name = "Paul", .State = String.Empty}, _
			  New Customer() With {.Name = "Peter", .State = "AL"}, _
			  New Customer() With {.Name = "John", .State = "AZ"}, _
			  New Customer() With {.Name = "Ralph", .State = "AK"}, _
			  New Customer() With {.Name = "Joyce", .State = Nothing} _
			 }
			Console.WriteLine("Using Distinct")
			For Each st In (From item In lCustomers Select item.State Distinct)
				Console.WriteLine(String.Format("State: {0}", st))
			Next

			Console.WriteLine()

			Console.WriteLine("Using Group By")
			For Each st In (From item In lCustomers Group item By item.State Into stateGroup = Group Select stateGroup.FirstOrDefault().State)
				Console.WriteLine(String.Format("State: {0}", st))
			Next
		Catch e As Exception
			Console.WriteLine(e.ToString())
		End Try
		Console.ReadLine()
	End Sub
End Module

Open in new window


Produces this output:Capture.JPG
-saige-
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
it_saigeDeveloperCommented:
@eghtebas, my previous post does not answer your latest question and was being developed while you were posting your latest question.

To answer, the reason why:
For Each state In states
	cmbState.Items.Add(state.State)
Next

Open in new window

Produces this error is because the linq query has modified the original enumerable (which was a list of customer), to create a new enumerable of string.  As such the string class has no property or definition for .State.

-saige-
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Once again, sorry for not paying attention. I have enough to make it work.

Regards,

Mike
0
it_saigeDeveloperCommented:
Drat, I did it again.  LOL

This adds the list of states to your combobox:
For Each state In states
	cmbState.Items.Add(state)
Next

Open in new window


-saige-
0
it_saigeDeveloperCommented:
No apology needed eghtebas.  I always believe that the additional comments provide a better understanding.  ;)

-saige-
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
What worked for me was:

       Dim states = From state In customerList Group state By state.State Into stateGroup = Group Select stateGroup.FirstOrDefault().State

        cmbState.Items.Add("<All>")

            ' ADD CUSTOMERS TO lstCustomers
        For Each state In states
            cmbState.Items.Add(state.ToString)
        Next

Open in new window


Without ToString I was getting System... for each row.

Wonderful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.