LINQ to SQL Serialization

• Download source - 35.63 KB

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.

1. 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.
2. 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 GetProduct() { 
    NorthWindDataContext db = new NorthWindDataContext();
    Product p = db.Products.Single(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:

1. A serializable business object class for each entity, and I do not need the association between entities
2. These business entity classes can be used in both XML serialization and binary serialization
3. 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>
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

/// 
/// Single Object Serialization
///
NorthWindDataContext db = new NorthWindDataContext(); 
Product product = db.Products.Single(prod => prod.ProductID == 2); 

//convert to SerializableEntity 
SerializableEntity<Product> entity = new SerializableEntity<Product>(product); 

XmlSerializer serizer = new XmlSerializer(entity.GetType()); 
System.IO.MemoryStream ms = new System.IO.MemoryStream(); 

//serialize 
serizer.Serialize(ms, entity); 

//deserialize 
ms.Position = 0; 
SerializableEntity<Product> cloneEntity= 
    serizer.Deserialize(ms) as SerializableEntity<Product>; 

//Get the original Product 
Product cloneProduct =cloneEntity.Entity; 

//Print the clone 
//The result is Chang 
ConsConsole.WriteLine("The result is {0}", cloneProduct.ProductName);
/// 
/// Collection XML Serialization
///
NorthWindDataContext db = new NorthWindDataContext(); 
List<SerializableEntity<Product>> products = 
    db.Products.ToList<Product, SerializableEntity<Product>>(); 

XmlSerializer serizer = new XmlSerializer(products.GetType()); 
System.Text.StringBuilder sb=new System.Text.StringBuilder(); 
System.IO.StringWriter sw=new System.IO.StringWriter(sb); 

//serialize 
serizer.Serialize(sw, products); 
sw.Close(); 
string xmlResult = sb.ToString(); 

//deserialize 
System.IO.StringReader sr = new System.IO.StringReader(xmlResult); 
List<SerializableEntity<Product>> cloneEntities = 
    serizer.Deserialize(sr) as List<SerializableEntity<Product>>; 

//Print the clones 
foreach (SerializableEntity clone in cloneEntities) 
{ 
    Console.WriteLine("The result is {0}", clone.Entity.ProductName); 
}

Binary Serialization Sample

/// 
/// Single Object Binary Serialization
///
NorthWindDataContext db = new NorthWindDataContext(); 
Product product = db.Products.Single />(prod => prod.ProductID == 2); 

//convert to SerializableEntity 
SerializableEntity<Product> entity = new 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(); 

//serialize 
serizer.Serialize(ms, entity); 

//deserialize 
ms.Position = 0; 
SerializableEntity<Product> cloneEntity = 
    serizer.Deserialize(ms) as SerializableEntity<Product>; 

//Get the original Product 
Product cloneProduct = cloneEntity.Entity; 

//Print the clone 
//The result is Chang 
Console.Console.WriteLine("The result is {0}", cloneProduct.ProductName); 
///
/// Collection Binary Serialization
///
NorthWindDataContext db = new NorthWindDataContext(); 
List<SerializablEntity<Product>> products = 
    db.Products.ToList<Product, SerializableEntity<Product>>(); 

System.Runtime.Serialization.Formatters.Binary.BinaryFormatter serizer 
= new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter(); 
System.IO.MemoryStream ms = new System.IO.MemoryStream(); 

//serialize 
serizer.Serialize(ms, products); 

//deserialize 
ms.Position = 0; 
List<SerializableEntity<Product>> cloneEntities = 
    serizer.Deserialize(ms) as List<SerializableEntity<Product>>; 
foreach (SerializableEntity<Product> cloneEntity in cloneEntities) 
{ 
    //Get the original Product 
    Product cloneProduct = cloneEntity.Entity; 
    //Print the clone 
    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> GetProduct() 
{ 
    NorthWindDataContext db = new NorthWindDataContext(); 
    Product p = db.Products.Single(prod => prod.ProductID == 2); 
    SerializableEntity<Product> entity = new SerializableEntity<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); //output is 2

How It Works???

The core of my solution is SerializableEntity<T> class which is a generic class implemented with IXMLSerializable and ISerializable interfaces.

[Serializable] //the attribute is required by the binary serialization 
[XmlSchemaProvider("MySchema")] //use customized schema for XML serialization
public class SerializableEntity<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.

//One default constructor, which is required by the XML and Binary serialization. 
public SerializableEntity() { }

private T _entity; 

//One parameterized constructor and one public property (Entity) 
//are used for passing and reading value to and from the SerializableEntity class
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.

//
//Binary Serialization.
//Implementation for ISerializable interface
//
#region ISerializable Members 

//this constructor is required for deserialization
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); 
            } 
        } 
    } 
} 

//this method is the implementation of ISerializable.GetObjectData member
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

//
//XML Serialization.
//Implementation for IXmlSerializable interface
//
#region IXmlSerializable Members 

//this method is the implementation of IXmlSerializable.GetSchema member. 
//It always returns null.
public System.Xml.Schema.XmlSchema GetSchema() { return null; } 

//this method is the implementation of IXmlSerializable.ReadXml member
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); 
                } 
            }
        } 
    }
} 

//this method is the implementation of IXmlSerializable.WriteXml member
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(); 
            } 
        } 
    } 
} 

//return xsd type
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/";

// This is the method named by the XmlSchemaProviderAttribute applied to the type. 
public static XmlQualifiedName MySchema(XmlSchemaSet xs) 
{ 
    // This method is called by the framework to get the schema for this type. 
    System.Text.StringBuilder sb = new System.Text.StringBuilder(); 
    System.IO.StringWriter sw = new System.IO.StringWriter(sb); 
    XmlTextWriter xw = new XmlTextWriter(sw); 

    // generate the schema for type T 
    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)) 
        {     
            //check whether the property is an Association 
            if (a.GetType() == typeof(System.Data.Linq.Mapping.AssociationAttribute)) 
            {     
                isAssociation = true; 
                break; 
            } 
        } 
        //only use the property which is not an Association 
        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> 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> ToList<TSource, TSerializableEntity>
        (this IEnumerable<TSource> source) 
            where TSource : class, new() 
            where TSerializableEntity : SerializableEntity<TSource>, new() 
    { 
        List<TSerializableEntity> list = new List<TSerializableEntity>(); 
        foreach (TSource entity in source) 
        { 
            list.Add(ToTSerializableEntity(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

• 20^th December, 2007: Initial post