LINQ to SQL Serialization

By WinSpace, 21 Dec 2007

4.73 (24 votes)

• 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 dropSuppliers 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:

 Collapse | Copy Code

[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:

 Collapse | Copy Code

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 ofProduct 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:

 Collapse | Copy Code

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

 Collapse | Copy Code

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

//convert to SerializableEntity 
SerializableEntity<Product><product /> entity = new SerializableEntity<Product><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 /><Product> cloneEntity= 
    serizer.Deserialize(ms) as SerializableEntity<product /><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<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); 

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

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

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

Binary Serialization Sample

 Collapse | Copy Code

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

//convert to SerializableEntity 
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(); 

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

//deserialize 
ms.Position = 0; 
SerializableEntity<product /><Product> cloneEntity = 
    serizer.Deserialize(ms) as SerializableEntity<Product><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><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(); 

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

//deserialize 
ms.Position = 0; 
List<SerializableEntity<Product><product />> cloneEntities = 
    serizer.Deserialize(ms) as List<SerializableEntity<Product><product />>; 
foreach (SerializableEntity<product /><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:

 Collapse | Copy Code

[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:

 Collapse | Copy Code

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 withIXMLSerializable and ISerializable interfaces.

 Collapse | Copy Code

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

 Collapse | Copy Code

//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.

 Collapse | Copy Code

//
//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 theSerializableEntity<T> class.

 Collapse | Copy Code

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:

 Collapse | Copy Code

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.

 Collapse | Copy Code

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!

Designing A Data Access Layer in LINQ to SQL

Designing A Data Access Layer in LINQ to SQL

21 February 2009

by Gayani Devapriya

Gayani provides the complete source code for a sample Data Access Layer for LINQ to SQL, using the Northwind Database, and explains how it all works and how to get good performance. She shows how easy it is to use stored procedure and views as well as queries to provide a robust run-time infrastructure for managing relational data as objects.

Introduction

Almost all current Programming languages model their data in terms of objects while the relational databases use entities and relationships for the same purpose.   LINQ to SQL is the first stage of LINQ technology, the aim of which is to bridge the gap between the two different ways of representing data entities. Before the introduction of LINQ, it was a common practice to devise database abstraction layers that translated the information between the relational representation of the database and the application’s domain-specific object models.

The aim of LINQ to SQL, which is another component in the ADO.NET family, was to provide a run-time infrastructure for managing relational data as objects without losing the facility of being able to query data, and use stored procedures. It does this by translating language-integrated queries into SQL for execution by the database, and then translating the results back into objects defined by the application domain. The application can manipulate the objects while LINQ to SQL stays in the background, tracking changes to the data automatically. LINQ does not aim to circumvent a data abstraction layer at the database level, based on stored procedures; in fact it makes their use easier for the programmer.

When an object is linked to relational data, it receives attributes to identify how properties correspond to columns within the database.  This mapping is done by translating the relational database schemas into object definitions, and can be done automatically by design-time tools.

LINQ to SQL can be used by any .NET language that has been built to provide Language-Integrated Query.  

This article discusses how LINQ to SQL can be used to design a data access layer and lists some best practices that can help to improve the performance of the application.

When to go for LINQ to SQL?

There are several ways to design a data access layer.  .NET Framework 3.5 introduced several LINQ providers, including LINQ to SQL. This was intended to provide LINQ over relational databases but was implemented only for SQL Server.  .NET 3.5 SP1 added the Entity Framework. This allowed the use of LINQ alongside any database including SQL Server, Oracle, DB2, MySQL, etc.
 In .NET 4.0, the Entity Framework will be the strategic  data access solution for  applications that require a  conceptual data model with strongly typed data, inheritance and relationships, to a whole range of relational database sources.  However, LINQ to SQL will be supported for Rapid Application Development with a SQL Server back end.

Designing a Data Access Layer

Layered architectures are generally preferred for applications because of the code reuse, flexibility, performance and maintainability.  In a layered architecture, the data access layer is mainly responsible of communicating with the database, whereas the business layer focuses on business logic and business rules. The presentation layer, of course, concentrates on the UI.

Choosing the data representation & transferring format between layers

LINQ to SQL works by mapping relational database schema to .NET classes.  This mapping is provided in theData Context which is the main source used to perform all query operations against the database. Classes modelled to map database tables with in the data context are known as Entity Classes. Properties of entity classes maps to table columns and each instance of the entity class represents a row with in the database table. These in-memory objects or entities are used as a transferring media of data via layers, but restricting the data context scope with in the class library.  (Northwind.Products.Data: in sample)

Determining which layer(s) should access data

Since the data access layer is the only layer that communicates directly with the data base, any other layer is expected to communicate with the database via this layer;  therefore this layer can be used to define methods that will use the data context to perform operations within the  database.  (Northwind.Products.DAL: in sample)

The following diagram elaborates the structure of the solution in the sample application.

Designing Entities

The choice of the entities to populate within the data context depends on the requirements. In our example, the purpose of Northwind.Products class library is to provide an interface to manipulate the product related information.  Therefore, the related tables such as Categories, Suppliers and Order Details were populated with the Producttable. A postfix of ‘_P’ is used to avoid naming conflicts when used with in multiple class libraries.  (Products.dbml in Northwind.Products: in sample)

Managing the Data Context

The data context object consists of the information of its entity classes, their tracking information and the mapping information. To load and persist the data context object in memory consumes a considerable amount of memory, so it is instantiated and used within the method scope and disposed of after use. (ProductsDAO.cs in Northwind.Products: in sample)

Managing database connections

The connection string is required at every instantiation of a data context instance. It is  therefore  read at the application start and stored in a common global  variable that can be  accessed throughout the application.(Settings.cs in Northwind.Common: in sample)

Manipulating Data using LINQ to SQL

There are many ways of manipulating data in LINQ to SQL. There is no restriction in using tables, stored procedures, views or functions.  All are possible, and the choice is purely an implementation decision. One way to implement Create, Read, Update,  and Delete (CRUD)  operations is to configure the behavior on the entity class at  design time by specifying the stored procedures, or to use base tables directly, as follows.

Using Base Tables

In this example, a RowVersion column with a timestamp type is added to each table, to identify the row status (modified/not) and to handle concurrency conflicts.

C# Code: Perform an insert/update on Product table.

//Insert/Update Product.

internal bool SaveProduct(Product oProduct)

{

       ChangeSet changeSet = null;

       int changeCount = 0;

using (ProductsDataContext productDC = newProductsDataContext(Settings.ConnectionString))

{

                productDC.DeferredLoadingEnabled = false;

                if (oProduct.RowVersion == null)

                {

                    productDC.Products.InsertOnSubmit(oProduct);

                    changeSet = productDC.GetChangeSet();

                    changeCount = changeSet.Inserts.Count;

                    productDC.SubmitChanges();

                }

                else

                {

                     productDC.Products.Attach(oProduct, true);

                    changeSet = productDC.GetChangeSet();

                    changeCount = changeSet.Updates.Count;

                    productDC.SubmitChanges();

                }

}

if (changeCount > 0) { return true; }

else { return false; }

}

First, the rowversion of the product entity is checked to identify whether to insert or update. Then, to insert a new product, the InsertOnSubmit method is called on the Products entity, with the new entity instance passed to it. For updating, the Attach method is called by parsing the entity with changed values, and this sets the modified status  to true, indicating that the entity has been modified.

The GetChangeSet method call on the data context provides a ChangeSet instance to track down the changes such as insert counts, update counts and delete counts. Finally, the SubmitChanges computes the changes to be made on the database and is executed to implement those changes.

Using Stored Procedures (SPs) & Functions to Query Data.

When a Stored Procedure or a SQL Function is added to a data context, it is marked as FunctionAttribute and its parameters are marked as ParameterAttributes, this identifies the procedure or Function as a method within the datacontext.

LINQ to SQL introduces various ways for handling Stored Procedures with known results; those with return results that change at execution according to parameters passed, and those that return data as output parameters.  We can’t hope to cover all the permutations here, so this article will  just show  how Stored procedures are used with a minimum number of entities within the data layer, by mapping its return type to a particular entity.

When a SP is dragged on to a data context in the IDE, it automatically creates its return type which can be identified by the postfix ‘Result’ (ex: Orders_SelectByShippedDateResult). Instead, if we know the return type of the Stored Procedure,  we can drag the Stored Procedure on to the entity within the IDE or change the Return Type property of the SP after adding the SP on to data context. It will result as follows.

C# Code: Using a SP to query data.

internal List<Order> GetOrdersByShippedDate(DateTime ShippedDate)

{

   using (SalesDataContext sales = new SalesDataContext(Settings.ConnectionString))

   {

        sales.DeferredLoadingEnabled = false;

        return sales.Orders_SelectByShippedDate(ShippedDate).ToList();

    }

}

C# Code: Using a SQL Function to query data.

//GetLastSalesDate is a scaler valued function added to the data context.

internal DateTime GetLastDate(string Type)

{

      using (SalesDataContext dcSales = new SalesDataContext(Settings.ConnectionString))

      {

                DateTime? _date;

                _date = dcSales.GetLastSalesDate(Type);

                if (_date.HasValue) { return _date.Value; }

                else { return new DateTime(); }

      }

}

(ProductsDAO.cs, SalesDAO.cs in Northwind.Products: in sample for more examples)

Handling Transactions

Though it is possible to define our own database transactions to group a set of database operations, it is always wiser to use the System.Transactions model, for it will take care of resources grouped under a single transaction and operations on multiple databases. Therefore these operations need to be scoped with the use of theTransactionscope object within the Business Layer, since a single unit of operation needs to be completed as a one business process. In the event of an error,  it will rollback the entire operation, whereas Complete will commit the operation if successful. (ProductBPO.cs in Northwind.Products: in sample)

C# Code:

public bool SaveProduct(Product oProduct)

{

            using (TransactionScope transcope = new TransactionScope())

            {

                ProductsDAO dao = new ProductsDAO();

                bool status = dao.SaveProduct(oProduct);

                transcope.Complete();

                return status;

            }

}

Handling Concurrency Conflicts

During an update, each field is checked for concurrency conflicts, this checking frequency is controlled by theUpdate Check property of each field that can be set to ‘Always’ or ‘WhenChanged’.  We will use  a rowversion of a  timestamp column in a table in order to detect  concurrency conflicts. Within the data context, the concurrency conflict exceptions are caught as a ChangeConflictException. The ChangeConflicts property of the data context exposes the collection of objects that caused the conflicts while SubmitChanges executes.

C# Code:

try

{

      productDC.SubmitChanges();

}

catch (ChangeConflictException cex)

{

                     

    foreach (ObjectChangeConflict conflictObject in productDC.ChangeConflicts)

    {

         // expose the neccessary information.

    }

                       

}

Features of the Data Context

Here are some of the most important features of the Data Context

Object Identity

The data context is intelligent enough to track the entities that were loaded already, so if a request was made for the same object again, it would not result in two entity instances being loaded but only one. This behaviour of maintaining the uniqueness of the identity of the loaded objects in the data context is called Object Identity. This ensures that in-memory objects are not duplicated.

C# Code:

internal List<Order> GetOrdersByShippedDate(DateTime ShippedDate)

{

     using (ProductDCDataContext productDC =
                new ProductDCDataContext(Settings.ConnectionString))

     {

                productDC.DeferredLoadingEnabled = false;

                DataLoadOptions loadOptions = new DataLoadOptions();

                loadOptions.LoadWith<Order>(o => o.Order_Details);

                productDC.LoadOptions = loadOptions;

                return productDC.Orders_SelectByShippedDate(ShippedDate).ToList();

     }

}

In the above example, load options are used to specify the child entity (Order_Details) that is to be loaded with parent entity (Order) and then the stored procedure (Orders_SelectByShippedDate) is called to filter the Orders. This results in Orders and Order_Details that is filtered by the criteria specified in the SP.  Another instance of Order is not required since it is specified in the load options.

Object Tracking and Loading

Once an entity is loaded, the data context will by default, persist its old values and the new values, as indicated by the ObjectTrackingEnabled status being set to true. When retrieving entities with one-to-one, or one-to-many, relationships with other entities, the related entities will also be loaded by default at the time of retrieval. This is indicated by DeferredLoadingEnabled status set to true.

The default behavior is not necessarily the best in every circumstance. It could lead to high memory consumption, which will eventually slow the application.  It is therefore better to disable the ObjectTrackingEnabled status when retrieving read-only data, and use Lazy Loading instead. The term  ‘Lazy Loading’ means that you opt to load the required enitites only. This is done by explicitly defining the entities to load using the DataLoadOptions instance after disabling the DeferredLoadingEnabled status.  Note that DeferredLoading requires ObjectTracking.

DataLoadOptions used with LoadWith<T> specifes which sub-objects should be  loaded  with the parent object, and AssociateWith<T>specifies the sub-objects and filter criteria on sub-objects to load when loading with the parent object.

C# Code:

internal Product GetProductDetails(int ProductID)

{

     using (ProductDCDataContext productDC =
                new ProductDCDataContext(Settings.ConnectionString))

     {

                productDC.DeferredLoadingEnabled = false;

                DataLoadOptions loadOptions = new DataLoadOptions();

                loadOptions.LoadWith<Product>(p => p.Category);

                productDC.LoadOptions = loadOptions;

                return (from p in productDC.Products

                        where p.ProductID == ProductID

                        select p).FirstOrDefault();

     }

}

In the above example, only the Category entity is loaded at the time of retrieving Products, rather than all the related entities.

Apart from these features, data context also ensures that changes about to be committed do not conflict with the changes already made. This is named as an Optimistic offline lock.  As well as tracking the changes made since the entities were loaded, it allows changes to be committed in a transactional manner, known as Unit of work. 

Some ‘best practices’ with LINQ to SQL

Simple optimization techniques can be used within the application to improve LINQ to SQL performance.

---

Access when needed.

‘Heavy’ objects such as data context must be accessed only needed and disposed quickly after use. (Refer: Managing the Data Context)

Keep things simple.

To avoid data context being bulky, populate only those entities that are actually required for the current process. (Refer: Designing Entities)

Turn off tracking if it is not required.

To avoid unneccessary identity management, when you merely wish to retrieve read-only data, set the ObjectTrackingEnabled status to false. (Refer: Features in the Data context)

Specify Drilldown data.

To avoid unneccessary loading of information, use AssociateWith to specifiy the extent of  drilldown to just  what is required, and no more. (Refer: Features in the Data context)

Analyze the SQL Queries executed.

With the use of the ‘LINQ to SQL Debug Visualizer’ that comes inSqlServerQueryVisualizer.dll, you are able to analyse the queries that are executed at runtime and can then optimize the queries as required. Since it is not integrated with .NET framework it needs to be downloaded and installed. (To install and runthe SQL Debug Visualizer:)