如何保留外部连接(我认为是外部加入,但我不是100%确定)两个数据表与以下表格和条件,同时保留两个表中的所有列?
dtblLeft:
- id col1 anotherColumn2
- 1 1 any2
- 2 1 any2
- 3 2 any2
- 4 3 any2
- 5 3 any2
- 6 3 any2
- 7 any2
dtblRight:
- col1 col2 anotherColumn1
- 1 Hi any1
- 2 Bye any1
- 3 Later any1
- 4 Never any1
dtblJoined:
- id col1 col2 anotherColumn1 anotherColumn2
- 1 1 Hi any1 any2
- 2 1 Hi any1 any2
- 3 2 Bye any1 any2
- 4 3 Later any1 any2
- 5 3 Later any1 any2
- 6 3 Later any1 any2
- 7 any2
条件:
>在dtblLeft中,col1不需要具有唯一的值.
>在dtblRight中,col1具有唯一的值.
>如果dtblLeft在col1中缺少一个外键,或者它在dtblRight中不存在一个外键,那么将插入空或空字段.
>加入col1.
我可以使用常规的DataTable操作,LINQ或其他.
我尝试了这个,但它删除重复:
- dtblA.PrimaryKey = new DataColumn[] {dtblA.Columns["col1"]}
- DataTable dtblJoined = new DataTable();
- dtblJoined.Merge(dtblA,false,MissingSchemaAction.AddWithKey);
- dtblJoined.Merge(dtblB,MissingSchemaAction.AddWithKey);
编辑1:
这是接近我所想要的,但它只有一个表的列(发现在这link):
- dtblJoined = (from t1 in dtblA.Rows.Cast<DataRow>()
- join t2 in dtblB.Rows.Cast<DataRow>() on t1["col1"] equals t2["col1"]
- select t1).CopyToDataTable();
编辑2:
这个link的答案似乎适用于我,但我不得不改变一下如下:
- DataTable targetTable = dtblA.Clone();
- var dt2Columns = dtblB.Columns.OfType<DataColumn>().Select(dc =>
- new DataColumn(dc.ColumnName,dc.DataType,dc.Expression,dc.ColumnMapping));
- var dt2FinalColumns = from dc in dt2Columns.AsEnumerable()
- where targetTable.Columns.Contains(dc.ColumnName) == false
- select dc;
- targetTable.Columns.AddRange(dt2FinalColumns.ToArray());
- var rowData = from row1 in dtblA.AsEnumerable()
- join row2 in dtblB.AsEnumerable()
- on row1["col1"] equals row2["col1"]
- select row1.ItemArray.Concat(row2.ItemArray.Where(r2 => row1.ItemArray.Contains(r2) == false)).ToArray();
- foreach (object[] values in rowData)
- targetTable.Rows.Add(values);
我也发现这个link,我可能会尝试,因为它似乎更简洁.
编辑3(11/18/2013):
更新表以反映更多的情况.
解决方法
感谢所有的帮助.这是我根据多种资源提出的:
- public static class DataTableHelper
- {
- public enum JoinType
- {
- /// <summary>
- /// Same as regular join. Inner join produces only the set of records that match in both Table A and Table B.
- /// </summary>
- Inner = 0,/// <summary>
- /// Same as Left Outer join. Left outer join produces a complete set of records from Table A,with the matching records (where available) in Table B. If there is no match,the right side will contain null.
- /// </summary>
- Left = 1
- }
- /// <summary>
- /// Joins the passed in DataTables on the colToJoinOn.
- /// <para>Returns an appropriate DataTable with zero rows if the colToJoinOn does not exist in both tables.</para>
- /// </summary>
- /// <param name="dtblLeft"></param>
- /// <param name="dtblRight"></param>
- /// <param name="colToJoinOn"></param>
- /// <param name="joinType"></param>
- /// <returns></returns>
- /// <remarks>
- /// <para>https://stackoverflow.com/questions/2379747/create-combined-datatable-from-two-datatables-joined-with-linq-c-sharp?rq=1</para>
- /// <para>http://msdn.microsoft.com/en-us/library/vstudio/bb397895.aspx</para>
- /// <para>http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html</para>
- /// <para>https://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server</para>
- /// </remarks>
- public static DataTable JoinTwoDataTablesOnOneColumn(DataTable dtblLeft,DataTable dtblRight,string colToJoinOn,JoinType joinType)
- {
- //Change column name to a temp name so the LINQ for getting row data will work properly.
- string strTempColName = colToJoinOn + "_2";
- if (dtblRight.Columns.Contains(colToJoinOn))
- dtblRight.Columns[colToJoinOn].ColumnName = strTempColName;
- //Get columns from dtblLeft
- DataTable dtblResult = dtblLeft.Clone();
- //Get columns from dtblRight
- var dt2Columns = dtblRight.Columns.OfType<DataColumn>().Select(dc => new DataColumn(dc.ColumnName,dc.ColumnMapping));
- //Get columns from dtblRight that are not in dtblLeft
- var dt2FinalColumns = from dc in dt2Columns.AsEnumerable()
- where !dtblResult.Columns.Contains(dc.ColumnName)
- select dc;
- //Add the rest of the columns to dtblResult
- dtblResult.Columns.AddRange(dt2FinalColumns.ToArray());
- //No reason to continue if the colToJoinOn does not exist in both DataTables.
- if (!dtblLeft.Columns.Contains(colToJoinOn) || (!dtblRight.Columns.Contains(colToJoinOn) && !dtblRight.Columns.Contains(strTempColName)))
- {
- if (!dtblResult.Columns.Contains(colToJoinOn))
- dtblResult.Columns.Add(colToJoinOn);
- return dtblResult;
- }
- switch (joinType)
- {
- default:
- case JoinType.Inner:
- #region Inner
- //get row data
- //To use the DataTable.AsEnumerable() extension method you need to add a reference to the System.Data.DataSetExtension assembly in your project.
- var rowDataLeftInner = from rowLeft in dtblLeft.AsEnumerable()
- join rowRight in dtblRight.AsEnumerable() on rowLeft[colToJoinOn] equals rowRight[strTempColName]
- select rowLeft.ItemArray.Concat(rowRight.ItemArray).ToArray();
- //Add row data to dtblResult
- foreach (object[] values in rowDataLeftInner)
- dtblResult.Rows.Add(values);
- #endregion
- break;
- case JoinType.Left:
- #region Left
- var rowDataLeftOuter = from rowLeft in dtblLeft.AsEnumerable()
- join rowRight in dtblRight.AsEnumerable() on rowLeft[colToJoinOn] equals rowRight[strTempColName] into gj
- from subRight in gj.DefaultIfEmpty()
- select rowLeft.ItemArray.Concat((subRight== null) ? (dtblRight.NewRow().ItemArray) :subRight.ItemArray).ToArray();
- //Add row data to dtblResult
- foreach (object[] values in rowDataLeftOuter)
- dtblResult.Rows.Add(values);
- #endregion
- break;
- }
- //Change column name back to original
- dtblRight.Columns[strTempColName].ColumnName = colToJoinOn;
- //Remove extra column from result
- dtblResult.Columns.Remove(strTempColName);
- return dtblResult;
- }
- }
编辑3:
该方法现在可以正常工作,并且当表具有2000行时,该方法仍然很快.任何建议/建议/改进将不胜感激.
编辑4:
我有一些情况导致我意识到,以前的版本是真正做一个内在的联合.该功能已被修改以解决该问题.我使用这个link的信息来弄清楚.