How to: Create a Data Service Using a LINQ to SQL Data Source (WCF Data Services)

Important

WCF Data Services has been deprecated and will no longer be available for download from the Microsoft Download Center. WCF Data Services supported earlier versions of the Microsoft OData (V1-V3) protocol only and has not been under active development. OData V1-V3 has been superseded by OData V4, which is an industry standard published by OASIS and ratified by ISO. OData V4 is supported through the OData V4 compliant core libraries available at Microsoft.OData.Core. Support documentation is available at OData.Net, and the OData V4 service libraries are available at Microsoft.AspNetCore.OData.

RESTier is the successor to WCF Data Services. RESTier helps you bootstrap a standardized, queryable, HTTP-based REST interface in minutes. Like WCF Data Services before it, Restier provides simple and straightforward ways to shape queries and intercept submissions before and after they hit the database. And like Web API + OData, you still have the flexibility to add your own custom queries and actions with techniques you're already familiar with.

WCF Data Services exposes entity data as a data service. The reflection provider enables you to define a data model that is based on any class that exposes members that return an IQueryable<T> implementation. To be able to make updates to data in the data source, these classes must also implement the IUpdatable interface. For more information, see Data Services Providers. This topic shows you how to create LINQ to SQL classes that access the Northwind sample database by using the reflection provider, as well as how to create the data service that is based on these data classes.

To add LINQ to SQL classes to a project

1. From within a Visual Basic or C# application, on the Project menu, click Add > New Item.

2. Click the LINQ to SQL Classes template.

3. Change the name to Northwind.dbml.

4. Click Add.

   The Northwind.dbml file is added to the project and the Object Relational Designer (O/R Designer) opens.

5. In Server/Database Explorer, under Northwind, expand Tables and drag the Customers table onto the Object Relational Designer (O/R Designer).

   A Customer entity class is created and appears on the design surface.

6. Repeat step 6 for the Orders, Order_Details, and Products tables.

7. Right-click the new .dbml file that represents the LINQ to SQL classes and click View Code.

   This creates a new code-behind page named Northwind.cs that contains a partial class definition for the class that inherits from the DataContext class, which in this case is NorthwindDataContext.

8. Replace the contents of the Northwind.cs code file with the following code. This code implements the reflection provider by extending the DataContext and data classes generated by LINQ to SQL:

   using System;
   using System.ComponentModel;
   using System.Collections;
   using System.Linq;
   using System.Reflection;
   using System.Data.Linq;
   using System.Data.Linq.Mapping;
   using System.Data.Services;
   using System.Data.Services.Common;
   
   namespace NorthwindService
   {
       // Define the key properties for the LINQ to SQL data classes.
       [DataServiceKeyAttribute("CustomerID")]
       public partial class Customer { }
   
       [DataServiceKeyAttribute("ProductID")]
       public partial class Product { }
   
       [DataServiceKeyAttribute("OrderID")]
       public partial class Order { }
   
       [DataServiceKeyAttribute("OrderID", "ProductID")]
       public partial class Order_Detail { }
   
       #region IUpdatable implementation
       // Define the IUpdatable implementation for LINQ to SQL.
       public partial class NorthwindDataContext : IUpdatable
       {
           // Creates an object in the container.
           object IUpdatable.CreateResource(string containerName, string fullTypeName)
           {
               Type t = Type.GetType(fullTypeName, true);
               ITable table = GetTable(t);
               object resource = Activator.CreateInstance(t);
               table.InsertOnSubmit(resource);
               return resource;
           }
   
           // Gets the object referenced by the resource.
           object IUpdatable.GetResource(IQueryable query, string fullTypeName)
           {
               object resource = query.Cast<object>().SingleOrDefault();
   
               // fullTypeName can be null for deletes
               if (fullTypeName != null && resource.GetType().FullName != fullTypeName)
                   throw new ApplicationException("Unexpected type for this resource.");
               return resource;
           }
   
           // Resets the value of the object to its default value.
           object IUpdatable.ResetResource(object resource)
           {
               Type t = resource.GetType();
               MetaTable table = Mapping.GetTable(t);
               object dummyResource = Activator.CreateInstance(t);
               foreach (var member in table.RowType.DataMembers)
               {
                   if (!member.IsPrimaryKey && !member.IsDeferred &&
                       !member.IsAssociation && !member.IsDbGenerated)
                   {
                       object defaultValue = member.MemberAccessor.GetBoxedValue(dummyResource);
                       member.MemberAccessor.SetBoxedValue(ref resource, defaultValue);
                   }
               }
               return resource;
           }
   
           // Sets the value of the given property on the object.
           void IUpdatable.SetValue(object targetResource, string propertyName, object propertyValue)
           {
               MetaTable table = Mapping.GetTable(targetResource.GetType());
               MetaDataMember member = table.RowType.DataMembers.Single(x => x.Name == propertyName);
               member.MemberAccessor.SetBoxedValue(ref targetResource, propertyValue);
           }
   
           // Gets the value of a property on an object.
           object IUpdatable.GetValue(object targetResource, string propertyName)
           {
               MetaTable table = Mapping.GetTable(targetResource.GetType());
               MetaDataMember member =
                   table.RowType.DataMembers.Single(x => x.Name == propertyName);
               return member.MemberAccessor.GetBoxedValue(targetResource);
           }
   
           // Sets the related object for a reference.
           void IUpdatable.SetReference(
               object targetResource, string propertyName, object propertyValue)
           {
               ((IUpdatable)this).SetValue(targetResource, propertyName, propertyValue);
           }
   
           // Adds the object to the related objects collection.
           void IUpdatable.AddReferenceToCollection(
               object targetResource, string propertyName, object resourceToBeAdded)
           {
               PropertyInfo pi = targetResource.GetType().GetProperty(propertyName);
               if (pi == null)
                   throw new Exception("Can't find property");
               IList collection = (IList)pi.GetValue(targetResource, null);
               collection.Add(resourceToBeAdded);
           }
   
           // Removes the object from the related objects collection.
           void IUpdatable.RemoveReferenceFromCollection(
               object targetResource, string propertyName, object resourceToBeRemoved)
           {
               PropertyInfo pi = targetResource.GetType().GetProperty(propertyName);
               if (pi == null)
                   throw new Exception("Can't find property");
               IList collection = (IList)pi.GetValue(targetResource, null);
               collection.Remove(resourceToBeRemoved);
           }
   
           // Deletes the resource.
           void IUpdatable.DeleteResource(object targetResource)
           {
               ITable table = GetTable(targetResource.GetType());
               table.DeleteOnSubmit(targetResource);
           }
   
           // Saves all the pending changes.
           void IUpdatable.SaveChanges()
           {
               SubmitChanges();
           }
   
           // Returns the actual instance of the resource represented
           // by the resource object.
           object IUpdatable.ResolveResource(object resource)
           {
               return resource;
           }
   
           // Reverts all the pending changes.
           void IUpdatable.ClearChanges()
           {
               // Raise an exception as there is no real way to do this with LINQ to SQL.
               // Comment out the following line if you'd prefer a silent failure
               throw new NotSupportedException();
           }
       #endregion
       }
   }
   

   Imports System.ComponentModel
   Imports System.Collections
   Imports System.Linq
   Imports System.Reflection
   Imports System.Data.Linq
   Imports System.Data.Linq.Mapping
   Imports System.Data.Services
   Imports System.Data.Services.Common
   
   ' Define the key properties for the LINQ to SQL data classes.
   <DataServiceKeyAttribute("CustomerID")> _
   Partial Public Class Customer
   End Class
   <DataServiceKeyAttribute("ProductID")> _
   Partial Public Class Product
   End Class
   <DataServiceKeyAttribute("OrderID")> _
   Partial Public Class Order
   End Class
   <DataServiceKeyAttribute("OrderID", "ProductID")> _
   Partial Public Class Order_Detail
   End Class
   #Region "IUpdatable implementation"
   ' Define the IUpdatable implementation for LINQ to SQL.
   Partial Public Class NorthwindDataContext
       Implements IUpdatable
       ' Creates an object in the container.
       Function CreateResource(ByVal containerName As String, ByVal fullTypeName As String) _
           As Object Implements IUpdatable.CreateResource
   
           Dim t = Type.GetType(fullTypeName, True)
           Dim table = GetTable(t)
           Dim resource = Activator.CreateInstance(t)
           table.InsertOnSubmit(resource)
           Return resource
       End Function
       ' Gets the object referenced by the resource.
       Function GetResource(ByVal query As IQueryable, ByVal fullTypeName As String) As Object _
        Implements IUpdatable.GetResource
           Dim resource = query.Cast(Of Object)().SingleOrDefault()
   
           ' fullTypeName can be null for deletes
           If fullTypeName IsNot Nothing AndAlso resource.GetType().FullName <> fullTypeName Then
               Throw New ApplicationException("Unexpected type for this resource.")
           End If
           Return resource
       End Function
       ' Resets the value of the object to its default value.
       Function ResetResource(ByVal resource As Object) As Object _
           Implements IUpdatable.ResetResource
           Dim t = resource.GetType()
           Dim table = Mapping.GetTable(t)
           Dim dummyResource = Activator.CreateInstance(t)
           For Each member In table.RowType.DataMembers
   
               If Not member.IsPrimaryKey AndAlso Not member.IsDeferred AndAlso _
                   Not member.IsAssociation AndAlso Not member.IsDbGenerated Then
                   Dim defaultValue = member.MemberAccessor.GetBoxedValue(dummyResource)
                   member.MemberAccessor.SetBoxedValue(resource, defaultValue)
               End If
           Next
           Return resource
       End Function
       ' Sets the value of the given property on the object.
       Sub SetValue(ByVal targetResource As Object, ByVal propertyName As String, _
                    ByVal propertyValue As Object) Implements IUpdatable.SetValue
           Dim table = Mapping.GetTable(targetResource.GetType())
           Dim member = table.RowType.DataMembers.Single(Function(x) x.Name = propertyName)
           member.MemberAccessor.SetBoxedValue(targetResource, propertyValue)
       End Sub
       ' Gets the value of a property on an object.
       Function GetValue(ByVal targetResource As Object, ByVal propertyName As String) _
       As Object Implements IUpdatable.GetValue
           Dim table = Mapping.GetTable(targetResource.GetType())
           Dim member = _
           table.RowType.DataMembers.Single(Function(x) x.Name = propertyName)
           Return member.MemberAccessor.GetBoxedValue(targetResource)
       End Function
       ' Sets the related object for a reference.
       Sub SetReference(ByVal targetResource As Object, ByVal propertyName As String, _
                        ByVal propertyValue As Object) Implements IUpdatable.SetReference
           CType(Me, IUpdatable).SetValue(targetResource, propertyName, propertyValue)
       End Sub
       ' Adds the object to the related objects collection.
       Sub AddReferenceToCollection(ByVal targetResource As Object, ByVal propertyName As String, _
                                    ByVal resourceToBeAdded As Object) _
                                    Implements IUpdatable.AddReferenceToCollection
           Dim pi = targetResource.GetType().GetProperty(propertyName)
           If pi Is Nothing Then
               Throw New Exception("Can't find property")
           End If
           Dim collection = CType(pi.GetValue(targetResource, Nothing), IList)
           collection.Add(resourceToBeAdded)
       End Sub
       ' Removes the object from the related objects collection.
       Sub RemoveReferenceFromCollection(ByVal targetResource As Object, ByVal propertyName As String, _
                                         ByVal resourceToBeRemoved As Object) _
                                         Implements IUpdatable.RemoveReferenceFromCollection
           Dim pi = targetResource.GetType().GetProperty(propertyName)
           If pi Is Nothing Then
               Throw New Exception("Can't find property")
           End If
           Dim collection = CType(pi.GetValue(targetResource, Nothing), IList)
           collection.Remove(resourceToBeRemoved)
       End Sub
       ' Deletes the resource.
       Sub DeleteResource(ByVal targetResource As Object) _
       Implements IUpdatable.DeleteResource
           Dim table = GetTable(targetResource.GetType())
           table.DeleteOnSubmit(targetResource)
       End Sub
       ' Saves all the pending changes.
       Sub SaveChanges() Implements IUpdatable.SaveChanges
           SubmitChanges()
       End Sub
       ' Returns the actual instance of the resource represented 
       ' by the resource object.
       Function ResolveResource(ByVal resource As Object) As Object Implements IUpdatable.ResolveResource
           Return resource
       End Function
       ' Reverts all the pending changes.
       Sub ClearChanges() Implements IUpdatable.ClearChanges
           ' Raise an exception as there is no real way to do this with LINQ to SQL.
           ' Comment out the following line if you'd prefer a silent failure
           Throw New NotSupportedException()
       End Sub
   End Class
   #End Region
   

To create a data service by using a LINQ to SQL-based data model

1. In Solution Explorer, right-click the name of your ASP.NET project, and then click Add > New Item.

2. In the Add New Item dialog box, select the WCF Data Service template from the Web category.

   

   Note

   The WCF Data Service template is available in Visual Studio 2015, but not in Visual Studio 2017 or later.

3. Supply a name for the service, and then click OK.

   Visual Studio creates the XML markup and code files for the new service. By default, the code-editor window opens.

4. In the code for the data service, replace the comment /* TODO: put your data source class name here */ in the definition of the class that defines the data service with the type that is the entity container of the data model, which in this case is NorthwindDataContext.

5. In the code for the data service, replace the placeholder code in the InitializeService function with the following:

   config.SetEntitySetAccessRule("Customers", EntitySetRights.ReadMultiple);
   config.SetEntitySetAccessRule("Orders", EntitySetRights.AllRead
                               | EntitySetRights.WriteMerge);
   config.SetEntitySetAccessRule("Order_Details", EntitySetRights.AllRead
                               | EntitySetRights.AllWrite);
   config.SetEntitySetAccessRule("Products", EntitySetRights.ReadMultiple);
   

   config.SetEntitySetAccessRule("Customers", EntitySetRights.ReadMultiple)
   config.SetEntitySetAccessRule("Orders", EntitySetRights.AllRead _
                               Or EntitySetRights.WriteMerge)
   config.SetEntitySetAccessRule("Order_Details", EntitySetRights.AllRead _
                               Or EntitySetRights.AllWrite)
   config.SetEntitySetAccessRule("Products", EntitySetRights.ReadMultiple)
   

   This enables authorized clients to access resources for the three specified entity sets.

6. To test the Northwind.svc data service by using a Web browser, follow the instructions in the topic Accessing the Service from a Web Browser.

See also

• How to: Create a Data Service Using an ADO.NET Entity Framework Data Source
• How to: Create a Data Service Using the Reflection Provider
• Data Services Providers