?
Solved

DataTable with Linq

Posted on 2009-02-19
19
Medium Priority
?
4,663 Views
Last Modified: 2013-11-11
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

0
Comment
Question by:u2envy1
  • 10
  • 8
19 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 23680256
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 23680261
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
 
LVL 21

Expert Comment

by:naspinski
ID: 23680282
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:u2envy1
ID: 23680331
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
 

Author Comment

by:u2envy1
ID: 23680386
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
 

Author Comment

by:u2envy1
ID: 23681165
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 23681188
Did you try:

dataGridView.DataSource = query.ToList();

http://www.hookedonlinq.com/ToListOperator.ashx
0
 

Author Comment

by:u2envy1
ID: 23681350
I get an error......
Cannot convert type 'System.Collections.Generic.List<AnonymousType#2>' to 'System.Data.DataTable'      
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 23681458
Hmm... probably need a bindingcontext than

BindingSource bs = new BindingSource();
bs.DataSource = query;
DataGridView.DataSource = bs;
0
 

Author Comment

by:u2envy1
ID: 23681552
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 23681559
Where did you get that error ?
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 23681595
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
 

Author Comment

by:u2envy1
ID: 23681726
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
 
LVL 53

Expert Comment

by:Dhaest
ID: 23681751
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
 

Author Comment

by:u2envy1
ID: 23681924
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
 

Author Comment

by:u2envy1
ID: 23689328
Does this make sense to you ?
Should I explain more ?
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 2000 total points
ID: 23689772
Creating a DataTable From a Query (LINQ to DataSet)
http://msdn.microsoft.com/en-us/library/bb386921.aspx
0
 

Author Comment

by:u2envy1
ID: 23689911
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
 

Author Closing Comment

by:u2envy1
ID: 31548713
Thx
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Integration Management Part 2
Loops Section Overview
Suggested Courses
Course of the Month14 days, 21 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question