Last post Mar 12, 2018 07:55 AM by DA924
Mar 12, 2018 06:11 AM|akhilrajau|LINK
I am developing a MVC dashboard application with SQL Server. I used Entity Framework Database First concept to get my records.
There is an application tool to do all CRUD operations. I need to use there database to populate some reports including charts. This tool uses multiple databases including MySQL and SQL. Connecting to this databases takes so much time mostly around 2-3 minutes.
They give read only permission all databases. I decided to create local sql database specifically used for this dashboard application only. This will separate production and also avoid lock issues and connection issues. I created separate services to import
data from production to my staging database and process the data from staging to my dashboard database.
I am using only one database and import needed tables in to this database so that i can use one context in my application.
I used database first approach to created model from database and working fine. Now i am little confused that in the following;
I am using only read not other operations (Edit, Update and Delete). So if i directly use ado.net with stored procedures, any performance difference?
I am thinking like EF will store database in the memory and it less performance that ado.net. But will get advantage of linq to sql.
Please help to decide this. EF is fine or ado.net is better in this case?
I think we need create view models manually and map from the stored procedures.
Mar 12, 2018 07:55 AM|DA924|LINK
They give read only permission all databases. I decided to create local sql database specifically used for this dashboard application only. This will separate production and also avoid lock issues and connection issues. I created separate services
to import data from production to my staging database and process the data from staging to my dashboard database.
One should not be having lock issues and connection issues. The only way there would be such issues is if one doesn't know how to do database access using an ORM like ADI.NET Entity Framework or just plain old ADO.NET with SQL Command objects with inline
T-SQL or sprocs and using either one effectively.
There is no advantage here with you making stored procedures, because ADO.NET EF's database engine uses the internal MS SQL Server stored procedure to submit generated T-SQL to the database engine for execution.
I am thinking like EF will store database in the memory and it less performance that ado.net. But will get advantage of linq to sql
Yes, EF will use Linq to return materialized objects in a List<T> from the database query that was done by the EF engine generating T-SQL that is submitted to the MS SQL Server database engine via the MS SQL Server internal stored procedure for execution
with the results retuned to the program as a collection of objects in a List<T>.
Then Linq-2-Objects can be used on the List<T> of objects in memory that has nothing to do ADO.NET or ADO.NET EF at that point. However, the following is true in an EF situation if the list of objects are disconnected from the EF connection, because otherwise,
if one does a Linq query using EF that retrieves 100, 000 records/objects and then goes into a loop on the 100, 000 objects in the list while the EF connection is not closed leaving the objects in a connected state in the list, then that's 100,000 reads again
as EF goes back to the database to read each object from the database as it iterates objects in the loop. That's 200,000 reads from the database when there should have been only 100, 000 reads from the database.
What's the purpose of using ADO.NET Entity Framework IMO if all one is doing in running sprocs from EF that one made. If one uses ADO.NET with SQL Command objects and sprocs, then one still has to put the results in a container like a List<T> that EF is
doing or one uses a datatable.
Where is the advantage of one doing it manually as opposed to EF doing it in this situation?
There is nothing wrong in using stored procedures for specific business needs, but to use them for simple CRUD operations with a database when EF can do it with no trouble, I'll go EF every time. :)