Solved

Casting/ formatting date in LINQ

Posted on 2014-12-01
11
178 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 32

Expert Comment

by:it_saige
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you very much.
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Not a problem, glad I was able to help.
0
 
LVL 32

Expert Comment

by:it_saige
Comment Utility
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
Comment Utility
Your posts are very valuable study material. This comment is for both Fernando and It_Saige.

Thanks,

Mike
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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