9

I am using ODP.NET. I have added an ADO.NET model to the project. That way, an edmx file was created. I chose "create from database" and after I selected the tables and click finish, edmx design surface appeared empty, however, the model browser window showed my added tables.

The problem now is that I cannot drag & drop the tables from model browser to the design surface. Please see this screenshot:

enter image description here

jstuardo
  • 3,901
  • 14
  • 61
  • 136

3 Answers3

5

Might be because of the below reasons:

  1. I believe you have to choose the "Update Model from Database..." context menu option and pick the tables from the list
  2. Ensure the table you are trying to drag and drop has primary key defined
Nagaraj Raveendran
  • 1,150
  • 15
  • 23
  • 1
    Yes.. I think it is the second you mentioned. I have opened the edmx file using the XML editor and the table definition appears commented out with a warning message telling that the table lacks a primary key. How can I bypass that restriction? Is there a way to do that? – jstuardo Oct 13 '16 at 18:36
  • If you do not have a primary key, then it is only read-only like a view in EF. Simple solution would be to add an id field that is identity increment to the table and make it a primary key. – Nagaraj Raveendran Oct 13 '16 at 18:42
  • EF does not mandate a primary key but if there is no primary key in the table then it treats any non-nullable non-binary column as a unique key. If it could not find any such column then it throws the warning you got. If adding a primary key is not an option try adding a row number column which is unique to the table. – Nagaraj Raveendran Oct 13 '16 at 18:48
  • Both comments point to alter the table structure. The fact is that this table is a big table that is used critically by other systems, so modifying table structure is not an option because I don't want other systems to have problems if I alter the table. All fields of that table are nullable, without exception – jstuardo Oct 13 '16 at 18:52
  • Two options: 1. If you do not want to alter the table structure, see if you could combine two or more columns to create a unique composite key. This would not work for you since you have mentioned all the fields are nullable without exception – Nagaraj Raveendran Oct 13 '16 at 19:02
  • 2. create a view with a isnull statement. select ISNULL(ROW_NUMBER() OVER (ORDER BY somecolumn), 0) AS id from TableName. EF will treat this as unique key. – Nagaraj Raveendran Oct 13 '16 at 19:08
  • A view is readonly, however, I need to do an Insert to the table. That is the only operation I need to do with it. If finally there is no solution with EF, I will need to send it to the trash and go back to old OleDb tecnology. I don't want so. – jstuardo Oct 13 '16 at 19:22
  • My personal opinion is that using tables without a unique key in EF is not a good option in terms of data integrity, performance and it would introduce bugs which will be difficult to manage once the application grows. I believe there is not a solution for your problem with EF. – Nagaraj Raveendran Oct 13 '16 at 19:30
  • I completely agree with you, but this is a big system that was developed some time ago by a bad developer... I cannot do anything against that – jstuardo Oct 13 '16 at 19:43
  • Legacy systems! – Nagaraj Raveendran Oct 13 '16 at 19:51
  • I have found that I can use ExecuteSqlCommand method beloging to database context in EF. I could issue an INSERT statement that way. Perhaps, I have some hope :-) – jstuardo Oct 13 '16 at 20:06
0

A couple of thoughts here.

1.) Does the user id in the app.config for this database have permissions to make changes?

2.) Do you have the ODT (Oracle Developer Tools) installed?

3.) Are you using consistent/compatable versions of ODT, the Oracle client (ODAC) and the database itself?

John Meyer
  • 2,296
  • 1
  • 31
  • 39
  • yes... all of them are true. In my application I am already calling stored procedures using ExecureSqlCommand method, so I will use the same for the INSERT statement – jstuardo Oct 13 '16 at 21:42
0

I guess it is Linq to SQL file, Linq to SQL file experience is bad in my case.

Instead of drag drop you need to do following

  • Right click on the EDMX designer surface. You have lot of options and among them choose the option 'Update Model from Database'.

  • Now, you will see the usual screen which has all the database objects in new window which you have seen while creating EDMX file.

  • From this windows chose the new table and generate EDMX model from it.

  • After that this model will appear in EDMX designer window.

Mostafiz
  • 7,243
  • 3
  • 28
  • 42
  • ???... Maybe you misunderstood the problem. I have done what you said but that way, edmx surface does not show any table. Finally, I have used ExecuteSqlCommand to issue an INSERT statement, what was the only operation I needed to do with it. – jstuardo Oct 20 '16 at 11:03
  • ohh, ok hope someone help you on that :) – Mostafiz Oct 20 '16 at 11:10