Databases
StackOverflow2013
Postsdbo
POA "Composable" Full Text Search with a Code First Model
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POA "Composable" Full Text Search with a Code First Model
    primarykey
    data
    text
    <p><strong>UPDATE</strong> 18 Sep 2013</p> <p>It looks like there isn't an easy way to do this. I'm holding out for a solution that involves some extension to Entity Framework.</p> <p>If you'd like to see these features in Entity Framework, vote for them on the <a href="http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions" rel="nofollow">user voice site</a>, perhaps <a href="http://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/1560711-full-text-search" rel="nofollow">here</a> and <a href="http://data.uservoice.com/forums/72025-entity-framework-feature-suggestions/suggestions/2686351-code-first-support-for-table-valued-functions" rel="nofollow">here</a></p> <hr> <p>There are several similar questions on SO but I can't find a question new and similar enough to have the answer I'm looking for.</p> <p>If this looks like information overload, jump down to <strong>In Summary</strong>.</p> <p><strong>Background</strong></p> <p>I'm writing a WebApi REST service to expose some pre-existing data through an OData end point. I'm using the <a href="http://msdn.microsoft.com/en-us/library/jj890573%28v=vs.111%29.aspx" rel="nofollow"><code>EntitySetContoller&lt;TEntity, TKey&gt;</code></a> to do all the grunt work for me. As well as the <a href="http://www.odata.org/documentation/odata-v2-documentation/uri-conventions/#4_Query_String_Options" rel="nofollow">standard OData parameters</a>, that are routed and translated by the base class, I've added some custom parameters, to allow specific functionality for my controller.</p> <p>My database server is MS SQL Server with a full text index on the <code>[BigText] NVarChar[4000]</code> column of the <code>[SomeEntity]</code> table.</p> <p>I have one limitation, <strong>I must use a Code First model.</strong></p> <pre><code>// Model POCO public class SomeEntity { public int Id { get; set; } public string BigText { get; set; } } // Simple Controller public class SomeEntityController : EntitySetController&lt;SomeEntity, int&gt; { private readonly SomeDbContext context = new SomeDbContext(); public override IQueryable&lt;SomeEntity&gt; Get() { var parameters = Request.GetQueryNameValuePairs() .ToDictionary(p =&gt; p.Key, p =&gt; p.Value); if (parameters.ContainsKey("BigTextContains") ( var searchTerms = parameters["BigTextContains"]; // return something special ... ) return this.context.SomeEntities; } // ... The rest is omitted for brevity. } </code></pre> <p><strong>The Problem</strong></p> <p>How to implement the <code>// return something special ...</code> part of my example?</p> <p>Obviously, the niave</p> <pre><code>return this.context.SomeEntities.Where(e =&gt; e.BigText.Contains(searchTerm)); </code></pre> <p>is completely wrong, it composes to a <code>WHERE</code> clause like</p> <pre><code>[BigText] LIKE '%' + @searchTerm + '%' </code></pre> <p>This doesn't use Full Text Searching so, doesn't support complex search terms and otherwise, performs terribley.</p> <p>This approach,</p> <pre><code>return this.context.SomeEntities.SqlQuery( "SELECT E.* FROM [dbo].[SomeEntity] E " + "JOIN CONTAINSTABLE([SomeEntity], [BigText], @searchTerm) FTS " + " ON FTS.[Key] = E.[Id]", new object[] { new SqlParameter("@searchTerm", searchTerm) }) .AsQueryable(); </code></pre> <p>Looks promising, it actually uses Full Text Searching, and is quite functional. However, you'll note that <a href="http://msdn.microsoft.com/en-us/library/gg679457%28v=vs.103%29.aspx" rel="nofollow"><code>DbSqlQuery</code></a>, the type returned from the <code>SqlQuery</code> function does not implement <code>IQueryable</code>. Here, it is coerced to the right return type with the <code>AsQueryable()</code> extension but, this breaks the "chain of composition". The only statement that will be performed on the server is the one specified in the code above. Any additional clauses, specified on the OData URL will be serviced on the API hosting web server, without benefitting from the indices and specialised set based functionality of the database engine.</p> <p><strong>In Summary</strong></p> <p>What is the most expedient way of accessing MS SQL Server's Full Text Search <a href="http://technet.microsoft.com/en-us/library/ms189760.aspx" rel="nofollow"><code>CONTAINSTABLE</code></a> function with an Entity Framework 5 Code First model and acquiring a "composable" result?</p> <p>Do I need to write my own <a href="http://msdn.microsoft.com/en-us/library/system.linq.iqueryprovider.aspx" rel="nofollow"><code>IQueryProvider</code></a>? Can I extend EF in some way?</p> <p>I don't want to use Lucene.Net, I don't want to use a Database Generated Model. Perhaps I could add extra packages or wait for EF6, would that help?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJodrell
    1. USJodrell
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POA "Composable" Full Text Search with a Code First Model
      1. USColin
      1. VTFavorite
    2. VO
      singulars
      1. POA "Composable" Full Text Search with a Code First Model
      1. USJodrell
      1. VTBountyStart
    3. VO
      singulars
      1. POA "Composable" Full Text Search with a Code First Model
      1. USTengiz
      1. VTFavorite
    1. COYou can define a stored procedure & then you can call with regular lQueryable
      singulars
      1. POA "Composable" Full Text Search with a Code First Model
      1. USAkash Kava
    2. CO@AkashKava, can you? Please show me how to call a SP and return a "composable" `IQueryable`.
      singulars
      1. POA "Composable" Full Text Search with a Code First Model
      1. USJodrell
    3. COCheckout last paragraph/section on this linq, it is little complicated, but if it works for you, I will put up as answer, http://www.scip.be/index.php?Page=ArticlesNET35
      singulars
      1. POA "Composable" Full Text Search with a Code First Model
      1. USAkash Kava
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload