CodeFirst and EntitySql

[esben]

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]

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

[Shalex]

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]

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