2

When creating a View based on a PIVOT query all the view fields become NULLABLE in the view metadata, is there any way to make them NOT NULLABLE?

I'm using the NVL function in the pivoted fields I need to be NOT NULL but they still become NULLABLE.

This is a problem to me because I'm using MS Entity Framework and it won't update the model based on tables or views without NOT NULLABLE columns.

Miguel Matos
  • 191
  • 7
  • possible duplicate of [Oracle: How to create a not null column in a view](http://stackoverflow.com/questions/11097839/oracle-how-to-create-a-not-null-column-in-a-view) – DazzaL Jan 22 '13 at 12:19
  • I don't think it applies on solving my problem. Since even if I create a new virtual column in the table being queried the PIVOT function will just turn that field nullable too. Am I missing something? – Miguel Matos Jan 22 '13 at 13:45
  • It being a PIVOT query is probably tangential to your core problem with the view metadata. – Jeffrey Kemp Jan 23 '13 at 07:50
  • @MiguelMatos What version of VS are you using? – Taryn Jan 25 '13 at 20:22

4 Answers4

1

@Miguel, I don't know the "MS Entity Framework" but the name makes it sound like a framework that is oriented towards editing data. 'Entity' is typically used for structuring a cache of database data in preparation for changing and updating that data back into the database. This does not seem like what you want to do.

Re-reading this question I believe that you have some sort of pivot generator you are using to create the view on-the-fly for the user. For this reason you do not intend to revise the Entity Model. I don't think you need an entity model at all.

An Entity Framework is likely to be looking for NOT NULL columns in order to find a 'primary key' or other row-level identifier that it can use. Why does it want these?

  • provide a key usable to update any row
  • provide key for paginating the result set
  • provide a key to support in-memory filtering of the result set
  • support dynamic sorting operations on the result set

I also surmise you have some sort of UI control that presents 'Entity' collections very nicely and so you want to use that control.

The control may not need an 'Entity' - check to see what its interface is. Perhaps there is a superclass of Entity or an interface that you can generate rather than an updateable Entity. If you can do that, you should be able to present it in the spiffy UI control and not hit the wall with your NULLABLE columns.

Andrew Wolfe
  • 2,020
  • 19
  • 25
  • You can see the entity framework as a layer that will be mapping the database objects to classes at the application domain, allowing the developers to interact with the database using strong typed objects. Our application is structured in a way the all the database accesses are made using the entity framework, it is our data access layer. – Miguel Matos Jan 29 '13 at 17:14
  • Right, but what you've described of the entity framework makes clear to me that it is strongly oriented towards updating data. This does not match your stated intention and it doesn't match the semantics of your view. Isn't there a non-updateable class or interface you can use instead of Entity? – Andrew Wolfe Jan 29 '13 at 18:15
  • +1 Andrew makes some great points here. I would also add that I've seen implementations that use BOTH an ORM framework and direct queries as required. – tbone Jan 29 '13 at 20:12
  • I understand your points, and their are valid. One of the main reasons for using an ORM framework, as Andrew stated, is to have persistent objects. In this specific case I'm not looking for updating the data, but I would like to be able to retrieve the data the same way we are using across all the application, allowing us to have a strong typed object without us having to create one manually for every view we are using and also to maintain only one database access layer. – Miguel Matos Jan 30 '13 at 09:52
0

One of possible solutions is generating a new table on the fly based on results of query and tuning constraints for this table after that. I don't like this method for too many dynamic SQL :)

Another solution is a prebuilt materialized view. Look here (Oracle docs) for "ON PREBUILT TABLE Clause".

knagaev
  • 2,897
  • 16
  • 20
  • I think that in both the suggestions you propose I won't be able to access live data, only snapshots created based on the query data. Am I missing something? Thanks. – Miguel Matos Jan 28 '13 at 15:13
  • Do you want to have an editable recordset as PIVOT query's result? I didn't expect this :) Usually PIVOT is used for folding data. So the result won't be allowed for editing. – knagaev Jan 29 '13 at 14:21
  • Miguel, can the answer in this post (http://stackoverflow.com/questions/11214448/represent-view-without-key-in-entity-framework) help you? – knagaev Feb 01 '13 at 06:44
0

You need to update your model in Visual Studio (VS). Because this doesn't know what type information is in every column. Then you have to specify in the query of pivot table the data type. For example, Use to_number for specify a explicit conversion. When you going to update the model in VS you must based in for example materialized view (with explicitly defined data types). Please create Materialized view with explicitly defined data types based in the pivot table (this have to contain not only nvl function else defined data types, string, number, etc ) and then Update your model. Only Materialized view? No, it can be a table (but is troublesome). Can be It direct of the pivot table? Does not always work (as in your case). Important Is to have defined data types.

Esneyder
  • 471
  • 1
  • 5
  • 19
  • Materialized view is not an option since it isn't based on live data, it has to be updated. When using a view, even if I use the NVL in a field it will still be nullable in the view metadata. Since all the fields are nullables, when updating the model in VS the view will not be added since the EF needs to have unique not nullable fields in order to add the table to the model. – Miguel Matos Jan 29 '13 at 16:10
0

You could use code-first if you don't have to many of these views, Scott Gu has a good article "Code first with existing database" that shows how to do this.

This might entail having 2 ways to access the db, which may or may not work for you.

Tommy Grovnes
  • 4,126
  • 2
  • 25
  • 40