Query conceptual model with Entity SQL

Question

My application is based on Entity Framework. I am offering users to query a particular table by saving their queries in another table. For example, TopQuery table in database stores all the queries which are popular among users. These queries are performed on table "TableData"

For test purposed, I have tried the following and it works. The only problem is that it returns all columns where as I would like to use columns that are mentioned by users in their queries.

string queryString =
        @"SELECT VALUE table FROM TestEntities.TableData AS table where table.col1 = 'test'";

    ObjectQuery<TableData> productQuery2 =
        new ObjectQuery<TableData>(queryString, context);

My problem is that if user stores a query in database like this, it doesn't work.

SELECT table.col1, table.col2, table.col3 FROM TestEntities.TableData AS table where table.col1 = "test"

I get the exception: The specified cast from a materialized System.Data.Objects.MaterializedDataRecord' to'TestEntities.TableData'type is not valid.

I have also tried this without any luck.

"SELECT it.col1, it.col2 FROM TableData WHERE it.col1 = 'test'"

What should I do in such case?

Regards,

score 0 · Accepted Answer · edited May 23 '17 at 10:34

You will never get ObjectQuery<TableData> once you try to select only subset of columns. Using ObjectQuery<TableData> works only when you select whole entity as your first query did - that is a strongly typed approach enforced by Entity framework.

ESQL doesn't support projection in the way Linq-to-entities does (by allowing you to project to a new anonymous or non mapped type). When using projection with ESQL you must work with ObjectQuery<DbDataRecord>.

Query conceptual model with Entity SQL

1 Answers1