How to pass array of column names to linq query

Question

Is it possible to pass the array pf column names to LINQ ? code same is as below. Here instead of specifying column names like "FirstName", "LastName" i would like to pass array with all the required column names. Is that possible?

public static void test(System.Data.DataTable dt, string[] columns)
{
    var dt2 = dt.AsEnumerable().Select(row => new
    {
        FirstName = row.Field<string>("FirstName"),
        Title = row.Field<string>("LastName")
    })
    .Distinct().ToList();
}

is it possible to write something like below?

public static void test(System.Data.DataTable dt, string[] columns)
{
    var dt2 = dt.AsEnumerable().Select(row => new
    {
        //**columns go here**
    })
    .Distinct().ToList();
}

Thank you.

Sure but you can't use an anonymous type because that has to be fixed at compile time (without a lot of work) but if you use another collection type (e.g. `Dictionary` or `List`) it is possible. — NetMage, Feb 04 '21 at 00:56
The actual question is "Why?" Where is the array of columns coming from? Why do you want something like an anonymous class? What do you expect the call to look like? — Jeremy Lakeman, Feb 04 '21 at 04:25
I have a use case where i need to export to excel only certain number of columns from DataTable. This is a legacy code . So instead of writing new Stored Procedure to retrieve the only few columns, I am using the above approach to get DataTable and and filtering results by passing columns list required for export. DataTable will have around 40 columns. But for export to excel i will be using only 20 columns. Earlier code used Datatable.DataView.ToDataTable(true, Columns[]). But this takes lot of time(in minutes) as the rows grow. Hence moving to LINQ. — GAP, Feb 05 '21 at 05:17

score 0 · Answer 1 · answered Feb 04 '21 at 01:28

As @NetMage pointed out in comments, anonymous objects are compile-time feature. So you might benefit from Dictionary of some sort.

One way to make it look somewhat more readable is to consider using ExpandoObject and dynamic for that:

public static List<ExpandoObject> test(System.Data.DataTable dt, string[] columns)
{
    return dt.AsEnumerable()
    .Distinct()
    .Select(row => {
        ExpandoObject expandoObj = new ExpandoObject();
        foreach (var colName in columns) {
            // ExpandoObject is compatible with the IDictionary interface, so we'd use that to populate
            ((IDictionary<string, object>)expandoObj).Add(colName, row.Field<string>(colName));
        }
        return expandoObj;
    }).ToList();
}

then you'd use dynamic to walk through properties as you see fit:

var rows = test(new DataTable(), new string[] { "FirstName", "LastName" });
foreach (dynamic row in rows)
{
    // note how we don't have to write dictionary accessors here.
    Console.WriteLine(row.FirstName); 
    Console.WriteLine(row.LastName);
}

Be however aware of performance implications and make a judgment call for your own use case. Here is a good summary of when to NOT use it, see if that applies to your case

Thank you. this will work for my use case. And tested for performance and it looks good. — GAP, Feb 05 '21 at 05:09

score 0 · Answer 2 · answered Feb 04 '21 at 01:29

0

How about this?
in this case you can't use intellisense...

        var dt2 = dt.AsEnumerable().Select(row =>
        {
            dynamic tmp = new ExpandoObject();
            IDictionary<string, object> wk = tmp;
            foreach (DataColumn item in row.Table.Columns)
            {
                wk.Add(item.ColumnName, row[item.ColumnName]);
            }

            return tmp;
        })
        .Distinct().ToList();

answered Feb 04 '21 at 01:29

るみねこ

1

Thank you for the code. this also solves the issue. used the one mentioned by Timur. – GAP Feb 05 '21 at 05:10

How to pass array of column names to linq query

2 Answers2