Tag Archives: LINQ to SQL

Adding OnSaving an OnSaved Events to LINQ to SQL Entities

Posted on by

As you know LINQ to SQL entities have OnCreated, OnLoaded, and OnValidate “events”; which are actually just partial methods which you implement in your matching partial custom classes for your LINQ to SQL entities. Two “events” that are missing that can be quite useful are OnSaving and OnSaved which would fire before writing to the database and after writing to the database.

Adding this functionality is fairly easy; first add a base class to your LINQ to SQL entities as described in my post Adding a Base Class (or Interface) to All LINQ to SQL Entities and add the virtual methods OnSaving and OnSaved as below.

public abstract class EntityBase
{
    internal virtual void OnSaving(ChangeAction changeAction) { }

    internal virtual void OnSaved() { }
}

Then for any LINQ to SQL entities that you wish to have hooked up to these “events” implement the partial class and override the methods, for example you could use the OnSaving “event” to set your audit data:

public partial class Customer
{
    internal override void OnSaving(ChangeAction changeAction)
    {
        base.OnSaving(changeAction);

        switch (changeAction)
        {
            case ChangeAction.Insert:
                InsertedDate = DateTime.Now;
                break;
            case ChangeAction.Update:
                UpdatedDate = DateTime.Now;
                break;
            default:
                break;
        }
    }
}

Create a class called ChangeEntity and add the properties ChangeAction and Entity to the class, this class will be used later so that we know what action is happening to each entity.

internal class ChangeEntity
{
    public ChangeAction ChangeAction { get; set; }

    public EntityBase Entity { get; set; }
}

Now what we need to do is override the SubmitChanges method on the data context, the parameterless SubmitChanges method cannot be overridden as it is not virtual so we override the method SubmitChanges(ConflictMode failureMode); which the parameterless method calls.

We will then create a list of all the entities that have been modified (i.e. inserted, updated, or deleted) using the ChangeSet object that is returned by the GetChangeSet() method – the returned object has a seperate list for Deletes, Inserts, and Updates which we will join together into a single list.

Then we build up a list of ChangeEntity objects which will contain a reference to each entity to be changed and an enum of type ChangeAction stating the action to take place for each entity; namely Insert, Update, or Delete.

Then we enumerate through the list of ChangeEntity and call the method OnSaving on each entity.

Then we call SubmitChanges on the base class, and finally we enumerate throught the list of ChangeEntity once again and call the method OnSaved on each entity.

Below is the required code, it is more verbose that I would usually write but the original code didn’t display nicely :) 

public partial class DataClassesDataContext
{
    public override void SubmitChanges(ConflictMode failureMode)
    {
        // Get the entities that are to be inserted / updated / deleted
        ChangeSet changeSet = GetChangeSet();

        // Get a single list of all the entities in the change set
        IEnumerable<object> changeSetEntities = changeSet.Deletes;
        changeSetEntities = changeSetEntities.Union(changeSet.Inserts);
        changeSetEntities = changeSetEntities.Union(changeSet.Updates);

        // Get a single list of all the enitities that inherit from EntityBase
        IEnumerable<ChangeEntity> entities = 
             from entity in changeSetEntities.Cast<EntityBase>()
             select new ChangeEntity()
             {
                 ChangeAction = 
                      changeSet.Deletes.Contains(entity) ? ChangeAction.Delete
                    : changeSet.Inserts.Contains(entity) ? ChangeAction.Insert
                    : changeSet.Updates.Contains(entity) ? ChangeAction.Update
                    : ChangeAction.None,
                 Entity = entity as EntityBase
             };

        // "Raise" the OnSaving event for the entities 
        foreach (ChangeEntity entity in entities)
        {
            entity.Entity.OnSaving(entity.ChangeAction);
        }

        // Save the changes
        base.SubmitChanges(failureMode);

        // "Raise" the OnSaved event for the entities
        foreach (ChangeEntity entity in entities)
        {
            entity.Entity.OnSaved();
        }
    }
}

Adding a Base Class (or Interface) to All LINQ to SQL Entities

Posted on by

If you need your all your LINQ to SQL entities to inherit from a base class or to implement an interface there is an easy way to do it. The first way that comes to mind for most people is to create a partial class for the entities and specify the inheritance (or interface implementation) in the partial class. While this will work it is a mission as it means you need a matching partial class for every LINQ to SQL entity in your DBML, and there is no guarantee that any new entities that are added by other team members will inherit from the base class or implement the interface.

There is a far simpler way to ensure that all LINQ to SQL entities inherit from a base class or implement an interface. Unfortunately there isn’t a way to do it from the LINQ to SQL designer, you need to open up the DBML file in a text editor. On the Database node simply add the attribute EntityBase along with the name of the base class or interface.

<Database Name="Northwind" 
	Class="MyDataContext" 
	xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007"
	EntityBase="MyEntityBase">
</Database>

If the base class or interface is not in the same namespace as your LINQ to SQL entities then you will need to specify the full qualified name of the base class or interface.

Adding a custom insert stored procedure to a LINQ entity

Posted on by

Normally when you commit a change to the database using LINQ it will generate SQL to perform the insert/update/delete action. Most of the time this is fine, but LINQ does provide the ability to override this behaviour and specify a stored procedure that should be executed instead of the default behaviour. And luckily it’s really easy.

First, create your stored procedure, normally if you are creating an insert or update stored procedure the proc will have a parameter for each field but this is not a requirement as you can map the individual fields to the properties of the class.

Drag the stored procedure onto your DBML so that the LINQ framework creates a method for the stored procedure, in this example the stored procedure is called InsertLessee.

LINQInsert1

Then right click on the LINQ entity in the DBML and select Configure Behaviour.

LINQInsert2

Then (1) choose the behaviour to replace, (2) select Customize and select the stored procedure from the drop down list, in this case InsertLessee, and (3) if needed alter the stored procedure parameter to class property mapping.

LINQInsert3

It is as simple as that, now whenever a Lessee is inserted the LINQ framework will call the stored procedure instead of generating SQL at runtime.

LINQ to SQL – How to know if you LINQ entity is New or Loaded from Database

Posted on by

The LINQ to SQL entites don’t have a property that lets you know if the instance of the object has been loaded from the database or has been locally created, i.e. new.

A simple solution to this problem is to add an enumeration with the enums New and Loaded:

public enum EntityState
{
    [Description("New")]
    New = 0,

    [Description("Loaded")]
    Loaded = 1
}

Then create a property in the partial class of your LINQ to SQL entity for the enumeration:

public EntityState EntityState
{
    get { return _EntityState; }
    private set { _EntityState = value; }
}

Then in the partial method OnCreated set the property’s value to New and in the partial method OnLoaded set it to Loaded:

partial void OnCreated()
{
    EntityState = EntityState.New;
}

partial void OnLoaded()
{
    EntityState = EntityState.Loaded;
}

Now if you create a new entity only the OnCreated method will be executed, and if its loaded from the db the OnCreated will be fired and then the OnLoaded. So you can now easily determine its state.

Of course you could also check the primary key’s value against the default for its type but that is not ideal, the primary key is not nullable so for an integer primary key you would have to check if it is 0 to determine if its new or not. Rather avoid that approach.

Disclaimer: This post is based on Orcas CTP June 2007