October 21, 2020 at 7:40 pm
I have a developer using Entity Framework and the metadata isn't returning for some stored procedures. Specifically ones that use Temp Tables. I've seen some EF articles about using SET FMTONLY OFF to get the metadata but I would prefer not to touch this.
Are there any examples/methods anyone has come across to resolve this issue?
Eventually I would like to replace temp tables in our code but it's not feasible at this time.
October 21, 2020 at 9:12 pm
Don't let your use of EF force you never to use temp tables, because sometimes temp tables are the best tool for the job!
For these procs, is there a final SELECT from a temp table and is that where the problem lies?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 21, 2020 at 11:25 pm
Yes and no.
There are procedures that use temporary tables and at the end throw out a string used by EF for another process.
Is there somewhere in EF that will tell you why it doesn't return any columns? I don't have access to it to see...
October 22, 2020 at 11:57 am
I don't know EF well enough to comment on that, unfortunately.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 22, 2020 at 12:26 pm
It's not EF, it's how SQL server handles procedures.
Metadata is retrieved during parsing time. Temporary tables are created and possibly modified in run time.
therefore, SQL Server has no means to tell if the temp table will exist at the time of the final SELECT and which columns will be present in that temporary table without executing the code of the procedure.
Try to use a permanent table, which contains temporary data sets.
_____________
Code for TallyGenerator
October 22, 2020 at 5:57 pm
This is just one of many good reasons to stop using frameworks of any kind. In the long run, all they do is get in the way of proper database usage and proper database design as well as good database performance. While they make it easier for a developer to make use of databases, it's probably cheaper to train your devs to properly code a good stored procedure and to rely on properly trained DBAs to design the databases. Frameworks only offer a false pretense - that working with a database is somehow easy. The answer to that is: ain't gonna happen in our lifetimes. The average web developer knows far too little about relational databases and thinks they know a heck of a lot more than they actually do. The prolific use of NOLOCK is testament to that.
Frameworks typically make it far more difficult to design a good trigger, because with every update, they actually update every single column in the table... every single time. That kind of thing is incredibly wasteful of resources. The same is true for SELECT and INSERT. There's no thought given by the framework to doing only what is needed. Stored procedures executed by a .Net database connection are a far better alternative.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply