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

Steven · Accepted Answer · 2010-05-17 14:24:49Z

3

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

answered May 17, 2010 at 14:24

Steven

174k27 gold badges355 silver badges453 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ilija veselica Over a year ago

yes, this is it. I'd never figure it out alone :) Although, it's not really practical solution, but it's important that it works. Thanks ;)

Collectives™ on Stack Overflow

nested linq-to-sql queries

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related