Instruct LINQ to Entities to Generate Oracle Compatible SQL

Question

I have a linq to entities query (EF 4.3)

var query = from item in db.TableTest
            select item.VAL;

which translates to this SQL statement

SELECT
"Extent1"."VAL" AS "VAL"
FROM "dbo"."TEST_TABLE" "Extent1"

The database is Oracle.

When I execute the query I get the message that the datatable does not exist. The problem is the ("dbo") part. If i remove it and execute this query directly (not through LINQ but through an oracleconnection etc)

SELECT
"Extent1"."VAL" AS "VAL"
FROM "TEST_TABLE" "Extent1"

then everything is ok. I get values back.

How can I instruct Linq To Entities to output Oracle compatible SQL?

score 2 · Answer 1 · answered Aug 10 '12 at 15:16

2

Assuming you have an entity model, make sure you correctly set the DDL generation template.

Also, you can remove the dbo for the database schema name to match your actual DB.

enter image description here

answered Aug 10 '12 at 15:16

ken2k

48,145
10
116
176

Thank you for the quick response. I am not allowed to alter anything in the database actually. I am an oracle newbie too. I don't have an entity model. I use POCO classes. I found that by annotating my entity class with the [Table("TABLE_NAME", Schema="SCHEMA_NAME")] attribute, i solved the problem – Thanasis Ioannidis Aug 10 '12 at 15:20
@Saysmaster I didn't knew if you were using code first or model first approach (you did not specified). In my answer, I assumed you were using an entity model, so I show how to modify the `Schema` property of the model. As you're actually using code first, you'll have to manually change the `Schema` property in the attributes, as you found by yourself. – ken2k Aug 10 '12 at 15:23
I can't autogenerate an Entity Model from this database for some reasons. It is mostly due to the fact that the generator cannot find the primary keys on the tables. So I tried to create POCO classes and map them manually to the tables I wanted... – Thanasis Ioannidis Aug 10 '12 at 15:27

score 1 · Accepted Answer · answered Aug 10 '12 at 15:24

Shortly after asking, i found a way to solve my problem

I had this POCO class

[Table("TEST_TABLE")]
public class MyEntity
{
    [Key, Column("VAL")]
    public string key_valye { get; set; }
}

The generated sql injected the "dbo". When i changed the table attribute to

 [Table("TEST_TABLE", Schema="ATT")]

This generated the "ATT"."TEST_TABLE" instead, which was actually the correct sql.

Instruct LINQ to Entities to Generate Oracle Compatible SQL

2 Answers2