Hi,
I've recently "inherited" a part built project from a developer who as left our company.

He had decided to use Entity Framework but I am finding it to be unstable and slow. I periodically have to reimport objects and stored procedures from the database as the model gets corrupted. I also find database processes to be slower than if I was just running stored procedures and ADO.net

The latest fresh hell it has started to pull is an error New transaction is not allowed because there are other threads running in the session.

I've looked on line and the answer seams to be to do with running updates while reading through data in a loop. Except the code that is throwing the error is not in any loop although it is calling a stored procedure that runs an update.

It could be the Framework or it could be how it has been implemented by my predecessor ( his idea of OOP and N tier left something to be desired,) the issue is it's a big project and I'm not sure the powers that be would be happy if I started again.

So I'm wondering if there is a way to just take the entity part out and build an old fashioned data layer with views and stored procedures... ( and optimised SQL in those proceedures)

I don't know, I have been told by some developers that Entity Framework is one of the "new things" in database development only recently (looking up some info on google, it originally came out 7 years ago, but is still under active development). I got some books on the subject, still need to read them, are you sure removing it is a good idea? If it is in fact a troublesome technology I would like to hear some more opinions of experienced developers.

It could be the Framework or it could be how it has been implemented by my predecessor

It's the latter, almost surely. The Entity Framework most certainly has its quirks, but being unstable and slow are not among them when used properly.

So I'm wondering if there is a way to just take the entity part out and build an old fashioned data layer with views and stored procedures...

It rather depends on the design, and I suspect the design is fairly entrenched in EF weirdness. My personal preference is to use more of an ADO.NET approach with EF to facilitate database compatibility, but that's not really at issue here. I suspect you're stuck optimizing a poor design. Sorry.

Hi all,
Still none the wiser as to what is going on here. The traces I run in SQL Profiler while the code is running don't give me any idea why the error occurs.

I think I'll start to phase out the entity stuff and do it the old fashioned way.. I can code in SQL so I don't need the layer of abstraction.

Hi all,
After drilling through the SQL procedure that was written, it turns out my predecessor was also really bad at writing SQL procedures!!

He seamed to have a pathalogical fear of SQL cursors and not be comfortable with variables either.

A table was being queried (with fields that were not needed,) and the results put into a temp table. Then a select Count statement was run on the temp table to find out the number of rows in the temp table and then a loop was run until the counter went down to zero, each time querying the temp table for the top 1 unprocessed row and then marking it as processed afterwards. During the loop multiple and sometimes unnecessary queries were run to manipulate the data. All to basically port data from one stage in the process to the next.

I rewrote using a cursor and loop with a query that combined several of his queries to bring back only necessary data that were stored in variables. I only needed to run one other query in the loop and perform a single calculation I also added error handling in the form of a Transaction with rollback and commit calls.

I still have instability problems with the entity but I really think I've inherited an awful mess. The really annoying thing is this guy was supposed to be senior to me and on way more money. This was the second of his projects I've looked at and I think I know why he has been let go!

Good desision! EF - sucks as almost all of the "modern" "state of art" sheety solutions :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.