COM-Interop and LINQ
COM interoperability has always been possible within C# and .NET, however, it was often less than optimal in how clean and easy the code was to write (or read). Earlier in this chapter, named and optional parameters were introduced, which improve coding against COM objects. And with the additional syntax improvements offered by the dynamic type, the code readability and conciseness is further improved.
To demonstrate how COM-Interop and LINQ might be combined, the following example shows how to use the contents of an Microsoft Excel spreadsheet in a LINQ to Objects query, allowing a query to be written in the following general form:
const int stateCol = 5;var q = from row in GetExcelRowEnumerator(filename, 1) where row[stateCol] == "WA" select row;
This query to successfully return all rows in a spreadsheet matching that shown in Figure 8-2, returning the rows that have the sixth column (index of 5, column index are zero based) equal to the text string WA. Microsoft Excel is often used as the source for importing raw data. Although there are many ways to import this data using code, the ability to run LINQ queries over these spreadsheets directly from C# might be of assistance for solving data quality issues with minimal code.
Figure 8-2: Sample Microsoft Excel spreadsheet to query using LINQ.
The strategy used to implement the Microsoft Excel interoperability and allow LINQ queries over Excel data is
[lb] Adding a COM-Interop reference to Microsoft Excel in order to code against its object model in C#.[lb] Massage and return the data from a chosen spreadsheet (by its file name) into an IEnumerable collection (row by row) that can be used as a source for a LINQ query.