how to write LINQ Query having multiple values from one Column?

Question

I need to find data from data table with multiple values from one column only.

Below is an example:

 var Value = (from r in datatable.AsEnumerable()
             where r.Field<string>(ColumnName).Contains("test1,test2")
             select r ).ToList();

Here ColumnName is my Datatable's column name and "test1, test2" are different values ( number of values are dynamic and have "," as delimiter ).

I want to make this Linq query which returns all records from datatable which have values equal to "test1" and "test2".

Edit : Above LINQ Query returns result like below SQL Query. Select * from Table where ColumnName in ('test1','test2')

Why use Linq here at all? Total overkill for such a simple task. — sloth, Nov 05 '13 at 08:51

score 3 · Accepted Answer · edited May 23 '17 at 12:31

Use Split + Join:

IEnumerable<string> values = "test1,test2".Split(',');
var matchingRows = from row in datatable.AsEnumerable()
                   join value in values
                   on row.Field<string>(ColumnName) equals value
                   select row;
DataTable tblResult = matchingRows.CopyToDataTable(); // or ToList

For the sake of completeness, the less efficient but more natural approach using Contains:

var matchingRows = from row in datatable.AsEnumerable()
                   where values.Contains(row.Field<string>(ColumnName))
                   select row;

score 1 · Answer 2 · answered Nov 05 '13 at 08:47

Build a list :

var filters = new [] {"test1", "test2"};

var Value = (from r in datatable.AsEnumerable()
             where filters.Contains(r.Field<string>(ColumnName))
             select r ).ToList();

This will be translate to what you want

Select * 
from Table 
where ColumnName in ('test1','test2')

score 0 · Answer 3 · answered Nov 05 '13 at 08:47

0

Try this :

var searchParameters = new []{ "test1", "test2" }; // or "test1,test2".Split(",")
var Value = (from r in datatable.AsEnumerable()
         where searchParameters.All(s => r.Field<string>(ColumnName).Contains(s))
         select r).ToList();

answered Nov 05 '13 at 08:47

Réda Mattar

4,193
1
16
19

score 0 · Answer 4 · answered Nov 05 '13 at 08:47

0

try this:

var Value = (from r in datatable.AsEnumerable()
                 where r.Field<string>(ColumnName).Contains("test1").Contains("test2")
                 select r ).ToList();

answered Nov 05 '13 at 08:47

Arnold

92
1
3

This sounds like it would cause alot of overhead and be very slow. – Oxymoron Oct 07 '14 at 22:16

score 0 · Answer 5 · answered Nov 05 '13 at 08:48

The clean and easy way would be so use Select.

Example:

var dt = new DataTable();
dt.Columns.Add("col1", typeof(string));
dt.Rows.Add(new object[] {"test1"});
dt.Rows.Add(new object[] {"test2"});
dt.Rows.Add(new object[] {"test3"});
dt.Rows.Add(new object[] {"test4"});

// Select * from Table where ColumnName in ('test1','test2')
var rows = dt.Select("col1 in ('test1','test2')");

rows is now:

enter image description here

score 0 · Answer 6 · answered Nov 05 '13 at 08:54

You can you or statement ' || '

 var Value = (from r in datatable.AsEnumerable()
                             where r.Field<string>(ColumnName).Contains(value1) || r.Field<string>(ColumnNamedatatable).Contains(value2)
                             select r).ToList();

score 0 · Answer 7 · answered Nov 05 '13 at 09:09

0

datatable.where(r=>{"test1","test2"}.Contains(r.ColumnName))

answered Nov 05 '13 at 09:09

invictus

95
2
8

how to write LINQ Query having multiple values from one Column?

7 Answers7