How to work with Views (relational tables), problem with single or multiple primary key (null oid not supported)

2 posts, 0 answers
  1. Cuneyt
    Cuneyt avatar
    36 posts
    Member since:
    Dec 2011

    Posted 31 Jan 2014 Link to this post

    Hi. i have problem with mapping database view. especailly with primary keys. i had "null oid" error or wrong result.
     
    I had two onetomany relational tables (tableQuestion , tableAnswer). TableQuestion could has many TableAnswer.
    And i needed these table into mixed one table at the database view.  i created view and then mapping.

    And of course gave me error that primary key should select.

    1- if I select primary key to second level table id (tableAnswer.id) i am taking null oid error. Because i have Questions that not answered yet. if i choose two primeray key result didnt change naturally.
    2- if i select primary key top level table id (tableQuestion.id) i am seeing only one first answer result, because all answer's TableQuestion.id is same. i cant see others answers for that question. all questions only has one answer with that way.

    I need extra id column for view mapped from database. how can i do this. i have to see all answers and questions that not answer yet.
    there one exp here about voa_keygen. but I am newby, could you explain simple , step by step.
    thanks.
  2. Boyan
    Admin
    Boyan avatar
    100 posts

    Posted 05 Feb 2014 Link to this post

    Hello Cuneyt,

    Telerik Data Access Runtime requires each object to be associated with an unique identifier. As it is described by this documentation article, this means that primary keys should be set even for mapped views. The most probable reason for the issue you are experiencing is that the primary key you have selected for the view contains one or more nullable columns. When choosing columns that will be part of the primary key of a view it is recommended to follow these rules:
    - Choose a combination of columns that will uniquely identify each row in the view (duplicates should not occur).
    - Choose a combination of not nullable columns in order to avoid the issue you described.

    You could make sure that all columns you chose to compose a primary key are not nullable using the Visual Designer in the following way: 
    1. Open Model Schema Explorer.
    2. Locate and expand the view.
    3. Select the column and press F4 to open the Property Window.
    4. IsNullable should be set to false.

    Alternatively If such set of columns cannot be chosen it is possible to introduce a new unique column in the view. For example it could represent the row numbers in the view. When the view is mapped this column should be chosen as a primary key. To demonstrate the approach of creating a view with row numbers (as FakeID) column the following code snippet uses the Northwind database:

    CREATE VIEW FakeIDView AS
    SELECT row_number() over (order by ProductID) as FakeID, p.ProductName, s.ContactName
    FROM Products as p
    JOIN Suppliers as s ON p.SupplierID = s.SupplierID



    Should you have any further questions, do not hesitate to contact us again.

    Regards,
    Boyan
    Telerik
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
  3. DevCraft banner
Back to Top