none
HOWTO LINQ/join multiple DataTables with multiple columns. RRS feed

  • Question

  • Please help,

    I have the following DataTables, which I want to use LINQ with.

            DataSet dsSample = new DataSet();
    
            // M...
    
            DataTable dtM = new DataTable("M");
            dtM.Columns.Add("GI", typeof(int));
            dtM.Columns.Add("UI", typeof(int));
            dsSample.Tables.Add(dtM);
    
            // S...
    
            DataTable dtS = new DataTable("S");
            dtS.Columns.Add("OI", typeof(int));
            dtS.Columns.Add("OT", typeof(string));
            dtS.Columns.Add("SI", typeof(int));
            dtS.Columns.Add("ST", typeof(string));
            dtS.Columns.Add("PR", typeof(string));
            dsSample.Tables.Add(dtS);
    
            // R...
    
            DataTable dtR = new DataTable("R");
            dtR.Columns.Add("I", typeof(int));
            dtR.Columns.Add("K", typeof(string));
            dtR.Columns.Add("N", typeof(string));
            dtR.Columns.Add("V", typeof(string));
            dsSample.Tables.Add(dtR);
    

    The query I want is like this:

    select
      R.I, R.K, R.N, R.V
    from
      dtR as R,
      dtS as S,
      dtM as M
    where
      R.I = 1
      and S.OI = R.I
      and S.OT = 'R'
      and S.PR like 'Y%'
      and S.ST = 'G'
      and M.GI = S.SI
      and M.UI = 4
     
    I do not have a problem using LINQ joins, but I do not know how to do this with the DataTables.
    The only things I know for sure is that with DataTables, one has to use "AsEnumerable" to link/join a DataTable, like (R in dtR.AsEnumerable()) and when dealing with literal types like the "R.I = 1" would be translated to something like "R.Field<int>("I").Equals(1)", but after that, I'm at a complete loss.

    Monday, December 14, 2015 1:52 PM

Answers

  • Here is how you could join on multiple columns if that's what you are asking:

            var res = (from R in dtR.AsEnumerable()
                       join S in dtS.AsEnumerable() on new { A = R.Field<string>("I"), B = "R" } equals new { A = S.Field<string>("OI"), B = S.Field<string>("OT") }
                       select R);


    As for "imply a scan of all records", LINQ is not anything like SQL Server or any other RDBMS so you cannot simply replace any T-SQL query with LINQ and expect the query to perform exactly the same. If you have a fairly complex T-SQL query and performance is an issue, you could create a stored prodecure and execute it using the SqlQuery method: http://stackoverflow.com/questions/20970416/using-stored-procedure-in-entity-framework

    Hope that helps.

    Please remember to close your threads by marking all helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    • Marked as answer by J-S-B Tuesday, December 15, 2015 7:02 PM
    Tuesday, December 15, 2015 4:18 PM

All replies

  • You could use the DataRowExtensions.Field<T> method something like this:

          var res = (from r in dtR.AsEnumerable()
                     join s in dtS.AsEnumerable() on r.Field<string>("I") equals s.Field<int>("OI").ToString()
                     join m in dtM.AsEnumerable() on s.Field<int>("SI") equals m.Field<int>("GI")
                     where s.Field<string>("OT").Equals("R")
                     && s.Field<string>("ST").Equals("G")
                     && s.Field<string>("PR").StartsWith("Y")
                      && m.Field<string>("UI").Equals(4)
                     select r);


    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    Monday, December 14, 2015 5:42 PM
  • Thanks for the reply, but the joins bother me, as they imply a scan of all records of "S" and "M" solely off S.OI to R.I, and M.GI to S.SI, which would be bad.

    I tried some other ways, to use the joins to add the multiple column filtering, but it does not work, which is why I was hoping to use a "non-join" or "equi-join" approach as in my original post. This said, is there a way to force the full set of filtering into each join, like the following (which, I can't get to compile).

            from R in dtR.AsEnumerable()
              join S in dtS.AsEnumerable() on
                R.Field<string>("I").Equals(S.Field<string>("OI"))
                &&
                S.Field<string>("OT").Equals("R")
                &&
                S.Field<string>("ST").Equals("G")
                &&
                S.Field<string>("P").StartsWith("Y")
              join M in dtM.AsEnumerable() on M.Field<string>("I").Equals(S.Field<string>("SI"))
            where R.Field<string>("I").Equals(1)
            select R

    Tuesday, December 15, 2015 2:27 AM
  • Here is how you could join on multiple columns if that's what you are asking:

            var res = (from R in dtR.AsEnumerable()
                       join S in dtS.AsEnumerable() on new { A = R.Field<string>("I"), B = "R" } equals new { A = S.Field<string>("OI"), B = S.Field<string>("OT") }
                       select R);


    As for "imply a scan of all records", LINQ is not anything like SQL Server or any other RDBMS so you cannot simply replace any T-SQL query with LINQ and expect the query to perform exactly the same. If you have a fairly complex T-SQL query and performance is an issue, you could create a stored prodecure and execute it using the SqlQuery method: http://stackoverflow.com/questions/20970416/using-stored-procedure-in-entity-framework

    Hope that helps.

    Please remember to close your threads by marking all helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    • Marked as answer by J-S-B Tuesday, December 15, 2015 7:02 PM
    Tuesday, December 15, 2015 4:18 PM
  • Thanks, this helped a lot!
    Tuesday, December 15, 2015 7:02 PM