?
Solved

Casting/ formatting date in LINQ

Posted on 2014-12-01
11
Medium Priority
?
194 Views
Last Modified: 2016-02-15
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
0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 8

Expert Comment

by:Ashutosh Vyas
ID: 40475720
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40476088
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40476238
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40476319
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
 
LVL 34

Expert Comment

by:it_saige
ID: 40476365
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
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 40476382
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40476425
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
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40476435
Thank you very much.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40476447
Not a problem, glad I was able to help.
0
 
LVL 34

Expert Comment

by:it_saige
ID: 40476461
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40476466
Your posts are very valuable study material. This comment is for both Fernando and It_Saige.

Thanks,

Mike
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

762 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