Linq How to run queries in chunks

Question

I have bundle of delete queries like following :-

DELETE FROM [Entry] 
WHERE CompanyId = 1 
  AND EmployeeId IN (3, 4, 6, 7, 14, 17, 20, 21, 22,....100 more) 
  AND Entry_Date = '2016-12-01' 
  AND Entry_Method = 'I'

SO in my code, i run this list of queries as below :-

using (var ctx = new ApplicationDbContext(schemaName))
        {
            foreach (var item in queries)
            {
                ctx.Database.ExecuteSqlCommand(item);
            }
        }

But due to the large number of queries executing it creates a lock on sql, so i decided to execute the queries in chunk, so i found the below code :-

SET ROWCOUNT 500
delete_more:
DELETE FROM [Entry] 
    WHERE CompanyId = 1 
      AND EmployeeId IN (3, 4, 6, 7, 14, 17, 20, 21, 22,....100 more) 
      AND Entry_Date = '2016-12-01' 
      AND Entry_Method = 'I'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0

Now the problem is How do i run this thing as i was running it previously through ctx.Database.ExecuteSqlCommand?

What is the way i can run this chunk query code in Linq?

Did you try just putting that whole statement in a string and running that using `ExecuteSqlCommand`? — Nick.Mc, Jan 02 '17 at 06:53
"doesn't work" doesn;t work for me. Do you get an error or does it not do what you expect? I have no idea if it actually works. It just makes sense to me that it would. — Nick.Mc, Jan 02 '17 at 11:30

score 1 · Accepted Answer · edited Jan 08 '17 at 19:15

1

I would create a SQL Server stored procedure that get the employee ids as a parameter. Let's call it 'sp_deleteEmployees' with the param @ids

Then in C# you create a string on the ids

string idsList = "3, 4, 6, 7, 14, 17, 20, 21, 22"
context.Database.ExecuteSqlCommand("usp_CreateAuthor @ids={0} ", idsList);

EDIT

Sorry, I guess I didn't understand the problem. If you need to delete the employees in chunk you can split the list of employee with this

public static List<IEnumerable<T>> Partition<T>(this IEnumerable<T> source, int length)
{
    var count = source.Count();
    var numberOfPartitions = count / length + ( count % length > 0 ? 1 : 0);

    List<IEnumerable<T>> result= new List<IEnumerable<T>>();

    for (int i = 0; i < numberOfPartitions; i++)
    {
        result.Add(source.Skip(length*i).Take(length));
    }

    return result;
}

You can use this method to split the list to small chunks and delete them one chunk at a time

edited Jan 08 '17 at 19:15

marc_s

732,580
175
1,330
1,459

answered Jan 02 '17 at 08:28

michael berezin

1,146
7
8

This thing works. but didn't solve my actual problem. But i will raise a different question. – Anup Jan 02 '17 at 12:56
sorry, i guess i didint understund the problem. – michael berezin Jan 02 '17 at 17:16
Here is new question - http://stackoverflow.com/questions/41436449/linq-how-to-prevent-locks-when-bulk-delete – Anup Jan 03 '17 at 04:35
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Jan 08 '17 at 19:16

Linq How to run queries in chunks

1 Answers1