LINQ to SQL Entity Base is a simple base class that is primarily designed to support LINQ to SQL in a disconnected way, which is one of the shortcomings of the LINQ to SQL technology at the present time. This is highly useful in an n-Tier, distributed or ASP.NET
environment where disconnected functionality is relavent.
I've included a small demonstration of the code working with the source code, please feel free to have a play around with it.
For additional information on how it works, visit my blog:
- Complete change tracking while disconnected from the data context.
- New, Modify and Detach change tracking works automatically. This means just change entity properties as usual and they will be change tracked appropriately.
- Support for cascading deletes is available without needing to set this up in the database schema.
- Committing to database is done via a single call, no need for any attach calls.
- Works with WFC. Change tracking information is transmitted with WCF messages, meaning that change tracking is available at both client and server.
- Flatten all entities into one list for powerful querying accross all entities.
- Serialization to XML string available, including current change tracking state.
- Keep the original values while change tracking to reduce the size of update statements when committing to the database.
- Supports either timestamps (version) columns or individual columns for concurrency checks.
- Uses references for entities during serialization to avoid duplicate copies of objects during de-serialization.
Visual Studio 2008 (not express) with SP1
Microsoft .Net 3.5 with SP1
HOW TO: Add the LINQ to SQL Entity Base to your Project
- Download the latest version of the sample project source code (see the "Downloads" tab above).
- Extract the "LINQEntityBase.cs" file from the "LINQEntityBaseExampleData" project and put it in your own solution (i.e. put it in the same project as the LINQ to SQL dbml file)
- Change the sample name space in the "LINQEntityBase.cs" file to the namespace of your project.
- Open the dbml model file in Visual Studio and in the properties for the model, set the Serialization Mode property to 'Uni Directional'
- Save the dbml model file.
- Edit the dbml model file for the DataContext in notepad or other text editor
- Add the "EntityBase" xml attribute to the "<Database>" xml element
- e.g. EntityBase="Northwind.LINQEntityBase", where "Northwind.LINQEntityBase" is the full namespace path to the LINQEntityBase class.
- save the dbml model file
- In the Visual Studio project, right click the dbml model file and select "Run Custom Tool".
All entities should now be subclassed to the LINQ to SQL entity base.
HOW TO: Start Tracking Changes
Pick a root entity, changes under this root entity(i.e. all children, and children's children ..) will be change tracked.
The code to start tracking changes from a customer and all child entities would be:
The code above will include change tracking for:
- Changes to the customer entity
- Changes to the customer's children entities (orders)
- Changes to the customer's children's children entities (order details)
HOW TO: Disconnect entities
If you want to work with the entities in a detached fashion, you will need to disconnect the entities first before you can re-attach them
- If you want to know how to disconnect, please read this blog post:
HOW TO: Add, Delete, Detach & Modify entities
- The entity base works with or without timestamps (version) columns.
HOW TO: Put all entity instances into one list
- For New entities being added:
- Simply add them to an entity collection or assign them to an entity property OR
- If adding a new entity without loading data first, Call the SetAsChangeTrackingRoot() method and pass in the "initialEntityState" parameter as EntityState.New
- Call SetAsInsertOnSubmit() explicity on the entity to mark it for insert in either case above.
- For Deletions use the SetAsDeletedOnSubmit() explicitly on the entity to mark it for deletion
- For Detaching, use the Remove Method OR assign Null to an entity property.
- For Modify, just modify the column properites of the entity OR call SetAsUpdateOnSubmit() explicitly on the entity to modify.
- To Ignore modifcations when submitting to the database, call SetAsNoChangeOnSubmit() explicitly.
The entity base has a ToEntityTree() method which will find all entities recusively that exist of as child of any entity. This can be used for powerful querying accross all entities that are related via parent-->child relationships.
This allows you to traverse a hierarchical structure of entities, returning all instances of those entities matching the criteria to a flat List
For example, the following code traverses a customer-->order-->order_detail relationship to return those order details that have a price which is > $10.00
customer.ToEntityTree().OfType<Order_Detail>().Where( od => od.Price > 10.00 );
var temp = from od in customer.ToEntityTree().OfType<Order_Detail>()
where Price >10.00
HOW TO: Detect current modifications
The following example is also handy, it will find all the modified entities recursively that exist under the customer.
from x in customer.ToEntityTree() where x.LINQEntityState == EntityState.Modified;
The LINQEntityState property can also be used to tell if an entity is:
- not changed tracked
- in original state
- in new state
- in modified state
- in deleted state
HOW TO: Commit the data
On the entity that has been identified as the change tracking root, simply call the SynchroniseWithDataContext() method and then call SubmitChanges() on the data context.
using (NorthWindDataContext db = new NorthWindDataContext())
db.DeferredLoadingEnabled = false;
customer.SynchroniseWithDataContext(db); //this line traverses all entities, attaching all of them as appropriate to the data context.
HOW TO: Turn on cascading deletes
When you call SynchroniseWithDataContext() as above, simply pass in true to the "CascadeDelete" parameter. You do not need to setup database constraints to enable this (as required in the original LINQ to SQL), the Entity Base will cascade delete
HOW TO: Keep original values when tracking changes
You may want to keep the original values around to reduce the size of the T-SQL update statement on tables with may columns (why update all fields when you can just update what's necessary) or if you want to use columns other than a timestamp (version)
column for concurrency checking. In order to this, just simple set the "OnModifyKeepOriginal" parameter to true when calling the SetAsChangeTrackingRoot() method.
HOW TO: Manually control change tracking
Use the following methods to overide default behaviour of the LINQ Entity base change tracking mechanism
SetAsInsertOnSubmit() - Indicates that the entity should be inserted into the database when syncronisation with the database occurs.
SetAsUpdateOnSubmit() - Indicates that the entity should be treated as an update when syncronisation with the database occurs.
SetAsDeletedOnSubmit() - This will delete the entity on syncronisation with the database (and optionally delete any child entities).
SetAsNoChangeOnSubmit() - The entity will not be syncronised with the database at all and is should be treated as unmodified.
HOW TO: Enable concurrency checking
You can either use timestamp (version) columns, or set the UpdateCheck property on the fields to use for concurrency checking. If you use the UpdateCheck property, you'll also need to set "OnModifyKeepOriginal" parameter to true when calling the
HOW TO: Use it with WCF
As long as both the entities and the entity base class are in the same assembly, and the assembly is referenced on both side of the WCF conversation (before you references the WCF service), it will just... well ... work! Of course you also need to set your
DataConext Serialization mode to Uni-directional in order for serialization to be available (Which WCF requires).
HOW TO: Use in ASP.Net
It's possible to put the entities in the session without the need for serialization, however if you want to put an entity in the View State or you are using an external session server like state server or SQL server, you'll need to serialize the entities
first. You can do this with the SerializeEntity() method which will serialize the information to a string. To re-inflate, just use the DeserializeEntity() method. Both these methods are available on the class as static methods.
HOW TO: Bookmark Entities
I've also added a property called "LINQEntityGUID" that contains a unique ID for each entity. This can be conveniently used to bookmark an Entity if you need this type of functionality.
Order_Detail odetail = New Order_Detail()
string bookmark = odetail.LINQEntityGuid;
// do something with the order, add it to an order somewhere under customer.orders...
// perhaps put the value of "bookmark" in ASP.NET session
odetail = null;
// later come back and grab the object again without keeping a reference to the object.
odetail = customer.ToEntityTree().Where( x => x.LINQEntityGUID == bookmark) as Order_Detail;
If you have any issues, feel free to email me or start a discussion