This project has moved. For the latest updates, please go here.

Query.ExecuteSchemaDataTable() & DataTable.PrimaryKey/DataTable.Constraints

Topics: Developer Forum, User Forum
Aug 31, 2007 at 12:22 AM
Edited Aug 31, 2007 at 2:28 AM
Hey everyone... I poked around here in the discussions, the source code, and the documentation and couldn't find the answer to my question, hence the post. Hope this isn't too 'newb' or redundant, but:

How exactly might I obtain PrimaryKey/Constraint values for the DataTable objects returned from Query.ExecuteDataTable() or Query.ExecuteSchemaDataTable()? Does this even make any sense?

For example, if I add a DataTable to a DataContext that has a primary key, I can't seem to obtain said key in the DataTable returned from either of those methods. I inspected the code executed from Query.ExecuteSchemaDataTable(), and it appears that this doesn't offer much more than a collection of columns.

Am I missing something here, or is there no way to get this information once a DataTable has been added to a DataContext?

Any help would be most appreciated, as I'm using NQuery in a rather large commercial project (due out soon), and I just stumbled across this problem today. Please advise if sample code would be useful, I would be most happy to provide it.

Thanks in advance.
Coordinator
Sep 1, 2007 at 2:24 PM

How exactly might I obtain PrimaryKey/Constraint values for the DataTable objects returned from Query.ExecuteDataTable() or Query.ExecuteSchemaDataTable()? Does this even make any sense?
For example, if I add a DataTable to a DataContext that has a primary key, I can't seem to obtain said key in the DataTable returned from either of those methods.

I am not quite sure if I understand your question. Let me rephrase your problem. If it is not what you were looking for please correct me. I suppose you have added a DataTable (e.g. Employees) to the DataContext and issue a query like

    SELECT  e.Id, -- Represents the PK
            e.Name -- Represents a regular Column
    FROM    Employees e

Now I guess you expect the DataTable returned by Query.ExecuteDataTable() (or Query.ExecuteSchemaDataTable()) to contain the information that the first column represents the primary key of table Employees. As you already figured out NQuery does not provide this piece of information. The problem is basically that this information might be misleading or even incorrect. Suppose your query would look like this:

    SELECT  '1' AS Id,
            'Mr. Smith' AS Name
    UNION   ALL
    SELECT  e.Id, -- Represents the PK
            e.Name -- Represents a regular Column
    FROM    Employees e

In this case the first column both contains the data of Employees.Id and a constant value. So this column neither represents the PK of table Employees nor the PK of the query results (this value might collide with a value of Employees.Id so it might not even be unique). There are even more subtle scenarios. For example consider this query:

    SELECT  e.Id,
            e.Name,
            o.OrderId
    FROM    Employees e
                RIGHT JOIN Orders o ON o.EmployeeId = e.Id

Although the first column contains the primary key of table Employees its values are neither unique nor non-null. This happens because the join multiplies rows of Employees with rows from Orders and in addition emits null values for employee columns when there are no matches. Of course NQuery can derive these facts but as you can see only very trivial queries (those that follow the simple SELECT ... FROM Table t WHERE .. with a single table) will provide the information you are looking for. So the value of such an analysis seems very limited to me.


Am I missing something here, or is there no way to get this information once a DataTable has been added to a DataContext?

May be you are looking for the "wrong solution" - what kind of problem are actually trying to solve?


Any help would be most appreciated, as I'm using NQuery in a rather large commercial project (due out soon), and I just stumbled across this problem today. Please advise if sample code would be useful, I would be most happy to provide it.

I am pleased to hear that you consider using NQuery in a large project. If you need further assistance please let me know.
Sep 1, 2007 at 5:50 PM

The problem is basically that this information might be misleading or even incorrect.

Thank you for your prompt, thorough response. I eventually came to the conclusion that you stated above after giving my situation further thought. It would (could) be quite incorrect to carry forward something like a primary key from the original DataTable in the results of either of those two methods.

The basis for my original concern came from a requirement to obtain this type of schema-related information from the results of a query like:

SELECT * FROM SomeTable WHERE SomeField = 'SomeValue'

Should 'SomeTable' have been added to the DataContext with a PrimaryKey value, then I suppose it might be valid to return the PrimaryKey as part of the returned DataTable from this particular query, but I can totally accept how NQuery shouldn't be expected to determine this, considering it's abilities. Please accept my apologies for considering this a "problem" in my original post.

NQuery has proven to be quite useful in the product we're developing, thank you very much for making it available.