locked
Fill DataTable Using Entity Framework using linq Query with all Possible way? RRS feed

  • Question

  • User1601010121 posted

    I want Code to be compact As this

    DataTable dt=DML.Gettable(Query);//This is static Function That returns DataTable

    But If i try this in entity framework using linq All one method i know is first Make Datatable then create Its column;

    DataTable dt=new DataTable();
    
    dt.Columns.Add();\\Create its columns

    Then

    EntityContext Obj=new EntityContext ();
    
    IEnumarable<DataRow> Query=IEnumarable<DataRow>(from variable in obj.tablename
    select new 
    { Column1=obj.col1,
    column2=obj.col2
    });
    
    foreach()
    {
    //Now loop through each row
    }

    Don't You Think this is Too many lines of code ?

    Isn't any short way to fill table? **Sometimes I have 50 to 60 Columns in DB table(Exceptional But yes) now if I want to fill a DataTable I have to create all columns by myself then fill it.**

    Then What's use of EF with Linq If my Code Is not short and compact

    Saturday, January 16, 2016 7:55 AM

Answers

  • User-271186128 posted

    Hi morfious90,

    You could refer to the following code to convert a List to a DataTable.

            public static DataTable ToDataTable<T>(List<T> items)
            {
                DataTable dataTable = new DataTable(typeof(T).Name);
    
                //Get all the properties
                PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
                foreach (PropertyInfo prop in Props)
                {
                    //Setting column names as Property names
                    dataTable.Columns.Add(prop.Name);
                }
                foreach (T item in items)
                {
                    var values = new object[Props.Length];
                    for (int i = 0; i < Props.Length; i++)
                    {
                        //inserting property values to datatable rows
                        values[i] = Props[i].GetValue(item, null);
                    }
                    dataTable.Rows.Add(values);
                }
                //put a breakpoint here and check datatable
                return dataTable;
            }

    LINQ statement:

                    List<Employee> list = (from qq in context.Employees
                                           select qq).ToList();
                    DataTable dt = ToDataTable<Employee>(list);
    
                    GridView2.DataSource = dt;
                    GridView2.DataBind();

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 18, 2016 2:24 AM