Flexible data access using the LINQ to SQL and entity frameworks

So far, Microsoft has released two products for developers to reduce impedance mis-tuning between the relational data field and object-oriented programming: LINQ to SQL and ADO. NET Entity frameworks. With any of these products, you can achieve Object Persistence without having to write most of the test code. However, applying these Object Relational ORM ing (ORM) technologies to the service-oriented application architecture poses new challenges for application developers. For example, how to create a data access layer (DAL) that separates Object persistence from other parts of the application so that one ORM provider can be used to swap out another provider as needed. How does one track changes to an object without installing the LINQ to SQL or object framework on the client? How can I insert, update, and delete multiple entities in a single transaction by calling the service only once? In this article, I will give a brief introduction and provide some suggestions on how to solve the above problems. First, I will create a dal for processing orders based on the northwind sample database. Two implementations of Dal (one using LINQ to SQL and the other using the Entity Framework) depend on one interface (see figure 1 ). Both LINQ to SQL and the Entity Framework have tools to generate entities based on the database architecture, but Dal does not use these entities. Instead, it only discloses data transmission objects (DTO) that are often ignored when they are related to their own durability ).
 
Figure 1 service-oriented architecture for order processing applications (click an image to view the larger image)
The LINQ to SQL and object frameworks use different methods to persistently disconnect entities, depending on whether to create new entities or update existing entities. Therefore, to call an appropriate API, you must first understand the object status. For example, if you have the updateorder method to accept orders with order details, you can add three parameters to the updateorder method to accept order details created, updated, and deleted. However, it is easy to confuse the method signature by detecting order details.
In addition, you can pass out-of-band change status information (for example, in soap or HTTP headers), but this will combine change tracking with communication protocols. Next I will introduce another method-change the State as part of the data conventions for each entity, and I suggest using this method:
[Datacontract]
Public Enum trackinginfo
{
[Enummember]
Unchanged,
[Enummember]
Created,
[Enummember]
Updated,
[Enummember]
Deleted
}
In addition to other attributes, each DTO has a trackingstate attribute enumerated using trackinginfo. Both parties only need to agree to this data agreement.
Although I have added the trackingstate attribute for each DTO, I have to follow up on objects created, updated, or deleted by clients that have not installed the LINQ to SQL or object framework, and set the trackingstate attribute to the corresponding value (see figure 1 ). I created a set of general change tracking to execute this task. This collection has two types: Extended observablecollection <t> for Windows Presentation Foundation (WPF) applications, and extended bindinglist <t> for Windows Forms applications. Each collection can cache deleted items before deleting items from the collection, and each collection has the getchanges method. This method returns only the inserted, updated, or deleted items.

Create a data access layer
Dal helps isolate other parts of the application from detailed information about Object persistence. Therefore, objects exposed through dal must exclude the rest of any specific data access technology. The LINQ to SQL and entity frameworks allow you to use the command line tool and the Visual Studio designer to create entities, but some projects in these code-generated entities are inconsistent with their source.
For example, to support the delayed loading of correlated objects, the collection type of LINQ to SQL uses entityref <t> and entityset <t> to indicate the data relationship, the Entity Framework uses entityreference <t> and entitycollection <t> to indicate navigation attributes. In addition, these entities Add code elements to support client server solutions (such as local method verification and Data Binding using inotifypropertychanged, if these entities are only used to communicate with the Windows Communication Foundation (WCF) service, you do not need to use these code elements.
Because the LINQ to SQL and object frameworks support the serialization of entities using datacontractserializer (marking using the datacontract and datamember attributes), you may want to use them for your service operations, no matter how much burden it will bring. After all, when you set a WCF Service reference, the collection type (for example, the collection type used by LINQ to SQL and the Entity Framework) is displayed as a simple array on the client.
However, if you set the serializationmode of LINQ to SQL datacontext from "NONE" to "unidirectional", only the "multiple" Square in one-to-many relationship has the datamember attribute. This indicates that if your order entity has two attributes: "customer" and "order_details", only order_details is marked with "datamember" and included in the data conventions, but "customer" cannot. On the other hand, the object framework may contain more fields than you want to include in the Data conventions, so that you can generate classes on the client to implement functions specific to the object framework. For example, entitykey, entitykeymember, entityobject, or entityreference.
For the above reasons, you should avoid exposing entities generated by using Dal to SQL or object framework tools, instead of returning simple DTO, which only serves to transmit data across service boundaries. For example, order DTO (see figure 2) only contains attributes, not methods. All DTO are defined in the ransferobjects namespace to distinguish it from the class of the same name generated by the tool.
Figure 2 DTO of the order in the northwind
Flexible Dal should be able to prevent order service from being affected by basic persistence technology changes. For example, if you choose to use SQL Server to store data and your object model is very similar to the database architecture, you decide to use the LINQ to SQL persistence object. However, shortly after you write the persistence logic of LINQ to SQL, you decide to use another database system (such as Oracle) or you want to use the ing function of the Entity Framework to separate the conceptual and logical architectures. Maybe a new data access technology has been released and you want to use it. If you have designed a flexible Dal Based on the plug-in architecture, you can switch the provider at any time according to the entries in APP. config.
To achieve this flexibility, I have created an idataprovider interface that provides methods to retrieve and update customer orders and retrieve supported customer and product information (see figure 3 ). In this example, the application contains two classes that implement idataprovider: sqldataprovider using LINQ to SQL; entitydataprovider using the Entity Framework and LINQ to entities (see figure 1 ). You can only use the Dal app. in the config file, set "dataprovider" to enter a fully qualified class name, and use the createinstance method of the Assembly class to create an instance of this class to convert it to idataprovider. Order Service has no special requirements on classes used to implement idataprovider. Therefore, you can select any data provider:
Idataprovider provider =
Assembly. getexecutingassembly ()
. Createinstance (settings. Default. dataprovider, true)
As idataprovider;
Figure 3 separating Dal from Interfaces
By default, the LINQ to SQL and object frameworks generate entities in the same namespace as the project. However, if you place the new "LINQ to SQL classes" project in the l2s project folder, this tool uses this folder name as the nested namespace, this helps to distinguish between the LINQ to SQL entity and the DTO entity. Similarly, if you place the new "ADO. NET Entity Data Model" in the l2e project folder, the Entity Framework entity also exists in the nested l2e namespace. In addition, you can specify the namespace in the attributes window of the LINQ to SQL and the visual component designer of the object framework.
Here, I choose to generate an object for both the LINQ to SQL and the Entity Framework, and convert the query result to DTO. There are two possibilities: completely eliminating the entity generated by the tool; ing DTO using XML files. Entity Framework already uses XML ing files. However, in the first version of the Entity Framework, using a simple C # object (POCO) won't be able to achieve full persistence transparency (PI ). In version 1, you can achieve this goal, but the entity must first implement two interfaces: ientitywithrelationships and ientitywithchangetracking. Therefore, you need to use ipoco (POCO + Interface) in this case ).. We hope that the future version of the object framework can improve the support for poco and persistence transparency.
LINQ to SQL does support poing poco to the database architecture using XML ing files. However, projection from l2s. Order or l2e. Order to DTO. Order provides greater flexibility. For example, the sqldataprovider getorder method shown in Figure 4 is based on l2s. the contactname attribute of the Order customer attribute is filled with DTO. the customername attribute of order actually reflects the relationship between customer and order. Similarly, I can set the productname attribute of DTO. orderdetail based on the product attribute of l2s. order_detail. In each case, I must configure the loadoptions attribute of datacontext to pre-load the LINQ to SQL entity.
Figure 4 The getorder method returns DTO. Order using the LINQ to SQL framework
Now let's go back to the getorder method of entitydataprovider. This method returns DTO. Order Based on l2e. Order using LINQ to entities (see figure 5 ). Note: The include operator is used to pre-load the order_details and order_details.product attributes. (For details about the LINQ operator, see the data point column "standard query operator of LINQ "). Neither the Entity Framework nor LINQ to SQL provides the ready-made delayed loading function. Therefore, you must specify the entities to be included in the query results. After retrieving the specified order, you can use it to create a new DTO. Order.
Figure 5 The getorder method returns DTO. Order using the LINQ to entities Method
Persists objects
The LINQ to SQL and object frameworks use different methods to persistently disconnect entities. For example, assume that you want to create a new order with one or more order details. As shown in figure 6, LINQ to SQL requires at least two lines of code: one line of code calls insertonsubmit to insert the order, and one line of code uses insertallonsubmit to insert the detailed items of the order. The Entity Framework only requires you to add the order to the orderset of objectcontext. It automatically adds the complete object chart (see figure 7 ). Both the LINQ to SQL and the Entity Framework adopt the following procedure: First insert the parent order, obtain the orderid value (the ID column in the database), and then insert the suborder details.
Figure 6 create an order using LINQ to SQL
Figure 7 create an order using LINQ to entities
Before introducing how to delete or update an object, I need to first introduce how to handle concurrency issues. You need to attach a disconnected entity to the LINQ to SQL to call deleteonsubmit. In this way, the system will prompt you to use LINQ to SQL to perform the optimal concurrency check. If you try to delete a project that has been changed by another user, the deletion will fail and send a changeconflictexception. In fact, there is no simple way to disable this behavior, because if you try to call deleteonsubmit without calling attach, LINQ to SQL will cause invalidoperationexception. However, the Entity Framework does not force you to handle change conflicts when deleting objects (see the code download included in this article ).
Figure 8 shows the code for updating an order using LINQ to SQL. Note that the attach method accepts the asmodified parameter of the bool type. If you set this parameter to "true" for LINQ to SQL, you want to use the timestamp column for concurrent management. In this way, the current timestamp value is included in the WHERE clause of the SQL update statement. When another user updates this record, this update does not affect any rows, and LINQ to SQL will cause changeconflictexception.
Figure 8 update an order using LINQ to SQL
With this policy, the client does not need to retain the original value or submit it along with the update. Instead, it passes these values to other overloading of the attach method. The system does not need to name this column. You only need to include the timestamp data type. After you add a table with a timestamp column to the Visual Studio LINQ to SQL designer, the updatecheck attribute of all object fields will be set to never, in addition, the time stamp is used to check the change conflict.
Currently, it is a little complicated to use the Entity Framework to update entities and manage concurrency compared with the LINQ to SQL statement, because even if the timestamp column is used in concurrency management, it also requires the original object value (see figure 9 ). (This aspect may be improved in the next version of the Entity Framework .) Because the client does not provide the original order, you need to retrieve the order from the database and set its updated attribute to the value provided by the client. This "original" value is the value you really need. Make sure to submit changes to the updated by detaching and attaching the order to the object context again, or calling acceptallchanges. Then, call applypropertychanges to update the original order using the value provided by the update order. In addition, you also need to perform a step before the Entity Framework can use the timestamp column for concurrent checks: that is, on the order object, set the concurrencymode value of the updated attribute from "NONE" to "fixed" (see Figure 10 ).
Figure 9 update an order using LINQ to entities
 
Figure 10 set the updated concurrencymode to "fixed"

Tracking changes across service boundaries
If you want to call the service only once to persist the changes of multiple entities in the same transaction, you need to use one method to determine the change status of each entity. This is why every DTO In the example application contains the trackingstate attribute. If this attribute is already set on the client, Dal can use the appropriate LINQ to SQL or object framework API to persistently insert, update, and delete disconnected entities. This allows you to pass the DTO array to the service operation for batch update, so that you can use a simple LINQ to objects query to find the Order details that are inserted, updated, or deleted. For example, to obtain only the Order details added to the order, you can use trackingstate created to query such items in the collection:
List <order_detail> inserteddetails =
(From OD in order. orderdetails
Where OD. trackingstate = trackinginfo. Created
Select New order_detail
{
Orderid = OD. orderid,
Productid = OD. productid,
Quantity = OD. quantity,
Unitprice = OD. unitprice,
Updated = OD. Updated = NULL? New byte [0]: OD. Updated
}). Tolist ();
You must convert the null value of updated to an empty byte array, because the timestamp column in the database table cannot be empty. After obtaining the list of inserted order details, you can use the appropriate LINQ to SQL API to add new entities by passing the Order Details set:
DB. order_details.insertallonsubmit (inserteddetails );
As you expected, the attachallonsubmit and deleteallonsubmit methods can meet your needs. When submitchanges is called for datacontext, LINQ to SQL ensures that the changes are submitted in the correct order (the parent-child sequence is used for the inserted project and the Parent-Child sequence is used for the deleted project ), and include all operations in a transaction. You can use the same method to use the LINQ to entities persistence order details. For a complete list of updateorder codes applicable to LINQ to SQL and the Entity Framework, see the download content.
Now, we can see that the Dal relies on the client to track the change status of each DTO. The most convenient mechanism for tracking object changes on the client is the generic set. To enable the set to update the trackingstate attribute, type parameters should be constrained to implement interfaces. (You can also use reflection to obtain and set the trackingstate attribute, but generic type constraints provide type security and better performance .) Enter the itrackable interface that only contains the trackingstate attribute:

Public interface itrackable
{
Trackinginfo trackingstate {Get; set ;}
}
 
As I mentioned earlier, there are two types of tracking Change sets: one for Windows Forms, extended bindinglist <t>, and the other for WPF applications, extended observablecollection <t>. Instead of repeatedly changing the tracking logic in these two sets, I concentrated the logic in the changetrackinghelper <t> class, this class derives from collection <t> and restricts t to implement itrackable (see figure 11 ). In addition, it also limits t to implement inotifypropertychanged to process the propertychanged event for each project and set the trackingstate attribute to updated.
Figure 11 change tracking helper class
After a project is added to a collection, its trackingstate is set to created. After a project is deleted, its trackingstate is set to deleted and cached in the collection of deleted projects. Before tracking, you must set the tracking attribute of the set to true to subscribe to the propertychanged event of each project. The getchanges method creates a new changetrackinghelper set, which contains only the items marked as creation, update, or deletion.

Configure the client
We have learned about a set of general tracking changes, which can be used now! I have placed these collections in a separate class library, so you only need to reference them from the client application. However, when you add a service reference to a client project, you must perform other steps to use the class generated by svcutil.exe in the collection. Although classes such as customer, product, order, and orderdetails already have the trackingstate attribute, they do not implement the itrackable interface required by changetrackinghelper <t>.
In addition, You must place the trackinginfo enumeration in the namespace that can be found in the tracking change set, but not the nested namespace, because svcutil.exe will place it here when you add a service reference. Finally, if the array of objects returned by the service operation is displayed as a collection of trace changes in the client application, it is better. This causes the data type of order. orderdetails to be set to changetrackingcollection <t> or changetrackinglist <t> (rather than the orderdetail array), thus reducing the workload of sending changed order details to the service.
To set a Windows form or WPF client application to track object changes using a collection of trace changes and send these changes to the service, follow these steps. Perform the following steps in the given order:
In a Windows form or WPF client application, set a reference to the clientchangetracker class library project or assembly (itrackable interface and tracking changes to the placement of the collection.
Add a service reference pointing to the metadata Endpoint or Web Service Description Language (WSDL) URL when the WCF Service Application is running. Before adding a service reference, it is very important to reference the clientchangetracker assembly. By default, service references reuse all types in the referenced assembly, because the trackinginfo enumeration contained in clientchangetracker has datacontract and enummember, svcutil.exe will reuse this enumeration and will not copy it to the reference created by the service reference. CS code file.
After setting a service reference, add a local class for each DTO class created by the service reference. For example, if your service operations expose the customer, product, order, and orderdetail objects, they will all be included in the Service reference. If all files of the project are displayed and service references and reference. svcmap nodes are expanded, you will see these classes in reference. CS. Write down the namespace to which they belong (it should be consistent with the location specified when you add the service reference ). Add the class file to the project and change the namespace to reflect the namespace in reference. CS. In this namespace, add public local classes for each DTO class and implement the itrackable interface:
Namespace wpfclient. orderservice
{
Public partial Class Customer: itrackable {}

Public partial class product: itrackable {}

Public partial class order: itrackable {}

Public partial class orderdetail: itrackable {}
}
Note that the trackingstate attribute is missing. Because this attribute is included in the Code Generation class as part of each DTO data convention, you do not need to insert this attribute here. To use these classes more easily in applications, you can also insert other code in these classes, such as initializing class Constructor (called when new is used to create classes; ). For examples, see download code.
Finally, you may want to configure the service reference to use changetrackingcollection <t> or changetrackinglist <t> as the collection type, rather than system. array. This means that when the service operation returns the order array, it will be embodied on the client as changetrackingcollection <order> or changetrackinglist <order>. The orderdetails attribute of each order may be displayed as a trace to change the collection type. Do you know how cool it is? To achieve this wonderful transformation, you must open the reference. svcmap file (display all project files and open the service reference for viewing) and then study XML in depth. For the WPF client, configure the collectionings ings element as follows:
<Collectionmappings>
<Collectionmapping typename = "changetracker. changetrackingcollection '1" Category = "list"/>
</Collectionmappings>
For Windows form clients, replace changetracker. changetrackingcollection '1 with changetracker. changetrackinglist '1.
Now, when the service application is running, right-click the service reference and select "Update Service reference", and re-compile the client application. If everything goes well, the project is successfully built and you can continue to perform the operation.
After completing these steps, the rest is to write code for the client application to retrieve some DTO and bind them to the UI (you can update DTO here and send it back to the service to save it to the database ). To retrieve an object from a service, you only need to create a service proxy instance and call the operation to obtain the customer, product, and order. For example, if the service contains the getorder method that accepts the orderid of the int type, your client code may be as follows:
Using (orderserviceclient proxy = new orderserviceclient ())
{
Currentorder = proxy. getorder (orderid );
}
If your data binding settings are correct, order and order details should be displayed on the user interface. Figure 12 shows the display of the sample application on the WPF client. Click "get order" to search for an existing order and select it from the customer order list. Click "new order" to create a new order; click the delete order button to delete the current order by calling deleteorder on the server proxy.
 
Figure 12 example WPF client application
Click the Save order button to call the createorder operation of the service to persist the newly created order, or call the updateorder operation to save the changes to the existing order. Both operations return an order object, including the current updated attribute for concurrent management and the new orderid attribute for inserting orders (the order table in northwind uses the ID column ). After obtaining the updated order, you can start the change tracking and set the Data Binding to use the order.
However, you still need to perform some operations before saving the existing order. We only want to transmit the Order details for creation, update, or deletion, instead of wasting network bandwidth to send order details that have not been changed. With changetrackingcollection <t> and changetrackinglist <t>, you can easily achieve this goal, because their public getchanges method only returns the Order details inserted, modified, or deleted. Code 13 for calling updateorder.
Figure 13 calling updateorder
Because I have configured the service reference to use changetrackingcollection <t> as the collection type, the orderdetails attribute of the Order class should belong to the type changetrackingcollection <orderdetail>, in addition, I can directly call getchanges to obtain only the Order details for addition, modification, or deletion, and pass them to the updateorder operation. In addition, because changetrackingcollection <t> can expand the observablecollection <t>, the data binding function supported by changetrackingcollection is very suitable for WPF applications (for example, implement inotifycollectionchanged so that the UI can update the project control when a project is added to or deleted from the collection ). Similarly, changetrackinglist <t> can expand the bindinglist <t>, which can be used as a valid data source for Windows Forms datagridview, so as to enhance the user experience by using the interface implemented by bindinglist <t>.

Summary
Now you have a flexible service-oriented application architecture. In this architecture, the client can completely ignore the object persistence method. We know that DTO allows more control over the structure and shape of the object model, which is just as attractive as allowing entities generated by cross-service boundary transfer tools. In addition, the build data access layer separates applications from any persistence technology you use, allowing you to change the data access implementation without affecting other parts of the application.
The emergence of the LINQ to SQL and ADO. NET Entity frameworks represents a huge leap in the ORM field, effectively reducing the impedance mis-tuning between objects and relational data. They support various solutions, including traditional client servers and service-oriented architecture. However, for developers, using DTO to update databases requires more work, especially when using timestamp values to detect conflicts between concurrent users. However, you can attach an object that is disconnected from the source context by adding the change status to the data conventions between the client and the service. Finally, we will introduce how the general set of tracking changes manages the change status on the client to allow you to process batch updates once you access the service.
For more information about object frameworks, see "ADO. Net: using entity frameworks to flexibly model data" (prepared by ELISA flasko ).

Anthony Sneed is a lecturer at developer training company developmentor. He has written and taught. NET Framework 3.5, LINQ, and Entity Framework related courses. In his spare time, he enjoys cooking and recording home videos, and his chilies are famous. You can contact Anthony Sneed either through a tony@tonysneed.com or by visiting the blog blog.tonysneed.com.