Member Avatar for Member #1104501

Hello,

I have spent a good long time looking at solutions on the web and can't seem to find any solution to fit my situation.

What I'm trying to do is grab 2 specific columns in my datatable with LINQ.

Here's a couple things that I have tried:

var query = dt.ASEnumerable().SELECT(s=>s.Field<int>("tablename")); - query returns no results. It says that the specified cast is not valid.

I have also tried many "from,select" queries and they yield no results either. I don't need to include any where clauses.

Would anyone happen to have any insight into how to get a result from LINQ?

  • 1 Contributor
  • 4 Replies
  • 1K Views
  • 34 Minutes Discussion Span
  • Latest Post Latest Post by Member #1104501

Dani AI

Generated

@lithium112 the "Specified cast is not valid" usually means the DataColumn type does not match the generic type you asked for, or the value is DBNull. Since your one-column version works, you can grab two columns by projecting each row into an anonymous object (or tuple) and making the value types nullable to tolerate DBNull.

using System.Data;      // DataRowExtensions / DataTableExtensions
using System.Linq;

var rows = dt.AsEnumerable()
    .Select(r => new
    {
        Id = r.Field<int?>("ID"),        // use int? if the column can be NULL
        Name = r.Field<string>("Name")   // returns null for DBNull automatically
    })
    .ToList();

If you actually want a DataTable with just those two columns (e.g., to bind to a control), let ADO.NET do the trimming:

DataTable twoCols = dt.DefaultView.ToTable(false, "ID", "Name");

Quick checks if it still “returns nothing” or throws:

  • Ensure the column names match exactly, including case and spaces. Your earlier sample used a space in the name, which is fine if it matches the DataColumn.ColumnName.
  • For ints, prefer Field<int?>("ID") or pre-filter: Where(r => r["ID"] != DBNull.Value). A plain Field<int>("ID") will throw on NULLs.
  • Make sure you actually enumerate (e.g., call ToList() or foreach); LINQ is deferred.
  • Verify you referenced System.Data.DataSetExtensions and have using System.Data; using System.Linq; so AsEnumerable() and Field<T>() are in scope.
Member Avatar for Member #1104501

I should also mention that I have tried something like this which also yields no results:

var query = from p in dt.AsEnumerable()
                                           select new
                                           {
                                               ID = p.Field<int>("column name")
                                           };
Member Avatar for Member #1104501

Ok, so 20 minutes after posting I finally found an answer after hours of searching. :) I'll post what I have here so that it may help others who have the same issue.

IEnumerable<string> query = from myrows in datatable.AsEnumerable()
                            select myrows.Field<string>("column_name");
Member Avatar for Member #1104501

The code works for 1 column name. I'm unable to pull 2 column names.

Member Avatar for Member #1104501

For now I think I'm going to go ahead and just use 2 separate queries to pull the 2 columns. It anyone does have any further insight, please share. Always open to learning new techniques.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.