Using Linq to count rows in a DataTable based on a field value

// Using Linq To DataSet to count rows based on a filter
// compared to
// Using the plain old Select() method that's been on DataTables since the beginning.
// Learn more about Linq To DataSet here: http://msdn.microsoft.com/en-us/library/bb386977(VS.100).aspx

using System;
using System.Linq;
using System.Data;
using System.Data.Common;

namespace ConsoleApplication
{
 class Program
 {
  static void Main(string[] args)
  {
   // Build up some sample data in a DataTable...
   DataTable dt = new DataTable();
   dt.Columns.Add("nMenuLevel", Type.GetType("System.Int32"));
   dt.Rows.Add(new object[] { 1 });
   dt.Rows.Add(new object[] { 1 });
   dt.Rows.Add(new object[] { 2 });
   dt.Rows.Add(new object[] { 3 });

   // Here's one way using Linq
   int linqCount1 = (from DataRow row in dt.Rows where (int)row["nMenuLevel"] == 1 select row).Count();
   Console.Write("linqCount1 = " + linqCount1.ToString() + "\n\n");

   // Here's another way using Linq
   int linqCount2 = dt.AsEnumerable().Where(c.Field("nMenuLevel") == 2).Count();
   Console.Write("linqCount2 = " + linqCount2.ToString() + "\n\n");

   // Here's a non-Linq way, using the old Select() method thats been available on DataTables
   // since the beginning. It's acutally much shorter and easier to read.
   // But watch out! Notice this Select syntaxt only requires 1 equal sign, not 2.
   int countWithSelect = dt.Select("nMenuLevel = 3").Count();
   Console.Write("countWithSelect = " + countWithSelect.ToString() + "\n\n");
   Console.ReadLine();
   }
 }
}

4 thoughts on “Using Linq to count rows in a DataTable based on a field value”

  1. Hi Matt..just browsed to this web site.
    Notice the Foxpro Logo and also Ruby on Rails

    Am currenlty looking at Web development tools and interested in yuor experience if coming from a Foxpro background

    1. Yes, I have a long FoxPro background and am still active in VFP daily. I have also written apps in Ruby on Rails, ASP.Net WebForms, and ASP.Net MVC. They are all fun to learn, and very capable.

      However, it is a huge jump any time you move from desktop app development to developing web or browser based apps. Get ready to learn lots of new things!

      I suggest you learn the following two (do not mess with WebForms):

      1. Ruby on Rails Since Ruby is a dynamic language, it’s similar to FoxPro in many ways.
      Check out this excellent whitepaper to teach VFP devs about Ruby on Rails: http://www.ita-software.com/papers/Borup_Ruby_published.pdf
      Learn how to install Rails on your Windows machine here: http://railsinstaller.org/
      Read this post about how I got started with Rails: http://mattslay.com/ruby-on-rails-forever/

      2. ASP.Net MVC – After you learn Rails, you will see that ASP.Net MVC is VERY similar. However, the data access and C# strongly typed language is a good different than what you are used to in VFP. I do suggest C# over VB.Net. Also, the Entity Framework 4.1 has made a lot of improvements in the .Net data access area.
      To learn about C# and the fundamentals of the .Net framework from a VFP perspective, I suggest this free e-book: http://foxcentral.net/microsoft/netforvfpdevelopers.htm
      Learn more about ASP.Net MVC: http://www.asp.net/mvc

    1. That is a formatting mistake where I have accidentally pasted in some html code. I will fix it.

      It should read:

      int linqCount2 = dt.AsEnumerable().Where(c.Field("nMenuLevel") == 2).Count();

Leave a Reply

Your email address will not be published. Required fields are marked *