Question which better using entity framework core or ado.net data reader ?

[ahmedaziz]

I work on blazor application server side I design web API to interact with razor pages

my issue is which is suitable for my scenario

using ado.net or entity framework core

what I try as below :

 [HttpGet]
        [Route("GetAllServersDetails")]
        public IActionResult GetAllServersDetails()
        {
            string query = "";


            query = "select s.ServerID, s.Server_Name as ServerName,isnull(s.ServerIp,'') as ServerIp,s.ServerTypeId,isnull(st.ServerType,'') as ServerType,s.OsTypeId,isnull(os.DetailsName,'')  as OsType,s.HostedZoneId,isnull(hostedzone.DetailsName,'') as HostedZone,s.ServerityId,isnull(serverity.DetailsName,'') as Serverity,s.HostedTypeId,isnull(hostedType.DetailsName,'') as HostedType,isnull(s.ServerRoleId,0) as ServerRoleId,isnull(serverrole.DetailsName,'') as ServerRole,isnull(s.DRRequired,0) as DRRequiredID,isnull(drrequired.DetailsName,'') as DRRequired,isnull(s.Remarks,'') as Remarks,isnull(s.OwnerFileNo,'') as ownerfilenumber, s.IsActive from [dbo].[ServerNames] s with(nolock)
inner join [dbo].[ServerTypes] st with(nolock) on st.ServerTypeId=s.ServerTypeId
left join  Details os with(nolock) on os.ID=s.OsTypeId and os.HeaderId=12
left join  Details hostedzone with(nolock) on hostedzone.ID=s.HostedZoneId and hostedzone.HeaderId=13
left join  Details serverity with(nolock) on serverity.ID=s.ServerityId and serverity.HeaderId=14
left join  Details hostedType with(nolock) on hostedType.ID=s.HostedTypeId and hostedType.HeaderId=15
left join  Details serverrole with(nolock) on serverrole.ID=s.ServerRoleId and serverrole.HeaderId=16
left join  Details drrequired with(nolock) on drrequired.ID=s.DRRequired and drrequired.HeaderId=17";


            try
            {
                ICollection<object> ApplicationsDataValues = new List<object>();
                using (var command = _context.Database.GetDbConnection().CreateCommand())
                {
                    command.CommandText = query;
                    command.CommandType = CommandType.Text;

                    _context.Database.OpenConnection();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ApplicationsDataValues.Add(new
                            {
                                ServerID = reader.GetFieldValue<Int32>(0),
                                ServerName = reader.GetFieldValue<string>(1).ToString(),
                                ServerIp = reader.GetFieldValue<string>(2).ToString(),
                                ServerTypeId = reader.GetFieldValue<Int32>(3),
                                
                                ServerType = reader.GetFieldValue<string>(4).ToString(),
                                OsTypeId = reader.GetFieldValue<Int32>(5),
                                
                                OsType = reader.GetFieldValue<string>(6).ToString(),
                                HostedZoneId = reader.GetFieldValue<Int32>(7),
                                
                                HostedZone = reader.GetFieldValue<string>(8).ToString(),

                                ServerityId = reader.GetFieldValue<Int32>(9),
                                Serverity = reader.GetFieldValue<string>(10).ToString(),


                                HostedTypeId = reader.GetFieldValue<Int32>(11),
                                HostedType = reader.GetFieldValue<string>(12).ToString(),

                                ServerRoleId = reader.GetFieldValue<Int32>(13),
                                ServerRole = reader.GetFieldValue<string>(14).ToString(),

                                DRRequiredID = reader.GetFieldValue<string>(15).ToString(),
                                DRRequired = reader.GetFieldValue<string>(16).ToString(),
                                
                                Remarks = reader.GetFieldValue<string>(17).ToString(),
                                ownerfilenumber = reader.GetFieldValue<string>(18).ToString(),
                                
                                IsActive = reader.GetFieldValue<bool>(19)//.ToString()


                            });
                        }
                    }
                }
                return StatusCode(200, ApplicationsDataValues); // Get all users   
            }
            catch (Exception e)
            {
                return StatusCode(500, e);
            }
        
        }

so are using data reader ado.net is best or using entity framework core

some developer tell me why use ado.net it is old technology and this is not good because it open and close connection with every load page and entity framework not do that

are this is correct

if that better or using entity framework core

I depend on code first technology

Result of statement above is 10000 rows as maximum

 

[jmcilhinney]

Either will work so either is suitable for your scenario. EF and other ORMs use ADO.NET under the hood anyway, so you're never avoiding ADO.NET. When .NET was new, everyone used ADO.NET because that's all there was. Then people started porting existing ORMs from Java and the like to .NET and some new ones were created too. It's completely up to you whether you use ADO.NET directly or EF or some other ORM. Personally, I use EF. It makes most data access operations easier and more intuitive than using ADO.NET directly and, for the curly situations, it allows you to fall back to raw SQL or the like.

 

[jmcilhinney]

some developer tell me why use ado.net it is old technology and this is not good because it open and close connection with every load page and entity framework not do that

are this is correct

Like I said, EF and other ORMs use ADO.NET under the hood anyway so, whatever ADO.NET does, an ORM does too. Whoever told you that doesn't really know what they're talking about. It is true that, if you're using ADO.NET properly, you will be creating, opening, using and closing/disposing a connection object each time you access the database. That's fine though. ADO.NET connections are light-weight objects. The actual database connection exists at a lower level and it does stay open for some time after it's first used in case it needs to be used again. That's why connection pooling exists. Given that EF and other ORMs use ADO.NET under the hood, they are going to treat connections in the same way, i.e. high-level connections get opened and closed as required but low-level connections stay open for some time so frequent database access will not require them to be opened every time.

 

[cjard]

it open and close connection with every load page

I think you need to stop listening to those people.

Unless specifically configured otherwise, calling Open() and Close() doesn't open or close anything. .NET chooses how many connections to hold open to the database simultaneously, and itmanages them invisibly. When you call Open, you borrow an already open connection. When you call Close, you give it back to .NET ready to be given out on the next Open call later

If you want to read more about this, google "connection pooling"

---

As to which is better, in 99% of the regular programming scenarios, EF Core is better but you must always bear in mind that, like nearly everything else in any programming language, all it does is build on top of things that already exist in order to make your life easier. It's possible to use it otmake a real mess, very quickly, if you dont understand what it does.

It does these things:

* Manages database connections, and opens and closes them
* Takes LINQ code that you write and turns it into SQL
* Downloads results and uses standard or custom converters to turn the result data into objects
* Watches objects it has created, for changes you make so it knows what SQL to execute to save the changes back to the DB

These are all things you do yourself when you work with ADO; you open the conenctions, you write the SQL, you copy the data into objects and you save it back. It's incredibly tedious and repetitive, and that's why someone decided "I will hand this tedious repetitive task to the computer, because that's what computers do best".

So you see, it's not a case of "X is better than Y"; they're the same thing, just different levels of automation

Nearly everything you ever use in computing, is a result of someone deciding to make a tedious, boring task easier and faster.. You use newtonsoft to parse json. You use HTTP to communicate with a remote computer. You use Unity to do 3D. Whatever you can think of, is just a convenience layer on top of another convenience layer. Every time something becomes obviously inconvenient, it is opportunity to change. Unless you really did one day sit down and design and build your own CPU, and hand assemble your own drivers and operating system, and all that goes on top of it.

 

[kareninstructor]

Considerations, with ADO, in your current code, if column names change in the database table(s) you need to manually update your code while with EF Core using EF Power tools simply run the reverse engineer process again and all code is updated. Also, everything is strongly typed. As indicated by current replies, under the hood ADO is used for EF Core. It can be said that behind the covers there is a lot of code, that may be true but when placing this code into a class project the code is reusable in one or many projects. Then there is a hybrid approach using ADO/SqlClient namespace classes together with Dapper which works off a connection, the downside for Dapper IMHO is how joins are done and is only a downside as there are not many code examples showing much with joins as per below for example but as with EF Core data is returned strongly typed.

public static async Task<List<Customers>> CustomersWithContacts1()
{
    await using SqlConnection cn = new(ConnectionString());

    var list = cn.Query<Customers, Contacts,  Countries, Customers>(
        SQL.CustomerWithContacts(), (customers,contacts,  country) =>
    {
        customers.Contact = contacts;
        customers.ContactId = contacts.ContactId;
        customers.CountryIdentifier = country.CountryIdentifier;
        customers.CountryIdentifierNavigation = country;
        return customers;

    }, splitOn: "ContactId,CountryIdentifier");

    return list.ToList();
}

With Dapper a developer still needs to pass a SQL statement which means like with ADO if table column names change so does the SQL statement used for Dapper.

My advice is to spend time with all options outside of a project to understand the strengths and weaknesses when making a decision which approach to use as using one may be a wrong path for a specific task or project.

None are outdated yet Microsoft flagship is EF Core currently and when looking at any of ASP.NET Core code samples from Microsoft EF Core is used.

Some useful information

• EF Core reverse engineering in depth docs.
• EF Core queries.
• EF Core change tracking docs.
• Case study, Dapper vs EF Core.
• EF Core Json columns article.

 

[cjard]

while with EF Core using EF Power tools simply run the reverse engineer process again and all code is updated

Just wanted to mention that, in my experience at least, it isn't the case that re-Reversing a database will also perform a refactor/rename of any properties bound to columns that have been renamed. The re-reverse will erase the old context and model files and put new ones, but then all the code references for the old name will gain a wiggly red line. The simplest solve I know of, given that non existent members cannot be renamed, is to either refactor/rename before you reverse or (if that boat has sailed) temporarily add another prop with the old name (resolving all the references to it) then rename the prop with the old name so it has the new name, then remove the now-duplicate prop

All in though it's a worthy point that, because EFCore is code-based rather than string based, refactoring tools can be used to greater effect. It certainly helps develop faster, because one can only find out about typos in SQL statement strings at runtime, whereas intellisense and compile time checking effectively preclude any opportunity to make a typo in functioning C# code

 

[cjard]

select s.ServerID, s.Server_Name as ServerName,isnull(s.ServerIp,'') as ServerIp,s.ServerTypeId,isnull(st.ServerType,'') as ServerType,s.OsTypeId,isnull(os.DetailsName,'') as OsType,s.HostedZoneId,isnull(hostedzone.DetailsName,'') as HostedZone,s.ServerityId,isnull(serverity.DetailsName,'') as Serverity,s.HostedTypeId,isnull(hostedType.DetailsName,'') as HostedType,isnull(s.ServerRoleId,0) as ServerRoleId,isnull(serverrole.DetailsName,'') as ServerRole,isnull(s.DRRequired,0) as DRRequiredID,isnull(drrequired.DetailsName,'') as DRRequired,isnull(s.Remarks,'') as Remarks,isnull(s.OwnerFileNo,'') as ownerfilenumber, s.IsActive from
  [dbo].[ServerNames] s with(nolock) 
  inner join [dbo].[ServerTypes] st with(nolock) on st.ServerTypeId=s.ServerTypeId 
  left join Details os with(nolock) on os.ID=s.OsTypeId and os.HeaderId=12 
  left join Details hostedzone with(nolock) on hostedzone.ID=s.HostedZoneId and hostedzone.HeaderId=13 
  left join Details serverity with(nolock) on serverity.ID=s.ServerityId and serverity.HeaderId=14 
  left join Details hostedType with(nolock) on hostedType.ID=s.HostedTypeId and hostedType.HeaderId=15 
  left join Details serverrole with(nolock) on serverrole.ID=s.ServerRoleId and serverrole.HeaderId=16 
  left join Details drrequired with(nolock) on drrequired.ID=s.DRRequired and drrequired.HeaderId=17

Your database seems over-normalised but for a query like this I'd either ask EFCore to download a select range of IDs to a dictionary and map it myself, or I'd turn this query into a view and connect EFCore to that

Perhaps also read up more on WITH(NOLOCK) before you blat it everywhere

 

[kareninstructor]

Just wanted to mention that, in my experience at least, it isn't the case that re-Reversing a database will also perform a refactor/rename of any properties bound to columns that have been renamed. The re-reverse will erase the old context and model files and put new ones, but then all the code references for the old name will gain a wiggly red line. The simplest solve I know of, given that non existent members cannot be renamed, is to either refactor/rename before you reverse or (if that boat has sailed) temporarily add another prop with the old name (resolving all the references to it) then rename the prop with the old name so it has the new name, then remove the now-duplicate prop

All in though it's a worthy point that, because EFCore is code-based rather than string based, refactoring tools can be used to greater effect. It certainly helps develop faster, because one can only find out about typos in SQL statement strings at runtime, whereas intellisense and compile time checking effectively preclude any opportunity to make a typo in functioning C# code

True that there is a erasure issue yet some of this can be handled with partial classes for EF Core or if the changes are small enough and working with dependency injection the changes in a DbContext should not matter as the connection aspect would be (for ASP.NET Core for instance) in Program.cs and with proper planning think that the DbContext name would not change. No matter, your point is correct, there is always the chance of something get messed up.

I tend to manually make changes, been doing it long enough that its easy with model configurations abstracted out of the DbContext but did not want to bring this up in my original reply for someone just starting with EF Core.