Load result of Linq-to-DataSet query with join into datatable

I have a Linq to dataset query that joins two tables and extracts the 开发者_StackOverflowdesired parameters from each. I need to get them into a DataTable to bind to a DataGridView. The example I found for doing this on MSDN is a trivial example taking a single value from a single table, but when I tried to change my query to follow it I was unable to do so. The CopyToDataTable() method requires that the query be assigned to a IEnumerable<DataRow>, but when I do so I'm told that an explicit cast is needed; but the cast fails at runtime with the exception:

Unable to cast object of type 'd__61`4[System.Data.DataRow,System.Data.DataRow,System.Int32,<>f__AnonymousType0`1[System.Int32]]' to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

Original working query:

var query = MyDataSet.Table1.AsEnumerable().Join(MyDataSet.Table2.AsEnumerable(),
    table1 => table1.Field<Int32>("Table1_Id"),
    table2 => table2.Field<Int32>("Table1_Id"),
    (table1, table2) => new
    {
        Table1ID = table1.Field<Int32>("Table1_Id")
        //Other parameters commented out to simplify the example
    });

Non-working query with explicit cast:

IEnumerable<DataRow> query = (IEnumerable<DataRow>)MyDataSet.Table1.AsEnumerable()
                                             .Join(MyDataSet.Table2.AsEnumerable(),
    table1 => table1.Field<Int32>("Table1_Id"),
    table2 => table2.Field<Int32>("Table1_Id"),
    (table1, table2) => new
    {
        Table1ID = table1.Field<Int32>("Table1_Id")
        //Other parameters commented out to simplify the example
    });

---

In both cases, you are creating a new 'anonymous type' to store the results.

To make the second one work, you would need something like:

var query = ... => new DataRow() 
{
});

except that is not going to work because DataRow has no public constructor and cannot be initialised this way.

So, use the first one and iterate over the results (note that I'm guessing a little here, and that you have to setup the columns for table3 first):

foreach (var row in query)
{
   var r = table3.NewRow();
   r["Table1ID"] = row.Table1ID;
   r["Table2ID"] = row.Table1ID;                
}

Edit:

 var query = ...;  // step 1

 query = query.ToList();  // add this,  step 2

 foreach(...) { }  // step 3

If you time the 3 steps above separately you will probably see that step 2 takes the most time.

---

Hi this is the other way to do it..

        //I have created datatable Address having AddressID<int32>,Name-srting,LastName-string
        DataSet ds= new DataSet();
        ds.Tables["Address"].Rows.Add(new object[] { 1, "Priya", "Patel" });
        ds.Tables["Address"].Rows.Add(new object[] { 2, "Bunty", "Rayapati" });
        ds.Tables["Address"].Rows.Add(new object[] { 3, "Birva", "Parikh" });
        //i have created Datatable AddressType having AddressTypeID int32 and State- string
        ds.Tables["AddressType"].Rows.Add(new object[] { 1, "Virginia" });
        ds.Tables["AddressType"].Rows.Add(new object[] { 2, "Nebraska" });
        ds.Tables["AddressType"].Rows.Add(new object[] { 3, "Philadeplhia" });

        DataTable dt1 = ds.Address.CopyToDataTable(); 
        DataTable dt2 = ds.AddressType.CopyToDataTable();
        DataTable dt3 = new DataTable();

        var query = dt1.AsEnumerable().Join(dt2.AsEnumerable(),
            dmt1 => dmt1.Field<Int32>("AddressID"),
                dmt2 => dmt2.Field<Int32>("AddressTypeID"),
        (dmt1, dmt2) => new 
        {
            Table1ID = dmt1.Field<Int32>("AddressID")
            //Other parameters commented out to simplify the example
        });
        query.ToList();
        //FullAddress is my third Datatable is having AID
        foreach (var row in query)
        {
            var r = ds.FullAddress.NewRow();
            r["AID"] = row.Table1ID;
            ds.FullAddress.Rows.Add(r.ItemArray);

        }

---

I was facing such error such error because after many joins statements in your LINQ query the compiler create new type that concatenate all your models

so so its hard to cast in directly

but it you know that the result contain only one specific Model you can do one more step to help the compiler to cast it which is

MyListOfTypeIEnumerable.ToArray()

take look at my problem and ToArray() solve it

public static IList<Letter> GetDepartmentLetters(int departmentId)
    {
        IEnumerable<Letter> allDepartmentLetters = from allLetter in LetterService.GetAllLetters()
            join allUser in UserService.GetAllUsers() on allLetter.EmployeeID equals allUser.ID into usersGroub
            from user in usersGroub.DefaultIfEmpty()
            join allDepartment in DepartmentService.GetAllDepartments() on user.DepartmentID equals allDepartment.ID
            where allDepartment.ID == departmentId
            select allLetter;
        return allDepartmentLetters.ToArray();
    }