Linq to Datatables

Datatable PMB(PostalCode,RID)
A1A2A3  000018
A1A2B3  000019
A1A2C3  000020
A1A2D3  000021


DATATABLE MOS(PostalCode,C_Grp,C_Type)
A1A2A3  P A16
A1A2A3  R A18
A1A2B3  F A06
A1A2C3  G B08
A1A2D3  K C09

I am using the following to get PMB Join MOS to get all the records int result DATATABLE Result(RID,C_Grp,C_Type)

var PMBQuery = ds1.Tables["PMB"].AsEnumerable();

var MosQuery = ds1.Tables["Mos"].AsEnumerable();

var query = (from m in MosQuery 

join p in PMBQuery  on m.Field<string>("PostalCode") equals p.Field<string>("PostalCode")

select new

{

RID=p.Field<string>("RID"),

F_Grp=m.Field<string>("C_GRP"),

F_Mos=m.Field<string>("C_MOS"),

 

}).ToList();

dgr3.DataSource = query;

Problem: I am just getting first matched record .
000018 P A16

I should get all the matched records

Any ideas?Am I missing something?


Thanks
Thiru

Try this way this is working perfect in my project.

DataClasses1DataContext dcx = new DataClasses1DataContext(connectionString);
        dcx.Log = Console.Out;

        var orders = from ord in dcx.Orders
             where ord.OrderDate.Value.Year == 1998
             orderby ord.CustomerID, ord.OrderDate
             select new {
               Order_ID = ord.OrderID,      // can just use ord.OrderID
               Customer_ID = ord.CustomerID,  // field name reassignment is optional
               Order_Date = ord.OrderDate  
             };

        this.dataGridView1.DataSource = orders.ToList();

I need a join two datatables and get the resulting datatable having columns from both input tables.
Thanks

You can join two or more table in query and get column which you need
this is working in my project

// Join 3 tables and show couple fields from each

        DataClasses1DataContext dcx = new DataClasses1DataContext(connectionString);

        var joined = from ord in dcx.Orders             // Inner Join
                join detail in dcx.Order_Details
                  on ord.OrderID equals detail.OrderID
                join product in dcx.Products
                  on detail.ProductID equals product.ProductID
          select new {
               ord.OrderID,
               ord.CustomerID,
               ord.OrderDate,
               detail.UnitPrice,
               detail.Quantity,
               product.ProductName
             };

        this.dataGridView1.DataSource = joined.ToList();

is anything wrong in my code that brings only First matched records.

BTW: I am using VS2010.  and Datatables are created from text and Csv files.

Hai


Instead Of using the Linq to Datatable U Can use the dataset Merge Concept Like this:
------------------------------------------------------------------------------------------------------------
For Eg tablename1,tablename2 Contains like this records:
----------------------------------------------------------------

TBLKEY EMPKEY EMPNAME  tablename1

----------- ------------------------------ ------------------------------

1       101     RAJA

2       105     POOJA

3       106     THRIU

 

EMPKEY EMPNAME        tablename2

------------------------------ ------------------------------

102     RAJA

103     KARTHIK

105      POOJA

109     ROJA

 

 

this.Page.Unload += new EventHandler(Page_Unload);

sconn.Open();

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter("SELECT * from tablename1", sconn);

da.Fill(ds);

 

 

da = new SqlDataAdapter("SELECT * from tablename2", sconn);

DataSet ds1 = new DataSet();

da.Fill(ds1);

//ds.Merge(ds1);

 

ds.Tables.Add(new DataTable());

ds1.Tables.Add(new DataTable());

 

// primary keys must be set in order for the merge to work

 

ds.Tables[0].PrimaryKey = new DataColumn[] { ds.Tables[0].Columns["EMPKEY"] };

ds1.Tables[0].PrimaryKey = new DataColumn[] { ds1.Tables[0].Columns["EMPKEY"] };

// this is the critical line

 

ds.Merge(ds1, true, MissingSchemaAction.Add);

output like this it will get in the ds after merge:
----------------------------------------------

Serial No	Key	Empid	Empname
1	1	101	RAJA
2		102	RAJA
3		103	KARTHIK
4	2	105	POOJA
5	3	106	THRIU
6		109	ROJA

Try This if its work give me reply.