Solved

Casting/ formatting date in LINQ

Posted on 2014-12-01
11
180 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
  • 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 33

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 62

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
 
LVL 33

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 33

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 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 33

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 33

Author Closing Comment

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

Expert Comment

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

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 33

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
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…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

25 Experts available now in Live!

Get 1:1 Help Now