DataSet - Ensuring PrimaryKey data when Filling with a query that is an outer join

Question

When filling a DataSet, I am using the "AddWithKey" value for the DataAdapter's MissingSchemaAction. According to MSDN:

Adding schema information to a DataSet before filling it with data ensures that primary key constraints are included with the DataTable objects in the DataSet.

But, when I do the following:

DataColumn[] foo = TheDataSet.Tables["Table1"].PrimaryKey;

I get foo.Length of 0. Is there something more that I need to do besides this?:

string TheQuery = "SELECT * FROM Table1 LEFT OUTER JOIN Table2 ON Table1.EVENT_NUM = Table2.EVENT_USER_NUM;
using (SqlDataAdapter TheDataAdapter = new SqlDataAdapter(TheQuery, TheConnection)
{
    TheDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    TheDataAdapter.Fill(TheDataSet, "Table1");
}

Note, if my query was:

string TheQuery = "SELECT * FROM Table1";

All would be fine. But, I don't get a PrimaryKey if there is a join. Is this even possible (to get the Primary key)?

EDIT: At first, I didn't realize this was a JOIN. Although, I've tested INNER JOINS that would return a PrimaryKey. The culprit seems to be the fact that this is an OUTER JOIN. Thus, I changed the title.

New info: Yes, there's a primary key, but I just realized this was on a query that was a JOIN (both tables have a Primary Key) — JustLooking, Jun 11 '10 at 17:32
please don't include tags like C# in the title. Keep them in the tags, where they belong. — John Saunders, Jun 11 '10 at 17:37

score 2 · Accepted Answer · answered Feb 24 '11 at 18:40

From msdn:

If the SelectCommand returns the results of an OUTER JOIN, the DataAdapter will not set a PrimaryKey value for the resulting DataTable. You must define the PrimaryKey yourself to ensure that duplicate rows are resolved correctly.

DataSet - Ensuring PrimaryKey data when Filling with a query that is an outer join

1 Answers1