LINQ to XML and SQL

From my presentation given at the London .Net User Group on 2008-04-28.

Since LINQ came out, we have been told about how LINQ creates a common way for developers to work with; XML, SQL, Objects, anything. But all the demos I have seen so far show examples of LINQ to SQL, LINQ to XML or LINQ to whatever. Never demonstrating mixing different "LINQs" together, maybe it is just me.

Recently a project required getting results from a web service and adding to it with data from a database. I thought this is a perfect opportunity to use LINQ as I want to create a single object based on data from two different sources.

The background to this is, the web service delivers a list of companies, but we require a flag to display if the company is still trading or not. Currently this is not available via the web service but the company can provide a csv file of company ids of the companies that are no longer trading, which you load into a table in your database. Once you receive your results from the web service you check to see if the id exists is in the database table.

First you need your XML and now days the new flavoured Object for working with XML is the XDocument class.

XDocument resultsXml = XDocument.Load(MapPath("Companies.xml"));                                      

Next create the DataContext for the LINQ to SQL

DataClassesDataContext db = new DataClassesDataContext()

Now use LINQ to XML to get each company and use LINQ to SQL to see if the id exists in the database, the .Any translates to EXISTS in SQL.

var companies = from results in resultsXml.Descendants("COMPANY")
    select new Company
    {
        Id = (int) results.Element("ID"),
        Name = (string) results.Element("NAME"),
        OutOfBusiness = db.OutOfBusinesses.Any(company => company.CompanyId == (int) results.Element("ID"))
    };

As you can see this is not much code, think how much you would have to write to do this without LINQ.

Here is the demonstration code

 

Keep on keeping on !