Coding Reflections

Combining Mapped Database Functions on Multiple DataContexts

2009-03-18T20:02:00.000-04:00

The LINQ to SQL designer provides some great flexibility for calling scalar and table-valued functions and stored procedures in your database. You usually just need to drag the function from the Server Explorer into the DBML designer’s method pane and indicate how the output of the function should be materialized. I’m a fan of keeping as much complex logic out of the database as possible, but sometimes you just can’t efficiently get the data you need from your database through LINQ into your middle tier. Table-valued functions are especially useful for queries that can’t be expressed easily (or at all) using standard LINQ expressions. Queries that can be made more efficient using ranking and windowing functions or recursive CTEs are good candidates since they often have no direct translation to LINQ. Best of all, since the generated methods for table-valued functions return an instance of IQueryable, you can use function results within more complex LINQ expressions and still have the entire query executed at the database.

As you have come to expect form LINQ to SQL, pretty much anything you get out of a DataContext is tied to that DataContext instance. This includes any instances of IQueryable you get from the generated methods for table-valued functions. In my last post, I discussed using the LINQ to SQL O/RM behind the repositories in domain-driven design. In this context, and in many other instances, it can be helpful to maintain just one DataContext instance for a given thread of execution or “unit of work.” A singleton instance per thread. This can be difficult to design, however, if some of your code relies on a particular derived DataContext type generated by the DBML designer to access its mapped functions. Worse yet, you might have taken a divide and conquer approach to the database design and used the designer to produce more than one type of derived DataContext. These DataContext classes could exist in separate assemblies and model different entities within the database (for example, maybe you have developed a reusable library for authentication and user management that has its own DataContext to interact with tables in the database).

When we look at the code generated for a derived DataContext, in a simple case we see properties defined for tables:

public System.Data.Linq.Table<Account> Accounts
{
	get
	{
		return this.GetTable<Account>();
	}
}

The method GetTable<T> is defined by the DataContext class, so regardless of the particular DataContext type you have, it’s clear that we can easily request a table of any entity type. The table properties generated by the designer are purely a convenience. If your code is only ever concerned with basic CRUD operations on tables (i.e. you never use mapped database functions), then you should be able to use an appropriately constructed instance of any DataContext, even the DataContext class itself, as your singleton DataContext instance.

Where you’ll get into trouble is when you need to use a mapped database function. For now I’m only going to tackle mapped table-valued functions like the one below. Stored procedures look a little different, but the approach I’m about to discuss can still be applied. Let’s look at the code that the designer generates for us for a simple mapped function (this mapped function uses a recursive CTE defined in the database to get the ancestors of a particular node in a hierarchy):

[Function(Name = "dbo.GetNodePath", IsComposable = true)]
public IQueryable<NodeParent> GetNodePath([Parameter(Name = "NodeID", DbType = "Int")] System.Nullable<int> nodeID)
{
    return this.CreateMethodCallQuery<NodeParent>(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), nodeID);
}

There isn’t very much going on here. We have a FunctionAttribute that tells LINQ to SQL that this is a mapped database function. It indicates the function name and that it is composable (meaning we can include this in larger LINQ expressions). The name of the method isn’t really important and the return type is simply whatever you select in the designer (LINQ to SQL tries its best to materialize the result as whatever type you request, or even an auto-generated type). The parameters to the method are each decorated with a ParameterAttribute to help translate the method parameters into SQL parameters. The body of the method calls a method inherited from DataContext with the current instance, the current method, and each of the parameters passed to the method.

The designer generates this code for us within a derived DataContext type. Ideally, we’d like to define this mapped function anywhere so that we can use it against any DataContext instance (especially our singleton instance we’re trying to keep).

Here is the metadata documentation from CreateMethodCallQuery:

//
// Summary:
//     Executes the table-valued database function associated with the specified
//     CLR method.
//
// Parameters:
//   instance:
//  The instance of the method invocation (the current object).
//
//   methodInfo:
//     Identifies the CLR method that corresponds to a database method.
//
//   parameters:
//     The array of parameters to be passed to the command.
//
// Type parameters:
//   TResult:
//     The type of the elements in the returned collection.
//
// Returns:
//     A collection of resultant values returned by the database query.
protected internal IQueryable<TResult> CreateMethodCallQuery<TResult>(object instance, MethodInfo methodInfo, params object[] parameters);

So far so good. We see that the method is protected internal, so if we want to call this from outside a derived DataContext, we’re going to have to make a proxy method. The proxy method would accept the same arguments as CreateMethodCallQuery and simply pass them along to the inherited method. Here is the first attempt:

public class DataContextProxy : DataContext
{
    public DataContextProxy(string connectionString) : base(connectionString) { }

    public new IQueryable<TResult> CreateMethodCallQuery<TResult>(object instance, MethodInfo methodInfo, params object[] parameters)
    {
        return base.CreateMethodCallQuery<TResult>(instance, methodInfo, parameters);
    }
}

public class DatabaseFunctions
{
    private DataContextProxy _Proxy;
    public DatabaseFunctions(DataContextProxy proxy) { _Proxy = proxy; }

    [Function(Name = "dbo.GetNodePath", IsComposable = true)]
    public IQueryable<NodeParent> GetNodePath([Parameter(Name = "NodeID", DbType = "Int")] System.Nullable<int> nodeID)
    {
        return _Proxy.CreateMethodCallQuery<NodeParent>(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), nodeID);
    }
}


class Program
{
    static void Main(string[] args)
    {
        using (DataContextProxy dc = new DataContextProxy(Settings.Default.DefaultConnection))
        {
            DatabaseFunctions dbf = new DatabaseFunctions(dc);

            dbf.GetNodePath(0).ToList();    //call the method and force execution by copying to a list
        }
    }
}

Again, this is the direction we’re going in: define your mapped functions in a way that allows you to execute them against any DataContext. Here our function is defined in a class called DatabaseFunctions, which receives an instance of our DataContextProxy class.

Executing this code produces the following exception:

System.InvalidOperationException was unhandled
Message="The method 'GetNodePath' is not mapped as a stored procedure or user-defined function."
Source="System.Data.Linq"

Well, it sure looks like that method is mapped! Time to break out Reflector! (Aside: if you’ve ever been curious about how a .NET assembly was implemented, and you’ve never heard of Reflector, go download it right now!)

We’re using an AttributeMappingSource, by default, to tell LINQ to SQL how to map our entities and functions to SQL statements. Derived instances of MappingSource are responsible for creating an instance of MetaModel that gives LINQ to SQL this mapping. There is also an internal class, System.Data.Linq.SqlClient.QueryConverter, that visits LINQ expressions and helps with the actual translation to SQL. A quick browse through the disassembled DataContext and QueryConverter classes reveals some important facts:

The CreateModel method of any mapping source is intended to examine types derived from DataContext when building a meta model.
The DataContext class creates a MetaModel by examining its own type: MetaModel model = mapping.GetModel(base.GetType());.
The QueryConverter VisitMethodCall method will only proceed to VisitMappedFunctionCall if the method is an instance method defined on a class derived from DataContext.

Our mapped function isn’t defined on a class derived from DataContext, so the QueryConverter won’t treat it as a mapped function. However, we don’t even get to that point yet since our mapped function is defined outside of the DataContextProxy class and the AttributedMetaModel isn’t aware of it. To take care of both, we’ll have DatabaseFunctions subclass DataContext and we’ll define our own MappingSource and MetaModel classes that dynamically build models as necessary based on the reflected type of the MethodInfo passed to CreateMethodCallQuery. First, the DynamicAttributeMappingSource:

internal class DynamicAttributeMappingSource : MappingSource
{
    private static AttributeMappingSource AttributeMapping = new AttributeMappingSource();

    private Dictionary<Type, MetaModel> _ModelDictionary = new Dictionary<Type, MetaModel>();
    private ReaderWriterLockSlim _ModelLock = new ReaderWriterLockSlim();

    public MetaModel Primary { get { return GetModel(typeof(DataContextProxy)); } }

    protected override MetaModel CreateModel(Type dataContextType)
    {
        MetaModel Result;

        _ModelLock.EnterReadLock();
        try
        {
            if (_ModelDictionary.TryGetValue(dataContextType, out Result))
                return Result;
        }
        finally
        {
            _ModelLock.ExitReadLock();
        }

        _ModelLock.EnterWriteLock();
        try
        {
            if (_ModelDictionary.TryGetValue(dataContextType, out Result))
                return Result;

            Result = new DynamicAttributedMetaModel(this, AttributeMapping.GetModel(dataContextType));
            _ModelDictionary.Add(dataContextType, Result);
        }
        finally
        {
            _ModelLock.ExitWriteLock();
        }

        return Result;
    }
}

There’s nothing fancy really going on here. A dictionary keeps track of models we’ve created for particular types and, in CreateModel, we have some thread-safe code for creating a model: we either get it out of the dictionary, or create a new one and put it into the dictionary. When we create our MetaModel instances, we give them a MetaModel generated by an AttributeMappingSource as a starting point. Here is the DynamicAttributedMetaModel:

internal class DynamicAttributedMetaModel : MetaModel
{
    public DynamicAttributeMappingSource DynamicSource { get; private set; }
    public MetaModel Surrogate { get; private set; }

    public DynamicAttributedMetaModel(DynamicAttributeMappingSource dynamicSource, MetaModel surrogate)
    {
        DynamicSource = dynamicSource;
        Surrogate = surrogate;
    }

    public override Type ContextType
    {
        get { return Surrogate.ContextType; }
    }

    public override string DatabaseName
    {
        get { return Surrogate.DatabaseName; }
    }

    public override MetaFunction GetFunction(System.Reflection.MethodInfo method)
    {
        Type MethodType = method.ReflectedType;

        if (Surrogate.ContextType == MethodType)
            return Surrogate.GetFunction(method);
        else
            return DynamicSource.GetModel(MethodType).GetFunction(method);
    }

    public override IEnumerable<MetaFunction> GetFunctions()
    {
        return Surrogate.GetFunctions();
    }

    public override MetaType GetMetaType(Type type)
    {
        return Surrogate.GetMetaType(type);
    }

    public override MetaTable GetTable(Type rowType)
    {
        return Surrogate.GetTable(rowType);
    }

    public override IEnumerable<MetaTable> GetTables()
    {
        return Surrogate.GetTables();
    }

    public override MappingSource MappingSource
    {
        get { return DynamicSource; }
    }

    public override Type ProviderType
    {
        get { return Surrogate.ProviderType; }
    }
}

This class is a proxy for a MetaModel, passing calls along to the surrogate model. The MappingSource property refers back to the DynamicAttributeMappingSource (since that is in fact our mapping source), and there is some extra logic in the GetFunction method. When the mapping for a function is requested, we check to see if the method is defined on a type that matches our model’s type. If it is, then it has already been mapped and we can easily get the MetaFunction instance describing that mapped function from our surrogate model. Otherwise, we have to get the MetaModel corresponding to the method’s type. Our (dynamic) MappingSource can provide that for us easily.

Let’s change our DataContextProxy to use our new mapping source by default. MappingSource instances provided here and by the framework are thread-safe. This allows us to use one static instance and avoid the overhead of using reflection to re-build our MetaModel instances every time we create a new DataContext (the MappingSource class even implements caching of models in much the same way the above code does).

private static MappingSource _Mapping = new DynamicAttributeMappingSource();

public DataContextProxy(string connectionString) : base(connectionString, _Mapping) { }

And for our DatabaseFunctions class:

public class DatabaseFunctions : DataContext
{
    public DatabaseFunctions(DataContextProxy proxy) : base(string.Empty) { _Proxy = proxy; }

…

Okay let’s give it a go with the same driver code as before!

System.InvalidOperationException was unhandled
Message="The query contains references to items defined on a different data context."
Source="System.Data.Linq"

Well I suppose we should have expected this. We have two DataContext types now: DataContextProxy and DatabaseFunctions. The stack trace for the exception reveals that the check is made by the QueryConverter class:

private SqlNode VisitMappedFunctionCall(MethodCallExpression mc)
{
    MetaFunction function = this.services.Model.GetFunction(mc.Method);
    this.CheckContext(this.VisitExpression(mc.Object));
    if (!function.IsComposable)
    {
        return this.TranslateStoredProcedureCall(mc, function);
    }
    if (function.ResultRowTypes.Count > 0)
    {
        return this.TranslateTableValuedFunction(mc, function);
    }
    ProviderType sqlType = ((function.ReturnParameter != null) && !string.IsNullOrEmpty(function.ReturnParameter.DbType)) ? this.typeProvider.Parse(function.ReturnParameter.DbType) : this.typeProvider.From(mc.Method.ReturnType);
    List<SqlExpression> functionParameters = this.GetFunctionParameters(mc, function);
    return this.sql.FunctionCall(mc.Method.ReturnType, sqlType, function.MappedName, functionParameters, mc);
}

private void CheckContext(SqlExpression expr)
{
    SqlValue value2 = expr as SqlValue;
    if (value2 != null)
    {
        DataContext context = value2.Value as DataContext;
        if ((context != null) && (context != this.services.Context))
        {
            throw Error.WrongDataContext();
        }
    }
}

OK, let’s think here. What is the QueryConverter actually working with? It’s trying to translate an expression tree associated with an IQueryable object into an SQL statement. The expression tree isn’t being executed, it’s just being parsed. We need an instance of an object to execute an instance method on that object, but there’s no reason we can’t have an expression tree that represents calling such a method on a null reference. If I can convince the QueryConverter that the context variable is null, then the CheckContext method will return successfully! The context variable is the instance of the derived DataContext that was used to call our mapped function. In our case, this is an instance of the class DatabaseFunctions.

So where is this part of the expression tree generated? By the DataContext, of course:

protected internal IQueryable<TResult> CreateMethodCallQuery<TResult>(object instance, MethodInfo methodInfo, params object[] parameters)
{
    this.CheckDispose();
    if (instance == null)
    {
        throw Error.ArgumentNull("instance");
    }
    if (methodInfo == null)
    {
        throw Error.ArgumentNull("methodInfo");
    }
    if (parameters == null)
    {
        throw Error.ArgumentNull("parameters");
    }
    if (!typeof(IQueryable<TResult>).IsAssignableFrom(methodInfo.ReturnType))
    {
        throw Error.ExpectedQueryableArgument("methodInfo", typeof(IQueryable<TResult>));
    }
    return new DataQuery<TResult>(this, this.GetMethodCall(instance, methodInfo, parameters));
}

private Expression GetMethodCall(object instance, MethodInfo methodInfo, params object[] parameters)
{
    this.CheckDispose();
    if (parameters.Length <= 0)
    {
        return Expression.Call(Expression.Constant(instance), methodInfo);
    }
    ParameterInfo[] infoArray = methodInfo.GetParameters();
    List<Expression> arguments = new List<Expression>(parameters.Length);
    int index = 0;
    int length = parameters.Length;
    while (index < length)
    {
        Type parameterType = infoArray[index].ParameterType;
        if (parameterType.IsByRef)
        {
            parameterType = parameterType.GetElementType();
        }
        arguments.Add(Expression.Constant(parameters[index], parameterType));
        index++;
    }
    return Expression.Call(Expression.Constant(instance), methodInfo, arguments);
}

The GetMethodCall method is responsible for generating the expression tree, which always uses the instance passed to CreateMethodCallQuery in Expression.Call(…). We can’t pass an instance of any type other than a type that declares our mapped function to CreateMethodCallQuery, since Expression.Call verifies that the instance type supports the specified method.

To circumvent this, we need to use our own GetMethodCall method and construct an instance of the DataQuery class (which is internal, unfortunately).

Here is the completed DataContextProxy class:

public class DataContextProxy : DataContext
{
    private static MappingSource _Mapping = new DynamicAttributeMappingSource();

    private static bool _ReflectionInit = false;
    private static ReaderWriterLockSlim _ReflectionLock = new ReaderWriterLockSlim();
    private static Type _DataQueryType;
    private static FieldInfo _DataContextProviderMember;
    private static MethodInfo _IProviderExecuteMethod;

    /// <summary>
    /// Construct a new instance of the data context proxy using a provided connection string and dynamic attribute mapping.
    /// </summary>
    /// <param name="connectionString">The connection string used to connect to the underlying database.</param>
    public DataContextProxy(string connectionString)
        : base(connectionString, _Mapping)
    {
    }

    #region Proxy Methods for Cross-Context Calls

    private static void InitReflection()
    {
        _ReflectionLock.EnterReadLock();
        try
        {
            if (_ReflectionInit)
                return;
        }
        finally
        {
            _ReflectionLock.ExitReadLock();
        }

        _ReflectionLock.EnterWriteLock();
        try
        {
            if (_ReflectionInit)
                return;

            _DataQueryType = typeof(DataContext).Assembly.GetType("System.Data.Linq.DataQuery`1", true);
            _DataContextProviderMember = typeof(DataContext).GetField("provider", BindingFlags.Instance | BindingFlags.NonPublic);
            _IProviderExecuteMethod = typeof(DataContext).Assembly.GetType("System.Data.Linq.Provider.IProvider").GetMethod("Execute");

            _ReflectionInit = true;
        }
        finally
        {
            _ReflectionLock.ExitWriteLock();
        }
    }

    /// <summary>
    /// Uses the mapping attributes on a method definition to execute a stored procedure.
    /// </summary>
    /// <param name="instance">The instance of the class that exposes the method.</param>
    /// <param name="method">The method invoked.</param>
    /// <param name="parameters">The parameters to the method invoked.</param>
    /// <returns>The execution result.</returns>
    public IExecuteResult ExecuteMethodCall(DataContext instance, MethodInfo method, params object[] parameters)
    {
        InitReflection();

        object Provider = _DataContextProviderMember.GetValue(this);
        return (IExecuteResult)_IProviderExecuteMethod.Invoke(Provider, new object[] { GetMethodCall(instance, method, parameters) });
    }

    /// <summary>
    /// Uses the mapping attributes on a method definition to query a table-valued function.
    /// </summary>
    /// <typeparam name="TElement">The type of element queried.</typeparam>
    /// <param name="instance">The instance of the class that exposes the method.</param>
    /// <param name="method">The method invoked.</param>
    /// <param name="parameters">The parameters to the method invoked.</param>
    /// <returns>The queryable result.</returns>
    public IQueryable<TElement> CreateMethodCallQuery<TElement>(DataContext instance, MethodInfo method, params object[] parameters)
    {
        InitReflection();

        Type DataQueryType = _DataQueryType.MakeGenericType(typeof(TElement));

        return (IQueryable<TElement>)Activator.CreateInstance(DataQueryType, this, GetMethodCall(instance, method, parameters));
    }

    /// <remarks>This is copied almost verbatim from System.DataLinq.DataContext:GetMethodCall.
    /// However, note that the instance is null but of the correct type.
    /// This exploits two loop-holes in System.Data.Linq.SqlClient.QueryConverter:
    /// (1) An instance inheriting DataContext is required to map functions, and
    /// (2) A null instance passes the CheckContext method by default.</remarks>
    private static Expression GetMethodCall(DataContext instance, MethodInfo methodInfo, params object[] parameters)
    {
        if (parameters.Length <= 0)
        {
            return Expression.Call(Expression.Constant(null, methodInfo.DeclaringType), methodInfo);
        }
        ParameterInfo[] infoArray = methodInfo.GetParameters();
        List<Expression> arguments = new List<Expression>(parameters.Length);
        int index = 0;
        int length = parameters.Length;
        while (index < length)
        {
            Type parameterType = infoArray[index].ParameterType;
            if (parameterType.IsByRef)
            {
                parameterType = parameterType.GetElementType();
            }
            arguments.Add(Expression.Constant(parameters[index], parameterType));
            index++;
        }
        return Expression.Call(Expression.Constant(null, methodInfo.DeclaringType), methodInfo, arguments);
    }

    #endregion
}

Notice how GetMethodCall passes null and a type to Expression.Constant, instead of the instance parameter. This circumvents the context type check performed by QueryConverter. We also have to use reflection to dig into some of the internal classes used by DataContext. Obviously if LINQ to SQL changes significantly, this may not work anymore (but hopefully they’ll have added this or similar functionality in).

The above code includes both the CreateMethodCallQuery method to handle table-valued functions and the ExecuteMethodCall method to handle stored procedures. Using either method appropriately, you can now define your mapped database functions outside of the designer generated code and use them against any data context!

LINQ to SQL and the Repository Pattern

2009-03-18T15:07:00.001-04:00

So it has been a while since I’ve blogged about anything, and for that I do apologize! Things have been pretty busy around here, which I suppose should be considered a good thing!

Recently I’ve been working with domain-driven design and trying to get a feel for how LINQ to SQL can be utilized as the O/R mapper behind repositories. In DDD, you usually have a repository for each domain entity to handle CRUD operations. With LINQ, your repository can expose instances of IQueryable<T> so that client code can efficiently execute complex queries on the domain using LINQ. LINQ to SQL does a great job translating this to SQL and materializing the results. Wonderful! And if you’re really picky about DDD, you can even define a separate interface for your entities within your domain model and avoid exposing designer generated classes outside of your repositories.

One problem with the repository approach is managing the life cycle of the DataContext object. If you have ever used LINQ to SQL, you’ve run into one problem or another with the DataContext class. Typically users run into one of the following problems:

You cannot use more than one instance of a DataContext in a single LINQ query, and
You cannot use objects materialized by one DataContext in a new DataContext instance.

So both problems can only happen if you have more than one instance of a DataContext floating around. Why might that be? Well, unless you constantly provide method parameters to pas around a single DataContext instance, it’s going to be really hard to encapsulate any common functionality anywhere! Here’s an example:

public IQueryable<Account> ListAccountsByLastName(string name)
{
    //we would normally do: using(DataContext dc...) but we can't, because then the DataContext would be disposed before returning our query.
    DataContext dc = new DataContext(Properties.Settings.Default.ConnectionString);
    return (from a in dc.GetTable<Account>()
            where a.LastName.Contains(name)
            select a);
}

public IQueryable<Account> ListAccountsByLastName(DataContext dc, string name)
{
    return (from a in dc.GetTable<Account>()
            where a.LastName.Contains(name)
            select a);
}

The first method has to come up with its own DataContext. The second method accepts a DataContext as the first parameter. Neither method is good: you either introduce a new DataContext and limit what can be accomplished with the instance IQueryable<T> and any materialized results, or you always have to be concerned with passing around a DataContext.

I’ve already discussed the second problem with a possible workaround.

I don’t know what the LINQ to SQL developers were thinking. They obviously didn’t take a lesson from the ADO.NET and System.Transactions developers. In my mind, the DataContext class should be available as part of an ambient scope, just like System.Transactions.TransactionScope. You should always be able to pick up the current DataContext. We can easily shield client code from any details required when handling the DataContext class by wrapping it up in a UnitOfWork class. The result: a clean separation of client code from the O/R mapper used by the repositories.

Here’s some sample code:

public Account CreateUser(string email, string firstName, string lastName)
{
    IUserRepository Users = IoC.ResolveService<IUserRepository>();

    Account NewUser = Users.CreateUser(email);

    NewUser.FirstName = firstName;
    NewUser.LastName = lastName;

    return NewUser;
}

public void Main()
{
    using (IUnitOfWork Work = UnitOfWork.Start())
    {
        CreateUser("someone@somewhere.com", "Michael", "P.");

        Work.Commit();
    }
}

Here we see both the Unit of Work and Inversion of Control patterns at work. A Unit of Work is simply an ambient context within which code executes. My implementation simply provides a dictionary for storing objects within that thread-specific context. It also supports a Commit operation that completes any transacted operations performed in the context. The Inversion of Control pattern allows me to define the repository that implements the IUserRepository interface somewhere other than in the client code. This implementation uses a section in the app.config file.

So within the CreateUser method above, we get an instance of the user repository and invoke its CreateUser method. Here’s that method:

public Account CreateUser(string email)
{
    Account TargetAccount = new Account()
    {
        Type = AccountType.Standard,
        Email = email,
    };

    Context.GetTable<Account>().InsertOnSubmit(TargetAccount);

    return TargetAccount;
}

Notice how there is no explicit creation of a DataContext even within the repository. The Context property is exposed by a repository base class:

protected DataContext Context { get { return DataContextProxy.Singleton; } }

The DataContextProxy class and Singleton property just help us manage a single instance of a DataContext stored in the current unit of work scope (an exception is raised if a unit of work hasn’t been started by the client code). The inversion of control pattern is actually used again by the DataContextProxy class to delegate creation of the DataContext instance. This allows the connection string (and optionally a mapping source) to be provided by client code.

By limiting the instances of DataContext to just one, within a unit of work scope, you never need to worry about which DataContext materialized a particular object or produced an instance of IQueryable<T>. Your repositories always have access to the current DataContext and your client code never has to worry about it or any complex attaching / detaching behavior.

Excellent! But you might be wondering: what do I do if I use stored procedures and table valued functions that have been generated by the LINQ to SQL designer for my particular data context. My repository only has access to plain old DataContext and the GetTable<T> method. Well, you could execute your database functions using the DataContext method ExecuteQuery. This will work, but you’ll get back an IEnumerable<T> instance for table value functions, not an IQueryable<T> instance. This means any further LINQ statements will be done in memory, not executed in SQL.

You could also use your own derived DataContext generated by the designer to call your database functions, but then you’d be subject to the limitations above when working with multiple DataContext instances. At least, you should be. In reality there is a loophole in the LINQ to SQL query converter that does allow you to successfully combine queries involving mapped database functions from different contexts. You don’t get this functionality automatically, but I’ll show you the rest of the DataContextProxy class and how it bypasses the DataContext method CreateMethodCallQuery next time!

Detaching LINQ to SQL Entities

2008-08-24T17:30:00.015-04:00

I've been playing around with the LINQ to SQL framework in .NET 3.5 for mapping classes to a SQL database. Exposing your data model through LINQ-able classes gives you the ability to write strongly typed queries in your code that are later translated to the necessary SQL commands. The framework materializes query results into objects for you, and provides methods for inserting, updating and deleting data. For a nice quick overview, I suggest checking out the LINQ to SQL - 5 Minute Overview.

One issue that I keep seeing with the DataContext class in this framework is the ability to use entities materialized in one context within another context.

Consider a simple application that supports logging a user in and having users post content.

//a static login method for the Account class
public static Account Login(string email, string password)
{
    using (CommunityDataContext dc = new CommunityDataContext())
    {
        //lookup the account
        Account ExistingAccount = (from a in dc.Accounts
                                  where a.Email == email
                                  select a).FirstOrDefault();

        if (ExistingAccount == null //username not found
           || ExistingAccount.Password != password)    //password doesn't match
            return null;

        //update the last seen timestamp
        ExistingAccount.LastSeen = DateTime.UtcNow;
        dc.SubmitChanges();

        return ExistingAccount;
    }
}

//some driver code
Account User = Account.Login("someone@somewhere.com", "password");
if (User == null)
throw new Exception("Login Failed.");

using (CommunityDataContext dc = new CommunityDataContext())
{
    Post NewPost = new Post()
    {
        Account = User,
        Content = "Test Content"
    };

    dc.Posts.InsertOnSubmit(NewPost);
    dc.SubmitChanges();
}

If we run this code, we'll find that an insert is attempted. What is being inserted might surprise you! Here is the log output from the DataContext:

DECLARE @output TABLE([AccountID] UniqueIdentifier)
DECLARE @id UniqueIdentifier
INSERT INTO [dbo].[Account]([Email], [Password], [LastSeen])
OUTPUT INSERTED.[AccountID] INTO @output
VALUES (@p0, @p1, @p2)
SELECT @id = AccountID FROM @output

SELECT [t0].[AccountID], [t0].[Created]
FROM [dbo].[Account] AS [t0]
WHERE [t0].[AccountID] = (@id)
-- @p0: Input VarChar (Size = 21; Prec = 0; Scale = 0) [someone@somewhere.com]
-- @p1: Input VarChar (Size = 8; Prec = 0; Scale = 0) [password]
-- @p2: Input SmallDateTime (Size = 0; Prec = 0; Scale = 0) [8/24/2008 8:09:35 PM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30428.1

You can see that we are trying to insert an Account instance, specifically the one I logged in as! Execution is haulted at this point within my code as an exception is raised (I have created a unique key constraint for account email addresses).

The issue is that the DataContext is treating the Account instance as a new account and attempting to insert it before inserting the new Post.

To prevent this behavior, inevitably the Attach method comes up. We might decide to try the following:

using (CommunityDataContext dc = new CommunityDataContext())
{
    dc.Accounts.Attach(User);
    Post NewPost = new Post()...

And now we reach the crux of our problem. The call to Attach raises a NotSupportException: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.

So there are many solutions that I've read about, just try searching Google. They range from:

Passing your DataContext instance to each of your methods so that a single instance is used,
Creating an ambient DataContext that is re-used automatically (avoids the extra argument required above),
Defining a custom Detach method for each of your entity classes that erases any connection to the original DataContext instance,
Defining a generic Detach method that uses reflection to produce a copy of your entity without any connection to the original DataContext,
Defining a generic Detach method that uses serialization to produce a copy of your entity.

None of these really seem like adequate solutions to me. They all introduce overhead (reflection or serialization) and/or complexity (extra arguments or maintaining a custom methods). The ambient DataContext solution seemed intriguing at first, but also presents its own problems.

So after playing around with the framework some more, I've settled on my own solution: define a generic Detach method that copies the entity in the same way that entities are materialized. Namely, I'm going to use the MetaModel provided by the DataContext class. Here is Detach, provided by an ExtendedDataContext:

public class ExtendedDataContext : DataContext
{
    /// <summary>
    /// Detaches an entity from its existing data context.
    /// </summary>
    /// <typeparam name="TEntity">The entity type.</typeparam>
    /// <param name="entity">The entity instance.
    /// <returns>A copy of the entity in a detached state.</returns>
    public TEntity Detach<TEntity>(
      TEntity entity) where TEntity : class, new()
    {
        if (entity == null)
            return null;

        //create a copy of the entity
        object Copy = new TEntity();

        //enumerate the data member mappings for the entity type
        foreach (MetaDataMember member in Mapping.GetMetaType(typeof(TEntity)).DataMembers)
        {
            if (member.IsAssociation    //skip associations and deferred members
              || member.IsDeferred)
              continue;

            //copy the member value
            member.StorageAccessor.SetBoxedValue(
              ref Copy,
              member.StorageAccessor.GetBoxedValue(entity));
        }

        return (TEntity)Copy;
    }
}

The algorithm is pretty concise: create a new instance of your entity and use its model metadata to copy data from the existing entity to the new instance. While copying, we skip associations and deferred members to avoid copying references to the original DataContext instance.

Please notice that the constructor for your entity will be called each time the Detach method is called. This means that the OnCreated extension method will also be called. This may or may not be a problem for you, depending on what code, if any, you define for the OnCreated extension. I provide another method to help transfer ownership of an entity from one DataContext to another, the Assume method:

/// <summary>
/// Detaches an entity from its existing data context and re-attaches it to this data context.
/// </summary>
/// <typeparam name="TEntity">The entity type.</typeparam>
/// <param name="entity">The entity instance, which will be replaced by the detached instance.
/// <remarks>The entity instance is copied by this method.</remarks>
public void Assume<TEntity>(
  ref TEntity entity) where TEntity : class, new()
{
    if (entity == null)
        return;

    entity = Detach(entity);
    GetTable<TEntity>().Attach(entity);
}

Going back to our original application, we can use the designer to change the base class of the generated CommunityDataContext to ExtendedDataContext, and change our driver code to the following:

Account User = Account.Login("someone@somewhere.com", "password");
if (User == null)
    throw new Exception("Login Failed.");

using (CommunityDataContext dc = new CommunityDataContext())
{
    dc.Assume(ref User);

    Post NewPost = new Post()
    {
        Account = User,
        Content = "Test Content"
    };

    dc.Posts.InsertOnSubmit(NewPost);
    dc.SubmitChanges();
}

The above will now work as expected! We can log in and get an Account instance that was created by one DataContext, and use that Account when creating a new Post in a later context.

I think that there are some real strengths to this approach.

You don't need to maintain any additional code to support each of your entities,
You can utilize the existing information provided by the MetaModel to avoid examining the entity type using reflection,
This method works for entities mapped using either the AttributeMappingSource or the XmlMappingSource.

If you dig through the code used by the LINQ to SQL framework, you'll see the MetaModel and related classes are used everywhere. In particular, the framework uses the model to compile classes at runtime that extend System.Data.Linq.SqlClientImplementation.ObjectMaterializer<> to provide faster materialization of entities (without reflection). Perhaps a future extension of this method will do the same.

Welcome

2008-08-24T15:08:00.004-04:00

There is an amazing array of knowledge available on other blogs relating to programming and specific software technologies. Whenever I hit a roadblock on a project, or can't seem to come up with that elegant solution to a problem, I frequently find someone who has blogged about the issue and come up with a useful alternative or solution.

It seems like there is a great community for building and sharing this kind of knowledge. So, I'd like to start contributing my own ideas to see if I can't help others.

I'm not going to bind this blog to any particular software technology or platform; however, as I post more content you might quickly realize that the majority of my experience is with .NET and other Microsoft technologies. This doesn't mean I'm not interested in leveraging other technologies, so feel free to comment on them as well.

I hope that you find the information here interesting and useful!