locked
Using an IN clause to Create a new DATVIEW in C# NO SQL RRS feed

  • Question

  • Hello,

    I would like to create a DataView that contains every ROW from a DataTable whoes key value is in another DataTable.  I already have the DataTables what I am after is a way to do the equivelent of:

    SELECT * FROM DataTableA where DataTableA.KeyColumn IN (SELECT KeyColumn FROM DataTableB)

    I am sure this can be done in LINQ but I am not familiar with the syntax of LINQ.

                DataTable a = new DataTable(); //Columns (KEY , FirstName, LastName, Address)
                DataTable b = new DataTable(); //Columns (Key, Location, Membership)
    
                //<LINQ query to SELECT all rows in A that have a.key in the key column of b.
    
                DataView c = new DataView(); // Will Contain  Key, FirstName, LastName, Address after the magic happens 
    

     

    Wednesday, March 20, 2013 7:45 PM

Answers

  • I think you'll get better perfomance using a JOIN clause, the equivalent of:

    SELECT DataTableA.*
    FROM DataTableA INNER JOIN DataTableB
    ON DataTableA.KeyColumn = DataTableB.KeyColumn

    The result is identical, but using the JOIN clause will cause the LINQ processor to create a hash internally for the repeated key lookups. This will NOT happen if you use Contains, the equivalent of IN.

    Also, you can't directly use a LINQ query object as a data source, so you will first have to materialize the results using CopyToDataTable.

    So here is the query comprehension LINQ for your simple join, along with the steps to copy the results into a DataTable, and then create a DataView from the DataTable:

    var q = from rowa in a.AsEnumerable()
            join rowb in b.AsEnumerable()
            on rowa["Key"] equals rowb["Key"]
            select rowa;
    
    var c_table = q.CopyToDataTable();
    var c = new DataView(c_table);

    And here is the same query using lambda syntax:

    var q = a.AsEnumerable().Join(b.AsEnumerable(),
                                  rowa => rowa["Key"],
                                  rowb => rowb["Key"], 
                                  (rowa, rowb) => rowa);


    Joshua Honig
    Learn more about data programming at bytecomb.com

    • Edited by Joshua Honig Wednesday, March 20, 2013 8:45 PM
    • Marked as answer by Bob Shen Wednesday, April 3, 2013 8:37 AM
    Wednesday, March 20, 2013 8:43 PM
  • Hope this helps William 

     static void Main(string[] args)
           {
               DataTable a = new DataTable();
               a.Columns.Add("KEY",typeof(int));a.Columns.Add("FirstName",typeof(string));a.Columns.Add("LastName",typeof(string));a.Columns.Add("Address",typeof(string));
               a.Rows.Add(new object[] { 1, "ABC","XYZ","Address1"});
               a.Rows.Add(new object[] { 2, "BBC","XYZ","Address2"});
               DataTable b = new DataTable();
               b.Columns.Add("KEY",typeof(int)); b.Columns.Add("Location",typeof(string)); b.Columns.Add("Membership",typeof(string));
               b.Rows.Add(new object[] { 1, "LOC1", "Member1" });
               b.Rows.Add(new object[] { 3, "LOC1", "Member1" });
    
               DataTable result = new DataTable();
               result.Columns.Add("KEY", typeof(int)); result.Columns.Add("FirstName", typeof(string)); result.Columns.Add("LastName", typeof(string));
               result.Columns.Add("Location", typeof(string)); result.Columns.Add("Membership", typeof(string));
               var  query =
                        from r1 in a.AsEnumerable()
                        join r2 in b.AsEnumerable() on r1.Field<int>("KEY") equals r2.Field<int>("KEY")
                       select new
                       {
                           KEY =  r1.Field<int>("KEY") ,
                           FirstName =   r1.Field<string>("FirstName"),
                           LastName = r1.Field<string>("LastName"),
                           Location = r2.Field<string>("Location"),
                           Membership = r2.Field<string>("Membership")
                       };
               foreach (var x in query)
               {
                   Console.WriteLine("{0} {1} {2} {3} {4} ", x.KEY, x.FirstName , x.LastName , x.Location , x.Membership );
                   result.Rows.Add(x.KEY, x.FirstName, x.LastName, x.Location, x.Membership);
               }
               DataView c = result.DefaultView;
    
    
           }

    • Marked as answer by Bob Shen Wednesday, April 3, 2013 8:37 AM
    Wednesday, March 20, 2013 8:45 PM

All replies

  • I think you'll get better perfomance using a JOIN clause, the equivalent of:

    SELECT DataTableA.*
    FROM DataTableA INNER JOIN DataTableB
    ON DataTableA.KeyColumn = DataTableB.KeyColumn

    The result is identical, but using the JOIN clause will cause the LINQ processor to create a hash internally for the repeated key lookups. This will NOT happen if you use Contains, the equivalent of IN.

    Also, you can't directly use a LINQ query object as a data source, so you will first have to materialize the results using CopyToDataTable.

    So here is the query comprehension LINQ for your simple join, along with the steps to copy the results into a DataTable, and then create a DataView from the DataTable:

    var q = from rowa in a.AsEnumerable()
            join rowb in b.AsEnumerable()
            on rowa["Key"] equals rowb["Key"]
            select rowa;
    
    var c_table = q.CopyToDataTable();
    var c = new DataView(c_table);

    And here is the same query using lambda syntax:

    var q = a.AsEnumerable().Join(b.AsEnumerable(),
                                  rowa => rowa["Key"],
                                  rowb => rowb["Key"], 
                                  (rowa, rowb) => rowa);


    Joshua Honig
    Learn more about data programming at bytecomb.com

    • Edited by Joshua Honig Wednesday, March 20, 2013 8:45 PM
    • Marked as answer by Bob Shen Wednesday, April 3, 2013 8:37 AM
    Wednesday, March 20, 2013 8:43 PM
  • Hope this helps William 

     static void Main(string[] args)
           {
               DataTable a = new DataTable();
               a.Columns.Add("KEY",typeof(int));a.Columns.Add("FirstName",typeof(string));a.Columns.Add("LastName",typeof(string));a.Columns.Add("Address",typeof(string));
               a.Rows.Add(new object[] { 1, "ABC","XYZ","Address1"});
               a.Rows.Add(new object[] { 2, "BBC","XYZ","Address2"});
               DataTable b = new DataTable();
               b.Columns.Add("KEY",typeof(int)); b.Columns.Add("Location",typeof(string)); b.Columns.Add("Membership",typeof(string));
               b.Rows.Add(new object[] { 1, "LOC1", "Member1" });
               b.Rows.Add(new object[] { 3, "LOC1", "Member1" });
    
               DataTable result = new DataTable();
               result.Columns.Add("KEY", typeof(int)); result.Columns.Add("FirstName", typeof(string)); result.Columns.Add("LastName", typeof(string));
               result.Columns.Add("Location", typeof(string)); result.Columns.Add("Membership", typeof(string));
               var  query =
                        from r1 in a.AsEnumerable()
                        join r2 in b.AsEnumerable() on r1.Field<int>("KEY") equals r2.Field<int>("KEY")
                       select new
                       {
                           KEY =  r1.Field<int>("KEY") ,
                           FirstName =   r1.Field<string>("FirstName"),
                           LastName = r1.Field<string>("LastName"),
                           Location = r2.Field<string>("Location"),
                           Membership = r2.Field<string>("Membership")
                       };
               foreach (var x in query)
               {
                   Console.WriteLine("{0} {1} {2} {3} {4} ", x.KEY, x.FirstName , x.LastName , x.Location , x.Membership );
                   result.Rows.Add(x.KEY, x.FirstName, x.LastName, x.Location, x.Membership);
               }
               DataView c = result.DefaultView;
    
    
           }

    • Marked as answer by Bob Shen Wednesday, April 3, 2013 8:37 AM
    Wednesday, March 20, 2013 8:45 PM