c# - Una búsqueda de texto "composable" con un modelo de Code First

.net entity-framework

Question

ACTUALIZACIÓN 18 sep 2013

Parece que no hay una manera fácil de hacer esto. Estoy esperando una solución que implique alguna extensión a Entity Framework.

Si desea ver estas funciones en Entity Framework, vote por ellas en el sitio de voz del usuario , quizás here y here

Hay varias preguntas similares sobre SO, pero no puedo encontrar una pregunta lo suficientemente nueva como para tener la respuesta que estoy buscando.

Si esto parece una sobrecarga de información, salta a En resumen .

Fondo

Estoy escribiendo un servicio REST de WebApi para exponer algunos datos preexistentes a través de un punto final de OData. Estoy usando el EntitySetContoller<TEntity, TKey> para hacer todo el trabajo EntitySetContoller<TEntity, TKey> por mí. Además de los parámetros OData estándar , que son enrutados y traducidos por la clase base, he agregado algunos parámetros personalizados, para permitir una funcionalidad específica para mi controlador.

Mi servidor de base de datos es MS SQL Server con un índice de texto completo en la [BigText] NVarChar[4000] de la tabla [SomeEntity] .

Tengo una limitación, debo usar un modelo de Code First.

// Model POCO
public class SomeEntity
{
    public int Id { get; set; }
    public string BigText { get; set; }
}

// Simple Controller
public class SomeEntityController : EntitySetController<SomeEntity, int>
{
    private readonly SomeDbContext context = new SomeDbContext();

    public override IQueryable<SomeEntity> Get()
    {
        var parameters = Request.GetQueryNameValuePairs()
            .ToDictionary(p => p.Key, p => p.Value);

        if (parameters.ContainsKey("BigTextContains")
        (
            var searchTerms = parameters["BigTextContains"];
            // return something special ... 
        )

        return this.context.SomeEntities;
    }

    // ... The rest is omitted for brevity.
}

El problema

¿Cómo implementar el // return something special ... parte de mi ejemplo?

Obviamente, el niave.

return this.context.SomeEntities.Where(e =>
    e.BigText.Contains(searchTerm));

está completamente equivocado, se compone de una cláusula WHERE como

[BigText] LIKE ''%'' + @searchTerm + ''%''

Esto no utiliza la búsqueda de texto completo, por lo tanto, no admite términos de búsqueda complejos y, de lo contrario, tiene un desempeño terrible.

Este enfoque,

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();

Parece prometedor, en realidad usa la búsqueda de texto completo y es bastante funcional. Sin embargo, observará que DbSqlQuery , el tipo devuelto por la función SqlQuery , no implementa IQueryable . Aquí, se AsQueryable() al tipo de retorno correcto con la extensión AsQueryable() , pero esto rompe la "cadena de composición". La única declaración que se realizará en el servidor es la especificada en el código anterior. Cualquier cláusula adicional, especificada en la URL de OData, será atendida en el servidor web de alojamiento de la API, sin beneficiarse de los índices y la funcionalidad especializada basada en conjuntos del motor de base de datos.

En resumen

¿Cuál es la forma más conveniente de acceder a la función CONTAINSTABLE búsqueda de texto completo de MS SQL Server con un modelo de Entity Framework 5 Code First y adquirir un resultado "composable"?

¿Necesito escribir mi propio IQueryProvider ? ¿Puedo extender EF de alguna manera?

No quiero usar Lucene.Net, no quiero usar un modelo generado por la base de datos. Tal vez podría agregar paquetes adicionales o esperar a EF6, ¿eso ayudaría?

Answer 1

No es perfecto, pero puede lograr lo que está buscando con 2 llamadas a la base de datos. La primera llamada recuperaría una lista de claves coincidentes de CONTAINSTABLE y luego la segunda llamada sería su consulta componible utilizando los ID que devolvió de la primera llamada.

//Get the Keys from the FTS
var ids = context.Database.SqlQuery<int>( 
          "Select [KEY] from CONTAINSTABLE([SomeEntity], [BigText], @searchTerm)", 
          new object[] { new SqlParameter("@searchTerm", searchTerm) });

//Use the IDs as an initial filter on the query
var composablequery = context.SomeEntities.Where(d => ids.Contains(d.Id));

//add on whatever other parameters were captured to the ''composablequery'' variable
composablequery = composablequery.Where(.....)

Answer 2

Tuve este mismo problema recientemente: EF 5 Code First FTS Queriable

Déjame extender ese post.

• Tu primera opción también fue la mía primero, al usar SqlQuery también necesitaba hacer más filtros, así que en lugar de escribir siempre en sql completo utilicé QueryBuilder, al que hice algunos cambios y agregué más funciones para satisfacer mis necesidades (podría subirlo a algún lugar si es necesario): QueryBuilder

• Después he encontrado otra idea que he implementado. Alguien ya lo mencionó aquí, y es usar SqlQuery que devolverá HashSet de identificadores y que puede usarlo en consultas de EF con Contains. Esto es mejor, pero no el más óptimo, ya que necesita 2 consultas y una lista de identificación en la memoria. Ejemplo:

      public IQueryable<Company> FullTextSearchCompaniesByName(int limit, int offset, string input, Guid accountingBureauId, string orderByColumn)
  {
      FtsQueryBuilder ftsQueryBuilder = new FtsQueryBuilder();
  
      ftsQueryBuilder.Input = FtsQueryBuilder.FormatQuery(input);
      ftsQueryBuilder.TableName = FtsQueryBuilder.GetTableName<Company>();
      ftsQueryBuilder.OrderByTable = ftsQueryBuilder.TableName;
      ftsQueryBuilder.OrderByColumn = orderByColumn;
      ftsQueryBuilder.Columns.Add("CompanyId");
  
      if (accountingBureauId != null && accountingBureauId != Guid.Empty)
          ftsQueryBuilder.AddConditionQuery<Guid>(Condition.And, "" , @"dbo.""Company"".""AccountingBureauId""", Operator.Equals, accountingBureauId, "AccountingBureauId", "");
  
      ftsQueryBuilder.AddConditionQuery<bool>(Condition.And, "", @"dbo.""Company"".""Deleted""", Operator.Equals, false, "Deleted", "");
  
      var companiesQuery = ftsQueryBuilder.BuildAndExecuteFtsQuery<Guid>(Context, limit, offset, "Name");
      TotalCountQuery = ftsQueryBuilder.Total;
      HashSet<Guid> companiesIdSet = new HashSet<Guid>(companiesQuery);
      var q = Query().Where(a => companiesIdSet.Contains(a.CompanyId));
      return q;
  }

• Sin embargo, EF 6 ahora tiene algo llamado Interceptores que pueden usarse para implementar FTS queriable, y es bastante simple y genérico (última publicación): Interceptores EF 6 para FTS . He probado esto y funciona bien.

!! OBSERVACIÓN: El código EF primero, incluso con la versión 6, no admite procedimientos almacenados personalizados. Solo hay algunas para operaciones de CUD predefinidas si lo entendí bien: Codificar primero Insertar / Actualizar / Eliminar asignación de procedimientos almacenados , por lo que no se puede hacer con ella.

Conclusión: si puede usar EF 6, vaya a la tercera opción, le da todo lo que necesita. Si está atascado con EF 5 o menos, la segunda opción es mejor que la primera pero no la más óptima.