select column from dataset using LINQ

Question

I am absolutely new to linq query, please help with this query, dataset has fields:

Name Value Client_owner.

I want to select Value if name == "searchtext"

DataTable results = (from d in ((DataSet)_MyDataset).Tables["Records"].AsEnumerable()
                     orderby d.Field<string>("Name") ascending
                     where d["Name"].ToString().ToLower().Contains(ProjectName.ToLower())
                     select d??).CopyToDataTable();

Can you clarify this a little bit? Are you trying to return specific columns from your datatable based based on searchtext? — Michael G, Oct 16 '09 at 15:02
yes, I just need the field "value" in the dataset results where name = searchstr. — , Oct 16 '09 at 15:04
and then do this in listitems, but don't know the syntax, please help. foreach (ListItem item in projList) { if (item.Value???IndexOf(results("value)??, numbercomparison??) ) { returnItems.Add(item); } } — , Oct 16 '09 at 15:08

score 4 · Answer 1 · edited Apr 15 '11 at 17:15

4

var query = (from d in _MyDataset.Tables["Records"].AsEnumerable()
                                 where d.Field<String>("Name").Equals("searchText", StringComparison.CurrentCultureIgnoreCase)
                                 select d).OrderBy(x => x.Field<String>("Name"));

if (query.Count() != 0) { DataTable result = query.CopyToDataTable(); }

if _MyDataSet is a DataSet object then the extra cast is not needed in your example.

then create an extension method to create a filtered data table:

public static class DataTableExtensions
{
    public static DataTable ReturnColumn(this DataTable dataTable, ICollection<String> fieldNames)
    {
        if (fieldNames.Count > 0)
        {
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                DataColumn col = dataTable.Columns[i];
                if (!fieldNames.Contains(col.ColumnName))
                {
                    dataTable.Columns.Remove(col);
                    i--;
                }
            }
        }

        return dataTable;
    }
}

you can then filter your datatable like so:

results.ReturnColumn(new List<String> { "Value" });

which will return a data table with only the column "Value"

edited Apr 15 '11 at 17:15

Community

1
1

answered Oct 16 '09 at 14:48

Michael G

6,695
2
41
59

Thanks, but why is is so complicated, is there any way in linq query to select a field? Select value – Oct 16 '09 at 16:14
Please so let me know how to access this field in this stmt: foreach (ListItem item in projList) { if (item.Text.IndexOf(results.Field(value)???.StringComparison.CurrentCultureIgnoreCase) > -1) { returnItems.Add(item); } } – Oct 16 '09 at 16:15
1

I suggest breaking the query down rather than trying to chain too many things together (read up on the Law of Demeter http://en.wikipedia.org/wiki/Law_of_Demeter). The `CopyToDataTable()` call will throw an InvalidOperationException if no rows are returned by the query. Assign the query separately, then `if (query.Count() != 0) { DataTable result = query.CopyToDataTable(); }`. – Ahmad Mageed Oct 16 '09 at 16:37
Sorry, but I don't know how to assign the query separately? Also I just want to select the column "Value", isn't there someting in linq query to select column? Like in SQL Select Name, adress, location from table? – Oct 16 '09 at 17:13

score 0 · Answer 2 · answered Oct 16 '09 at 16:21

0

if you want to return a list of "values" you can do like so:

    IList<String> results = (_MyDataset.Tables["Records"].AsEnumerable().Where(
        d => d.Field<String>("Name").Equals("searchText", StringComparison.CurrentCultureIgnoreCase)).Select(
        d => d.Field<String>("Value")).OrderBy(x => x).ToList<String>());

answered Oct 16 '09 at 16:21

Michael G

6,695
2
41
59

can results be datatable instead of IList? Datatable results = (_MyDataset.Tables["Records"].AsEnumerable().Where( d => d.Field("Name").Equals("searchText", StringComparison.CurrentCultureIgnoreCase)).Select( d => d.Field("Value")).OrderBy(x => x).ToList()); – Oct 16 '09 at 17:14
That's what my first answer does. There isn't a way to do this with just LINQ query; as far as I know. – Michael G Oct 16 '09 at 18:34

score 0 · Answer 3 · answered Oct 16 '09 at 19:01

The easiest way to get what you want it to do something like this:

     DataTable newDataTable = new DataTable("filteredTable");
        Func<DataTable, String[], DataRow, DataRow> NewRow = delegate(DataTable targetTable, String[] columns, DataRow srcRow)
        {
            if (targetTable == null)
                throw new ArgumentNullException("targetTable");
            if (columns == null)
                throw new ArgumentNullException("columns");
            if (srcRow == null) throw new ArgumentNullException("srcRow");

            if (columns.Count() == 0) throw new ArgumentNullException("srcRow");

            DataRow row = targetTable.NewRow();

            foreach (var column in columns)
            {
                if (!targetTable.Columns.Contains(column))
                    targetTable.Columns.Add(column);

                row[column] = srcRow[column];
            }


            return row;
        };

        var query = (from d in _MyDataset.Tables["Records"].AsEnumerable()
                     where d.Field<String>("Name").Equals("searchText", StringComparison.CurrentCultureIgnoreCase)
                     select NewRow(newDataTable, new[] { "Value" }, d));

        if (query.Count() != 0) { DataTable result = query.CopyToDataTable(); }

There is no way to do this with just LINQ

select column from dataset using LINQ

3 Answers3

Linked