Finding Unmatched Records in Dataset Tables Using Linq

Finding Unmatched Records in Dataset Tables using MS Access as a prototype.

Finding Unmatched Records in Dataset Tables Using Linq

 

In our previous article, Comparing Datasets using Linq, we discussed how to compare snapshots of the data taken over periods of time. Now we have decided to share another one of our “How To’s”.  Let’s consider a quite common problem:  “How to find unmatched records?”  We will use Linq to solve this problem.  So, here is the scenario we will work on:

For this demo, assume that we have two tables: Product and Orders, with very simple data structures listed below.


To populate test data we used the following code on form load event

private void Form1_Load(object sender, EventArgs e)

        {

            product = new Warehouse.ProductDataTable();

            orders = new Warehouse.OrdersDataTable();

 

            while (product.Rows.Count<10)

            {

                DataRow dr = product.NewProductRow();

                dr.ItemArray = ((product.Rows.Count + 1).ToString()+","+

                    "Product descr. " + (product.Rows.Count + 1).ToString()).Split(',');

                product.Rows.Add(dr);

            }

            //adding rows to orders

            while (orders.Rows.Count < 5)

            {

                DataRow dr = orders.NewOrdersRow();

                dr.ItemArray = ((orders.Rows.Count + 1).ToString() + "," +

                     ((orders.Rows.Count + 1)*2).ToString()).Split(',');

                orders.Rows.Add(dr);

            }

 

            dgvProduct.DataSource = product;

            dgvOrders.DataSource = orders;

 

        }

When the form “loads”, the code listed above is invoked and the result is that our tables will have the data shown in the image below:



As you can see from the image, the Orders table contains only products with even ID numbers and now we will answer the question: “How to find products that has not been ordered?” 

This a really useful business task – which allows a business to analyze purchase patterns.

And once again, Linq will provide a stylish and easy to use/understand, yet expedient solution.
We added a command button to our form and on button click event added the following code

private void button1_Click(object sender, EventArgs e)

        {

            var prod = product.AsEnumerable();

            var ord = orders.AsEnumerable();

 

            var result = (from p in prod

                          join o in ord on p.ProductId equals o.ProductId

                          into combinedResult

                          from f in combinedResult.DefaultIfEmpty()

                          select new { ProductID = p.ProductId,

                              ProductDescr=p.ProductDescr,

                                       MissingProd =  (f==null?0:f.ProductId)

                          }).Where(a=> a.MissingProd==0);

        }

In a few words, this is what we achieved: The combination of the join and into keywords creates an equivalent of SQL outer join, and then from the combined result of that execution (combinedResult) we selected records where the custom defined field MissingProd is equal to 0.  There is one “trick” that needs some explanation: We used the expression (f==null?0:f.ProductId) to replace null (absent) rows with 0 to prevent exceptions and to mark the missing rows.

Hope that this will help.  The results produced are shown below:



Aaron Katz
Yuri Kasan

By Aaron Katz   Popularity  (3396 Views)
Biography - Aaron Katz

Aaron J. Katz -- Director of New Technolgy for FOJP Service Corporation in New York. Aaron has over 25 years experience as a developer using a variety of technologies. Significant experience in guiding corporations in the appropriate use of technology to achieve business goals. Certified PMP (Project Management Professional).