which is better use data reader ado.net technology or entity framework core 7 ?

Question

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

Answer 1

It depends, lets look at three approaches.

Dapper

public static async Task<IEnumerable<Categories>> GetCategories()
{
    await using SqlConnection cn = new(ConnectionString());
    return await cn.QueryAsync<Categories>(SqlStatements.GetCategories);
}

EF Core

List<Categories> categories = await _context.Categories.ToListAsync();

ADO

var list = new List<Category>();

using var cn = new SqlConnection() { ConnectionString = ConnectionString };
var selectStatement = "SELECT CategoryID, CategoryName FROM dbo.Categories;";
using var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement };
cn.Open();
var reader = cmd.ExecuteReader();

while (reader.Read())
{
	list.Add(new Category() { Id = reader.GetInt32(0), Name = reader.GetString(1) });
}

return list;

For a novice I would recommend EF Core first, ADO second, Dapper third.

For a seasoned developer EF Core first, Dapper second, ADO third.

Why the difference in skill level? For the novice it may appear easy at first but once into joins it can be difficult but not so for the seasoned developer but could also be difficult for a intermediate developer depending on how well one learns.

Joins are done very differently with Dapper e.g. using splitOn.

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

	var list = cn.Query<Customers, Contacts,  Countries, Customers>(
		SQL.CustomerWithContacts1(), (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 EF Core joins are both with .Include, no mess, no fuss.

public async Task<List<Customers>> OrderByOnNavigation(string ordering)
{

	return await _context.Customers
		.Include(c => c.CountryIdentifierNavigation)
		.Include(c => c.Contact)
		.Include(c => c.ContactTypeIdentifierNavigation)
		.OrderByColumn(ordering)
		.ToListAsync();

}

Answer 2

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

It is not correct. The EF also opens and closes connection similarly to that of DataAdapter.Fill method.

I recommend that you use the "entity framework core 7" if you need to use new technologies such as the code first, scaffolding and others.

Answer 3

the library to use usually depends on the tradeoff's between raw performance and developer productivity. you have to make these calls.

• EF is high level ORM library that abstracts database access. Under the covers it uses ado.net connections, data readers, query support and connection pooling.
• Dapper is mini ORM library that extends ado.net sql command to have parameter and result mapping to objects.
• Ado.net is the low level library used by both.

the choice is also effected if you are code first or database first shop. EF works best with code first, while Dapper and Ado work best with database first. Also there is no issue in mixing all three in the same project. they will share the connection pool.

for instance if using EF, and you need to call a sp or execute a set operation with .FromSql(), you need to actually add a DbSet entity to the context to match the result set. You might want to use Dapper, where you could use any POCO object or even dynamic:

Answer 4

From the database side, we very much prefer if you use something where you actually write the database code, that is ADO .NET or Dapper. If you use things like EF or some other ORM, the DBA will complain over your queries, but you will not know what she or he is talking about and that person will have no clue when you show him/her the code.