DataTable with Linq

How do I create a DataTable with Linq var result ?
Im getting an error :
InvalidCastException was Unhandled.

---------------------
Unable to cast object of type 'System.Data.Linq.DataQuery`1[<>f__AnonymousTypee`13[System.Int64,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.String,System.Nullable`1[System.DateTime]]]' to type 'System.Data.DataTable'.
DataTable myTable = (DataTable)rsEmp;
 
 using (EmployeeDataContext empCtx = new EmployeeDataContext(GlobalMethods.SqlConnectionString))
            {
                var rsEmp = from emp in empCtx.Employees
                                          join payp in empCtx.PayPeriods on emp.PayperiodID equals payp.PayPeriodID
                                          join rst in empCtx.Rosters on emp.RosterID equals rst.RosterID
                                          join dep in empCtx.Departments on emp.DepartmentID equals dep.DepartmentID
                                          join div in empCtx.Divisions on emp.DivisionID equals div.DivisionID
                                          join cst in empCtx.CostCentres on emp.CostCentreID equals cst.CostCentreID
                                          join grp in empCtx.Groups on emp.GroupID equals grp.GroupID
                                          where emp.OrganizationID == UserClass.OrganizationID
                                          orderby emp.EmployeeNumber
                                          select new
                                          {
                                              emp.EmployeeID,
                                              emp.EmployeeNumber,
                                              emp.EmployeeName,
                                              emp.Surname,
                                              emp.BadgeNumber,
                                              payp.PayPeriodName,
                                              rst.RosterName,
                                              div.DivisionName,
                                              dep.DepartmentName,
                                              cst.CostCentreName,
                                              grp.GroupName,
                                              emp.EmploymentStatus,
                                              emp.EndOfEmployment
                                          };
                DataTable myTable = (DataTable)rsEmp;
                DataView filteredData = new DataView(myTable);
 
         
            }

Open in new window

u2envy1Asked:
Who is Participating?
 
DhaestConnect With a Mentor Commented:
Creating a DataTable From a Query (LINQ to DataSet)
http://msdn.microsoft.com/en-us/library/bb386921.aspx
0
 
DhaestCommented:
Actually, you have no need to return a dataTable from LINQ query. You can return a result as type of List<T>, IQueryable<T>, or IQueryable instead. All of them can be sorted and filtered. You can query the result again in BLL.

0
 
DhaestCommented:
While there are some good examples of how to do this out there, there is nothing built in. See the following link for a way to do this in your own code.

http://blogs.msdn.com/aconrad/archive/2007/09/07/science-project.aspx

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
naspinskiCommented:
Why do you want to put it in a datatable when you alreayd have it in a strongly typed IEnumerable?  It's like taking a step backwards...
0
 
u2envy1Author Commented:
It previously written without linq & we had to upgrade the code to use linq.
I would like to keep it how it was done & just convert it to linq.
Its doing a search on a DataView. I would have to put it in a DataTable then view.
It was done like this......

 DataTable myTable = (DataTable)dataGridView.DataSource;
            DataView filteredData = new DataView(myTable);
 
            filteredData.RowFilter = getFilterString();
 
            dataGridView.DataSource = filteredData;

Open in new window

0
 
u2envy1Author Commented:
I found this simple method.
Why am I getting an error on PropertyInfo[]

The type or namespace name 'PropertyInfo' could not be found (are you missing a using directive or an assembly reference?)
public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
{
     DataTable dtReturn = new DataTable();
 
     // column names 
     PropertyInfo[] oProps = null;
 
     if (varlist == null) return dtReturn;
 
     foreach (T rec in varlist)
     {
          // Use reflection to get property names, to create table, Only first time, others 
          will follow 
          if (oProps == null)
          {
               oProps = ((Type)rec.GetType()).GetProperties();
               foreach (PropertyInfo pi in oProps)
               {
                    Type colType = pi.PropertyType;
 
                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()      
                    ==typeof(Nullable<>)))
                     {
                         colType = colType.GetGenericArguments()[0];
                     }
 
                    dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
               }
          }
 
          DataRow dr = dtReturn.NewRow();
 
          foreach (PropertyInfo pi in oProps)
          {
               dr[pi.Name] = pi.GetValue(rec, null) == null ?DBNull.Value :pi.GetValue
               (rec,null);
          }
 
          dtReturn.Rows.Add(dr);
     }
     return dtReturn;
}

Open in new window

0
 
u2envy1Author Commented:
How do I get his to work ?
 using (EmployeeDataContext empCtx = new EmployeeDataContext(GlobalMethods.SqlConnectionString))
            {
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                var query = from emp in empCtx.Employees.AsEnumerable()
                            join payp in empCtx.PayPeriods on emp.PayperiodID equals payp.PayPeriodID
                            join rst in empCtx.Rosters on emp.RosterID equals rst.RosterID
                            join dep in empCtx.Departments on emp.DepartmentID equals dep.DepartmentID
                            join div in empCtx.Divisions on emp.DivisionID equals div.DivisionID
                            join cst in empCtx.CostCentres on emp.CostCentreID equals cst.CostCentreID
                            join grp in empCtx.Groups on emp.GroupID equals grp.GroupID
                            where emp.OrganizationID == UserClass.OrganizationID
                            orderby emp.EmployeeNumber
                            select new
                            {
                                EmployeeID = emp.Field<T>("EmployeeID"),
                                EmployeeNumber = c.Field<string>("EmployeeNumber"),
                                EmployeeName = c.Field<string>("EmployeeName"),
                                Surname = c.Field<string>("Surname"),
                                BadgeNumber = c.Field<string>("BadgeNumber"),
                                PayPeriodName = c.Field<string>("PayPeriodName"),
                                RosterName = c.Field<string>("RosterName"),
                                DivisionName = c.Field<string>("DivisionName"),
                                DepartmentName = c.Field<string>("DepartmentName"),
                                CostCentreName = c.Field<string>("CostCentreName"),
                                GroupName = c.Field<string>("GroupName"),
                                EmploymentStatus = c.Field<string>("EmploymentStatus"),
                                EndOfEmployment = c.Field<DateTime>("EndOfEmployment")
 
                            };
 
 
 
                dt = (DataTable)dataGridView.DataSource;
                DataView filteredData = new DataView(dt);
 
                filteredData.RowFilter = getFilterString();
 
                dataGridView.DataSource = filteredData;
 
            }

Open in new window

0
 
DhaestCommented:
Did you try:

dataGridView.DataSource = query.ToList();

http://www.hookedonlinq.com/ToListOperator.ashx
0
 
u2envy1Author Commented:
I get an error......
Cannot convert type 'System.Collections.Generic.List<AnonymousType#2>' to 'System.Data.DataTable'      
0
 
DhaestCommented:
Hmm... probably need a bindingcontext than

BindingSource bs = new BindingSource();
bs.DataSource = query;
DataGridView.DataSource = bs;
0
 
u2envy1Author Commented:
Error..........

Unable to cast object of type 'System.Windows.Forms.BindingSource' to type 'System.Data.DataTable'.
 BindingSource bs = new BindingSource();
                bs.DataSource = query;
 
                dataGridView.DataSource = bs;
                DataTable myTable = (DataTable)dataGridView.DataSource;
                               
                DataView filteredData = new DataView(myTable);

Open in new window

0
 
DhaestCommented:
Where did you get that error ?
0
 
DhaestCommented:
Another approach:
Creating a DataView Object (LINQ to DataSet)
http://msdn.microsoft.com/en-us/library/bb669080.aspx


DataView view = query.AsDataView();

Open in new window

0
 
u2envy1Author Commented:
Got error on DataTable myTable = (DataTable)dataGridView.DataSource;

They do not show how to build the DataSet. Cant find the FillDataSet() Method on Microsoft
http://msdn.microsoft.com/en-us/library/bb669080.aspx
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
0
 
DhaestCommented:
The question that still remains: why would you turn it back into a dataset ?

What part did work already ? Running linq and showing this result into your datagridview ? (if so, post the code that goes allright until now and then show us why and where you need it into a datatable)
0
 
u2envy1Author Commented:
We doing a dynamic search on the DataView.
code without linq
private void dataBindDataGridView()
        {
            try
            {
                //create a new dataset
                DataSet dataSet = new DataSet();
 
                SqlCommand selectCommand = new SqlCommand("proc_SelectEmployees", sqlConn);
                //SELECTion via a stored procedure
                selectCommand.CommandType = CommandType.StoredProcedure;
 
                SqlDataAdapter dataAdapter = new SqlDataAdapter();
                dataAdapter.SelectCommand = selectCommand;
                //populate the dataSet with the data adapter
                dataAdapter.Fill(dataSet, "Employees");
 
                //populate the table with the dataset
                DataTable empTable = dataSet.Tables["Employees"];
 
                //dataBind the dataTable to the dataGridView
                dataGridView.DataSource = empTable;
 
                dataGridView.Columns[13].Visible = false; //employee status - used to set background colour
                dataGridView.Columns[14].Visible = false; //employment end date
}
 
 //search based on user input
        private void search()
        {
            dataBindDataGridView();
 
            DataTable myTable = (DataTable)dataGridView.DataSource;
            DataView filteredData = new DataView(myTable);
 
            filteredData.RowFilter = getFilterString();
 
            dataGridView.DataSource = filteredData;
            dataGridView.ClearSelection(); 
            colourBackground();
        }
 
        private string getFilterString()
        {
            string myFilterString = "";
 
            if (txtName.Text != "")
            {
                if (myFilterString != "")
                {
                    myFilterString += "AND";
                }
                myFilterString += "([First Name] LIKE '%" + txtName.Text + "%')";
            }
            if (txtSurname.Text != "")
            {
                if (myFilterString != "")
                {
                    myFilterString += "AND";
                }
                myFilterString += "([Last Name] Like '%" + txtSurname.Text + "%')";
            }
            if (txtEmpNo.Text != "")
            {
                if (myFilterString != "")
                {
                    myFilterString += "AND";
                }
                myFilterString += "([Employee Number] LIKE '%" + txtEmpNo.Text + "%')";
            }
            if (txtBadgeNo.Text != "")
            {
                if (myFilterString != "")
                {
                    myFilterString += "AND";
                }
                myFilterString += "([Badge Number] LIKE '%" + txtBadgeNo.Text + "%')";
            }
 
            //
            // Div, Dpt, Grp, Css
            //
 
            if ((cmbDivision.SelectedValue.ToString() != "All") || (cmbDepartment.SelectedValue.ToString() != "All") || (cmbGroup.SelectedValue.ToString() != "All") || (cmbCostCentre.SelectedValue.ToString() != "All"))
            {
                
 
                if (cmbDivision.SelectedValue.ToString() != "All")
                {
                    if (myFilterString != "")
                    {
                        myFilterString += "AND";
                    }
                    myFilterString += "([Division] = '" + cmbDivision.SelectedValue.ToString() + "')";
                }
                if (cmbDepartment.SelectedValue.ToString() != "All")
                {
                    if (myFilterString != "")
                    {
                        myFilterString += "AND";
                    }
                    myFilterString += "([Department] = '" + cmbDepartment.SelectedValue.ToString() + "')";
                }
                if (cmbGroup.SelectedValue.ToString() != "All")
                {
                    if (myFilterString != "")
                    {
                        myFilterString += "AND";
                    }
                    myFilterString += "([Group] = '" + cmbGroup.SelectedValue.ToString() + "')";
                }
                if (cmbCostCentre.SelectedValue.ToString() != "All")
                {
                    if (myFilterString != "")
                    {
                        myFilterString += "AND";
                    }
                    myFilterString += "([Cost Center] = '" + cmbCostCentre.SelectedValue.ToString() + "')";
                }
            }
            messageBar.Message = myFilterString;
            return myFilterString;
        }

Open in new window

0
 
u2envy1Author Commented:
Does this make sense to you ?
Should I explain more ?
0
 
u2envy1Author Commented:
The DataSet is filled with ADO.net. I need all Code to be in Linq.
Is it so hard to do this ?
 using (EmployeeDataContext empCtx = new EmployeeDataContext(GlobalMethods.SqlConnectionString))
            {
                var rsEmp = from emp in empCtx.Employees
                                          join payp in empCtx.PayPeriods on emp.PayperiodID equals payp.PayPeriodID
                                          join rst in empCtx.Rosters on emp.RosterID equals rst.RosterID
                                          join dep in empCtx.Departments on emp.DepartmentID equals dep.DepartmentID
                                          join div in empCtx.Divisions on emp.DivisionID equals div.DivisionID
                                          join cst in empCtx.CostCentres on emp.CostCentreID equals cst.CostCentreID
                                          join grp in empCtx.Groups on emp.GroupID equals grp.GroupID
                                          where emp.OrganizationID == UserClass.OrganizationID
                                          orderby emp.EmployeeNumber
                                          select new
                                          {
                                              emp.EmployeeID,
                                              emp.EmployeeNumber,
                                              emp.EmployeeName,
                                              emp.Surname,
                                              emp.BadgeNumber,
                                              payp.PayPeriodName,
                                              rst.RosterName,
                                              div.DivisionName,
                                              dep.DepartmentName,
                                              cst.CostCentreName,
                                              grp.GroupName,
                                              emp.EmploymentStatus,
                                              emp.EndOfEmployment
                                          };
                DataTable myTable = (DataTable)rsEmp;
                DataView filteredData = new DataView(myTable);
 
         
            }

Open in new window

0
 
u2envy1Author Commented:
Thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.