LINQ Expression Trees and the Specification Pattern

Over the past couple of months I have tried to immerse myself in domain-driven design, which includes learning about its purpose, the methodology, and the domain patterns presented in Evans’ book and built upon in many other venues (blogs, conferences, etc.). While I have not worked on a full-fledged DDD project, I have fiddled with a lot of patterns. One of these is the Specification pattern, which says to introduce a predicate-like Value Object into the domain layer whose purpose is to evaluate whether an object meets some criteria. From what I’ve read in Evans’ book, specification objects typically have an isSatisfiedBy method that takes a domain object and returns a boolean. The specification therefore encapsulates a predicate that can be used to test an object to see if it satisfies the criteria.

image

The problem that Evans later calls out is that of querying a data store using specification objects as filters. Because using the specification to filter records from the database requires that those records be selected and reconstituted into objects, it can be inefficient for some applications to use specification objects as is. (Imagine using a specification object on one million rows in the Customer table just to find the gold Customers!) Surely we can do better.

Ideas

One idea in the book is to allow a repository to help with the implementation and utilize double dispatch to keep the separation of domain and infrastructure in tact. Application code calls a method on a repository to query for objects based on a specification. That repository passes itself to a method on the specification object, so the specification can utilize the repository’s power to query for the objects that fulfill the criteria, and then return that data to the application.

image

Another alternative is to harness the power of LINQ and expression trees to represent the predicate that the specification object encapsulates. This means that we can (1) use the expression trees in the infrastructure to let the data store take care of filtering and (2) still represent our rule in one location without resorting to compromises in the repository API.

Expression trees are abstract syntax trees that can represent the predicates that specification objects strive to encapsulate. With these expression trees, certain O/R mappers like LINQ to SQL, the Entity Framework, and LLBLGen Pro can determine the intent of the code and translate it into the corresponding T-SQL code to run against the database.

Creating an expression tree is very simple. In fact, if you’ve used any of the O/R mappers I mentioned above, you’ve probably used them already. Here’s an example of an expression tree being used in LINQ to SQL to generate the WHERE clause in the corresponding T-SQL query below.

NorthwindDataContext db = new NorthwindDataContext();

db.Products.Single(p => p.ProductName == "Aniseed Syrup");

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

FROM [dbo].[Products] AS [t0]

WHERE [t0].[ProductName] = @p0

– @p0: Input NVarChar (Size = 13; Prec = 0; Scale = 0) [Aniseed Syrup]

– Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

Normally the lambda expression ‘p => p.ProductName == "Aniseed Syrup"’ would be treated as a Func<Product, bool>. However, in this particular usage the compiler infers that it is an Expression<Func<Product, bool>>. The difference means that the LINQ to SQL library no longer has a method pointer. Instead, it has a tree which represents what that method does. LINQ to SQL can visit the nodes in this tree and translate what it finds into SQL without ever invoking the code itself. A simple Func does not have that capability; it is simply a method pointer, like any other delegate type.

I hope you start to see how expression trees and the specification pattern can be very powerful together. If in addition to exposing an isSatisfiedBy method on the specification object, we add something which exposes the raw Expression, the repository can compose this Expression into the query and filter the results using the infrastructure. Let’s look at some code.

For this example, let’s continue to use the Products table from Northwind. The specification we implement here will tell us whether a product is a low stock product i.e. whether the number of units in stock for a particular product falls below a certain threshold. That threshold is defined in another system, so we will feed that data to the specification.

Let’s start with the basics. Here’s the base class for all Specifications. Instead of using IsSatisfiedBy, we expose a method which returns an expression tree of type Expression<Func<T, bool>>.

public abstract class Specification<T>

{

    public abstract Expression<Func<T, bool>> IsSatisfied();

}

The Expression class is in the System.Linq.Expressions namespace which is a part of System.Core.dll. Remember, this is just a different representation of IsSatisfiedBy; instead of keeping the logic embedded in a method in the specification object, we package the logic in an expression tree. The predicate still receives an object and returns a boolean. Other classes, like the ProductRepository, can now leverage this expression tree to optimize the query it sends to the database.

public partial class ProductRepository : IProductRepository

{

    public IQueryable<Product> SelectSatisfying(Specification<Product> specification)

    {

        return this.context.Products.Where(specification.IsSatisfied());

    }

}

Here we use the Entity Framework to select the Products that match a certain Product Specification. (The field "context" is the ObjectContext, in this case.) However, we could switch this for any data access technology that can leverage expression trees and retrieve similar results.

The next step is to implement the actual specification.

public class LowStockSpecification : Specification<Product>

{

    public LowStockSpecification(int lowStockThreshold)

    {

        this.LowStockThreshold = lowStockThreshold;

    }

 

    public int LowStockThreshold

    {

        get;

        private set;

    }

 

    public override Expression<Func<Product, bool>> IsSatisfied()

    {

        return p => p.UnitsInStock < this.LowStockThreshold;

    }

}

Evans says that specifications should be value objects, so I’ve taken that to heart and made this class immutable. This allows us to make some optimizations with specifications (caching the expression tree, introducing an IsSatisfiedBy by reusing the logic in the expression tree, etc.) if we would like.

This final code snippet shows how to leverage the specification and repository together.

public class ProductReorderingService

{

    private IProductRepository productRepository;

 

    public ProductReorderingService(IProductRepository productRepository)

    {

        this.productRepository = productRepository;

    }

 

    public void ReorderLowStockProducts()

    {

        LowStockSpecification spec = new LowStockSpecification(5);

        foreach (var p in this.productRepository.SelectSatisfying(spec))

        {

            // Reorder product

        }

    }

}

Composing Specifications

One property of specifications is that they can be combined to form more interesting predicates. This would allow our ProductRepository to support queries that involve multiple specification instances—for example, a filter that checks for units with low stock OR units whose stock is below their re-order level. The most common implementation I’ve seen of this requirement involves three new classes, AndSpecification<T>, OrSpecification<T>, and NotSpecification<T>. While it’s easy enough to implement these when all you worry about is IsSatisfiedBy (e.g. spec1.IsSatisfiedBy(o) && spec2.IsSatisfiedBy(o) for the AndSpecification<T>), it’s actually a bit tricky to do this with expressions.

Fortunately, it’s not impossible, and Colin Meek has it documented on his blog post about combining predicates in the Entity Framework, but the concepts apply more generally to any provider that can use expression trees. Be careful though; if you’re using the Entity Framework you will have to copy more code than you would with LINQ to SQL. I am not sure about LLBLGen Pro.

If you use the extension methods that Colin provides for AND’ing and OR’ing expression trees together, you’ll end up with these implementations of AndSpecification<T> and OrSpecification<T>:

public class AndSpecification<T> : Specification<T>

{

    private Specification<T> spec1;

    private Specification<T> spec2;

 

    public AndSpecification(Specification<T> spec1, Specification<T> spec2)

    {

        this.spec1 = spec1;

        this.spec2 = spec2;

    }

 

    public override Expression<Func<T, bool>> IsSatisfied()

    {

        return this.spec1.IsSatisfied().And(this.spec2.IsSatisfied());

    }

}

public class OrSpecification<T> : Specification<T>

{

    private Specification<T> spec1;

    private Specification<T> spec2;

 

    public OrSpecification(Specification<T> spec1, Specification<T> spec2)

    {

        this.spec1 = spec1;

        this.spec2 = spec2;

    }

 

    public override Expression<Func<T, bool>> IsSatisfied()

    {

        return this.spec1.IsSatisfied().Or(this.spec2.IsSatisfied());

    }

}

We’ll have to write the NotSpecification<T> ourselves, but this is not as involved as And and Or, even with the Entity Framework. We essentially take the body of the expression tree from the original specification and negate the result. Using the patterns you can read about in Colin’s blog post, we can use the following class as our NotSpecification<T>.

public class NotSpecification<T> : Specification<T>

{

    private Specification<T> originalSpec;

 

    public NotSpecification(Specification<T> originalSpec)

    {

        this.originalSpec = originalSpec;

    }

 

    public override Expression<Func<T, bool>> IsSatisfied()

    {

        Expression<Func<T, bool>> originalTree = this.originalSpec.IsSatisfied();

        return Expression.Lambda<Func<T, bool>>(

            Expression.Not(originalTree.Body),

            originalTree.Parameters.Single()

        );

    }

}

This is all well and good, but doesn’t this tie my domain to my infrastructure?

I think you can find arguments for both viewpoints. The specification pattern allows you to encapsulate a predicate to determine whether an object matches a condition. My opinion is whether that predicate is exposed as a method or an expression tree, the intent is preserved and there is one place where the criteria for a specification are checked. It does require you to use infrastructure that can utilize expression trees, but I would say that there is nothing about expression trees that tie them to the infrastructure layer directly. The details of the underlying data store have not leaked into the domain layer. If I had a provider that could use expression trees for XML or an object database store, then my domain layer would not change.

I enjoy learning about DDD and what other folks have done in this area. I’d love to hear your feedback.

Using LINQ to SQL and EF in Sharepoint under Medium Trust

Code Access Security (CAS) is a large area of the .NET Framework that doesn’t often get the developer attention it deserves—at least, until it causes problems. Very recently I fielded a question on why the Entity Framework throws a SecurityException when executing a query in a Sharepoint-hosted web site run under medium trust. The answer, I thought, is definitely worth sharing.

The Repro

The failure looks very similar to the exception described on this forum post. However, the forum post describes a SecurityException thrown from an XBAP application, not an ASP.NET application. The reason for the failure, however, is the same. Take the following EF query, for example:

var customer = (from c in db.Customers

               where c.CustomerID == "ALFKA"

               select c).FirstOrDefault();

It’s a very simple query, which runs in medium trust without any errors. But consider the more useful use case:

public Customer GetCustomerById(string customerId)

{

    using (NorthwindEntities db = new NorthwindEntities())

    {

        return (from c in db.Customers

                where c.CustomerID == customerId

                select c).FirstOrDefault();

    }

}

The fundamental difference is that we’re using a parameter value to supply the criterion for our query. This throws the following exception in Sharepoint under medium trust:

System.MethodAccessException was unhandled by user code
  Message="System.Runtime.CompilerServices.StrongBox`1..ctor(System.__Canon)"
  Source="mscorlib"
  StackTrace:
       at System.Reflection.MethodBase.PerformSecurityCheck(Object obj, RuntimeMethodHandle method, IntPtr parent, UInt32 invocationFlags)
       at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
       at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
       at System.Linq.Expressions.ExpressionCompiler.AddGlobal(Type type, Object value)
       at System.Linq.Expressions.ExpressionCompiler.GenerateConstant(ILGenerator gen, Type type, Object value, StackType ask)
       at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)
       at System.Linq.Expressions.ExpressionCompiler.GenerateMemberAccess(ILGenerator gen, Expression expression, MemberInfo member, StackType ask)
       at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)
       at System.Linq.Expressions.ExpressionCompiler.GenerateLambda(LambdaExpression lambda)
       at System.Linq.Expressions.ExpressionCompiler.CompileDynamicLambda(LambdaExpression lambda)
       at System.Linq.Expressions.ExpressionCompiler.Compile(LambdaExpression lambda)
       at System.Linq.Expressions.ExpressionCompiler.Compile[D](Expression`1 lambda)
       at System.Linq.EnumerableExecutor`1.Execute()
       at System.Linq.EnumerableExecutor`1.ExecuteBoxed()
       at System.Data.Objects.ELinq.ClosureBinding.ParameterBinding.EvaluateBinding()
       at System.Data.Objects.ELinq.ClosureBinding.TryCreateClosureBinding(Expression expression, ClrPerspective perspective, Boolean allowLambda, HashSet`1 closureCandidates, ClosureBinding& binding, TypeUsage& typeUsage)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.EqualsTranslator.TypedTranslate(ExpressionConverter parent, BinaryExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.Convert()
       at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)

  InnerException: System.Security.SecurityException
       Message="Request for the permission of type ‘System.Security.Permissions.ReflectionPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ failed."
       Source="mscorlib"
       StackTrace:
            at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
            at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
            at System.Security.PermissionSetTriple.CheckSetDemand(PermissionSet demandSet, PermissionSet& alteredDemandset, RuntimeMethodHandle rmh)
            at System.Security.PermissionListSet.CheckSetDemand(PermissionSet pset, RuntimeMethodHandle rmh)
            at System.Security.PermissionListSet.DemandFlagsOrGrantSet(Int32 flags, PermissionSet grantSet)
            at System.Security.CodeAccessSecurityEngine.ReflectionTargetDemandHelper(Int32 permission, PermissionSet targetGrant, CompressedStack securityContext)
            at System.Security.CodeAccessSecurityEngine.ReflectionTargetDemandHelper(Int32 permission, PermissionSet targetGrant)
       InnerException:

A Closer Look

The bolded text clues us into the real problem—a demand for ReflectionPermission failed. But we’re not doing reflection…or are we?

In order for our C# code to access the value of the customerId parameter, the compiler creates a new private nested class with a single public field for the customerId. When creating the expression tree to represent the query above, the compiler inserts code to instantiate this new class and adds that instance as a node in the tree. When the Entity Framework later processes the expression tree, it attempts to create an instance of that private class. (See above—RuntimeConstructorInfo.Invoke)

So what does permissions does this call to RuntimeConstructorInfo.Invoke require? In .NET 3.5, the demand is satisfied when all assemblies in the stack trace have one of the following sets of permissions:

  1. ReflectionPermission with ReflectionPermissionFlag.MemberAccess
  2. ReflectionPermission with ReflectionPermissionFlag.RestrictedMemberAccess UNION PermissionSet of the assembly which contains the member being reflected.

In .NET 2.0, ASP.NET medium trust did not allow reflection on non-public members. When Microsoft released .NET 3.5, the ReflectionPermission featured a new option: RestrictedMemberAccess (RMA). The concept is a bit tricky to understand the first time, so here’s an example of what RMA really means.

Take two assemblies, A and B. Assembly A is loaded in medium trust, and Assembly B is loaded in medium trust. Because both assemblies have the same trust level and because medium trust grants RMA, Assembly A can use reflection to discover non-public members in Assembly B. If a new assembly, Assembly C, is loaded in full trust, then Assembly A cannot use reflection to discover non-public members in Assembly C. If Assembly A is granted ReflectionPermission with MemberAccess, then it can use reflection on any assembly to discover any non-public member. Shawn Farkas discusses RMA in more detail here.

Assuming all this makes sense, let’s return to the problem. Our assembly is loaded in medium trust, and the code tries to reflect into the same assembly to find this non-public type generated by the compiler. However, the demand for ReflectionPermission still fails. This doesn’t make sense; the assembly’s permission set is equal to itself, and medium trust grants RMA…

The Solution

The key here is that medium trust in Sharepoint is not what we expect. The web.config file for our application points us in the right direction:

<trust level="WSS_Medium" originUrl="" />

The WSS_Medium trust level is not the same as the medium trust level offered by ASP.NET. On further investigation in the same web.config file, we see that WSS_Medium is defined in an external policy file:

<trustLevel

  name="WSS_Medium"

  policyFile="C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions12configwss_mediumtrust.config" />

If you open the policy file, you’ll see this scavenger hunt leads us to one conclusion: Sharepoint medium trust does not include ReflectionPermission with RestrictedMemberAccess. To add this permission, you’ll need to make two changes to the policy file. First, add the following SecurityClass element to the configuration/mscorlib/security/policy/PolicyLevel/SecurityClasses element.

<SecurityClass

  Name="ReflectionPermission"

  Description="System.Security.Permissions.ReflectionPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>

Second, add the RMA permission to the configuration/mscorlib/security/policy/PolicyLevel/NamedPermissionSets/PermissionSet[@Name='SPRestricted'] element. It should be the only one with child elements.

<IPermission

  class="ReflectionPermission"

  version="1"

  Flags="RestrictedMemberAccess"/>

Once the application pool is recycled, the CLR will grant you the RMA permission, and you will be able to run LINQ to SQL and EF queries like those shown above without any fear of SecurityExceptions.

LINQ to SQL: Updating Entities

Updating entities using any object-relational mapper can be difficult for at least two reasons: (1) the number of update options that these ORM frameworks provide and (2) the complexity of requirements in today’s business scenarios. With this post I want to discuss a few of the ways that you can update entities in LINQ to SQL to fit your particular scenario.

What Do I Need?

Before we jump into code, you have to consider how your product requirements influence your update decisions. There are two questions you can ask to help:

  1. Can multiple clients update the same database record at the same time?
  2. If this can happen, what data is saved when multiple simultaneous updates occur?

Question 1′s answer is usually yes, but there are some environments where it is no, such as single-user applications and applications where users own specific data that nobody else can update.

Question 2 is a bit more complicated. Consider a web application that allows customers to choose their desired seat(s) on a commercial flight. The typical use case is that the customer chooses his seats and successfully submits his choice, but what is the expected behavior when two people choose the same seat and click submit at the same time? Clearly, the customer whose seat choice was saved first should win, and the application should ask the "losing" customer to make another choice.

image

Although less common, it’s also possible for the last update to overwrite all previous changes. This can be very dangerous, especially if the users whose updates were overwritten aren’t notified. There is also a balance, where the last update wins only if a particular column or set of columns were updated.

I have targeted the discussion of these questions so far toward optimistic concurrency since that is the only concurrency control that LINQ to SQL supports. It is interesting to contrast it with pessimistic concurrency, but it is out of scope for this post.

What Can LINQ to SQL Offer?

Once you’ve established what you need, it’s easier to match your requirements with what LINQ to SQL offers. But what does LINQ to SQL offer? Here we’ll look at a few simple use cases for updates.

Retrieve Existing Entity, Change Properties, Update

This is a popular pattern in many object-relational mappers to update entities. The code below shows an example of how to update a Product in the Northwind database using this technique.

using (NorthwindDataContext db = new NorthwindDataContext())

{

    // Retrieve the existing entity. Database Call 1

    Product product = db.Products.First(p => p.ProductID == 1);

 

    // Change the properties. LINQ to SQL knows

    // these specific properties have changed.

    product.UnitsInStock = 14;

    product.UnitsOnOrder = 100;

 

    // Flush the changes. Database Call 2

    db.SubmitChanges();

}

 

When the application calls the SubmitChanges method, LINQ to SQL knows what specific properties it needs to update in its SQL statement. This is because the DataContext is aware of the Product entity since it was retrieved from the database in the first place.

Note that this approach requires two separate calls to the database—one to select the Product entity and one to update it. In highly contentious situations, it is possible that another client can change that Product entity in between these two database calls. In that case, LINQ to SQL will either allow the last update or will throw a ChangeConflictException with the message "Row not found or changed." Which happens when? The answer is "it depends."

If you drag tables from the Server Explorer to your DBML designer and start using LINQ to SQL without changing the designer, then your update from the code above will look like this. (To get this information for your queries, set the DataContext.Log property to Console.Out. Alternatively, you can try a few other listeners, courtesy of Damien Guard.)

UPDATE [dbo].[Products]

SET [UnitsInStock] = @p9, [UnitsOnOrder] = @p10

WHERE ([ProductID] = @p0) AND ([ProductName] = @p1) AND ([SupplierID] = @p2) AND

([CategoryID] = @p3) AND ([QuantityPerUnit] = @p4) AND ([UnitPrice] = @p5) AND

([UnitsInStock] = @p6) AND ([UnitsOnOrder] = @p7) AND ([ReorderLevel] = @p8) AND

(NOT ([Discontinued] = 1))

– @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]

– @p1: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Chai]

– @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [1]

– @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [1]

– @p4: Input NVarChar (Size = 18; Prec = 0; Scale = 0) [10 boxes x 20 bags]

– @p5: Input Money (Size = 0; Prec = 19; Scale = 4) [18.0000]

– @p6: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [39]

– @p7: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [0]

– @p8: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [10]

– @p9: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [14]

– @p10: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [100]

– Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

 

In most cases, this doesn’t match what you expect. However, it does give you a clue why the ChangeConflictException’s message is "Row not found or changed." If the WHERE clause of the SQL UPDATE statement filters out the row you’re looking for, then no update occurs. And the only case when the WHERE clause would filter it out is when another client has updated one of the properties on the Product between the time it was retrieved and the time it was updated.

So what if you don’t want this behavior, and you just want to compare the values of the primary keys? This is also a simple change. Navigate to your entity type (Product) in the DBML designer and select all the columns that you do not want to participate in optimistic concurrency. Then go to the Properties window and change the "Update Check" property to Never. See below for details.

image

Re-running the LINQ to SQL code shown above will now yield much smaller SQL, at the cost of eliminating concurrency checking.

UPDATE [dbo].[Products]

SET [UnitsInStock] = @p1, [UnitsOnOrder] = @p2

WHERE [ProductID] = @p0

– @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]

– @p1: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [14]

– @p2: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [100]

– Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

 

You may notice another property in the property grid called "Time Stamp." If you have a timestamp column in your table, and its Time Stamp property is set to "True," then Update Check on other properties will be ignored.

A quick summary of this information:

  1. If you have a column whose "Time Stamp" property is true, then only that column will be used for optimistic concurrency checking.
  2. Otherwise, only columns whose "Update Check" property is set to "Always" (or "WhenChanged" and they are changed) will be used for optimistic concurrency checking.
  3. Primary keys are always used as part of the WHERE clause to identify the target tuple in the database.

Attach an Entity, Change Properties, Update

We can now use this overview of concurrency checking in LINQ to SQL to demonstrate the next technique, which does not require two round trips to the database. It is important, however, that you have all the information that LINQ to SQL needs for concurrency checking before using it.

using (NorthwindDataContext db = new NorthwindDataContext())

{

    // Create a new entity to "attach" to the context.

    // This means LINQ to SQL will recognize changes to

    // the entity _after_ we attach it.

    Product product = new Product();

 

    // Set all properties that LINQ to SQL will use for concurrency checking.

    // Here it is just our primary key, but you will also need

    // to set values for the timestamp property (if it exists) or

    // all of the properties whose Update Check is set to "Always."

    // You will also need to set values for properties whose

    // Update Check is set to "When Changed," if that value has changed in this update.

    product.ProductID = 1;

 

    // Attach the entity to the context. Now the DataContext

    // can track changes on the product variable without selecting

    // the existing Product entity from the database.

    db.Products.Attach(product);

 

    // Change the properties. LINQ to SQL knows

    // these specific properties have changed.

    // You need to ensure that these values differ from their previous value

    // (e.g. product.UnitsInStock = 0; will not update UnitsInStock to 0.)

    product.UnitsInStock = 14;

    product.UnitsOnOrder = 100;

 

    // Flush the changes.

    db.SubmitChanges();

}

 

It’s the same basic pattern as before, but instead of allowing the DataContext to retrieve the Product entity from the database, we put it there ourselves with the Attach method. You have to be very careful with Attach and default values. If I changed the code to set product.UnitsInStock to 0 instead of 14, the update wouldn’t occur because there would be no property change. Before the Attach, product.UnitsInStock is 0 (the default value for an Int32); before SubmitChanges, product.UnitsInStock is still 0. There was no change to the value, and thus there will be no update of the UnitsInStock column when SubmitChanges is called. To get around this you can try using one of the two other overloads for Attach. The first overload is useful for when you have both the old entity and the new entity. The code below shows how this works.

using (NorthwindDataContext db = new NorthwindDataContext())

{

    // Create the old product. You can imagine a UI tier

    // creating this instance and rehydrating all of the "old"

    // values before sending this to the data layer. Again,

    // the only properties that you need to set are those

    // that participate in optimistic concurrency. So, for

    // example, if ProductName’s Update Check was set to "Always,"

    // I would need to set that here.

    //

    // However, if you don’t set every property that you are updating,

    // you cannot be sure that your updates will go through. For example,

    // if oldProduct.UnitsInStock were 15 (in the database) but unset (i.e. 0)

    // in the code below, and newProduct.UnitsInStock were 0, then LINQ to SQL

    // will not update the UnitsInStock column.

    Product oldProduct = new Product

    {

        ProductID = 1

    };

    Product newProduct = new Product

    {

        ProductID = 1,

        UnitsInStock = 14,

        UnitsOnOrder = 100

    };

 

    db.Products.Attach(newProduct, oldProduct);

 

    // Flush the changes.

    db.SubmitChanges();

}

 

When Attach(entity, original) executes, LINQ to SQL sees what properties have different values between the original entity and the current entity and uses them to update the tuple in the database when SubmitChanges is called. Again, it’s critical that the original entity (oldProduct, in this example) has all of its properties that participate in optimistic concurrency set to the values that were originally retrieved from the database.

You can use the third overload of the Attach when no properties participate in optimistic concurrency or the only property that does represents a timestamp column. If the latter is true, then the timestamp property’s value must match the value in the database for the update to be successful. This Attach overload also tells LINQ to SQL that every property has changed on the entity. This gets around the problems posed by the previous two Attach overloads, but you must now make sure that you set every property; otherwise, the unset properties will be set to NULL or the appropriate default value for the type as part of the update.

using (NorthwindDataContext db = new NorthwindDataContext())

{

    // No need to create the old product anymore, because

    // the only property that participates in optimistic concurrency

    // checking is the ProductID.

    Product product = new Product

    {

        ProductID = 1,

        UnitsInStock = 14,

        UnitsOnOrder = 100

    };

 

    db.Products.Attach(product, true);

 

    // Flush the changes. This will not do what we want!

    db.SubmitChanges();

}

 

The generated SQL for the code above reveals the following:

UPDATE [dbo].[Products]

SET [ProductName] = @p1, [SupplierID] = @p2, [CategoryID] = @p3,

[QuantityPerUnit] = @p4, [UnitPrice] = @p5, [UnitsInStock] = @p6,

[UnitsOnOrder] = @p7, [ReorderLevel] = @p8, [Discontinued] = @p9

WHERE [ProductID] = @p0

– @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]

– @p1: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [Null]

– @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [Null]

– @p4: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p5: Input Money (Size = 0; Prec = 19; Scale = 4) [Null]

– @p6: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [14]

– @p7: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [100]

– @p8: Input SmallInt (Size = 0; Prec = 0; Scale = 0) [Null]

– @p9: Input Bit (Size = 0; Prec = 0; Scale = 0) [False]

– Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

 

In this case it’s actually more difficult to apply only the changes we want, and we might as well use one of the other Attach overloads. Also, you should note that there is functionally no difference between Attach(entity) and Attach(entity, false).

Bringing It All Together

Now you know what your requirements dictate for updates as well as the factors involved in updating records with LINQ to SQL, we need to bring all this information together. Remember those questions I asked in the beginning? Here they are again just in case.

  1. Can multiple clients update the same database record at the same time?
  2. If this can happen, what data is saved when multiple simultaneous updates occur?

If multiple clients can’t update the record simultaneously, then you can use any of the options that I discussed in the previous section and not worry about optimistic concurrency. The "best" choice depends on your scenario. For example, if you have a smart client with a DataContext that is long-lived, then you may not even need to worry about which scenario you use. The DataContext will track the changes to the object on the UI and then you can call SubmitChanges immediately the code reaches your data layer. But if your DataContext is short-lived, then Attach(entity, true) may be useful if you retrieve the entity from one context and update it with another. In stateless environments (like ASP.NET), Attach(entity) and retrieving the entity from the database to update are probably the easier options. But beware the caveat with Attach!

If multiple clients can update the same record simultaneously, you will need to carefully consider which data can be safely overwritten and which cannot. You can then configure LINQ to SQL to recognize your choices with the "Update Check" property in the DBML designer. Attach is then probably the better option, because if your optimistic concurrency checks fail, then a ChangeConflictException is thrown to alert you. If you retrieve the entity from the database and then update its properties, you must explicitly check for concurrency violations. Otherwise, the risk is always present that you overwrite data that you don’t want to.

In this post I haven’t discussed how to resolve conflicts when LINQ to SQL throws a ChangeConflictException. There are a lot of useful links located here that do just that, in particular the three last how-tos: how to resolve concurrency conflicts by retaining database values, by overwriting database values, and by merging with database values.

I hope this has been a useful source of information on updates in LINQ to SQL. Of course, this covers only the APIs themselves and doesn’t discuss how you would integrate this into, for example, an n-tier ASP.NET application. This is something I’m definitely interested in doing for a future post.

EDIT: I made a few changes to this post to make a possible pitfall with Attach more explicit. You should definitely consider this before choosing an update strategy. Thanks to Damien Guard for pointing this out.

Data Access Confusion/Pain

I recently had an idea to retarget my blogging efforts to address confusing/little-known scenarios and pain points with LINQ to SQL and the Entity Framework. Since I’m using these technologies just about all the time now I have noticed that there is no end to the amount of discussion they bring up—from high-level architectural concerns like supporting n-tier and IoC to low-level implementation-specific questions like how to use the Attach family of methods in both technologies.

I’m going to prioritize my posts based on feedback I get personally (from here and other sources) and what I see on Twitter; KristoferA challenged twitterers (is that right?) to tweet their pains with both technologies with #linqtosqlpain and #efpain. I certainly have a few of my own that I want to address too.

(And yes, I have joined Twitter, too!)

DBML Fixup Preview

Back in February I blogged about DBML Fixup—a tool whose goal was to sync LINQ to SQL models with their respective database schemas from within the Visual Studio environment, as well as to handle running various fixup tasks on models. Now it’s six months later, and I have a much clearer vision of what the tool is meant to be as well as a firmer command on VSX. The following screencast is a preview of the features of DBML Fixup.

 

 

Here are some previous posts which (briefly) describe the domain:

http://blogs.rev-net.com/ddewinter/2008/02/16/the-linq-to-sql-model/

http://blogs.rev-net.com/ddewinter/2008/02/16/linq-to-sql-and-database-schema-sync/

 

Any ideas about directions for the tool? Or maybe something that wasn’t covered in the screencast? I’d love to hear your ideas.

The LINQ to SQL Metamodel

For my DBML Fixup project, I’ve really had to drill into the structure of LINQ to SQL—including how the data is stored in the .dbml file, how various changes in the designer affect the serialized XML (which is the format of the .dbml file), and what the various elements and attributes in the DBML file actually mean. (Honestly, what does the IsForeignKey attribute on the Association element imply as its meaning? I thought all "associations" represented foreign keys!)

At first I tried to dissect LINQ to SQL by keeping track of the various elements and attributes and their representations in the XML and the designer in big Excel tables. They quickly became unwieldy, and I decided to cut my losses and model the LINQ to SQL schema (DBMLSchema.xsd). Needless to say this was a very challenging exercise, but I gleaned a significant amount of knowledge about LINQ to SQL after finishing it.

The purpose of this post is to post the various pages of the LINQ to SQL model; I want this post to clarify the infrastructure of LINQ to SQL to others, so I would appreciate if you can comment on any inconsistencies or missing constraints you can see. I used Object-Role Modeling (ORM) for the model, so if you are not familiar with that, you may want to check out the following resources:

  1. Object Role Modeling: An Overview, http://msdn2.microsoft.com/en-us/library/aa290383(VS.71).aspx
  2. Halpin, T. 2001. Information Modeling and Relational Databases: From Conceptual Analysis to Logical Design.San Francisco: Morgan Kaufmann Publishers

The model starts like this…here’s the database page. The derivation rules aren’t on the diagram explicitly, as they are stored as part of the properties of the fact type. Other advantages of having the .orm file are that I’ve put definitions on all of the fact types to clarify their meaning, in the case that the predicate text is still not clear enough. If anyone wants the .orm file, feel free to contact me at david.dewinter AT (@) rev-net DOT (.) com. (You can open .orm files with Visual Studio after installing Project NORMA.) I will also put up a contact form soon…

Continue reading

LINQ to SQL and Database Schema Sync

Since the new year I’ve been working on a project that attempts to solve the following problem:

How do I keep my generated entity classes in sync with the current database schema using LINQ to SQL?

For some people, this question is moot. It’s easy enough to utilize the current “resync” mechanism in LINQ to SQL by deleting all the entity classes and functions (the LINQ to SQL term for sprocs and UDFs) on the designer surface and then redragging and dropping the tables and functions of interest, thus updating the entity classes by regenerating from scratch. However, for others, this solution is not good enough for a few reasons:

Continue reading