Entity Framework execute stored procedure in a loop

Question

I am trying to loop through a list and execute a stored procedure on every item in the list.

foreach(value in values) {
    Context.Database
        .ExecuteSqlCommand(
            "sp_ProcedureName @value1, @value2, @value3",
            new SqlParameter("@value1", value.value1.ToString()),
            new SqlParameter("@value2", value.value2.ToString()),
            new SqlParameter("@value3", value.value3.ToString()));
}

I keep getting the following error:

New transaction is not allowed because there are other threads running in the session.

I tried to use the async method as well. But that didn't seem to work. Is there something I am missing. Or is it not possible to run a stored procedure inside a loop?

is something else, like other threads do something with your context? — T.S.
– T.S., Commented Dec 13, 2017 at 5:29
Possible duplicate of Entity Framework Insert stored procedure in a loop — Arjun Prakash
– Arjun Prakash, Commented Dec 13, 2017 at 5:29
@ArjunPrakash Yes, there are recorded instances that this error is missleading — T.S.
– T.S., Commented Dec 13, 2017 at 5:32
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), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Dec 13, 2017 at 5:39
Just guessing: is values the result of a database call? have you tried ToList() to force it to load all data? — Nick.Mc
– Nick.Mc, Commented Dec 13, 2017 at 5:41

Nick.Mc · Accepted Answer · 2017-12-13 13:18:54Z

2

Perhaps the db call to populate values has opened a transaction, stopping the inside call functioning.

Try using .ToList() in the call that populates values to preload all of the values first.

answered Dec 13, 2017 at 13:18

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick.Mc Over a year ago

@Rainman answer will probably also solve this problem but it might eat up quite a few connections

Emre Kabaoglu · Accepted Answer · 2017-12-13 08:54:48Z

0

It could be better to create new Context instance for per execution;

            foreach (value in values)
            {
                using (var yourContext = new YourDbContext())
                {
                    yourContext.Database
                        .ExecuteSqlCommand(
                            "sp_ProcedureName @value1, @value2, @value3",
                            new SqlParameter("@value1", value.value1.ToString()),
                            new SqlParameter("@value2", value.value2.ToString()),
                            new SqlParameter("@value3", value.value3.ToString()));
                }
            }

answered Dec 13, 2017 at 8:54

Emre Kabaoglu

13.2k4 gold badges27 silver badges48 bronze badges

Collectives™ on Stack Overflow

Entity Framework execute stored procedure in a loop

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related