CODEWITHSUNDEEP
c#wpflinqdatatableilist
user2779848
user2779848

Reputation: 39

Compare datatable to IList

I have a DataTable, called dt, that has two columns and is populated by reading data from a CSV file. The 2 columns are Keys and their respective Descriptions approximately 7000 rows.

Now I have IList<string> keys, which is nothing but keys (same keys as in the DataTable).

How do I match the IList<string> keys to the DataTable and retrieve a final output as a new DataTable that has only the rows matching with the IList?

Upvotes: 0

Views: 227

Answers (2)

user3505782
user3505782

Reputation: 53

In case of Entity framework:

class DbItem { public int Key { get; set; } public int Value { get; set; } }

var keysInMemory = new List<int>(); // put your Keys here
var values = new StringBuilder();
values.AppendFormat("{0}", keysInMemory[0]);
for (var i = 1; i < keysInMemory.Count; i++)
            values.AppendFormat(", {0}", keysInMemory[i]);

var sql = string.Format(@"SELECT dt.Key Key, dt.Value Value FROM [dbo].[dt] where Key  IN ({0})", values.ToString());

var result = await _dbContext.Database.SqlQuery<DbItem>(sql).ToListAsync();

Note that "Contains" with IQueryable has REALLY BAD performance

Upvotes: 0

Habib
Habib

Reputation: 223362

You can use:

DataTable filtered = dt.AsEnumerable()
                        .Where(r => list.Contains(r.Field<int>("id")))
                        .CopyToDataTable();

You can also create a HashSet<T> and use that in your query. 

List<int> list = new List<int>();
//.... ids in the list

HashSet<int> hashSet = new HashSet<int>(list);
DataTable filtered = dt.AsEnumerable()
                        .Where(r => hashSet.Contains(r.Field<int>("id")))
                        .CopyToDataTable();

Upvotes: 1

Related Questions