I have a simple setup of a Typed DataSet connected to a ObjectDataSource. I am using a middle tier function for my CRUD and filtering. I am experimenting with using LINQ to filter the data.
But quite simply The following code doesnt work:
----
Dim uTable, returnTable
As UserDataTable
uTable = _Adapter.GetData()
Dim results =
From rows In uTable.Rows
Select rows
returnTable = results
----------
But "results" can not be converted to the type UserDataTable.
"results" is of the type Generic.IEnumerable(of Object)
Watching Beth Massi's How-Do-I Video Series, she shows how one can simply call ".AsDataSet" like the following
"returnTable = results.AsDataSet"
But the .AsDataSet method is not avilible to me in my Intellisense.
Im not sure that this is a great use of LINQ to be honest! Whats the point of using LINQ to select the rows when you already have it :
uTable.Rows ? I can only see that its to reduce performance!
and even if you did mean to do this why would you then place it into another table? You have the results as "results", whats the advantage of converting this to a table afterwards?
typedDataTable.Rows is a DataRowCollection and it gives you un-typed datarows. To access the typed data rows you would use typedDataTable(index). Also, if you try to use typedDataTable.AsEnumerable to query via LINQ, you will also end up with un-typed datarows.
Note that you cannot cast typedDataTable.AsEnumerable to IEnumerable(Of TypedDataRow). You also cannot cast typedDataTable to IEnumerable(Of TypedDataRow).
Using LINQ to query a DataTable is in many cases, much faster than using DataTable.Select to query. I have timed both methods and I have always gained significant performance changing DataTable.Select to use LINQ instead. Try it for yourself!
If you wish to query over typed datarows then you could write a generic extension method called AsTypedEnumerable where T : DataTable and U : DataRow.
What you should do to acheive this is loop over the rows in the typedDataTable and add them to a List(Of TypedDataRow) and then return the List. The return type on your method would be IEnumerable(Of TypedDataRow).
Here is an example of using such a method:
Dim rows as List(Of MyTypedRow) = (From row as MyTypedRow In typedDataTable.AsTypedEnumerable
Where row.Name.ToLower().StartsWith("a")
Select row).ToList
The harder problem, as you mentioned, is returning a typed datatable after the query. This is something that I may take a stab at when I get time. If I can get it to work I'll post it here. Also I can post a AsTypedEnumerable extension method when I get the
chance.
Dim vdex As Integer = 0
For Each v In values
Dim dr As DataRow = dt.NewRow()
dr(0) = values.Item(vdex).CID
dr(1) = values.Item(vdex).Code
dr(2) = values.Item(vdex).Mod
dr(3) = values.Item(vdex).Mod2
dr(4) = values.Item(vdex).Status
dr(5) = values.Item(vdex).GovState
dr(6) = values.Item(vdex).AreaCode
dr(7) = values.Item(vdex).Description
dr(8) = values.Item(vdex).Fee1
dr(9) = values.Item(vdex).Fee2
dr(10) = values.Item(vdex).Fee3
dr(11) = values.Item(vdex).FileType
dr(12) = values.Item(vdex).DateEff
dr(13) = values.Item(vdex).DateEntered
dr(14) = values.Item(vdex).LastModified
dr(15) = values.Item(vdex).ModVersion
dr(16) = CStr(values.Item(vdex).IsDeleted)
dr(17) = values.Item(vdex).CodeType
dt.Rows.Add(dr)
vdex = vdex + 1
Next
Dim rdex As Integer = 0
For Each row In dt.Rows
DBHelper.AggVersion(dt.Rows(rdex))
DBHelper.AggUpdate(dt.Rows(rdex), F1Chg, F2Chg, F3Chg)
rdex = rdex + 1
Next
---------------------------------
Public Shared Function Linq1(ByVal FT As String, ByVal CT As String, ByVal ST As String, ByVal AC As String, ByVal DE As String) As List(Of tbu_fsc_Fee)
Dim dc As New FSCDataContext
Dim values = (From c In dc.tbu_fsc_Fees _
Where (c.FileType = FT) AndAlso (c.GovState = ST) AndAlso (c.CodeType = CT) AndAlso (c.AreaCode = AC) _
AndAlso (c.DateEff = DE) AndAlso (c.IsDeleted = False) _
Select c).ToList()
Return values
End Function
---------------------
Public Shared Sub AggVersion(ByVal dr As DataRow)
Dim dc As New FSCDataContext
Try
Dim matchedCID = (From c In dc.tbu_fsc_Fees _
Where c.CID = dr.Item(0).ToString _
Select c).SingleOrDefault
JLibertor
Member
100 Points
22 Posts
Casting LINQ as Typed Data Table (using LINQ Results and ObjectDataSource for Filtering)
Dec 17, 2007 08:21 PM|LINK
I have a simple setup of a Typed DataSet connected to a ObjectDataSource. I am using a middle tier function for my CRUD and filtering. I am experimenting with using LINQ to filter the data.
But quite simply The following code doesnt work:
----
Dim uTable, returnTable As UserDataTableuTable = _Adapter.GetData()
Dim results = From rows In uTable.Rows Select rowsreturnTable = results
----------
But "results" can not be converted to the type UserDataTable.
"results" is of the type Generic.IEnumerable(of Object)
Watching Beth Massi's How-Do-I Video Series, she shows how one can simply call ".AsDataSet" like the following
"returnTable = results.AsDataSet"
But the .AsDataSet method is not avilible to me in my Intellisense.
linq objecttdatasource
DavidKiff
Star
8660 Points
1733 Posts
Re: Casting LINQ as Typed Data Table (using LINQ Results and ObjectDataSource for Filtering)
Dec 17, 2007 10:48 PM|LINK
Im not sure that this is a great use of LINQ to be honest! Whats the point of using LINQ to select the rows when you already have it :
uTable.Rows ? I can only see that its to reduce performance!
Visit my site
Follow me on Twitter
DavidKiff
Star
8660 Points
1733 Posts
Re: Casting LINQ as Typed Data Table (using LINQ Results and ObjectDataSource for Filtering)
Dec 17, 2007 10:50 PM|LINK
and even if you did mean to do this why would you then place it into another table? You have the results as "results", whats the advantage of converting this to a table afterwards?
Visit my site
Follow me on Twitter
JLibertor
Member
100 Points
22 Posts
Re: Casting LINQ as Typed Data Table (using LINQ Results and ObjectDataSource for Filtering)
Dec 18, 2007 04:58 PM|LINK
OMG If you want to post a "I don't think that's a good idea anyway" reply, at least add something to help the situation!
The code I have posted is a simplification of what I am trying to do. It illustrates the problem in as simple terms as possible.
Do you really want me to post hundreds of lines of classes, page-behind and interactions so you can sift through and approve them before you answer?
TutanRamen
Member
2 Points
2 Posts
Re: Casting LINQ as Typed Data Table (using LINQ Results and ObjectDataSource for Filtering)
Apr 25, 2012 03:29 PM|LINK
typedDataTable.Rows is a DataRowCollection and it gives you un-typed datarows. To access the typed data rows you would use typedDataTable(index). Also, if you try to use typedDataTable.AsEnumerable to query via LINQ, you will also end up with un-typed datarows. Note that you cannot cast typedDataTable.AsEnumerable to IEnumerable(Of TypedDataRow). You also cannot cast typedDataTable to IEnumerable(Of TypedDataRow).
Using LINQ to query a DataTable is in many cases, much faster than using DataTable.Select to query. I have timed both methods and I have always gained significant performance changing DataTable.Select to use LINQ instead. Try it for yourself!
If you wish to query over typed datarows then you could write a generic extension method called AsTypedEnumerable where T : DataTable and U : DataRow.
What you should do to acheive this is loop over the rows in the typedDataTable and add them to a List(Of TypedDataRow) and then return the List. The return type on your method would be IEnumerable(Of TypedDataRow).
Here is an example of using such a method:
Dim rows as List(Of MyTypedRow) = (From row as MyTypedRow In typedDataTable.AsTypedEnumerable
Where row.Name.ToLower().StartsWith("a")
Select row).ToList
The harder problem, as you mentioned, is returning a typed datatable after the query. This is something that I may take a stab at when I get time. If I can get it to work I'll post it here. Also I can post a AsTypedEnumerable extension method when I get the chance.
TheHoboLord
Member
12 Points
6 Posts
Re: Casting LINQ as Typed Data Table (using LINQ Results and ObjectDataSource for Filtering)
Mar 15, 2013 08:45 PM|LINK
Honestly, I have been working on linq to datable for a few days now.
This madness is what I got.....
So ... this is to allow deleting without actually deleting the record to ensure I have the raw data still there.
Oh and this is for and aggregate function on the fees.
Hope This Helps!
Dim dt As New DataTable
Dim values As System.Collections.Generic.List(Of tbu_fsc_Fee)
values = DBHelper.Linq1(DropDownList2.Text, DropDownList4.Text, ST.Substring(0, 2), ST.Substring(3, 3), Calendar1.SelectedDate)
dt.Columns.Add(New System.Data.DataColumn("CID", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("Code", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("Mod", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("Mod2", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("Status", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("GovState", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("AreaCode", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("Description", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("Fee1", GetType(Decimal)))
dt.Columns.Add(New System.Data.DataColumn("Fee2", GetType(Decimal)))
dt.Columns.Add(New System.Data.DataColumn("Fee3", GetType(Decimal)))
dt.Columns.Add(New System.Data.DataColumn("FileType", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("DateEff", GetType(Date)))
dt.Columns.Add(New System.Data.DataColumn("DateEntered", GetType(Date)))
dt.Columns.Add(New System.Data.DataColumn("LastModified", GetType(Date)))
dt.Columns.Add(New System.Data.DataColumn("ModVersion", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("IsDeleted", GetType(String)))
dt.Columns.Add(New System.Data.DataColumn("CodeType", GetType(String)))
Dim vdex As Integer = 0
For Each v In values
Dim dr As DataRow = dt.NewRow()
dr(0) = values.Item(vdex).CID
dr(1) = values.Item(vdex).Code
dr(2) = values.Item(vdex).Mod
dr(3) = values.Item(vdex).Mod2
dr(4) = values.Item(vdex).Status
dr(5) = values.Item(vdex).GovState
dr(6) = values.Item(vdex).AreaCode
dr(7) = values.Item(vdex).Description
dr(8) = values.Item(vdex).Fee1
dr(9) = values.Item(vdex).Fee2
dr(10) = values.Item(vdex).Fee3
dr(11) = values.Item(vdex).FileType
dr(12) = values.Item(vdex).DateEff
dr(13) = values.Item(vdex).DateEntered
dr(14) = values.Item(vdex).LastModified
dr(15) = values.Item(vdex).ModVersion
dr(16) = CStr(values.Item(vdex).IsDeleted)
dr(17) = values.Item(vdex).CodeType
dt.Rows.Add(dr)
vdex = vdex + 1
Next
Dim rdex As Integer = 0
For Each row In dt.Rows
DBHelper.AggVersion(dt.Rows(rdex))
DBHelper.AggUpdate(dt.Rows(rdex), F1Chg, F2Chg, F3Chg)
rdex = rdex + 1
Next
---------------------------------
Public Shared Function Linq1(ByVal FT As String, ByVal CT As String, ByVal ST As String, ByVal AC As String, ByVal DE As String) As List(Of tbu_fsc_Fee)
Dim dc As New FSCDataContext
Dim values = (From c In dc.tbu_fsc_Fees _
Where (c.FileType = FT) AndAlso (c.GovState = ST) AndAlso (c.CodeType = CT) AndAlso (c.AreaCode = AC) _
AndAlso (c.DateEff = DE) AndAlso (c.IsDeleted = False) _
Select c).ToList()
Return values
End Function
---------------------
Public Shared Sub AggVersion(ByVal dr As DataRow)
Dim dc As New FSCDataContext
Try
Dim matchedCID = (From c In dc.tbu_fsc_Fees _
Where c.CID = dr.Item(0).ToString _
Select c).SingleOrDefault
If Not matchedCID Is Nothing Then
matchedCID.LastModified = Date.Now
matchedCID.IsDeleted = True
dc.SubmitChanges()
End If
Catch ex As Exception
Throw ex
End Try
End Sub
-------------------------
Public Shared Sub AggUpdate(ByVal dr As DataRow, ByVal Agg1 As String, ByVal Agg2 As String, ByVal Agg3 As String)
Dim dc As New FSCDataContext()
Try
Dim Btable As Table(Of tbu_fsc_Fee) = DBHelper.GetFeeTable()
Dim tab As New tbu_fsc_Fee With {
.Code = dr.Item(1), _
.Mod = dr.Item(2), _
.Mod2 = dr.Item(3), _
.Status = dr.Item(4), _
.GovState = dr.Item(5), _
.AreaCode = dr.Item(6), _
.Description = dr.Item(7), _
.Fee1 = (CDec(dr.Item(8)) * CDec(Agg1)), _
.Fee2 = (CDec(dr.Item(9)) * CDec(Agg2)), _
.Fee3 = (CDec(dr.Item(10)) * CDec(Agg3)), _
.FileType = dr.Item(11), _
.DateEff = dr.Item(12), _
.DateEntered = dr.Item(13), _
.LastModified = Date.Now, _
.CodeType = dr.Item(17), _
.ModVersion = (dr.Item(15) + 1), _
.IsDeleted = False}
Btable.InsertOnSubmit(tab)
Btable.Context.SubmitChanges()
Catch ex As Exception
Throw ex
End Try
End Sub
objecttdatasource