Linq-to-sql nested 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 what my database tables look like. Thus, the table contactshas one type field xml. This field contains the name of the company file, and I need to read it. I tried this as follows:

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

but I get the following error:

Access to the System.String Value member System.Xml.Linq.XElement is not legal as type System.Collections.Generic.IEnumerable`1 [System.Xml.Linq.XElement].

Any solution for this?

Thanks in advance,
Ile

Answer 1

, LINQ to SQL Descendants XElement.Value SQL, , , . LINQ to Objects. :

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);