Joining LINQ to SQL and LINQ to Excel
A couple of weeks ago, I posted some code to use LINQ to query tables in an Excel spreadsheet. Because you can query databases using LINQ to SQL, you can write code to query a database and do a join with data in an Excel spreadsheet. This can enable some interesting scenarios – let’s say that you have a database that contains some sort of master records, and you’ve downloaded another table and significantly modified it through one means or another. Without using this technique, you would either need to a) upload your modified records into a temporary table, or b) export the master records in some fashion. However, by combining LINQ to SQL with LINQ to Objects in this fashion, you can write some pretty simple C# code that uses LINQ to join data across two separate, disparate data sources.
This blog is inactive.
New blog: EricWhite.com/blog
Blog TOCHere is the code to open the spreadsheet, query a database using LINQ to SQL, and project a collection of an anonymous type that contains data from both data sources:
using (SpreadsheetDocument spreadsheet =
SpreadsheetDocument.Open(filename, false))
{
DataContext db =
new DataContext(@"C:\Users\ericwhit\Documents\NorthwindDatabase\NORTHWND.MDF");
Table<Customer> Customers = db.GetTable<Customer>();
var q1 =
from cust in Customers
where cust.City == "London"
select cust;
var q2 = from c in q1.ToList()
join o in spreadsheet.Table("Order").TableRows() on
c.CustomerID equals (string)o["CustomerID"]
select new
{
OrderID = o["OrderID"],
CustomerID = o["CustomerID"],
CompanyName = c.CompanyName
};
foreach (var order in q2)
Console.WriteLine(order);
}
This code first queries the database for all customers in London. It then joins those records with the records in the Excel table, and projects the collection of anonymous types.
The mechanism for executing the LINQ to SQL query is significantly different from the mechanism to execute the query on the spreadsheet. You are not allowed to do the join directly in the database query:
var q1 =
from cust in Customers
where cust.City == "London"
select cust;
So we do the join in the next query. Note that we have to create a list from the LINQ to SQL query using the ToList extension method:
var q2 = from c in q1.ToList()
join o in spreadsheet.Table("Order").TableRows() on
When run, it produces the following:
{ OrderID = 10355, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10383, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10453, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10558, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10707, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10741, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10743, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10768, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10793, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10864, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10920, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10953, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 11016, CustomerID = AROUT, CompanyName = Around the Horn }
{ OrderID = 10289, CustomerID = BSBEV, CompanyName = B's Beverages }
{ OrderID = 10471, CustomerID = BSBEV, CompanyName = B's Beverages }
{ OrderID = 10484, CustomerID = BSBEV, CompanyName = B's Beverages }
{ OrderID = 10538, CustomerID = BSBEV, CompanyName = B's Beverages }
{ OrderID = 10539, CustomerID = BSBEV, CompanyName = B's Beverages }
{ OrderID = 10578, CustomerID = BSBEV, CompanyName = B's Beverages }
{ OrderID = 10599, CustomerID = BSBEV, CompanyName = B's Beverages }
{ OrderID = 10943, CustomerID = BSBEV, CompanyName = B's Beverages }
{ OrderID = 10947, CustomerID = BSBEV, CompanyName = B's Beverages }
{ OrderID = 11023, CustomerID = BSBEV, CompanyName = B's Beverages }
{ OrderID = 10435, CustomerID = CONSH, CompanyName = Consolidated Holdings }
{ OrderID = 10462, CustomerID = CONSH, CompanyName = Consolidated Holdings }
{ OrderID = 10848, CustomerID = CONSH, CompanyName = Consolidated Holdings }
{ OrderID = 10364, CustomerID = EASTC, CompanyName = Eastern Connection }
{ OrderID = 10400, CustomerID = EASTC, CompanyName = Eastern Connection }
{ OrderID = 10532, CustomerID = EASTC, CompanyName = Eastern Connection }
{ OrderID = 10726, CustomerID = EASTC, CompanyName = Eastern Connection }
{ OrderID = 10987, CustomerID = EASTC, CompanyName = Eastern Connection }
{ OrderID = 11024, CustomerID = EASTC, CompanyName = Eastern Connection }
The project and code are attached. The Open XML spreadsheet that contains the table used in this query is in the debug/bin directory.