1

I use a SQL Procedure to update many rows in one table based on a crieteria, here a date.

CREATE PROCEDURE [S_SourceProduct].P_U_SourceProductSetDisableBeforeDate
@UpdateDate DATETIME
AS
SET NOCOUNT ON;
UPDATE [S_SourceProduct].T_E_SourceProduct
SET [Disabled]=1
WHERE [S_SourceProduct].T_E_SourceProduct.UpdateDate <  @UpdateDate

I didn't do it with entity because I know only 2 ways:

  • Retrieve all items and loop on each one => slower than my stored procedure with a lot of rows.
  • Copy my procedure in a string and use it as string query => no build checking about syntaxe or column name and so on.

Is there an other except up both ?

Julian50
  • 2,462
  • 1
  • 21
  • 30
  • You *can* use stored procedures with EF, btw. – Rob May 06 '15 at 14:39
  • you might find this helpful as a start: http://stackoverflow.com/a/5942176/2495283. It's related to Bulk Inserts, but the principles are the same. – Claies May 06 '15 at 14:39
  • @Robert You right.Actualy, I do this way. But I'm looking for a way to generate the query with entity. – Julian50 May 06 '15 at 14:41
  • You mean you want to create the stored procedure in your DB if it does not exist yet? – Rob May 06 '15 at 14:43
  • @Claies Yes You are right, but this is the 1 first way I know. But this way is pretty slow with big amount of rows. Entity will send one query for each row even you use savechanges at the end – Julian50 May 06 '15 at 14:44

1 Answers1

3

If you don't mind using an extension to Entity Framework, this can be achieved using EntityFramework.Extended

For example: 

//delete all users where FirstName matches
context.Users.Delete(u => u.FirstName == "firstname");

//update all tasks with status of 1 to status of 2
context.Tasks.Update(
    t => t.StatusId == 1, 
    t2 => new Task {StatusId = 2});
ozz
  • 5,098
  • 1
  • 50
  • 73