linq-to-sql - 嵌套的 linq-to-sql 查询

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我描述了我的数据库表的样子。因此，contacts 表有一个字段是 xml 类型。在该字段中存储了公司文件名，我需要阅读它。我用这种方式尝试过:

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

但我得到以下错误:

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

有什么解决办法吗？

提前致谢，
艾尔

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