标签： Linq

Webservice中LinqToSQL执行ToList序列化产生循环引用错误

问题描述：在做Tiffany的时候，用到Linq通过Webservice返回数据给Flash。但是当执行结果集.ToList()的时候出现如下错误：
System.InvalidOperationException: 生成 XML 文档时出错。 ---> System.InvalidOperationException: 序列化类型 Tiffany.Photo 的对象时检测到循环引用。
在 System.Xml.Serialization.XmlSerializationWriter.WriteStartElement(String name, String ns, Object o, Boolean writePrefixed, XmlSerializerNamespaces xmlns)
在 Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationWriter1.Write3_Photo(String n, String ns, Photo o, Boolean isNullable, Boolean needType)
在 Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationWriter1.Write2_Vote(String n, String ns, Vote o, Boolean isNullable, Boolean needType)
在 Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationWriter1.Write3_Photo(String n, String ns, Photo o, Boolean isNullable, Boolean needType)
在 Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationWriter1.Write5_ArrayOfPhoto(Object o)
在 Microsoft.Xml.Serialization.GeneratedAssembly.ListOfPhotoSerializer.Serialize(Object objectToSerialize, XmlSerializationWriter writer)
在 System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle, String id)
--- 内部异常堆栈跟踪的结尾 ---
在 System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle, String id)
在 System.Xml.Serialization.XmlSerializer.Serialize(TextWriter textWriter, Object o, XmlSerializerNamespaces namespaces)
在 System.Xml.Serialization.XmlSerializer.Serialize(TextWriter textWriter, Object o)
在 System.Web.Services.Protocols.XmlReturnWriter.Write(HttpResponse response, Stream outputStream, Object returnValue)
在 System.Web.Services.Protocols.HttpServerProtocol.WriteReturns(Object[] returnValues, Stream outputStream)
在 System.Web.Services.Protocols.WebServiceHandler.WriteReturns(Object[] returnValues)
在 System.Web.Services.Protocols.WebServiceHandler.Invoke()

问题分析：查看报错内容，“检测到循环引用”说明Linq的关系引用上存在问题。

问题解决：打开DataClassses-关联-子属性-访问权限，改为Internal

Linq的LEFT JOIN和RIGHT JOIN方案

Linq只有join，就是inner join啦，只有用如下方案：

var result = from p in snsDC.NT_User
                         join q in snsDC.NT_Photo on p.Portrait equals q.PhotoID into pq
                         from r in pq.DefaultIfEmpty()
                         where userIDs.ToArray().Contains(p.UserID.ToString())
                         select new { p.UserID, p.UserName, r.FilePath };

Linq的Contains错误

今天用Linq的Contains实现SQL下(WHERE...IN)语句，有如下错误：
“方法“Boolean Contains(System.Guid)”不支持转换为 SQL。”

源代码如下：
var result = from p in snsDC.NT_User
                         join q in snsDC.NT_Photo on p.Portrait equals q.PhotoID into pq
                         from r in pq.DefaultIfEmpty()
                         where userIDs.Contains(p.UserID.ToString())
                         select new { p.UserID, p.UserName, r.FilePath };

分析：
估计是Linq无法将IList的Contains方法正确翻译为SQL语句。

改为：
var result = from p in snsDC.NT_User
                         join q in snsDC.NT_Photo on p.Portrait equals q.PhotoID into pq
                         from r in pq.DefaultIfEmpty()
                         where userIDs.ToArray().Contains(p.UserID.ToString())
                         select new { p.UserID, p.UserName, r.FilePath };