Entity Framework 5, Code First, full-text search, but IQueryable through CreateQuery?

Question

I am using .NET 4.5 and EF 5 using the Code First approach, and now I need to implement full-text search. I already read a lot about this, and so far my conclusions are:

• Stored procedures or table value functions cannot be mapped to First code.

• However, I can name them using dynamic sql

  dbContext.Database.SqlQuery <Movie> (Sql, parameters)

But this returns IEnumerable, and I want IQueryable so that I can do more filtering before retrieving data from the db server. I know that I can send these parameters to the Db function, but I do not want this.

• What I found that could satisfy my needs is the CreateQuery function from the IObjectContextAdapter, which looks like this: (Select all for the test only):

  IQueryable <film> result = ((IObjectContextAdapter) dbContext) .ObjectContext.CreateQuery <Movie> ("SELECT * FROM Movie");

• However, the execution of this throw excludes "System.Data.EntitySqlException was unhandled HResult = -2146232006 Message = The syntax of the request is invalid. The closest term is' * ', line 1, column 9.'

So the questions are:

• Why am I getting this exception and can it be fixed?

• If there is no way with First code to make FTS, which returns IQueryable?

Answer 1

Try it like this:

ObjectQuery<Movie> query = objectContext.CreateQuery<Movie>(@"SELECT VALUE movie FROM Movies"); 

As for why , see these links

Differences from Transact-SQL Unlike Transact-SQL, Entity SQL does not support the use of the * argument in a SELECT clause. Instead

Entity SQL Object Reference - SELECT
"SELECT VALUE" - value keyword in LINQ / Entity Framework query