Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 771
  • Last Modified:

In-Memory Query

I have the following code:

            Dim xAll = (From xx In _Context.Vessel_Owners
                        Select xx)

            Dim xSearch1 = (From xx In _Context.Vessel_Owners
                           Where xx.Natregno = txtNatregnoSearch.Text.ToString()
                            Select xx)

            Dim xSearch2 = (From xx In _Context.Vessel_Owners
                          Where xx.Passport_No = txtPassportNoSearch.Text.ToString()
                           Select xx)

            Dim xSearch3 = (From xx In _Context.Vessel_Owners
                           Where xx.Owner_Surname = txtOwnerSurnameSearch.Text.ToString()
                            Select xx)

            Dim xSearch4 = (From xx In _Context.Vessel_Owners
                           Where xx.Owner_Firstname = txtOwnerFirstnameSearch.Text.ToString()
                            Select xx)

            Dim finalQuery = CreateEmptyEnumerable(xAll)

            If Not String.IsNullOrEmpty(txtNatregnoSearch.Text.ToString()) Then

                finalQuery = xSearch1

            End If
            If Not String.IsNullOrEmpty(txtPassportNoSearch.Text.ToString()) Then

                finalQuery = finalQuery.Union(xSearch2)
            End If
            If Not String.IsNullOrEmpty(txtOwnerSurnameSearch.Text.ToString()) Then

                finalQuery = finalQuery.Union(xSearch3)
            End If
            If Not String.IsNullOrEmpty(txtOwnerFirstnameSearch.Text.ToString()) Then

                finalQuery = finalQuery.Union(xSearch4)
            End If

The finalQuery is producing an In-Memory Query which I am trying to transform into a Datatable.  

When I do the following:    Dim dr = TryCast(finalQuery , DataTable)
dr is returning Nothing

How can i get the  finalQuery into a Datatable?
  • 3
  • 2
1 Solution
Alan WarrenCommented:
can you put a break on the line Dim dr = TryCast(finalQuery , DataTable)  and then in the Immediate window: type, ?finalQuery and hit enter.

Would be nice to see if that finalQuery is syntactically correct.

dpdmembersAuthor Commented:
In the Immediate window I get the following:

In-Memory Query
    Results: Expanding will process the collection

When you expand the process you see all the records.
dpdmembersAuthor Commented:
I have a attached a copy of what u see when u moused over the finalQuery
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Fernando SotoCommented:
Hi dpdmembers

The problem you are having is that the type Vessel_Owners can not be converted / cast to a datatable. The reason it is not throwing an error is that the TryCast does not throw an exception if an error happens it will return Nothing when an exception has happened.

In order to convert it to a DataTable you will need to iterate over the result set and get a new row from the DataTable by using DataTableInstance.NewRow() and then filling in the column data. But before starting you will need to create a DataTable with all of its columns defined.
dpdmembersAuthor Commented:
Can u please send me a link with that example in VB preferably.  Thanks
Fernando SotoCommented:
Hi dpdmembers;

Here is some sample code.

' Create the DataTable object
Dim dt = New DataTable()

' Create one column for each column in source object and
' giving it the correct data type
Dim column = New DataColumn("CompanyName", GetType(System.String))
' Add the column to the DataTable
column = New DataColumn("ContactName", GetType(System.String))
column = New DataColumn("Address", GetType(System.String))
column = New DataColumn("City", GetType(System.String))
column = New DataColumn("Country", GetType(System.String))
column = New DataColumn("Phone", GetType(System.String))
column = New DataColumn("Fax", GetType(System.String))

' Linq to SQL data context
Dim ctx = New DataClasses1DataContext()
' Get data
Dim allCustomers = From c In ctx.Customers
                   Select c

' Iterate through the result set and assign to a row and add to DataTable
For Each c As Customer In allCustomers
    Dim row = dt.NewRow()
    row("CompanyName") = c.CompanyName
    row("ContactName") = c.ContactName
    row("Address") = c.Address
    row("City") = c.City
    row("Country") = c.Country
    row("Phone") = c.Phone
    row("Fax") = c.Fax

' Now you can display in Gridview
DataGridView1.DataSource = dt

Open in new window


Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now