Click here to Skip to main content
15,075,492 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I am using datatable and it takes too long to add datatable. How can I edit the datatable so that it doesn't run slowly or what can I use instead of the datatable?

How can I edit this code works very slow?

What I have tried:

public System.Data.DataTable Excels()
{
var employeeInf=myentities.Employee.Select(x=>new {
   ID=x.ID,
   Name=x.Name,
   Surname=x.Surname,
   Branch=x.Branch
}).ToList();

var dataTable=new System.Data.DataTable();
dataTable.Columns.Add("ID");
dataTable.Columns.Add("Name");
dataTable.Columns.Add("Surname");
dataTable.Columns.Add("Branch");

int i=1;

foreach(var t in employeeInf9
{
   var newRow=dataTable.NewRow();
   newRow[ID]=t.ID;
   newRow[ID]=t.Name;
   newRow[ID]=t.Surname;
   newRow[ID]=t.Branch;

  dataTable.Rows.Add(newRow);  i++;

}
Posted
Updated 21-May-20 8:26am
v2

Here's a solution for Entity Framework 6, cobbled together from a couple of StackOverflow posts:
c# - Returning datatable using entity framework - Stack Overflow[^] - doesn't work with parameterized queries;
How can I get the parameters of an Entity Framework query? - Stack Overflow[^] - shows how to access the query parameters;
C#
using System;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;

namespace EntityFramework
{
    public static class DbContextExtensions
    {
        private static class ReflectionCache<T>
        {
            public static readonly PropertyInfo InternalQuery = typeof(DbQuery<T>).GetProperty("InternalQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
        }
        
        public static ObjectQuery<T> GetObjectQuery<T>(this IQueryable<T> source)
        {
            if (source is null) throw new ArgumentNullException(nameof(source));
            if (!(source is DbQuery<T> query)) throw new NotSupportedException();
            
            var internalQuery = ReflectionCache<T>.InternalQuery.GetValue(query, null);
            var objectQueryProperty = internalQuery.GetType().GetProperty("ObjectQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
            return (ObjectQuery<T>)objectQueryProperty.GetValue(internalQuery, null);
        }
        
        private static DbCommand ToDbCommand<T>(IQueryable<T> source, out DbProviderFactory dbFactory)
        {
            if (source is null) throw new ArgumentNullException(nameof(source));
            
            var objectQuery = source.GetObjectQuery();
            
            var connection = objectQuery.Context.Connection;
            if (connection is EntityConnection ec) connection = ec.StoreConnection;
            
            dbFactory = DbProviderFactories.GetFactory(connection);
            
            var command = dbFactory.CreateCommand();
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            command.CommandText = objectQuery.ToTraceString();
            
            foreach (var parameter in objectQuery.Parameters)
            {
                var dbParameter = command.CreateParameter();
                dbParameter.ParameterName = parameter.Name;
                dbParameter.Value = parameter.Value;
                command.Parameters.Add(dbParameter);
            }
            
            return command;
        }
        
        public static DbCommand ToDbCommand<T>(this IQueryable<T> source)
        {
            return ToDbCommand(source, out _);
        }
        
        public static DataTable ToDataTable<T>(this IQueryable<T> source)
        {
            if (source is null) throw new ArgumentNullException(nameof(source));
            
            using (var command = ToDbCommand(source, out var dbFactory))
            using (var adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = command;
                
                var dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
    }
}
With this helper class in place, your code becomes:
C#
public DataTable Excels()
{
    var employeeInf = myentities.Employee.Select(x => new
    {
        x.ID,
        x.Name,
        x.Surname,
        x.Branch
    }); // NB: No "ToList" call here.
    
    return employeeInf.ToDataTable();
}
   
Comments
Maciej Los 21-May-20 15:59pm
   
Nice. I have to bookmark this answer ;)
I'd suggest to use DataTableExtensions.CopyToDataTable Method (System.Data) | Microsoft Docs[^] with DataTable.LoadDataRow Method (System.Data) | Microsoft Docs[^]
See:
C#
using System.Data;

public DataTable EmployeesDT
{
    DataTable dt =new DataTable();
    dt.Columns.Add("ID");
    dt.Columns.Add("Name");
    dt.Columns.Add("Surname");
    dt.Columns.Add("Branch");


    dt = employeeInf=myentities.Employee
        .Select(x=> dt.LoadDataRow(new object[]{x.ID, x.Name, x.Surname, x.Branch}, false))
        .CopyToDataTable();
    //done!

    return dt;
}
   
Comments
Richard Deeming 21-May-20 13:44pm
   
I'm not convinced. :)

LoadDataRow creates a new DataRow and adds it to dt.

CopyToDataTable then creates a new DataTable instance, clones each row returned by the Select, and adds the cloned row to the new DataTable instance.

You then store the new DataTable in the dt variable, throwing away the old instance.

That's effectively loading the data twice, and temporarily using twice the memory.
Maciej Los 21-May-20 15:56pm
   
Well...
You can't load data into datatable without creating datatable previously. Neither without using CopyToDataTable() method.
;)
Richard Deeming 21-May-20 16:01pm
   
But you don't have to load it twice. :)
DataTable dt =new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("Surname");
dt.Columns.Add("Branch");

foreach (var item in myentities.Employee.Select(x => new { x.ID, x.Name, x.Surname, x.Branch }))
{
    dt.LoadDataRow(new object[] { item.ID, item.Name, item.Surname, item.Branch }, false);
}

return dt;
Maciej Los 21-May-20 16:15pm
   
OK. I got it!
Thanks, Richard.
Looks like you're updating the same column in your for loop. Without a "record count" no one can tell what is "slow". And "ToList" here is redundant. You can try specifying "no tracking".
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900