Get unmatched records by comparing datatables in c#

I want to get the unmatched records by comparing two DataTables

eg :

Table 1

TransID BookingID BookingStatus BookingType 1 11 Y Paid 2 12 N UnPaid 3 13 N Paid

Table 1

TransID BookingID BookingStatus BookingType 1 11 Y Paid 2 12 Y UnPaid 4 14 Y Paid

I want to compare the above two table by using columns TransID and BookingID and I want the result as Below

[Unmatched Records]

TransID BookingID BookingStatus(Table 1) BookingType(Table 1) BookingStatus(Table 2) BookingType(Table 2) 2 12 N UnPaid Y Unpaid 3 13 N Paid NA(NULL) NA(NULL) 4 14 NA(NULL) NA(NULL) Y Paid

How can I achive this by using LINQ ?????????

I tried comparing datatables by following method :

var result = from dataRows1 in dtTable1.AsEnumerable() join dataRows2 in dtTable2.AsEnumerable() on new { TransID = dataRows1["TransID"], BookingID = dataRows1["BookingID"] } equals new { TransID = dataRows2["TransID"], BookingID = dataRows2["BookingID"] } into rows from row in rows.DefaultIfEmpty() select dtResult.LoadDataRow(new object[] { dataRows1["TransID"], dataRows1["BookingID"], dataRows1["BookingStatus"], dataRows1["BookingType"], row==null? "NA" : row["BookingType"] }, false); //==== copy output of result into datatable named 'dtResult' ============================= result.CopyToDataTable(); // This point records will be loaded in dtResult data table

This gives me only matched records .

-------------Problems Reply------------

The below code works and gives the result set you asked for. Couple of things though that I took liberties on 1. In your join condition, you only have the TransId and BookingId, but in your sample 'unmatched' data, it looks like you also want the booking status to be different, so I added that to my join - you can match whatever fields you want obviously. 2. Not sure what the scoop is with your select having an extra column after BookingType, seems redundant, but again, you can format the output however you need. But as far as the query, you basically need a full outer join, which Linq doesn't really support, but the below will give you the equivalent of two unioned outer joins, doing the same thing.

var result = (from d1 in dtTable1.AsEnumerable()
join d2 in dtTable2.AsEnumerable() on new { TransID = d1["TransID"], BookingID = d1["BookingID"], BookingStatus =d1["BookingStatus"] } equals new { TransID = d2["TransID"], BookingID = d2["BookingID"], BookingStatus =d2["BookingStatus"] } into leftJoin
from d2 in leftJoin.DefaultIfEmpty()
where d2 == null
select dtResult.LoadDataRow(new object[]
{
d1["TransID"],
d1["BookingID"],
d1["BookingStatus"],
d1["BookingType"]

}, false)).Union(
from dt2 in dtTable2.AsEnumerable()
join dt1 in dtTable1.AsEnumerable() on new { TransID = dt2["TransID"],
BookingID = dt2["BookingID"] } equals new { TransID = dt1["TransID"], BookingID = dt1["BookingID"] } into rightJoin
from dt1 in rightJoin.DefaultIfEmpty()
where dt1 == null
select dtResult.LoadDataRow(new object[]
{
dt2["TransID"],
dt2["BookingID"],
dt2["BookingStatus"],
dt2["BookingType"]
}, false));

result.CopyToDataTable();

Category:c# Views:0 Time:2017-12-06
Tags: linq datatable

Related post

  • How to get unmatched records using SQL query? 2010-04-21

    Actually if i am doing INNER JOIN of two tables then i will get all matched records from the two tables. But i want to get all the unmatched rows only. Is there any way to do that? Or Any JOIN available for that? --------------Solutions-------------

  • Efficient way to get unique records from datatable 2011-08-13

    I have a DataTable with multiple columns including AccountNumber, Year, and Month. I am exporting the information in this table to an Excel workbook. Since this table has the potential to be extremely large, I must check the number of records in the

  • How to compare two columns to find unmatched records in MySQL 2011-10-02

    I have a MySQL table with 2 columns and each column has thousands of records For Example 15000 Email addresses in Column1 and 15005 Email addresses in column 2 How to find those 5 records from 15005 which are unmatched in column1? I wish MySql query

  • Separating/comparing DataTable records: Is there a better way? 2011-12-02

    Had trouble titling this question as it's difficult to put into words what I'm trying to do, but in this example I have three datatables that are taken from a database. a "people" table, a "groups" table, and a "peopleInGroups" link table. I have two

  • Unable to get distinct records from datatable using DefaultView.ToTable in VB.NET VS2005. 2012-03-14

    I am building a custom search control in VB.NET and came across a problem getting distinct records into my datagridview. I want to bring exact matches back first so I run 2 of the same query on the same table with a slight difference: select ... like

  • How to get unique records of specific columns of data table 2012-02-19

    I have a DataTable imported from Excel file. Data i need is only unique from specific columns of the DataTable. The unique data i meant is like when a command DISTINCT is used in SQL Select Query. I want to get the list of the unique data from the Da

  • Problems with find unmatched records query 2013-06-25

    I have two tables of landowner parcel data. Table one is from the county tax and land owner records. Table two is from GIS software. Both tables have a "Parcel_ID" field. I need a query that will show me records in table one that do not have a match

  • Delete unmatched records 2015-01-13

    I have a database that is linked to an sql view called dbo_WTA_CityCab_Data. every 30 seconds I have an append Query that updates local table tbl_TaxiData. I have tbl_TaxiData set up with a primary key which prevents the import of duplicate data. My

  • how to get second record in linq to sql 2009-05-05

    I have a exchange rate table. I need to get current rate and previous rate and then compare results. I can get first record using FirstOrDefault. When i using ElementAtOrDefault,error show "The query operator 'ElementAtOrDefault' is not supported". H

  • How to fetch unmatching records from two SQL tables? 2009-07-08

    I want to fetch the unmatching records from two table in SQL, the table structure is as follows: Table1 Id Name 1 Prashant 2 Ravi 3 Gaurav 5 Naween 7 Sachin Table2 Id Name 1 Prashant 2 Ravi 4 Alok 6 Raja The output I want is Id Name 3 Gaurav 4 Alok 5

  • C#: retrieve the first n records from a DataTable 2010-03-04

    I have a DataTable that contains 2000 records. How would you retrieve the first 100 records in the DataTable? --------------Solutions------------- If it implements IEnumerable<T>: var first100 = table.Take(100); If the type in question only imp

  • How i Get Distinct record from DataSet? 2010-08-20

    I have a Static Dataset and i want to Distinct Records from this DataSet how is it possible? --------------Solutions------------- See here: http://stackoverflow.com/questions/1199176/how-to-select-distinct-values-from-datatable This link suggests thi

  • How to get all records within the last Two Days from the current Date using EF 4? 2011-09-21

    EF 4 in C#. I have my query, at the moment I'm able to filter the result by the current Date (just date not considering TIME). I need to filter the result FROM the last two days TO the current Date (no idea how to do it). I tried in my query currente

  • How to get the records count using Syncsort? 2011-12-15

    MY requirement is to get same record counts using JCL - Syncsort. MY Input File contains the packed decimal values in 58-60 position. I need to get the record count when the input in between 01 and 05 range. Actually I tried to convert PD values into

  • How to get previous record and next record values in ruby on rails 2012-02-29

    I have two requirements in my project. 1) I have to add a condition in database query where the current update time of a particular ID should be less than the next ID creation time. 2) Here I have to get a record of previous ID and compare with curre

  • Any chance to get unique records using Linq (C#)? 2009-04-07

    I got a list<list<string>> in list[x][0] are records from which I want to choose unique records thus such record wouldn't be in any other list[x][0], when I choose it, i'd like whole row list[x] to be chosen. I haven't found the appropria

  • Writing a query to get all records in a group based on an item in the group 2009-07-08

    Sorry for the unhelpful title, but hopefully I can explain this well enough. Lets say I have three tables, Item ItemKey ItemName Group GroupKey GroupItem GroupKey ItemKey Given an ItemKey, how would I get all records from the item table that belong t

  • Get the records of last month in SQL server 2009-09-15

    I want to get the records of last month based on my db table [member] field "date_created". What's the sql to do this? For clarification, last month - 1/8/2009 to 31/8/2009 If today is 3/1/2010, I'll need to get the records of 1/12/2009 to 31/12/2009

  • How do I get all records from tableA with a left outer join and a where condition on tableB? 2009-09-28

    Basically, what I want is if there is a record in tableB of type 'X' I want to see it, otherwise I don't, but I want all records from tableA. I know I could accomplish this by putting the tableB.type = 'X' in the LEFT OUTER JOIN ON clause, but I can'

  • What is the fastest, easiest way to get several records into a database using Ruby on Rails? 2009-10-31

    I'm new with Ruby on Rails and want to get several records into the SQLite3 database so that I can manipulate the data. I am following a lesson, and wonder what is the fastest, most effective way to get the data into the database, which has four tabl

  • How to get a records based on two conditions and second condition must be checked only if first one failed? 2009-11-09

    Is there a way to get a records in single query based on two conditions and second condition must be cheked only if first one failed. For example how can I get all records where time > NOW() and if there are no such records to get a record with ma

  • How to get a record base on date from a group using T-SQL 2010-02-21

    Here is the data Flag Zone Info Date R North AAA 2010-2-14 R North AAA 2010-2-24 T North AAA 2010-2-4 R South AAA 2010-2-23 T South AAA 2010-2-14 R EAST AAA 2010-2-22 T EAST AAA 2010-2-11 T EAST AAA 2010-2-1 T EAST AAA 2010-2-14 R WEST AAA 2010-2-29

  • after running insert or update query, need the last inserted record and compare in vb.net sql server 2010-03-11

    i have 2 queries in vb.net with an if clause - if x=0 then insert into table1 else update table1 both queries have 5 fields. now what i want to do is after this insert or update takes place, i need to look at this inserted/updated record and compare

  • i want to get next record of the table whenever clicking on button in swing form 2010-04-08

    I want to create a JFrame with some TextFields and Buttons .I want to get next record in the table whenever i clicks on a "next button" and also a "previousbutton",clicks on it get the prprevous value from the table. --------------Solutions----------

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.177 (s). 11 q(s)