Archive for April, 2009

Using LINQ to SQL and EF in Sharepoint under Medium Trust

April 22nd 2009

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 Files\Common Files\Microsoft Shared\Web Server Extensions\12\config\wss_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.

Posted by David DeWinter under Entity Framework & LINQ to SQL | 2 Comments »

LINQ to SQL: Updating Entities

April 7th 2009

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.

Posted by David DeWinter under LINQ to SQL | 1 Comment »