Why isn't it possible to combine LINQ to XML with LINQ to SQL?

Question

I have this piece of code:

var xml = XDocument.Load(filePath);
var taxReturns = (from t in xml.Descendants("aangiftes").Elements()
    where t.Name == "ib"
    select new Domain.TaxReturn
    {
        FiscalNumber = t.Attribute("sofinr").Value,
        Guid = Guid.Parse(t.Attribute("guid").Value),
        LastFormOpen = t.Attribute("lastformview").Value,
        Name = string.Empty, 
        TaxYear = t.GetAttributeValue<short>("belastingjaar"),
        TaxForm =  unitOfWork.TaxForms.FirstOrDefault(tf => tf.Code == t.Attribute("biljetsoort").Value),
    }).ToArray();

When I run this code, I get this exception:

LINQ to Entities does not recognize the method System.Xml.Linq.XAttribute Attribute(System.Xml.Linq.XName)-method, and this method cannot be translated into a store expression

However, when I extract this line into a method, it works fine.

Could someone explain this behavior? I don't understand this behavior :/

Answer 1

This is because LINQ tries to compile the code inside Domain.TaxReturn {...} into SQL. There is no corresponding API in SQL for XElement.Attribute(...) so it fails.

Try to move the XElement dependent code outside of Domain.TaxReturn {...}
Like this:

var xml = XDocument.Load(filePath);
var taxReturns = (from t in xml.Descendants("aangiftes").Elements()
    where t.Name == "ib"
    let sofinr = t.Attribute("sofinr").Value
    let guid = Guid.Parse(t.Attribute("guid").Value)
    let lastformview = t.Attribute("lastformview").Value
    let belastingjaar = t.GetAttributeValue<short>("belastingjaar")
    let biljetsoort = t.Attribute("biljetsoort").Value
    select new Domain.TaxReturn
    {
        FiscalNumber = sofinr ,
        Guid = guid,
        LastFormOpen = lastformview,
        Name = string.Empty, 
        TaxYear = belastingjaar,
        TaxForm = unitOfWork.TaxForms.Single(tf => tf.Code == biljetsoort),
    }).ToArray();

(untested)

Also, you probably want to use Enumerable.Single instead of Enumerable.FirstOrDefault, or is null a valid case?

Answer 2

As said in the documentation -

When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution

http://msdn.microsoft.com/en-us/library/bb386976%28v=vs.110%29.aspx

Carefully check the execution order, "The SQL is generated first and then executed"

You can readily understand why this happens. There is no alternatives of System.Xml.Linq.XAttribute Attribute(System.Xml.Linq.XName) in SQL.

For example .OrderBy() has an SQL equivalent order by and thus when LINQ sees this method it converts it to order by and the query is performed. But when linq sees System.Xml.Linq.XAttribute Attribute(System.Xml.Linq.XName), there is no SQL equivalent for this and LINQ throws an error saying it does not recognize it.

But when you extract the line into a method, that method is executed first, so by this time LINQ to SQL has already worked and all the entities you are looking for are brought inside the RAM and now you can perform any LINQ to XML. This time the queries are not converted to SQL, as the entities are already in RAM as managed objects and your code works just fine.