Solved

Casting/ formatting date in LINQ

Posted on 2014-12-01
11
185 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 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

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

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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