This library enables mass data manipulation operations for LINQ-to-entity. Currently the following operations are supported:
  • INSERT INTO table (column1, column2, …) SELECT proj1, proj2, … FROM (entityQuery)
using EFMassOperations;

	myEntities.user.Insert(
		(from history in myEntities.userhistory
		 where history.operation == "INSERT"
		 && history.operationid > 506
		 select new
		 {
			history.displayname,
			history.username,
			loweredusername = history.username.ToLower(),
			history.foreignuserid,
			createdate = history.syncdate
		 }),
		 (insertedUser) => new
		 {
			insertedUser.displayname,
			insertedUser.username,
			insertedUser.loweredusername,
			insertedUser.foreignuserid,
			insertedUser.createdate,
		 });

Insert operation is available for ObjectSet<T>. As its first argument it accepts LINQ-to-entity query which selects newly inserted data. It goes as entityQuery in SQL. The query should return only columns used for insertion as anonymous object. The second argument is an expression (or lambda) which maps properties of entity being inserted to the same anonymous type as query returns so the library could establish correct column order.
  • UPDATE table SET column1=proj1, column2=proj2, … FROM (entityQuery) WHERE somePredicate
	myEntities.user.Update(
		(from history in myEntities.userhistory
		 where history.operation == "UPDATE"
		 select new
		 {
			 history.foreignuserid,
			 history.displayname,
			 history.username,
			 lowered = history.username.ToLower()
		 }),
		 (updateProperties) => new user
		 {
			 displayname = updateProperties.displayname,
			 username = updateProperties.username,
			 loweredusername = updateProperties.lowered
		 },
		 (usr, update) => (usr.foreignuserid == update.foreignuserid));

Update operation is available for ObjectSet<T>. As its first argument it accepts LINQ-to-entity query which selects data used for update and for filtering rows needed for update. The query goes as entityQuery in SQL.
The second argument goes in SET sql expression. It is an expression (lambda) establishing a mapping of properties selected by LINQ query to updated EntityObject's properties. So library could generate columns assignments in correct order. Only property access and assignment is allowed in second argument. Expressions like the following are NOT allowed:
	myEntities.product.Update(
		(from discount in myEntities.productDiscount
		 select new
		 {
			discount.productId,
			discount.percent
		 },
		 (updateInfo) => new product
		 {
			product.releasePrice = product.price * (100.0 - updateInfo.percent) / 100.0
		 },
		 (prod, update) => prod.productId == update.productId);

It could and should be changed like this:
	myEntities.product.Update(
		(from discount in myEntities.productDiscount
		 join prod in myEntities.product on discount.productId == prod.productId
		 select new
		 {
			discount.productId,
			newPrice = prod.price * (100.0 - discount.percent) / 100.0
		 },
		 (updateInfo) => new product
		 {
			product.releasePrice = updateInfo.newPrice
		 },
		 (prod, update) => prod.productId == update.productId);

The third argument is WHERE predicate expression (lambda) filtering entities to be updated by comparing with selected objects. Only the following operations are allowed in this expression: ==, !=, <, >, <=, >=, &&, ||, !. If there's a need for arithmetic or other expression kinds this operations should be placed in selecting query like in the example above.
  • DELETE FROM table WHERE EXISTS/NOT EXISTS (SELECT * FROM (entityQuery) WHERE somePredicate)
	myEntities.user.DeleteWhereExists(
		(from history in myEntities.userhistory
		 where history.operation == "DELETE"
		 select history),
		 (usr, selected) => usr.foreignuserid == selected.foreignuserid);

DeleteWhereExists and DeleteWhereNotExists are available for ObjectSet<T>. The first argument is LINQ-to-entity query going in the place of entityQuery in sql. The second argument is a predicate filtering entities to be deleted. It goes in place of somePredicate in sql. The same rules apply as for Update third argument.
  • DELETE FROM table
	myEntities.user.DeleteAll();

DeleteAll is defined for ObjectSet<T>. And does what it means.

Note: Any operation is executed immediately and returns number of rows affected.

Last edited Nov 1, 2011 at 7:35 AM by alabax, version 7

Comments

No comments yet.