nested linq-to-sql queries

Question

var result = (
    from contact in db.Contacts                             
    join user in db.Users on contact.CreatedByUserID equals user.UserID
    orderby contact.ContactID descending
    select new ContactListView
    {
        ContactID = contact.ContactID,
        FirstName = contact.FirstName,
        LastName = contact.LastName,
        Company = (
            from field in contact.XmlFields.Descendants("Company")
            select field.Value).SingleOrDefault().ToString()
    }).Take(10);

Here I described how my database tables look like. So, contacts table has one field that is xml type. In that field is stored Company filename and I need to read it. I tried it using this way:

Company = (
    from field in contact.XmlFields.Descendants("Company")
    select field.Value).SingleOrDefault().ToString()

but I get following error:

Member access 'System.String Value' of 'System.Xml.Linq.XElement' not legal on type 'System.Collections.Generic.IEnumerable`1[System.Xml.Linq.XElement].

Any solution for this?

Thanks in advance,
Ile

Answer 1

The problem here is that LINQ to SQL is trying to transform the Descendants extension method and XElement.Value to SQL, but it of course fails. You will have to do this last transformation with LINQ to Objects. This would work:

var temp = (
    from contact in db.Contacts                             
    join user in db.Users on contact.CreatedByUserID equals user.UserID
    orderby contact.ContactID descending
    select new
    {
        contact.ContactID, contact.FirstName, contact.LastName, contact.XmlFields
    })
    .Take(10);

var tempArray = temp.ToArray();

IEnumerable<ContactListView> result =
    from contact in tempArray
    let company =
        (from field in contact.XmlFields.Descendants("Company")
         select field.Value).SingleOrDefault()
    select new ContactListView()
    {
        ContactID = contact.ContactID,
        FirstName = contact.FirstName,
        LastName = contact.LastName,
        Company = company == null ? null : company.ToString()
    }).Take(10);