Solved

LINQ-to-Object, Distnct records

Posted on 2014-11-13
16
231 Views
Last Modified: 2016-02-15
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

0
Comment
Question by:Mike Eghtebas
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 32

Expert Comment

by:it_saige
ID: 40440604
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40440622
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
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 100 total points
ID: 40440713
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40440740
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40440763
You state, "It didn't make any difference.", same exact error on the same line in the code?
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 40440765
What is the type of state.State?
0
 
LVL 32

Expert Comment

by:it_saige
ID: 40440785
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40440791
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 32

Expert Comment

by:it_saige
ID: 40440818
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
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40440999
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
 
LVL 32

Accepted Solution

by:
it_saige earned 400 total points
ID: 40441040
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
 
LVL 32

Expert Comment

by:it_saige
ID: 40441053
@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
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
ID: 40441057
Once again, sorry for not paying attention. I have enough to make it work.

Regards,

Mike
0
 
LVL 32

Expert Comment

by:it_saige
ID: 40441060
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
 
LVL 32

Expert Comment

by:it_saige
ID: 40441072
No apology needed eghtebas.  I always believe that the additional comments provide a better understanding.  ;)

-saige-
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40441093
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now