Multiple entity updates with Entity Framework – EF Fetch Updates

 

[Updated Sample 22-07-2008 ]

New Features

Support call methods in expressions ( contains..)

Support external call methods in expressions

Support various entity members call methods ( substring, tolower, toupper )

Support access to variables in expressions

[Updated Sample 17-07-2008 ]

Fix bugs

 

In Spanish 

Before you start reading this post, ensure you have some free time, because the post can be a bit longJ.

Probably, many of those who have played with Entity Framework have asked himself how to update or delete several entities at a time; for instance, how to indicate EF that we want to update all customers in Madrid to certain values. Actually, once they knew that there is no support by default for these tasks, some have researched how much worst is to execute several independent updates and/or deletes (even when executed in batch) with respect to a unique UPDATE or DELETE sentence specifying a filter, and have posted about the differences in the MSDN Entity Framework newsgroups. In this post, I pretend to show a possible implementation of this task; I have served here of several ideas taken from the blog of Alex James, member of the EF product team.

Let's start from the beginning:

About metadata

One of the most important problems we must solve along this way is to obtain the correspondences, within an EDM conceptual model, between our model entities and the underlying relational database. This task, which could seem very simple (at least, that's what I thought when I started researching about this matter), is not easy at all. MetadataWorkspace, the main class within EF that allows us to work with conceptual model's metadata, does not offer the possibility of obtaining information on the C-S space, the mapping space between objects and storage. So here we hit our first roadblock... After asking several questions to the product team (sorry Danny Simmons), it is clear that, at least in this first version, there won't be a way to obtain the entity-to-table mapping information. Alex James, in one of his posts, talks about querying EntitySet elements present in conceptual models; there he comments that, given a simple entity within a conceptual model, if we check out its SELECT query we'll find a sentence with the form SELECT [ColumnName] AS [PropertyName] ... FROM [Table] ... This opened a possible way to know how to associate the name of a property with the name of the underlying column, to solve the metadata problem. Regretfully, I have observed that if we rename an entity's property, the generated query doesn't reflect this change and continues showing [ColumnName] as [PropertyName], where [PropertyName] is the old name of the property.

At this point, I decided that I should directly access the metadata specification, getting to the resources that the Entity modeler embeds into the resulting assembly (this is the default option since VS2008 SP1 Beta). Using this approach, the only thing necessary is to parse the MSL used by the EF infrastructure. The following XML fragment represents part of the contents of one such MSL document, where we can see that the eBook entity maps to the eBook table, and the mapping of the different properties of the entity.

<EntitySetMapping Name="eBook">

<EntityTypeMapping TypeName="IsTypeOf(MAmazonModel.eBook)">

<MappingFragment StoreEntitySet="eBook">

<ScalarProperty Name="idProducto" ColumnName="idProducto" />

<ScalarProperty Name="ISBN_13" ColumnName="ISBN_13" />

<ScalarProperty Name="ISBN_10" ColumnName="ISBN_10" />

<ScalarProperty Name="Edicion" ColumnName="Edicion" />

<ScalarProperty Name="Paginas" ColumnName="Paginas" />

<ScalarProperty Name="Indice" ColumnName="Indice" />

<ScalarProperty Name="LenguajeLibro" ColumnName="Lenguaje" />

<ScalarProperty Name="CapituloEjemplo" ColumnName="CapituloEjemplo" />

</MappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

Query expressiveness

Once we have in hand the information regarding the mapping of our conceptual entities to the underlying database, the next question is how to implement a mechanism such that the developer can specify the different parts of an UPDATE sentence (SET and WHERE), and a DELETE sentence (WHERE). As we are working with EF and LINQ to Entities, we don't want to lose the blessing of strong typing; our goal is to express these parts using elements of our programming language, and not "dumb" character strings. So here we will leverage expression trees, used internally by EF, LINQ to SQL and other IQueryable LINQ providers. Thanks to expression trees, we will offer the programmer the possibility of writing strongly typed queries from which SQL sentences will be generated automatically at runtime. For more information on expression trees, I recommend the book of my friend Octavio Hernandez, "C#3.0 y LINQ".

For the SET part of our UPDATE sentence we can use the MemberInitExpression expression type, which allows us to specify how a concrete type is initialized. The next sentence shows an example of this kind of expression:

    Expression<Func<Customer, Customer>> memberExpression = c => new Customer() { FirstName="Unai" };

Note how the use of an expression of this type allows us to specify, in a relatively easy way, the SET part of an UPDATE sentence; in this case, the intended translation is something similar to UPDATE [TABLE] SET FirstName='Unai'.

For the WHERE part of the UPDATE y DELETE sentences, we could use a BinaryExpression, just like this:

Expression<Func<Product, bool>> binary = p => p.id > 21 && p.Title != "The title";

Just like in the previous case, it's quite intuitive to see that this could be translated to something like WHERE id > 21 AND Title <> 'The title'

At this point, we already know how we will allow the specification of the different parts of our sentences, using expression trees formed of nodes like those previously shown. So now we must create a parser for those expressions, a task that can be easier or tougher depending on the features we would like to support. The following method can serve as a very simple SET parser, and is shown so that you can understand the work involved in generating the corresponding SQL fragment.

static void ParseExpression(Expression<Func<Customer, Customer>> expression)

{

if (expression!= null)

{

MemberInitExpression initExpression = inner.Body as MemberInitExpression;

if (initExpression != null)

{

var result = (from m in initExpression.Bindings.OfType<MemberAssignment>()

select new { PropertyName = m.Member.Name, Value = m.Expression.ToString() }).ToList();

StringBuilder sb = new StringBuilder();

sb.Append("SET ");

sb.Append(result[0].PropertyName);

sb.Append("=");

sb.Append(result[0].Value);

for (int i = 1; i < result.Count; i++)

{

sb.Append(",");

sb.Append(result[i].PropertyName);

sb.Append("=");

sb.Append(result[i].Value);

}

Console.WriteLine(sb.ToString());

}

else

Console.WriteLine("The expression is not valid");

}

else

Console.WriteLine("The expression is not valid");

}

Among the many different problems left for parser of this kind to solve we must mention the translation of assignments like new Customer () { Fecha= DateTime.Now }, the use of variables inside those assignments, method calls...

Closing the circle... ¡or opening it!

Now that we have a mechanism to access the metadata information in a not very elegant, but effective wayJ, and we have way to specify the DML operations we'd like to execute, the only thing left is to implement the execution of those commands against the relational store. For that matter, we can create extension methods like UpdateAll() o DeleteAll() for ObjectQuery<T>, the base class for all queryable objects in EF. From ObjectQuery we can have access to the EF connection string, which includes the specific storage connection provider and connection string, so this operation is not difficult to implement.

<add name="MAmazonEntities" connectionString="metadata=res://*/MAmazonModel.csdl|res://*/MAmazonModel.ssdl|res://*/MAmazonModel.msl;provider= System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;Initial Catalog=MAmazon;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

With this post, I'm including a sample that shows all we have talked about here. In that implementation, expression tree analyzers are capable of handling additional elements like expressions with assignments, like in the following fragment:

using (MAmazonEntities entities = new MAmazonEntities())

{

entities.Product.UpdateAll(p => new Product() { LaunchDate = new DateTime(2008,2,2), Title = "Unai" }, p => p.id > 21 && p.Title != "Hello");

entities.Product.UpdateAll(p => new Product() { LaunchDate = DateTime.Now }, p => p.id > 20);

entities.Product.DeleteAll(p => p.id > 20);

Console.ReadLine();

}

Hope it helps!

Sample entity Framework

Published 17/7/2008 11:32 por Unai
Archivado en:
Comparte este post:
http://geeks.ms/blogs/unai/archive/2008/07/17/multiple-entity-updates-with-entity-framework-ef-fetch-updates.aspx

Comentarios

# re: Multiple entity updates with Entity Framework – EF Fetch Updates

Hi all!

Nice site!

Bye

Wednesday, October 1, 2008 12:51 PM por Pepenzymn

# argent

This brings me to an idea:...

Sunday, August 2, 2009 2:21 AM por Jan-Kaas

# Rulette Trick

Sometimes it's really that simple, isn't it? I feel a little stupid for not thinking of this myself/earlier, though.

Thursday, August 20, 2009 11:39 PM por Roulette Tips

# re: Multiple entity updates with Entity Framework – EF Fetch Updates

When I try to download the sample zip file it seems to be corrupt.  Can you post it again?

Thanks!

Friday, October 1, 2010 2:47 PM por Eric Grover