Introduction
I am a huge fan of LINQ to SQL feature of the .NET Framework 3.5. If you don't know what LINQ to SQL is, please read the white paper here.
I like the way in which it makes database coding simple and easy. Developers do not have to use different programming models (CLR functions and SQL Stored Procedures) and switch between different programming languages (C#/VB.NET and T-SQL). LINQ to SQL designer in Visual Studio 2008 also makes our life even easier - we can just drag and drop the tables/Views/Store Procedures to the LINQ to SQL designer surface. The designer will automatically generate the .NET classes to represent these database entities even including the relationships between the tables.
Problem
My project includes several layers, some layers require the business objects to be serialized before sending to another layer. Right now, I am using strong-typed datasets to present more than two hundred tables in the database, but I still had to manually create nearly 100 serializable business objects in order to represent the entities.
LINQ to SQL has given me the hope to minimize the effort to create these business object classes. Those entity classes created by LINQ to SQL designer look like a very good candidate for the business objects that my project needs.
But very quickly, I realized that I was wrong.
- LINQ to SQL classes do not support binary serialization. Although I can manually modify them to meet my needs, it is a very time-consuming job, and difficult to maintain if the table is changed in the future.
- LINQ to SQL classes cannot be serialized by XML serializer if there is a relationship between tables.
I am going to use the Northwind database and a WebService
project in order to demonstrate the problem for XML serialization.
First, let's create a WebService
project and add a LINQ to SQL class named NorthWind.dbml, and then drag and drop Suppliers
and Products
tables to the LINQ to SQL designer surface. You can see that there are two classes generated to represent the Supplier
and Product
entities in these two tables, and also the association to represent the relationship between Suppliers
and Products
tables.
Create a WebMethod
to return a Product
object (ProductID=1
) in Service.cs:
[WebMethod]
public Product<product /> GetProduct() {
NorthWindDataContext db = new NorthWindDataContext();
Product p = db.Products.Single<product />(prod => prod.ProductID == 1);
return p;
}
The execution of this WebMethod
(GetProduct
) fails with the following exception:
System.InvalidOperationException: There was an error generating the XML document. --->
System.InvalidOperationException:
A circular reference was detected while serializing an
object of type Product.
at System.Xml.Serialization.XmlSerializationWriter.WriteStartElement
(String name, String ns,
Object o, Boolean writePrefixed, XmlSerializerNamespaces xmlns)
at Microsoft.Xml.Serialization.GeneratedAssembly.
XmlSerializationWriter1.Write3_Product
(String n, String ns, Product o, Boolean isNullable, Boolean needType)
at Microsoft.Xml.Serialization.GeneratedAssembly.
XmlSerializationWriter1.Write2_Supplier
(String n, String ns, Supplier o, Boolean isNullable, Boolean needType)
at Microsoft.Xml.Serialization.GeneratedAssembly.
XmlSerializationWriter1.Write3_Product
(String n, String ns, Product o, Boolean isNullable, Boolean needType)
at Microsoft.Xml.Serialization.GeneratedAssembly.
XmlSerializationWriter1.Write4_Product
(Object o)
at Microsoft.Xml.Serialization.GeneratedAssembly.ProductSerializer.Serialize
(Object objectToSerialize, XmlSerializationWriter writer)
at System.Xml.Serialization.XmlSerializer.Serialize
(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces,
String encodingStyle, String id)
--- End of inner exception stack trace ---
at System.Xml.Serialization.XmlSerializer.Serialize
(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces,
String encodingStyle, String id)
at System.Xml.Serialization.XmlSerializer.Serialize(TextWriter textWriter, Object o)
at System.Web.Services.Protocols.XmlReturnWriter.Write
(HttpResponse response, Stream outputStream, Object returnValue)
at System.Web.Services.Protocols.HttpServerProtocol.WriteReturns
(Object[] returnValues, Stream outputStream)
at System.Web.Services.Protocols.WebServiceHandler.WriteReturns(Object[] returnValues)
at System.Web.Services.Protocols.WebServiceHandler.Invoke()
The reason behind this exception is that the Product
object has a property Supplier
(Product.Supplier
) which holds a reference of a Supplier
object, and this Supplier
object has a property Products
which points to a set of Product
objects including the first Product
object. So there is a circular reference. When Xmlserializer
detected this circular reference, it reports InvalidOperationException
.
There are two workarounds, please read Rick Strahl's excellent Web log here. The first workaround is to change the association to internal in order to make the XML serialization work but somehow compromise the LINQ to SQL functionality. The second one is to use WCF serialization by setting DataContext
to Unidirectional Serialization mode and modify the association to non-public. Neither of the workarounds meet my needs, neither of them works under Binary Serialization, and I don't want to modify the DataContext
. Remember I have more than 200 tables, it is a huge job to maintain a modified Datacontext
.
What I need for my project are:
- A serializable business object class for each entity, and I do not need the association between entities
- These business entity classes can be used in both XML serialization and binary serialization
- The work for generating these classes should be minimized and reusable for other similar projects
Solution
So here is my solution - LinqSqlSerialization.dll class library. It contains two classes:
public class SerializableEntity<t /><T>
public static class EnumerableExtension
How to Use LinqSqlSerialization Classlibrary
What you need to do is simply adding the LinqSqlSerialization.dll to your project references.
The following are some samples to show you how easy it is to serialize and deserialize the LINQ to SQL classes in your project.
XML Serialization Sample
NorthWindDataContext db = new NorthWindDataContext();
Product product = db.Products.Single<product />(prod => prod.ProductID == 2);
SerializableEntity<Product><product /> entity = new SerializableEntity<Product><product />(product);
XmlSerializer serizer = new XmlSerializer(entity.GetType());
System.IO.MemoryStream ms = new System.IO.MemoryStream();
serizer.Serialize(ms, entity);
ms.Position = 0;
SerializableEntity<product /><Product> cloneEntity=
serizer.Deserialize(ms) as SerializableEntity<product /><Product>;
Product cloneProduct =cloneEntity.Entity;
ConsConsole.WriteLine("The result is {0}", cloneProduct.ProductName);
NorthWindDataContext db = new NorthWindDataContext();
List<SerializableEntity<Product<serializableentity<product />>> products =
db.Products.ToList<Product, SerializableEntity<Product><product />>();
XmlSerializer serizer = new XmlSerializer(products.GetType());
System.Text.StringBuilder sb=new System.Text.StringBuilder();
System.IO.StringWriter sw=new System.IO.StringWriter(sb);
serizer.Serialize(sw, products);
sw.Close();
string xmlResult = sb.ToString();
System.IO.StringReader sr = new System.IO.StringReader(xmlResult);
List<SerializableEntity<Product><serializableentity<product />> cloneEntities =
serizer.Deserialize(sr) as List<SerializableEntity<Product><serializableentity<product />>;
foreach (SerializableEntity<product /> clone in cloneEntities)
{
Console.WriteLine("The result is {0}", clone.Entity.ProductName);
}
Binary Serialization Sample
NorthWindDataContext db = new NorthWindDataContext();
Product product = db.Products.Single />(prod => prod.ProductID == 2);
SerializableEntity<Product><product /> entity = new SerializableEntity<Product><product />(product);
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter serizer
= new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
System.IO.MemoryStream ms = new System.IO.MemoryStream();
serizer.Serialize(ms, entity);
ms.Position = 0;
SerializableEntity<product /><Product> cloneEntity =
serizer.Deserialize(ms) as SerializableEntity<Product><product />;
Product cloneProduct = cloneEntity.Entity;
Console.Console.WriteLine("The result is {0}", cloneProduct.ProductName);
NorthWindDataContext db = new NorthWindDataContext();
List<SerializablEntity<Product><serializableentity<product />> products =
db.Products.ToList<Product, SerializableEntity<Product><product />>();
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter serizer
= new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
System.IO.MemoryStream ms = new System.IO.MemoryStream();
serizer.Serialize(ms, products);
ms.Position = 0;
List<SerializableEntity<Product><product />> cloneEntities =
serizer.Deserialize(ms) as List<SerializableEntity<Product><product />>;
foreach (SerializableEntity<product /><Product> cloneEntity in cloneEntities)
{
Product cloneProduct = cloneEntity.Entity;
Console.WriteLine("The result is {0}", cloneProduct.ProductName);
}
Web Service Sample
Add the GetProduct
function in Service.cs and run the WebService
:
[WebMethod]
public SerializableEntity<product /><Product> GetProduct()
{
NorthWindDataContext db = new NorthWindDataContext();
Product p = db.Products.Single<product />(prod => prod.ProductID == 2);
SerializableEntity<product /><Product> entity = new SerializableEntity<Product><product />(p);
return entity;
}
On the client side, when the client generated the proxy classes from WSDL, these proxy classes have the same name as the entity classes generated by LINQ to SQL designer, and also have the same properties except those association properties.
You can use the proxy classes like this:
localhost.Service service= new localhost.Service();
localhost.Product product = service.GetProduct();
Console.WriteLine(product.ProductID);
How It Works???
The core of my solution is SerializableEntity<T>
class which is a generic class implemented with IXMLSerializable
and ISerializable
interfaces.
[Serializable]
[XmlSchemaProvider("MySchema")]
public class SerializableEntity<t /><T> :
IXmlSerializable, ISerializable where T : class, new()
Generics is my favorite feature of .NET Framework. We can use Generics to define a set of actions or behaviors for certain types. In this case, I implemented a Generic SerializableEntity
class with IXmlSerializable
and ISerializable
, and the type parameter T
is those LINQ to SQL classes. So it can greatly minimize the workload, I don't have to implement the serialization for each class.
The SerializableEntity
class only has several methods and the logic is pretty straightforward except that I did some special implementation for XML serialization.
public SerializableEntity() { }
private T _entity;
public SerializableEntity(T entity)
{
this.Entity = entity;
}
public T Entity
{
get { return _entity; }
set { _entity = value; }
}
Use Reflection to read and write the values from and to LINQ to SQL entity objects for both binary and XML serializations.
#region ISerializable Members
public SerializableEntity(SerializationInfo info, StreamingContext context)
{
_entity = new T();
PropertyInfo[] properties = _entity.GetType().GetProperties();
SerializationInfoEnumerator enumerator = info.GetEnumerator();
while (enumerator.MoveNext())
{
SerializationEntry se = enumerator.Current;
foreach (PropertyInfo pi in properties)
{
if (pi.Name == se.Name) {
pi.SetValue(_entity, info.GetValue(se.Name, pi.PropertyType), null);
}
}
}
}
public void GetObjectData(SerializationInfo info, StreamingContext context)
{
PropertyInfo[] infos = _entity.GetType().GetProperties();
foreach (PropertyInfo pi in infos)
{
bool isAssociation = false;
foreach (object obj in pi.GetCustomAttributes(true))
{
if (obj.GetType() == typeof(System.Data.Linq.Mapping.AssociationAttribute))
{ isAssociation = true; break; }
}
if (!isAssociation) {
if (pi.GetValue(_entity, null) != null) {
info.AddValue(pi.Name, pi.GetValue(_entity, null));
}
}
}
}
#endregion
#region IXmlSerializable Members
public System.Xml.Schema.XmlSchema GetSchema() { return null; }
public void ReadXml(System.Xml.XmlReader reader)
{
_entity = new T();
PropertyInfo[] pinfos = _entity.GetType().GetProperties();
if (reader.LocalName == typeof(T).Name)
{
reader.MoveToContent();
string inn = reader.ReadOuterXml();
System.IO.StringReader sr=new System.IO.StringReader(inn);
System.Xml.XmlTextReader tr = new XmlTextReader(sr);
tr.Read();
while (tr.Read())
{
string elementName = tr.LocalName;
string value = tr.ReadString();
foreach (PropertyInfo pi in pinfos)
{
if (pi.Name == elementName)
{
TypeConverter tc = TypeDescriptor.GetConverter(pi.PropertyType);
pi.SetValue(_entity, tc.ConvertFromString(value), null);
}
}
}
}
}
public void WriteXml(System.Xml.XmlWriter writer)
{
PropertyInfo[] pinfos = _entity.GetType().GetProperties();
foreach (PropertyInfo pi in pinfos)
{
bool isAssociation = false;
foreach (object obj in pi.GetCustomAttributes(true))
{
if (obj.GetType() == typeof(System.Data.Linq.Mapping.AssociationAttribute))
{
isAssociation = true;
break;
}
}
if (!isAssociation)
{
if (pi.GetValue(_entity, null) != null)
{
writer.WriteStartElement(pi.Name);
writer.WriteValue(pi.GetValue(_entity, null));
writer.WriteEndElement();
}
}
}
}
private static string GetXsdType(string nativeType)
{
string[] xsdTypes = new string[]{"boolean", "unsignedByte",
"dateTime", "decimal", "Double",
"short", "int", "long", "Byte", "Float", "string", "unsignedShort",
"unsignedInt", "unsignedLong", "anyURI"};
string[] nativeTypes = new string[]{"System.Boolean", "System.Byte",
"System.DateTime", "System.Decimal",
"System.Double", "System.Int16", "System.Int32", "System.Int64",
"System.SByte", "System.Single", "System.String", "System.UInt16",
"System.UInt32", "System.UInt64", "System.Uri"};
for (int i = 0; i < nativeTypes.Length; i++)
{
if (nativeType == nativeTypes[i]) { return xsdTypes[i]; }
}
return "";
}
#endregion
Because I am using a single Generics
class to wrap the LINQ to SQL classes, it means each entity type needs its own schema, so I implemented the customized schema which is dynamically generated based on the type parameter. Please note that the method name should be the same name for the XmlSchemaProviderAttribute
of the SerializableEntity<T>
class.
private static readonly string ns = "http://tempuri.org/";
public static XmlQualifiedName MySchema(XmlSchemaSet xs)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
System.IO.StringWriter sw = new System.IO.StringWriter(sb);
XmlTextWriter xw = new XmlTextWriter(sw);
xw.WriteStartDocument();
xw.WriteStartElement("schema");
xw.WriteAttributeString("targetNamespace", ns);
xw.WriteAttributeString("xmlns", "http://www.w3.org/2001/XMLSchema");
xw.WriteStartElement("complexType");
xw.WriteAttributeString("name", typeof(T).Name);
xw.WriteStartElement("sequence");
PropertyInfo[] infos = typeof(T).GetProperties();
foreach (PropertyInfo pi in infos)
{
bool isAssociation = false;
foreach (object a in pi.GetCustomAttributes(true))
{
if (a.GetType() == typeof(System.Data.Linq.Mapping.AssociationAttribute))
{
isAssociation = true;
break;
}
}
if (!isAssociation)
{
xw.WriteStartElement("element");
xw.WriteAttributeString("name", pi.Name);
if (pi.PropertyType.IsGenericType) {
Type[] types = pi.PropertyType.GetGenericArguments();
xw.WriteAttributeString("type", "" + GetXsdType(types[0].FullName));
} else {
xw.WriteAttributeString("type", "" +
GetXsdType(pi.PropertyType.FullName));
} xw.WriteEndElement();
}
}
xw.WriteEndElement();
xw.WriteEndElement();
xw.WriteEndElement();
xw.WriteEndDocument();
xw.Close();
XmlSerializer schemaSerializer = new XmlSerializer(typeof(XmlSchema));
System.IO.StringReader sr = new System.IO.StringReader(sb.ToString());
XmlSchema s = (XmlSchema)schemaSerializer.Deserialize(sr);
xs.XmlResolver = new XmlUrlResolver();
xs.Add(s);
return new XmlQualifiedName(typeof(T).Name, ns);
}
The SerializableEntity<T>
is only for the single object, but when we use LINQ to SQL, most of the time we are working with a set of entities, like this:
NorthWindDataContext db = new NorthWindDataContext();
var products = from p in db.Products
Select p;
Because most of the query results are implemented by the System.Collections.Generic.IEnumerable<t /><T>
interface, I use the Extension feature of .NET Framework 3.5 to implement the EnumableExtesion
class to easily add a new function to an existing class.
public static class EnumerableExtension
{
public static List<tserializableentity /><TSerializableEntity> ToList<tsource, /><TSource, TSerializableEntity>
(this IEnumerable<TSource><tsource /> source)
where TSource : class, new()
where TSerializableEntity : SerializableEntity<tsource /><TSource>, new()
{
List<TSerializableEntity><tserializableentity /> list = new List<TSerializableEntity><tserializableentity />();
foreach (TSource entity in source)
{
list.Add(ToTSerializableEntity<tsource, />(entity));
}
return list;
}
}
I can use it in the LINQ to SQL query to return a typed list of serializable entities directly. It makes the code compact and easy-to-understand.
Now with this LinqSqlSerialization
class library, I don't have to manually create or modify these business objects and moving the entity objects across the layers is worry-free. A happy ending!
History
- 20th December, 2007: Initial post