{"id":3055,"date":"2014-03-15T03:26:56","date_gmt":"2014-03-15T03:26:56","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2014\/03\/15\/entity-framework-5-code-first-full-text-search-but-iqueriable-via-createquery-collection-of-common-programming-errors-2\/"},"modified":"2014-03-15T03:26:56","modified_gmt":"2014-03-15T03:26:56","slug":"entity-framework-5-code-first-full-text-search-but-iqueriable-via-createquery-collection-of-common-programming-errors-2","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2014\/03\/15\/entity-framework-5-code-first-full-text-search-but-iqueriable-via-createquery-collection-of-common-programming-errors-2\/","title":{"rendered":"Entity Framework 5, Code First, Full Text Search but IQueriable via CreateQuery?-Collection of common programming errors"},"content":{"rendered":"

Thanks NSGaga, adding VALUE help resolve this issue, but there was still some problem, and after some more researching I found query that works (just add ‘as movie’ at end):<\/p>\n

@\"SELECT VALUE movie FROM Movies as movie\"\n<\/code><\/pre>\n
Anyone who need this, take into consideration that table name is ‘Movie’ and that ‘Movies’ is name of DbSet in DbContext.<\/p>\n
Now it works for queries like this, however I still didn’t resolve it for FTS. For DB I am using Potgresql and FTS is implemented using tsvector. Anyway standard query in PGadmin is:<\/p>\n
SELECT * FROM dbo.\"Movie\" WHERE title_tsvector @@ to_tsquery('incept:*');\n<\/code><\/pre>\n
So here I put:<\/p>\n
@\"SELECT VALUE movie FROM Movies as movie WHERE title_tsvector @@ to_tsquery('incept:*')\"\n<\/code><\/pre>\n
But it does not work, get error:<\/p>\n
System.Data.EntitySqlException was unhandled\nHResult=-2146232006\nMessage=The query syntax is not valid., line 1, column 61\nSource=System.Data.Entity\nColumn=0\nErrorContext=\"\"\nErrorDescription=\"\"\nLine=0\nStackTrace: at System.Data.Common.EntitySql.CqlLexer.yylex()\n<\/code><\/pre>\n
It seems the problem is that EF is not aware of tsvector, what is additional column in this table.<\/p>\n
    \n
  • Is there any solution for this?<\/li>\n
  • I could put this select and where query into stored procedure but I couldn’t call that either from CreateQuery ?<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
    Thanks NSGaga, adding VALUE help resolve this issue, but there was still some problem, and after some more researching I found query that works (just add ‘as movie’ at end): @”SELECT VALUE movie FROM Movies as movie” Anyone who need this, take into consideration that table name is ‘Movie’ and that ‘Movies’ is name of […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-3055","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/3055","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=3055"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/3055\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=3055"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=3055"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=3055"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}