The LINQ to SQL platform works faster than the ADO.NET Entity Framework. It occurs because LINQ to SQL has no difficult mappings which exist in ADO.NET Entity Framework. However, many examples, which are shown in ADO.NET Data Services, are based on ADO.NET Entity Framework. It is logical, that in the loaded applications, the power of ADO.NET Entity Framework is not important; however, a speed criterion is very important. ADO.NET Data Services platform does not impose constraints on data access technology and also allows construction of web service on the basis of LINQ to SQL.

However, in practice, while using ADO.NET Data Services together with LINQ to SQL, some questions may appear. The goal of this article is to clear up such questions and to use ADO.NET Data Services and LINQ to SQL correctly.

There are two problems which appear while using LINQ to SQL as a data access platform for service of ADO.NET Data Services: detection of key fields in data model and updating of data.

The essence of a detection of key fields matter consists in the following. When one tries to access a web service, one can receive an error of impossibility to detect a key field or fields. To resolve these problems, let us try to understand how ADO.NET Data Services detects key fields of the entity.

The following algorithm is used to detect whether the fields are key or not. At first the field named "EntityTypeID" is searched among a set of properties ("EntityType" is the name of entity you need). For example, if the entity is named "Customer," the key field will be "CustomerID." If this property is found, it is a key field. If such property is not found, the search of a field with a name "ID" will be started. If this property is found, it is a key field. If such a property is not found, the error will be generated.

So, let us make a small experiment to prove the reasoning. Let us create a small table, "Customer," which contains fields "ID" and "Name." For SQL Server scheme and LINQ-to-SQL model see Figure 1.

Figure 1: Simple scheme of data

Let us construct data service on the basis of this model and then we will try to refer to it. We will see that infrastructure of ADO.NET Data Services has detected field "ID" as a key one. For the result of access to web service see Figure 2.

Figure 2: Simple data service

Similar results can be received in the case of using field "CustomersID" as a key one. As "CustomersID" field is a field of the table "Customer," it will be detected as a key one.

So, let us make another experiment. Now we will create the table "Customers," and as a key field we will use a field with the name "CustomerID." This name of a field differs from "ID." This name also does not meet the scheme "EntityTypeID" because "Customer" and "Customers" have different spelling. For the SQL Server scheme and LINQ to SQL model see Figure 3.

Figure 3: Scheme of data with custom key field

If we try to access this web service ,we will receive an error message (see Figure 4).

Figure 4: Error message (unable to detect a key field)

There are two ways to solve this error. The first way is to change the entity name to "Customer." In this case the "CustomerID" field corresponds to the "EntityTypeID"scheme. If entity renaming is impossible, it is possible to use DataServiceKey attribute for the explicit setting of key fields.

DataServiceKey attribute is set for entity. All fields, which should be key, are specified in parameters of this attribute. It is possible to specify several fields. It is also possible to open generated code by LINQ to SQL designer and to mark entity with this attribute. However, your code will be destroyed when data model is changed in the designer. Therefore, it is better to create a partial class and to mark it with attribute. The example of such code is shown in Listing 1.

Listing 1 - The usage of DataServiceKey attribute

using System.Data.Services.Common;
[DataServiceKey("CustomerID")]
public partial class Customers
{
}

After "Customers" class is marked with the DataServiceKey attribute, it can be correctly used as a data model for ADO.NET Data Services.

It is known that ADO.NET Data Services also allows changing data. For this purpose the data model should have IUpdatable interface. By means of this interface, data is updating. The LINQ to SQL data model does not have this interface. This is a matter of a problem.

The reasons of it consist that LINQ-to-SQL platform was released much earlier than ADO.NET Data Services. As IUpdatable interface is part of ADO.NET Data Services, it was impossible to implement it in LINQ to SQL model. To solve this problem, implement IUpdatable as an interface.

Interface IUpdatable should be implemented in a data context. For these purposes it is possible to create a partial class and to implement the interface there. We choose a data context; click the right button of the mouse and choose "View code" item.

Figure 5: Creation of a partial class

After creating a class and setting IUpdatable interface as base it is possible to receive a stub code as shown in Listing 2.

Listing 2 - Stub code for IUpdatable interface

public partial class CustomersDataContext : IUpdatable
{
      public void AddReferenceToCollection(object targetResource, string 
            propertyName, object resourceToBeAdded)
      {
            throw new System.NotImplementedException();
      }
 
      public void ClearChanges()
      {
            throw new System.NotImplementedException();
      }
 
      public object CreateResource(string containerName, string fullTypeName)
      {
            throw new System.NotImplementedException();
      }
 
      public void DeleteResource(object targetResource)
      {
            throw new System.NotImplementedException();
      }
 
      public object GetResource(System.Linq.IQueryable query, string fullTypeName)
      {
            throw new System.NotImplementedException();
      }
 
      public object GetValue(object targetResource, string propertyName)
      {
            throw new System.NotImplementedException();
      }
 
      public void RemoveReferenceFromCollection(object targetResource, 
            string propertyName, object resourceToBeRemoved)
      {
            throw new System.NotImplementedException();
      }
 
      public object ResetResource(object resource)
      {
            throw new System.NotImplementedException();
      }
 
      public object ResolveResource(object resource)
      {
            throw new System.NotImplementedException();
      }
 
      public void SaveChanges()
      {
            throw new System.NotImplementedException();
      }
 
      public void SetReference(object targetResource, string propertyName,
            object propertyValue)
      {
            throw new System.NotImplementedException();
      }
 
      public void SetValue(object targetResource, string propertyName, 
            object propertyValue)
      {
            throw new System.NotImplementedException();
      }
}

We can see that IUpdatable interface consists of resource getting methods, resource creation and resource deletion. It also contains methods for getting and setting value of a field, creation and deletion of references and saving changes. Apparently this interface is simple and laconic enough. We can implement the given interface once and use it for various models, without dependence on what data is used there.

The following code can be one of the possible implementations.

Listing 3 - Implementation of IUpdatable interface

partial class CustomersDataContext : IUpdatable
{
    public object CreateResource(string containerName, string fullTypeName)
    {
        Type t = Type.GetType(fullTypeName);
        ITable table = (ITable)this.GetType().GetProperty(containerName).
            GetValue(this, null);
        object resource = Activator.CreateInstance(t);
        table.InsertOnSubmit(resource);
        return resource;
    }
 
    public object GetResource(IQueryable query, string fullTypeName)
    {
        object resource = null;
 
        foreach (object o in query)
        {
            if (resource != null)
            {
                throw new Exception("Expected a single response");
            }
            resource = o;
        }
 
        if ((fullTypeName != null) && 
            (resource.GetType() != Type.GetType(fullTypeName)))
        {
            throw new Exception("Unexpected type for resource");
        }
        
        return resource;
    }
 
    public object ResetResource(object resource)
    {
        Type t = resource.GetType();
        MetaTable table = this.Mapping.GetTable(t);
        object dummyResource = Activator.CreateInstance(t);
        foreach (var member in table.RowType.DataMembers)
        {
            if ((member.IsPrimaryKey == false) && (member.IsDeferred == false) &&
                (member.IsAssociation == false) && (member.IsDbGenerated == false))
            {
                object defaultValue = member.MemberAccessor.
                    GetBoxedValue(dummyResource);
                member.MemberAccessor.SetBoxedValue(ref resource, defaultValue);
            }
        }
        return resource;
    }
 
    public void SetValue(object targetResource, string propertyName, 
            object propertyValue)
    {
        PropertyInfo pi = targetResource.GetType().GetProperty(propertyName);
        if (pi == null)
        {
            throw new Exception("Can not find property");
        }
        pi.SetValue(targetResource, propertyValue, null);
    }
 
    public object GetValue(object targetResource, string propertyName)
    {
        PropertyInfo pi = targetResource.GetType().GetProperty(propertyName);
        if (pi == null)
        {
            throw new Exception("Can not find property");
        }
        return pi.GetValue(targetResource, null);
    }
 
    public void SetReference(object targetResource, string propertyName,
      object propertyValue)
    {
        this.SetValue(targetResource, propertyName, propertyValue);
    }
 
    public void AddReferenceToCollection(object targetResource,
      string propertyName, object resourceToBeAdded)
    {
        PropertyInfo pi = targetResource.GetType().GetProperty(propertyName);
        if (pi == null)
        {
            throw new Exception("Can not find property");
        }
        System.Collections.IList collection = (System.Collections.IList)pi.
            GetValue(targetResource, null);
        collection.Add(resourceToBeAdded);
    }
 
    public void RemoveReferenceFromCollection(object targetResource,
      string propertyName, object resourceToBeRemoved)
    {
        PropertyInfo pi = targetResource.GetType().GetProperty(propertyName);
        if (pi == null)
        {
            throw new Exception("Can not find property");
        }
        
        System.Collections.IList collection = (IList)pi.
            GetValue(targetResource, null);
        collection.Remove(resourceToBeRemoved);
    }
 
    public void DeleteResource(object targetResource)
    {
        ITable table = this.GetTable(targetResource.GetType());
        table.DeleteOnSubmit(targetResource);
    }
 
    public void SaveChanges()
    {
        this.SubmitChanges();
    }
 
    public object ResolveResource(object resource)
    {
        return resource;
    }
 
    public void ClearChanges()
    {
    }
}

[Download Source]

In this article we analyzed typical problems which appear while using ADO.NET Data Services together with LINQ to SQL. Apparently, these problems are solved in a simple way. Thus, we can use all power of these two platforms without any restrictions. All examples of a code in this article are accessible for downloading from the link mentioned in the downloads section.