Linq: select where in List

Question

Struggeling with some LinqToExcel filtering here...

Ive got a List<string> columnsToFilter that contains 9 strings, and with that i want to filter out the data for certain columns in a List<Row>, where Row contains the properties

IEnumerable<string> ColumnNames
Cell this[string columnName]

So: List<Row> has say 30 rows, each having 12 ColumnNames. Now i want to filter that List<Row> using List<string> columnsToFilter so that i end up with a List<Row> of 30 rows and 9 ColumnNames.

I can select the data for one column by quering the columnname:

var result = content.Select(m => m["Column1"]).ToList();

Now i want to filter the data based on a List of strings List<string> columnsToFilter. Whats the best way to achieve that?

Kinda not sure what you're asking. You're asking how to 'filter', but then you use 'filter' when you're talking about `Select`, which is generally considered a 'projection' but not a 'filter'. 'Filters' are typically functions like `Where`, or `Count`, `First`, `Single` to do filtering. — sircodesalot, Oct 02 '13 at 14:11
youre right, i meant select, ive updated the question. i can select the data for one column using a string, but i want to select the data for multiple columns using a list of strings — stefjnl, Oct 02 '13 at 14:22
from here http://stackoverflow.com/questions/6772267/linq-syntax-selecting-multiple-columns it looks like you can just use || — Matt Bodily, Oct 02 '13 at 14:52
yes, but then you have to know how many items are in the 'List' and that can vary as well... — stefjnl, Oct 02 '13 at 15:01
I think what you're trying to say is that you want to build a dynamic select statement based on the contents of a list. I'd check out this question: http://stackoverflow.com/questions/606104/how-to-create-linq-expression-tree-with-anonymous-type-in-it for a hint on how to do this. — Kittoes0124, Oct 02 '13 at 16:34

score 4 · Answer 1 · answered Oct 02 '13 at 16:34

4

Is this what you are looking for?

var colnames = new List<string>();
var rows = new Dictionary<string, object>();
var result = rows.Where(kv => colnames.Contains(kv.Key)).Select(kv => kv.Value);

answered Oct 02 '13 at 16:34

Dweeberly

4,668
2
22
41

Thats more or less direction i wanna go but in my case, rows is not a dictionary but an IEnumerable, where Row has the above mentioned properties. So, colnames.Contains(kv.Key) doesnt work. In my case it's colnames.Contains(kv.ColumnNames) where ColumnNames is an IEnumerable. All i want is to do is using the IEnumerable ColumnNames property on the IEnumerable object to select only the rows that match against the colnames List – stefjnl Oct 03 '13 at 08:26

Robert Tanenbaum · Answer 2 · 2013-10-08T16:55:18.917

Define an object called MyObject which has the property names corresponding to the 9 columns that you want to select.

var excel = new ExcelQueryFactory("excelFileName");
var myObjects = from c in excel.Worksheet<MyObject>()
                       select c;

Bingo. You can now iterate through the 30 objects with the 9 columns as properties. Remember that LinqToExcel will happily fill objects even if they don't have all the columns represented.

You could even have a property or method as part of MyObject called "row" that would be a Dictionary() object so you could say myObject.row["ColumnName"] to reference a value if you preferred that syntax to just saying myObject.ColumnName to get the value. Personally I would rather deal with actual properties than to use the dictionary convolution.

I didn't know that you could map an object to _a selection_ of the excelfile, thanks for that! Unfortunately, `excel.GetColumnNames("sheettitle")` is of no use for me (renders mostly F10, F12 etc as columnnames). So if there is a way to map myObject.row to a column index or something, that would be great. — stefjnl, Oct 10 '13 at 14:05

stefjnl · Accepted Answer · 2013-10-10T14:08:16.117

I ended up doing this in two steps:

        foreach (var column in columnNumbers)
        {
            yield return data.Select(m => m[column].Value.ToString()).ToList();
        }

Now I have the data I need, but with the rows and columns swapped, so i had to swap rows for columns and vice versa:

        for (int i = 1; i < rowCount; i++)
        {
            var newRow = new List<string>();

            foreach (var cell in list)
            {
                newRow.Add(cell[i]);
            }

            yield return newRow;
        }

Linq: select where in List

3 Answers3