INNER JOIN Equivalent of SQL in ADO.NET

Question

I have data loaded in 2 of my typed datatables

I am trying to get results from it and fill it in another untyped datatable as i would with the following sql query 

SELECT    Col1,Col2,col3
FROM         Table2 INNER JOIN
                      Table1 ON Table2.ForeignKey = Table1.PrimaryKey
WHERE     (Table1.Col4 = 'Something')

I presume this can only be done by Linq 

but unfortunately I don't know Linq and I need to accomplish this task ASAP

So, please any suggestion is highly appreciated to point me in the right direction

---

Don't miss to 'Vote As Helpful' or 'Mark As Answer' a deserving post.

Answer 1

var items = from t1 in table1
            join t2 in table2 on t1.PrimaryKey equals t2.ForeignKey
            where t1.Col4 == "Something"
            select new { PrimaryKey = t1.PrimaryKey, Col4 = t1.Col4 };

The syntax for doing joins in LINQ is documented here: http://msdn.microsoft.com/en-us/library/bb397941.aspx and here: http://msdn.microsoft.com/en-us/library/bb311040.aspx

Michael Taylor - 6/6/2012
http://msmvps.com/blogs/p3net

Answer 2

var items = from t1 in table1
            join t2 in table2 on t1.PrimaryKey equals t2.ForeignKey
            where t1.Col4 == "Something"
            select new { PrimaryKey = t1.PrimaryKey, Col4 = t1.Col4 };

The syntax for doing joins in LINQ is documented here: http://msdn.microsoft.com/en-us/library/bb397941.aspx and here: http://msdn.microsoft.com/en-us/library/bb311040.aspx

Michael Taylor - 6/6/2012
http://msmvps.com/blogs/p3net

thank you very much sir

this has got me started but I get no rows returned from the query:

IEnumerable<DataRow> pendingRows = (from t1 in oDataSet.SalesFiles
                              join t2 in oDataSet.SalesTransactions on t1.FilesReference equals t2.FilesReference
                              where t1.FileStatus == "Pending"
                              select new { FilesReference = t1.FilesReference, FileStatus = t1.FileStatus }) as IEnumerable<DataRow>;


            DataTable dtPendingRows = pendingRows.CopyToDataTable();

Could you please glance through the code and tell me where am I going wrong ?

---

Don't miss to 'Vote As Helpful' or 'Mark As Answer' a deserving post.

Answer 3

The query seems to be correct so I recommend that you run your query against the backend SQL and confirm the data is correct.  If it is then try a simpler query against each of the tables in oDataSet to confirm that they contain the data you want. 

Michael Taylor - 6/10/2012
http://msmvps.com/blogs/p3net

Answer 4

The query seems to be correct so I recommend that you run your query against the backend SQL and confirm the data is correct.  If it is then try a simpler query against each of the tables in oDataSet to confirm that they contain the data you want. 

Michael Taylor - 6/10/2012
http://msmvps.com/blogs/p3net

Yes, I also thought that data was not being loaded but it would seem that casting to DataRow was the problem

the following query seems to be working fine for me under the same circumstances:

var pendingRows = from t1 in oDataSet.SalesFiles
                              join t2 in oDataSet.SalesTransactions on t1.FilesReference equals t2.FilesReference
                              where t1.FileStatus == "Pending"
                              select t2 ;

            DataTable dtPendingRows = pendingRows.CopyToDataTable();

I got it working but can't figure out why the previous one won't work

could u please identify ?

---

Don't miss to 'Vote As Helpful' or 'Mark As Answer' a deserving post.

Answer 5

In you're code you said select new { ... } but then cast the entire result to IEnumerable<DataRow>.  select new causes an anonymous type to be created so the results of the LINQ statement is IEnumerable<X> where X is some unknown type created by the compiler.  By using the 'as' operator you are trying to convert it to a DataRow enumeration which it isn't so you get null back.  There is no reason to do any casting.  The compiler (through type inferencing) knows the type so it is unnecessary.  The results of your query is an IEnumerable of the anon type that was generated.

Michael Taylor - 6/11/2012
http://msmvps.com/blogs/p3net