CODEWITHSUNDEEP
vb.netlinq
user1263981
user1263981

Reputation: 3157

return Datatable from linq query

I am trying to return data table from linq query but getting error message. I am using .net framework 4.0 in VS2012.

<Table>
Public Class StaffOfficeAccess

<Column(CanBeNull:=False, IsPrimaryKey:=True, IsDbGenerated:=True)>
Public Property StaffOfficeAccessID As Int32 = 0
<Column>
Public Property StaffID As Int32 = 0
<Column>
Public Property OfficeID As Int32 = 0
<Column(IsVersion:=True, IsDbGenerated:=True)>
Public Property Version As Byte()
End Class

'----------------------------'

Public Function GetByStaffID(ByVal staffID As Int32) As DataTable

    Dim query As IEnumerable(Of DataRow) = CType((From oa In db.StaffOfficeAccess.AsEnumerable() _
            Join o In db.Office.AsEnumerable() On oa.OfficeID Equals o.OfficeID _
            Select oa.OfficeID,
                 o.OfficeName), Global.System.Collections.Generic.IEnumerable(Of Global.System.Data.DataRow))

    Dim table As DataTable = System.Data.DataTableExtensions.CopyToDataTable(query)

    Return table
End Function

'-------error----------'

Unable to cast object of type 'd__614[staff.Objects.StaffOfficeAccess,AMIS.Objects.Office,System.Int32,VB$AnonymousType_02[System.Int32,System.String]]' to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

I have tried the example here https://msdn.microsoft.com/en-us/library/bb396189%28v=vs.110%29.aspx but no luck. I don't get CopyToDataTable in VS2012.

Upvotes: 1

Views: 2052

Answers (2)

Fabio
Fabio

Reputation: 32453

Just another approach by using Aggregate extension method MSDN

Dim dt As New DataTable()
dt.Columns.Add("OfficeID", GetType(Integer))
dt.Columns.Add("OfficeName", GetType(String))

Dim query = From oa In db.StaffOfficeAccess.AsEnumerable()
            Join o In db.Office.AsEnumerable() 
            On oa.OfficeID Equals o.OfficeID
            Select oa.OfficeID, o.OfficeName

query.Aggregate(Of DataTable)(dt, 
                              Function(dtb, o)
                                  Dim dr As DataRow = dtb.NewRow()
                                  dr.SetField("OfficeID", o.OfficeID)
                                  dr.SetField("OfficeName", o.OfficeName)
                                  dtb.Rows.Add(dr)
                                  Return dtb
                              End Function)

Upvotes: 1

user1263981
user1263981

Reputation: 3157

I have tried this and it works.

    Dim dt As New DataTable()
    dt.Columns.Add("OfficeID", GetType(Integer))
    dt.Columns.Add("OfficeName", GetType(String))


    Dim query = From oa In db.StaffOfficeAccess.AsEnumerable() _
                   Join o In db.Office.AsEnumerable() On oa.OfficeID Equals o.OfficeID _
                   Select oa.OfficeID,
                        o.OfficeName

    For Each item In query
        Dim dr As DataRow = dt.NewRow()
        dr("OfficeID") = item.OfficeID
        dr("OfficeName") = item.OfficeName
        dt.Rows.Add(dr)
    Next

Upvotes: 0

Related Questions