Casting/ formatting date in LINQ

Following code is populating dgv shown below. This part works okay. OrderDate column needs to be revised to display the first row "n/a" as string and the rest as d/m/yyyy.

To do this, I can change ...  0, .OrderDate = "1/1/2014"} to ...  0, .OrderDate = "n/a"} to fix the first row, but I do not know how to handle line 9 to cast the dates as string like "1/15/2006".

Question: How casting or formatting in LINQ is done?

    Private Sub DisplayAllOrder()

        Dim Os As List(Of initialRowOrder) = (From o In db.tblOrders Join c In db.tblCustomers On c.CustomerID Equals o.CustomerID _
                                      Order By c.FirstName & " " & c.LastName _
                                      Select New initialRowOrder With _
                                      { _
                                          .CustName = c.FirstName & " " & c.LastName, _
                                          .OrderID = o.OrderID, _
                                         .OrderDate = o.OrderDate _
                                      }).ToList()

        Dim initialRowOrder As New initialRowOrder() With {.CustName = "- all -", .OrderID = 0, .OrderDate = "1/1/2014"}
        Os.Insert(0, initialRowOrder)

        'join item in db.B on p.CardID equals item.CardID
        If Os.Count > 0 Then
            dgOrders.DataSource = Os.ToList
            lblNoRecords2.Visible = False
        Else
            dgOrders.DataSource = Nothing
            lblNoRecords2.Visible = True
        End If

    End Sub
    Public Class initialRowOrder
        Public Property CustName As String
        Public Property OrderID As Integer
        Public Property OrderDate As String
    End Class

Open in new window

DGV1.png
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.

Ashutosh VyasFounder, InitQubeCommented:
Select New initialRowOrder With _
                                      { _
                                          .CustName = c.FirstName & " " & c.LastName, _
                                          .OrderID = o.OrderID, _
                                         .OrderDate = o.OrderDate.ToString("M/d/yyyy") _
                                      }).ToList()

Open in new window

0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
o.OrderDate.ToString("M/d/yyyy") results in an error:
LINQ to Entity does not recognizes the method 'System.String ToString(System.String)' method, and yhis method cannot br translated into a store expression.
0
Fernando SotoRetiredCommented:
Hi eghtebas;

Leave the Linq query as written in the question and change your initialRowOrder class as shown below.

Public Class initialRowOrder
    Public Property CustName As String
    Public Property OrderID As Integer

    Dim _orderDate As String
    Public Property OrderDate As String
        Get
            Return _orderDate
        End Get
        Set(value As String)
            _orderDate = DateTime.Parse(value).ToString("M/d/yyyy")
        End Set
    End Property
End Class

Open in new window


If this does not work for you please post the version of Entity Framework you are using.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
This works when I keep .OrderDate = "1/1/11"}, but when I change it to: .OrderDate = "n/a"} I get an error:
The string was not recognized as a valid Date Time. There is an unknown word starting at index 0.

BTW, I have Entity Framework 6.1.1
 
 Private Sub DisplayAllOrder()

        Try

            Dim Os As List(Of initialRowOrder) = (From o In db.tblOrders Join c In db.tblCustomers On c.CustomerID Equals o.CustomerID _
                                          Order By c.FirstName & " " & c.LastName _
                                          Select New initialRowOrder With _
                                          { _
                                              .CustName = c.FirstName & " " & c.LastName, _
                                              .OrderID = o.OrderID, _
                                              .OrderDate = o.OrderDate _
                                          }).ToList()

            Dim initialRowOrder As New initialRowOrder() With {.CustName = "- all -", .OrderID = 0, .OrderDate = "1/1/11"}
            Os.Insert(0, initialRowOrder)

            If Os.Count > 0 Then
                dgOrders.DataSource = Os.ToList
                lblNoRecords2.Visible = False
            Else
                dgOrders.DataSource = Nothing
                lblNoRecords2.Visible = True
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try

    End Sub
    'Public Class initialRowOrder
    '    Public Property CustName As String
    '    Public Property OrderID As Integer
    '    Public Property OrderDate As String
    'End Class
    Public Class initialRowOrder
        Public Property CustName As String
        Public Property OrderID As Integer

        Dim _orderDate As String
        Public Property OrderDate As String
            Get
                Return _orderDate
            End Get
            Set(value As String)
                _orderDate = DateTime.Parse(value).ToString("M/d/yyyy")
            End Set
        End Property
    End Class

Open in new window

0
it_saigeDeveloperCommented:
You could do this within your Linq statement as follows:
Dim tempDate As DateTime
Dim Os As List(Of initialRowOrder) = (From o In db.tblOrders Join c In db.tblCustomers On c.CustomerID Equals o.CustomerID _
  Order By c.FirstName & " " & c.LastName _
  Select New initialRowOrder With _
    { _
	.CustName = c.FirstName & " " & c.LastName, _
	.OrderID = o.OrderID, _
	.OrderDate = If(DateTime.TryParseExact(o.OrderDate, "MMM dd yyyy hh:mmtt", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, tempDate), tempDate.ToString("M/dd/yyyy"), "n/a") _
    }).ToList()

Dim initialRowOrder As New initialRowOrder() With {.CustName = "- all -", .OrderID = 0, .OrderDate = "1/1/2014"}
Os.Insert(0, initialRowOrder)

Open in new window


-saige-
0
Fernando SotoRetiredCommented:
Change the initialRowOrder class as follows.

Public Class initialRowOrder
    Public Property CustName As String
    Public Property OrderID As Integer

    Dim _orderDate As String
    Public Property OrderDate As String
        Get
            Return _orderDate
        End Get
        Set(value As String)
            Dim dt As DateTime
            If DateTime.TryParse(value, dt) Then
                _orderDate = dt.ToString("M/d/yyyy")
            Else
                '' String was not in DateTime format so assign it na
                _orderDate = "n/a"
            End If

        End Set
    End Property
End Class

Open in new window

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
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Before I try Fernando's last post...

Saige,

I got an error using:
  Private Sub DisplayAllOrder()
        Dim tempDate As DateTime

        Try
            Dim Os As List(Of initialRowOrder) = (From o In db.tblOrders Join c In db.tblCustomers On c.CustomerID Equals o.CustomerID _
              Order By c.FirstName & " " & c.LastName _
              Select New initialRowOrder With _
                { _
                .CustName = c.FirstName & " " & c.LastName, _
                .OrderID = o.OrderID, _
                .OrderDate = If(DateTime.TryParseExact(o.OrderDate, "MMM dd yyyy hh:mmtt", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, tempDate), tempDate.ToString("M/dd/yyyy"), "n/a") _
                }).ToList()

            Dim initialRowOrder As New initialRowOrder() With {.CustName = "- all -", .OrderID = 0, .OrderDate = "n/a"}
            Os.Insert(0, initialRowOrder)

            If Os.Count > 0 Then
                dgOrders.DataSource = Os.ToList
                lblNoRecords2.Visible = False
            Else
                dgOrders.DataSource = Nothing
                lblNoRecords2.Visible = True
            End If


        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        End Try

    End Sub

Open in new window

ef2
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you very much.
0
Fernando SotoRetiredCommented:
Not a problem, glad I was able to help.
0
it_saigeDeveloperCommented:
Whether you use Fernando's method (which should work now that he has recommended a tryparse) or mine next implementation (which is going to pull the TryParseExact method out and replace it with a call to a function).  You will receive the same results.

Here is the function call that I was referring to:
Private Sub DisplayAllOrder()
	Try
		Dim Os As List(Of initialRowOrder) = (From o In db.tblOrders Join c In db.tblCustomers On c.CustomerID Equals o.CustomerID _
			Order By c.FirstName & " " & c.LastName _
			Select New initialRowOrder With _
			{ _
				.CustName = c.FirstName & " " & c.LastName, _
				.OrderID = o.OrderID, _
				.OrderDate = ParseOrderDate(o.OrderDate) _
			}).ToList()

		Dim initialRowOrder As New initialRowOrder() With {.CustName = "- all -", .OrderID = 0, .OrderDate = "n/a"}
		Os.Insert(0, initialRowOrder)

		If Os.Count > 0 Then
			dgOrders.DataSource = Os.ToList
			lblNoRecords2.Visible = False
		Else
			dgOrders.DataSource = Nothing
			lblNoRecords2.Visible = True
		End If
	Catch ex As Exception
		MessageBox.Show(ex.Message.ToString())
	End Try
End Sub

Public Function ParseOrderDate(ByVal orderDate As String) As String
	Dim tempDate As DateTime
	Return If(DateTime.TryParseExact(orderDate, "MMM dd yyyy hh:mmtt", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, tempDate), tempDate.ToString("M/dd/yyyy"), "n/a")
End Function

Open in new window


-saige-
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Your posts are very valuable study material. This comment is for both Fernando and It_Saige.

Thanks,

Mike
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.