Combine 2 Datatables with the common rows only

I have 2 datatables:

_wizardStepSelectUnitDataSet.WizardStepSelectUnits_UnitsSelectedInOtherAgreements

and

_wizardStepSelectUnitDataSet.WizardStepSelectUnits_SelectedUnits

The schemas are the same, I need to kn开发者_开发问答ow which rows exist on both datatables,

How can I do that into a new datatable?

There is no CODE: I only have 2 datatables already designed. They are already filled with some data in memory.

I need to know which are the common rows between both datatables using one ID field.

I cant copy the code of the designer, you will be overwhelmed.

I tried this: var result = from r in _wizardStepSelectUnitDataSet.WizardStepSelectUnits_UnitsSelectedInOtherAgreements.AsEnumerable() join c in _wizardStepSelectUnitDataSet.WizardStepSelectUnits_SelectedUnits.AsEnumerable() on r.Field("UnitId") equals c.Field("UnitId") select r;

        DataRow[] dr = result.Select(
            String.Format(CultureInfo.InvariantCulture.DateTimeFormat,"StartDate <= #{0}#", stopDate));

However I got a compiler error: Error 7 The type arguments for method 'System.Linq.Enumerable.Select(System.Collections.Generic.IEnumerable, System.Func)' cannot be inferred from the usage. Try specifying the type arguments explicitly. C:\BITProjects\TeamSystem\luival\refm\DEV\AgreementModule\GUI\WorkItems\UC021_HuurovereenkomstWizard_WorkItem.cs 511 28 Ceusters.REFM.AgreementModule.GUI

Thank you

I'm not that good with LINQ, but maybe you want to try this approach?

var result = from r in dt.AsEnumerable()
                     join c in dt2.AsEnumerable()
                     on r.Field<string>("col1") equals c.Field<string>("col1")
                     select r;

And then for all your columns in both tables.

I'm sure there is a ways comparing complete rows instead of fields/columns.

Identify a unique_id field in the tables and then,

SELECT * FROM table1, table2 WHERE table1.unique_id = table2.unique_id

Once you're happy with what's returned you can build your insert statement based on the select.

Use System.Data.DataTable.Merge() , see this: http://msdn.microsoft.com/en-us/library/fk68ew7b.aspx

EDIT: Might not be a relevant answer, I misread "exist on both tables". Yes, what you are looking for is the intersection as others have speculated.