locked
INNER JOIN Equivalent of SQL in ADO.NET RRS feed

  • 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.

    Wednesday, June 6, 2012 1:50 PM

Answers

All replies

  • 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

    • Proposed as answer by Kris444 Wednesday, June 6, 2012 2:37 PM
    • Marked as answer by Md.Ibrahim Monday, June 11, 2012 11:24 AM
    Wednesday, June 6, 2012 2:07 PM
  • 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.

    • Marked as answer by Md.Ibrahim Monday, June 11, 2012 11:24 AM
    • Unmarked as answer by Md.Ibrahim Monday, June 11, 2012 11:24 AM
    Sunday, June 10, 2012 7:00 AM
  • 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

    Sunday, June 10, 2012 8:23 PM
  • 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.

    Monday, June 11, 2012 6:06 AM
  • 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

    Monday, June 11, 2012 2:01 PM