Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C LINQ SQL Server Entities
Hello,
 
I am trying to write a query using Linq to Entities that should return a DataTable.
 
Following is my Entity query .
 
 
 DataTable MembersDT = new DataTable();
            MembersDT.Columns.Add("PatientAccountNumber", typeof(string));
            MembersDT.Columns.Add("PatientName", typeof(string));
            MembersDT.Columns.Add("BirthDate", typeof(string));
 
var query = (from p in billEntity.DataAccounts
                         where p.Duplicate == false
                         select new
                         {
                             ChartNumber = p.PatientAccountNumber,
                             Name = p.PatientName,
                         }).Take(1000);
 
I searched web and found that there a method called CopyToDataTable(). but i am not getting idea how to use it. The following code is posted at the following link in MSDN.
 
http://msdn.microsoft.com/en-us/library/bb386921.aspx[^]
 
Code is as follows which is similar to my requirement.
 
DataTable table = new DataTable();
DataColumn dc = table.Columns.Add("NewId", typeof(int));
dc.AutoIncrement = true;
table.Columns.Add("ExtraColumn", typeof(string));
 
var query = from i in items
             where i.Price > 9.99
             orderby i.Price
             select new { i.Price, i.Genre };
 
query.CopyToDataTable(table, LoadOption.PreserveChanges);
 
Kindly suggest.
 
Chowdary.
Posted 24-Sep-12 22:14pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
When I first saw your question I was a little confused about the combination of DataTables and Entity Framework (which uses LINQ to Entities to query it). They are sort of two different worlds.
 
DataSet/DataTables and Entity Framework are different techniques to retrieve data from a database. You might want to search CodeProject for the difference or Google it. The Entity Framework can be queried using LINQ to Entity. DataSets/DataTable were introduced in .NET 1.0. Which is about 7 years prior to the introduction of LINQ. However Microsoft build a LINQ to DataSet to query datasets the 'LINQ way'. You might want to read this article on Wikipedia about LINQ[^].
 
You will discover that they look the same but are different as well. The article you mentioned[^] is only about LINQ to DataSet. The statement can't be used to copy data from a LINQ query to a DataTable.
 
There might be reasons why you want to use LINQ to Entity to query a database and transfer the data to a DataTable. Maybe you use DataSets in your interface and don't want to change that. If you are building from scratch you might want to reconsider doing this.
 
Back to the real question, how to convert a LINQ to Entity result to a DataTable. In the example below I have made a little adjustment to the DataTable to match the columns in the LINQ to Entity query:
    DataTable table = new DataTable();
    DataColumn dc = table.Columns.Add("NewId", typeof(int));
    dc.AutoIncrement = true;
    table.Columns.Add("Price", typeof(double));
    table.Columns.Add("Genre", typeof(string));
 
    using (TestEntities context = new TestEntities())
    {
        var query2 = from i in context.Items
                     where i.Price > 9.99
                     orderby i.Price
                      select new { i.Price, i.Genre };
    }
I have added the TestEntities context = new TestEntities() because this is the way to define your workspace (comparable to the DataSet). The TestEntities model is connecting to a SQL Server database with a 'Item' table (containing a ID primary key, a Price and a Genre column).
In the query you see a context.Items. This is a ObjectSet and is comparable to the DataTable.
 
I'll show you 3 methods to solve this.
1) Using lambda expressions to add the item to the table
2) Using lambda but with a support method
3) Using a partial class Item
 

The first solution uses a lambda to solve it. If you haven't used any lambda expressions you might want to look at this article about Lambda expressions[^]. Or you could have a look at the other solutions (but you will meet them when you are using LINQ). The following code converts the query to a List (using ToList()). A List has the LINQ method ForEach (LINQ equivalent of the foreach statement) and loops through the results. The Lambda creates a method to add the results to the datatable.
        private void Method1
        {
            DataTable table = new DataTable();
            DataColumn dc = table.Columns.Add("NewId", typeof(int));
            dc.AutoIncrement = true;
            table.Columns.Add("Price", typeof(double));
            table.Columns.Add("Genre", typeof(string));
 
            using (TestEntities context = new TestEntities())
            {
                var query2 = from i in context.Items
                             where i.Price > 9.99
                             orderby i.Price
                             select new { i.Price, i.Genre };
 
                query2.ToList().ForEach((n) =>
                {
                    DataRow row = table.NewRow();
 
                    row.SetField<double>("Price", n.Price);
                    row.SetField<string>("Genre", n.Genre);
 
                    table.Rows.Add(row);
                });
 
            }
        }
 
You could use a conventional foreach loop and a submethod. The select new { i.Price, i.Genre} is a so called anonymous type. Anonymous types have the advantage of being a dynamically created type (no formal class definition needs to exists), but the disadvantage is that there are not know outside the scope of the method. So in the next example i use select i to return the whole Item object (ofcourse you don't need the sub method. If you place the adding inside the foreach then you could still use the anonymous type).
        private void Method2()
        {
            DataTable table = new DataTable();
            DataColumn dc = table.Columns.Add("NewId", typeof(int));
            dc.AutoIncrement = true;
            table.Columns.Add("Price", typeof(double));
            table.Columns.Add("Genre", typeof(string));
 
            using (TestEntities context = new TestEntities())
            {
                var query2 = from i in context.Items
                            where i.Price > 9.99
                            orderby i.Price
                            select i;
 
                foreach (var n in query2)
                {
                    AddItemToDataTable(table, n);
                }
           }
         }
 
        private void AddItemToDataTable(DataTable table, Item item)
        {
            DataRow row = table.NewRow();
 
            row.SetField<double>("Price", item.Price);
            row.SetField<string>("Genre", item.Genre);
 
            table.Rows.Add(row);
        }
 

A third possible solution is extending the Item object using a partial class (see: http://msdn.microsoft.com/en-us/library/wa80x488(v=vs.80).aspx[^]). This is a clean way to solve this, but does expected certain columns in your DataTable. The Entity Framework generate its entity classes as partial. Thus giving you the opertunity to expand them. This is done rather easy, like this (make sure the namespace is exactly the same as the namespace of your Entity Framework model):
    public partial class Item
    {
        public void FillDataTable(DataTable table)
        {
            DataRow row = table.NewRow();
 
            row.SetField<double>("Price", this.Price);
            row.SetField<string>("Genre", this.Genre);
 
            table.Rows.Add(row);
        }
    }

Calling this method is quite simple now. You do need the non-anonymous select i version as the anonymous version won't have a clue where to find the partial class.
        private void Method3()
        {
            DataTable table = new DataTable();
            DataColumn dc = table.Columns.Add("NewId", typeof(int));
            dc.AutoIncrement = true;
            table.Columns.Add("Price", typeof(double));
            table.Columns.Add("Genre", typeof(string));
 
            using (TestEntities context = new TestEntities())
            {
                var query2 = from i in context.Items
                             where i.Price > 9.99
                             orderby i.Price
                             select i;
 
                query2.ToList().ForEach(n => n.FillDataTable(table));
            }
        }
 
It has become quite a long answer. Though DataTables and Entities aren't the same and it is not possible to integrate them with one line of code, it is possible. I hope this is what you were looking for.
 
Regards
 
Martijn
  Permalink  
Comments
chowdary2005 at 26-Sep-12 0:13am
   
Appreciate your patience for typing this much of your story. Really thank ful for your support. Thanks a lot.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

 DataTable objdtOcc = (DataTable)Session["eUW_SessionManager"];
 var result = from str in objdtOcc.AsEnumerable() where str.Field<string>("Description").ToUpper().StartsWith(e.Text.ToUpper()) select str;
if (result.Count() > 0)
              {
                  DataTable resultTbl = result.CopyToDataTable();</string>
}

Hope this help u
Thanks
  Permalink  

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



Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 27 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100