CodeFirst and EntitySql

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
esben
Posts: 43
Joined: Tue 05 Jul 2011 09:40

CodeFirst and EntitySql

Post by esben » Tue 22 Nov 2011 09:16

Hi
I currently have a codefirst context (fluent mapping based on model file in entity developer), on oracle server using dotConnect for Oracle.

Now i want to dynamically build an entity sql statement (the query itself should be storeable in the database so no Linq i guess), but I have a hard time getting any Entity SQL to actually work.

Here is what i tried (in the generated DataContext class) so far.

Method 1 ObjectQuery:

Code: Select all

string commandQuery = @"SELECT VALUE RetailerCategory FROM RetailerCategory";
var objectContext = (this as IObjectContextAdapter).ObjectContext;
var query = objectContext.CreateQuery(commandQuery);
var listOfStuff = query.ToList();
Method 2 EntityCommand:

Code: Select all

string commandQuery = @"SELECT VALUE RetailerCategory FROM RetailerCategory";
EntityConnection entityConnection = new EntityConnection((this as IObjectContextAdapter).ObjectContext.MetadataWorkspace, this.Database.Connection);
var entityCommand = entityConnection.CreateCommand();
entityCommand.CommandText = commandQuery;
entityCommand.Connection.Open();
var result = entityCommand.ExecuteReader(CommandBehavior.SequentialAccess);

while (result.Read())
{
    Console.WriteLine(result);
}
entityCommand.Connection.Close();
Error message in both cases:
'RetailerCategory' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly. Near simple identifier, line 1, column 36.

Colum 36 i either a Number(10) (Oracle DB) no matter if it is zero or 1 based index. Colums before it also have number(10) as definition so doubt it is relevant.

The RetailerCategory entity maps to a table with a different name, but I was under the impression that table names didn't matter in ESQL.

I have NO clue on how to proceed, any ideas will be most welcome (and no, fallback to "real sql" is not an option).

Aiden
Posts: 1
Joined: Wed 23 Nov 2011 12:45
Contact:

Post by Aiden » Wed 23 Nov 2011 12:55

these code information is very useful for us i am very thankful to you for this

Shalex
Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 25 Nov 2011 10:39

esben wrote:string commandQuery = @"SELECT VALUE RetailerCategory FROM RetailerCategory";
SELECT in EntitySQL is described here: http://msdn.microsoft.com/en-us/library/bb399554.aspx (refer to the Example section).
You should specify the corresponding DbSet collection (after FROM) and set the alias (after AS), which you have used in this query before:

Code: Select all

string commandQuery = @"SELECT VALUE RetailerCategory FROM MyDbContext.RetailerCategories AS RetailerCategory";

esben
Posts: 43
Joined: Tue 05 Jul 2011 09:40

Post by esben » Mon 28 Nov 2011 13:43

Thanks for the help. Thought I had verified my ESQL, apparently not because now it works.

Post Reply