CODEWITHSUNDEEP
vb.netlinq
GDutton
GDutton

Reputation: 176

How to get a count of the items returned by a LINQ query

I am trying to get the count of the items returned by a LINQ query. I would assume it is .Count but apparently not. I am loading a DataTable from Excel then getting the distinct rows based on an identifier. I am now trying to get a count of the items returned. Leases contains the data of whose rows I would like a count of. I tried a few things (now commented out) but have been unsuccessful.

Perhaps a LINQ expert can help me out?

Here is my code:

Dim Leases As Object
Dim dtData As System.Data.DataTable = LoadExcelAsDb(oFile.FullName)
Try
    ' How many leases are there
    Leases = From row In dtData.AsEnumerable() Select row.Field(Of Object)("F2") Distinct
    '    LeaseCt = New System.Linq.SystemCore_EnumerableDebugView(Of Object)(Leases).Items.count 
    '    LeaseCt = New System.Linq.Enumerable(Of Object)(Leases).Items.count
    '    LeaseCt = Leases.Count
Catch ex As Exception
    Debug.WriteLine("Error reading files" + ex.ToString)
End Try

''' <summary>
''' Loads an excel file as a datatable
''' </summary>
''' <param name="sFilename">Filename of excel to be loaded </param>   
''' <returns>Populated Datatable</returns>
Private Function LoadExcelAsDb(sFilename As String) As System.Data.DataTable
    ' Connect to Excel get table
    Dim oConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFilename + " ; Extended Properties=Excel 12.0;")
    Dim oCommand As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", oConnection)

    Dim DtSet As System.Data.DataTable = New System.Data.DataTable
    Try
        oCommand.Fill(DtSet)
    Catch ex As Exception
        Debug.WriteLine("Error reading files" + ex.ToString)
    End Try

    Return DtSet
End Function

Upvotes: 1

Views: 2376

Answers (2)

TnTinMn
TnTinMn

Reputation: 11791

A primary concept that you need to grasp when working with LINQ is that the object being queried must be enumerable. This means that as a minimum it implements the interface System.Collections.IEnumerable. Preferably, the queried object will implement the System.Collections.Generic.IEnumerable(Of T) interface.

in your LINQ statement:

Leases = From row In dtData.AsEnumerable() Select row.Field(Of Object)("F2") Distinct

the queried object is the result dtData.AsEnumerable(). The Datatable extension method AsEnumerable returns a System.Data.EnnumerableRowCollection(Of System.Data.DataRow), so the row variable in the statement is of type DataRow.

The statement selects row.Field(Of Object)("F2"). Since this returns a single object of type System.Object, the query result will be of type System.Collections.Generic.IEnumerable(Of Object). However, you are assigning the result to the variable Leases that you have defined this way:

Dim Leases As Object

There is not much you can do with the variable declared this way. It would be better is you declared it to be the type returned from the query.

Dim Leases As System.Collections.Generic.IEnumerable(Of Object).

Defined this way, the Leases would be extensible by the methods declared in the System.Linq.Enumerable class that would allow calling the Count extension method.

Upvotes: 1

djangojazz
djangojazz

Reputation: 13232

Typically DataTable with ADO.NET and Linq do not play nice with newer well typed structures that are generally something like List(Of (PocoObject)). However typically you can cheat it in my experience with something like:

Sub Main()
   Dim dt As DataTable = New DataTable()
   dt.Columns.Add(New DataColumn("Id", GetType(Integer)))
   Dim row As DataRow = dt.NewRow()
   row("Id") = 1
   dt.Rows.Add(row)
   Dim row2 As DataRow = dt.NewRow()
   row2("Id") = 2
   dt.Rows.Add(row2)

   'Shows 2 rows
    Console.WriteLine(dt.Select().ToList().Count)

    Console.ReadLine()
End Sub

The key is a DataTable HAS NO 'ToList()' extension until you 'Select' stuff from it. DataTable with Linq IMHO is like driving a car the wrong way down a highway. Because nothing is well typed and as an example with some production code I have to get something out of a 'DataSet' I have in a WinForm solution and then just get a particular set of columns out to a POCO Object to make lists I would have to do something like this:

Dim ls = ds.Tables("tProduct").Select().Select(Function(x) New Product With {.ProductID = CInt(x("ProductId")?.ToString), .ProductDesc = x("ProductName")?.ToString}).ToList()

Fun stuff. Notice the double select(), that is not a typo. One is an extension off of System.Data.DataTable and one is from System.Linq.Enumerable. So you need to Select before you can select lol.

Upvotes: 1

Related Questions