Encuentra elementos coincidentes por combinación compleja de atributos -- # campo con sql campo con linq campo con linq-to-sql campo con entity-framework-core camp Relacionados El problema

Find matching items by complex combination of attributes


0
vote

problema

Español

En mi base de datos, tengo artículos, que reflejan los atributos en los documentos que se llenan por los usuarios. Cada valor dado en un documento, por ejemplo. Selecciona una opción determinada para un campo o selecciona una casilla de verificación, se convierte en un elemento / atributo en mi tabla.

Tales atributos podrían ser: Fumador, No fumador, Región (Europa, Estados Unidos, ...), Color de cabello

En las tablas, esto se ve aproximadamente como lo siguiente:

  Template.page.events({     'click button': function (event) {          var $button     = $(event.target),             method      = $button.attr('data-method');     } }); 4  

Para ofrecer posibilidades de búsqueda, será posible permitir que los usuarios puedan construir consultas genéricas. Por ejemplo, me gustaría encontrar los documentos, que tienen los siguientes atributos:

  Template.page.events({     'click button': function (event) {          var $button     = $(event.target),             method      = $button.attr('data-method');     } }); 5  

(resultaría en el documento # 1 que se encuentra)

¿Cómo puedo ejecutar dicha consulta de la manera más eficiente, y tal vez use EF-Core y Linq-to-SQL para empujar hacia abajo a SQL? ¿Cómo podría consultar en realidad para eso en el plan SQL la forma más eficiente?

Puedo hacerlo bastante fácil en la memoria, pero como mi base de datos contiene 100k + elementos, esto podría ser muy pronto.

¡Gracias por cualquier ayuda en esto!


update : Preguntas relacionadas sobre SO

  • diseño de la base de datos para etiquetar
Original en ingles

In my database, I have items, which reflect attributes on documents being filled out by users. Each value given in a document, e.g. you select a certain option for a field or select a check-box, becomes an item/attribute in my table.

Such attributes could be: Smoker, Non-Smoker, Region (Europe, USA, ...), Hair-Color

In the tables, this roughly looks like the following:

Document ID | Name 1  | doc-1 2  | doc-2 3  | doc-3  Attribute ID | Name 1  | Smoker 2  | Non-Smoker 3  | Region-Europe 4  | Region-USA 5  | Hair-Brown 6  | Hair-Blond  Item ID | Document | Attribute 1  | 1        | 1 2  | 1        | 4 3  | 2        | 2 4  | 2        | 3 5  | 2        | 5 6  | 3        | 2 7  | 3        | 6 

To offer search possibilities, it shall be possible to allow the users for building generic queries. For example, I would like to find the documents, which have the following attributes:

(Smoker AND Region-USA) OR (Non-Smoker AND Region-Europe AND Hair-Blond) 

(would result in document #1 being found)

How can I execute such a query in the most efficient way, and maybe use EF-core and linq-to-sql to push that down to SQL? How could I actually query for that in plan SQL the most efficient way?

I can do it quite easy in memory, but since my database contains 100k+ items, this might get slow pretty soon.

Thank you for any help on this!


Update: Related questions on SO

  • Database Design for Tagging
              
     
     

Lista de respuestas

0
 
vote
vote
La mejor respuesta
 

Más investigaciones me mostraron, lo que ya esperaba: la solución que utiliza el SQL en la declaración podría usarse y en realidad está trabajando para enviar muy bien ese trabajo de consulta al servidor, pero puede que no sea tan eficiente en una gran cantidad de etiquetas.

Afortunadamente, los usuarios no ejecutarán consultas muy complejas de forma regular y aceptarían un poco de tiempo de espera en consultas complejas, para que pueda descuidar que en mi caso.

Para vincular las fuentes para estas declaraciones:

  • https://stackoverflow.com/a/48480/8504288
  • https://dba.stackexchange.com/a/176816

Para dibujar aproximadamente la solución final ahora, aquí hay algún código para eso:

Mediante el uso de la declaración en la subsija, puedo filtrar todos los documentos, que tienen un cierto atributo aplicado. Al combinar aquellos en declaraciones usando y / o, puedo construir mi expresión deseada.

  SELECT i.Document FROM   Item i INNER JOIN Attribute a on i.Attribute = a.ID WHERE     i.Document IN (        SELECT ii.Document         FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID        WHERE ai.Name = "Smoker"     )     AND     i.Document IN (        SELECT ii.Document         FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID        WHERE ai.Name = "Region-USA"     )     OR     i.Document IN (        SELECT ii.Document         FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID        WHERE ai.Name = "Non-Smoker"     )     AND     i.Document IN (        SELECT ii.Document         FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID        WHERE ai.Name = "Region-Europe"     )     AND     i.Document IN (        SELECT ii.Document         FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID        WHERE ai.Name = "Hair-Blond"     )   

mejora de rendimiento

Para limitar la cantidad de combinaciones necesarias en las subintectivas, se podría seleccionar primero las ID de los atributos necesarios.

  SELECT ID, Name FROM Attribute WHERE Name in ('Smoker', 'Non-Smoker', ...)   

Usando estas ID, la subcogía se vería mucho más fácil, ya que podríamos omitir la unión:

  SELECT i.Document FROM   Item i INNER JOIN Attribute a on i.Attribute = a.ID WHERE     i.Document IN (SELECT ii.Document FROM Item ii WHERE ii.Attribute = 1) -- Smoker     AND     i.Document IN (SELECT ii.Document FROM Item ii WHERE ii.Attribute = 4) -- Region-USA     OR     ...   

actualización

tiempos medidos para ambos enfoques

Ejecuté una consulta similar a la consulta mencionada anteriormente: (1 y 2) o (3 y 4 y 4) en SQL Server, con un conjunto de documentos (130), los elementos (4122) y los atributos (4122) y ~ 400). Los siguientes tiempos se pueden medir en mi máquina:

  • Primero enfoque, con unirse a la subcisería de en: ~ 12 segs
  • 2nd Enfoque, con la búsqueda de ID de atributo primero: ~ 3.5 segs
 

More research showed me, what I already expected: the solution using the SQL IN statement could be used and is actually working to nicely dispatch that query work to the server, but may not be that efficient on a large number of tags.

Luckily, users won't execute very complex queries on a regular basis and would accept a little waiting time on complex queries, so I can neglect that in my case.

To link the sources for these statements:

  • https://stackoverflow.com/a/48480/8504288
  • https://dba.stackexchange.com/a/176816

To roughly sketch the final solution now, here is some code for that:

By using the IN statement, in the sub-query, I can filter all documents, which have a certain attribute applied. By combining those IN statements using AND/OR, I can build my desired expression.

SELECT i.Document FROM   Item i INNER JOIN Attribute a on i.Attribute = a.ID WHERE     i.Document IN (        SELECT ii.Document         FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID        WHERE ai.Name = "Smoker"     )     AND     i.Document IN (        SELECT ii.Document         FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID        WHERE ai.Name = "Region-USA"     )     OR     i.Document IN (        SELECT ii.Document         FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID        WHERE ai.Name = "Non-Smoker"     )     AND     i.Document IN (        SELECT ii.Document         FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID        WHERE ai.Name = "Region-Europe"     )     AND     i.Document IN (        SELECT ii.Document         FROM Item ii INNER JOIN Attribute ai on ii.Attribute = ai.ID        WHERE ai.Name = "Hair-Blond"     ) 

Performance improvement

To maybe limit the amount of JOINs needed in the sub-queries, one could select the IDs of the attributes needed first.

SELECT ID, Name FROM Attribute WHERE Name in ('Smoker', 'Non-Smoker', ...) 

Using these IDs, the sub-query would look much easier, as we would be able to skip the JOIN:

SELECT i.Document FROM   Item i INNER JOIN Attribute a on i.Attribute = a.ID WHERE     i.Document IN (SELECT ii.Document FROM Item ii WHERE ii.Attribute = 1) -- Smoker     AND     i.Document IN (SELECT ii.Document FROM Item ii WHERE ii.Attribute = 4) -- Region-USA     OR     ... 

Update

Measured times for both approaches

I did execute a similar query as the mentioned query above: (1 AND 2) OR (3 AND 4 AND 4) on SQL Server, with a reasonably sized set of Documents (130), Items (4122) and Attributes (~400). The following times could be measured on my machine:

  • 1st approach, with JOIN in sub-query of IN: ~ 12 secs
  • 2nd approach, with ID lookup of attribute first: ~3.5 secs
 
 
0
 
vote

Aquí hay una clase de extensión LINQ para ayudar con las consultas de construcción. Dejo analizar la expresión y construir la consulta correcta como ejercicio para el lector :).

Primero, aquí está la base de lo que construiremos:

  public class DocItemJoin {     public Documents d { get; set; }     public IEnumerable<int> ig { get; set; } }  var DocItems = Document.GroupJoin(Item, d => d.ID, i => i.Document, (d, ig) => new DocItemJoin { d = d, ig = ig.Select(i => i.Attribute) });  // (Smoker AND Region-USA) OR (Non-Smoker AND Region-Europe AND Hair-Blond)     var ans = DocItems.Where(dig => (dig.ig.Contains(1) && dig.ig.Contains(4)) || (dig.ig.Contains(2) && dig.ig.Contains(3) && dig.ig.Contains(6)))                   .Select(dig => dig.d);   

Uso de DocItems Como base, podemos consultar para cada atributo usando Contains .

Uso de la biblioteca de extensión, podemos crear la misma consulta dinámicamente:

  var whereLeft = 1.HasAttrib().qAnd(4.HasAttrib()); var whereRight = 2.HasAttrib().qAnd(3.HasAttrib()).qAnd(6.HasAttrib()); var whereBody = whereLeft.qOr(whereRight); var ans = DocItems.Query(whereBody);   

Finalmente, aquí está la clase de extensión, la crea el Expression Árboles:

  public static class QueryBuilder {     private static MethodInfo containsMethod = typeof(Enumerable).GetMethods().Single(mi => mi.Name == "Contains" && mi.GetParameters().Length == 2).MakeGenericMethod(typeof(int));      public static MethodCallExpression qContains(this Expression p, int attrib) => Expression.Call(containsMethod, p, Expression.Constant(attrib));     public static BinaryExpression qAnd(this Expression l, Expression r) => Expression.AndAlso(l, r);     public static BinaryExpression qOr(this Expression l, Expression r) => Expression.OrElse(l, r);      static ParameterExpression digParm = Expression.Parameter(typeof(DocItemJoin), "dig");     static MemberExpression digParmig = Expression.Property(digParm, "ig");      public static MethodCallExpression HasAttrib(this int attrib) => digParmig.qContains(attrib);      static Expression<Func<DocItemJoin, Documents>> selectLambda = Expression.Lambda<Func<DocItemJoin, Documents>>(Expression.Property(digParm, "d"), digParm);      public static IQueryable<Documents> Query(this IQueryable<DocItemJoin> src, Expression whereBody)         => src.Where(Expression.Lambda<Func<DocItemJoin, bool>>(whereBody, digParm)).Select(selectLambda); }   
 

Here is a LINQ extension class to help with building queries. I leave parsing the expression and building the correct query as an exercise for the reader :).

First, here is the basis for what we will build:

public class DocItemJoin {     public Documents d { get; set; }     public IEnumerable<int> ig { get; set; } }  var DocItems = Document.GroupJoin(Item, d => d.ID, i => i.Document, (d, ig) => new DocItemJoin { d = d, ig = ig.Select(i => i.Attribute) });  // (Smoker AND Region-USA) OR (Non-Smoker AND Region-Europe AND Hair-Blond)     var ans = DocItems.Where(dig => (dig.ig.Contains(1) && dig.ig.Contains(4)) || (dig.ig.Contains(2) && dig.ig.Contains(3) && dig.ig.Contains(6)))                   .Select(dig => dig.d); 

Using DocItems as the base, we can query for each attribute using Contains.

Using the extension library, we can build the same query dynamically:

var whereLeft = 1.HasAttrib().qAnd(4.HasAttrib()); var whereRight = 2.HasAttrib().qAnd(3.HasAttrib()).qAnd(6.HasAttrib()); var whereBody = whereLeft.qOr(whereRight); var ans = DocItems.Query(whereBody); 

Finally, here is the extension class the builds the Expression trees:

public static class QueryBuilder {     private static MethodInfo containsMethod = typeof(Enumerable).GetMethods().Single(mi => mi.Name == "Contains" && mi.GetParameters().Length == 2).MakeGenericMethod(typeof(int));      public static MethodCallExpression qContains(this Expression p, int attrib) => Expression.Call(containsMethod, p, Expression.Constant(attrib));     public static BinaryExpression qAnd(this Expression l, Expression r) => Expression.AndAlso(l, r);     public static BinaryExpression qOr(this Expression l, Expression r) => Expression.OrElse(l, r);      static ParameterExpression digParm = Expression.Parameter(typeof(DocItemJoin), "dig");     static MemberExpression digParmig = Expression.Property(digParm, "ig");      public static MethodCallExpression HasAttrib(this int attrib) => digParmig.qContains(attrib);      static Expression<Func<DocItemJoin, Documents>> selectLambda = Expression.Lambda<Func<DocItemJoin, Documents>>(Expression.Property(digParm, "d"), digParm);      public static IQueryable<Documents> Query(this IQueryable<DocItemJoin> src, Expression whereBody)         => src.Where(Expression.Lambda<Func<DocItemJoin, bool>>(whereBody, digParm)).Select(selectLambda); } 
 
 
   
   

Relacionados problema

49  Marco de entidad Core RC2 Tabla Nombre Pluralización  ( Entity framework core rc2 table name pluralization ) 
¿Hay alguna manera de hacer lo que este código hizo en EF Core RC 2? protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuil...

-1  Actualizando una de las entidades relacionadas  ( Updating one of the related entity ) 
Estoy desarrollando un sistema de placa de anuncios (como parte de mi entrenamiento de ASP.NET MVC). Tengo una comprensión básica del modelado de datos, pero ...

2  Creación de bases de datos con EF Core + Código Primero + Migraciones  ( Database creation with ef core code first migrations ) 
En el caso de una aplicación principal de marco de entidades con código primero y migraciones, No logro tener la base de datos creada si no está existente. ...

1  Marco de entidad único en múltiples columnas  ( Entity framework unique across multiple columns ) 
Dada la siguiente tabla Año - int Empleado - String Elevarwo - String ¿Cómo podemos prevenir los valores duplicados en diferentes columnas? Tomar el s...

0  Marco de entidades 7 Procedimiento almacenado Resultado al modelo  ( Entity framework 7 stored procedure result to model ) 
Tengo un modelo que se asigna a la base de datos a través de EF7. Todas las propiedades son idénticas a las columnas mapeadas de la tabla. Ahora tengo un pr...

3  Auto-referencias en EF Core Error: la declaración de inserción en conflicto con la clave de la misma tabla de la clave extranjera  ( Self referencing in ef core error the insert statement conflicted with the fore ) 
Tengo un modelo Category con la auto-referencia y funcionó bien en la creación de bases de datos aún cuando intento insertar una categoría principal (una en...

0  Repositorios en .Net Core Library  ( Repositories in net core library ) 
Estoy tratando de envolver las acciones básicas del modelo (como ID, etc.) en los repositorios. Sin embargo, estoy frente a dos problemas: 1. Cadena de con...

0  ASP.NET CORE IDENTIDAD CON CLEARDB MYSQL  ( Asp net core identity with cleardb mysql ) 
Estoy tratando de establecer la identidad del usuario en mi aplicación ASP.NET Core 1, que alojó en Azure. Además, tengo una base de datos MySQL Cleardb. y ...

15  EntidadFramework Core Base de base de datos Primer enfoque de pluralización de nombres de tablas  ( Entityframework core database first approach pluralizing table names ) 
Tenemos la base de datos existente con nombres de tablas pluralizadas. Por ejemplo, Documents . Estoy tratando de usar el nuevo EF Core7 y Asp.Net Core c...

1  EF Core 3.1 no traduce la consulta apropiadamente  ( Ef core 3 1 not translating query appropriately ) 
Tengo una consulta que he escrito eso, dice que no está traduciendo correctamente. La consulta es la siguiente: var query = from a in context.PlaceSyncs ...




© 2022 respuesta.top Reservados todos los derechos. Centro de preguntas y respuestas reservados todos los derechos