EF Core - Partitioning Filter

Navigation
7

I'm working on a database that has the following characteristic, a 90% clustered index that integrates two columns: FilialID and DataCriacao .

These tables are partitioned by FilialID , simple creation or removal of a Filial causes the re-creation of these partitions.

At the system level, FilialID is unique and can be obtained globally, so I need to add a filtro to all queries, where all entities that have the FilialID property will be filtered by it.

For example, if I make the following query.: 

var entity = db.EntitiesA
    .Include(x => x.EntitiesB)
    .Include(x => c.EntitiesC)
    .Find(x => x.EntityAID = id);

should generate a query similar to the following.: 

SELECT *
FROM EntitiesA A
JOIN EntitiesB B ON A.EntityAID = B.EntityAID
JOIN EntitiesB C ON A.EntityAID = C.EntityAID
WHERE 
    A.EntityAID = @id AND
    A.FilialID = @filialId AND
    B.FilialID = @filialId AND
    C.FilialID = @filialId

MOTIVATION

  

Queries

     

Queries that use partition elimination could have comparable or   improved performance with larger number of partitions. Queries that do   not use partition elimination could take longer to execute as   number of partitions increases.

     

For example, assume the table has 100 million rows and columns A, B, and   C. In scenario 1, the table is divided into 1000 partitions on column   A. In scenario 2, the table is divided into 10,000 partitions on   column A. A query on the table that has a WHERE clause filtering on   column A will perform partition elimination and scan one partition.   That same query may run faster in scenario 2 as there are fewer rows   to scan in a partition. A query that has a WHERE clause filtering on   column B will scan all partitions. The query may run faster in   scenario 1 than in scenario 2 as there are fewer partitions to scan.

     

Queries that use operators such as TOP or MAX / MIN on columns other   than the partitioning column may experience reduced performance with   partitioning because all partitions must be evaluated.

The result of the above query will be the same, if I enter the FilialID or omit the same, but when informing it, the partitions referring to the other Filiais will be ignored, thus avoiding unnecessary%% and improving performance of the query.

EDIT

Attempt using locks of Query Filter , but does not work with Includes. 

public MyContext()
{
    this.Filter<EntityA>(set => set.Where(entity => entity.FilialID == Global.FilialID));
    this.Filter<EntityB>(set => set.Where(entity => entity.FilialID == Global.FilialID));
    this.Filter<EntityC>(set => set.Where(entity => entity.FilialID == Global.FilialID));
}
    
asked by anonymous 15.05.2017 / 20:02

3 answers

2

On the date of this response:

Two alternatives:

  • Return to Entity Framework 6;
  • Use a painful way.

The painful form is: 

var entity = db.EntitiesA
    .Include(x => x.EntitiesB)
    .Include(x => c.EntitiesC)
    .Where(...)
    .FiltrarPorFilial();

.Include() is an extension: 

public static IEnumerable<T> FiltrarPorFilial(this IQueryable<T> consulta)
{
    foreach (var registro in consulta.ToList()) 
    {
        // Faça aqui yield return de todos os registros que estejam nas condições desejadas. 
    }
}
    
23.05.2017 / 04:00
3

The gypsy's response led me to EF Core 2.0 , just like the Roadmap "and Preview of it.

In EF 2.0 you can do the following: 

public abstract class EntidadeBase
{
    public Guid FilialID { get; set; }
    public DateTime DataCriacao { get; set; }
}

public class EntidadeA : EntidadeBase
{
    public Guid EntidadeAID { get; set; }

    public ICollection<EntidadeB> EntidadesB { get; set; }
    public ICollection<EntidadeC> EntidadesC { get; set; }
}

public class EntidadeB : EntidadeBase
{
    public Guid EntidadeBID { get; set; }
    public Guid EntidadeAID { get; set; }
    public EntidadeA EntidadeA { get; set; }
}

public class EntidadeC : EntidadeBase
{
    public Guid EntidadeCID { get; set; }
    public Guid EntidadeAID { get; set; }
    public EntidadeA EntidadeA { get; set; }
}

public class MyContext : DbContext
{
    public static Guid FilialID { get; set; }

    public DbSet<EntidadeA> EntidadesA { get; set; }
    public DbSet<EntidadeB> EntidadesB { get; set; }
    public DbSet<EntidadeC> EntidadesB { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<EntidadeBase>().HasQueryFilter(p => p.FilialID == MyContext.FilialID);
    }
}
    
23.05.2017 / 14:44
2

I do not know if I understood your question very well, but it seems that you want to generate a generic query with a global filter, since in your application the FilialId field is a global field.

An approach that can meet your need would be to create a IQueryable of the desired table within the context, which would already implement your filter, below an example: 

public class MyContext : DbContext
{
    public DbSet<EntityA> EntitiesASet { get; set; }

    public IQueryable<EntityA> EntitiesA
    {
        return EntitiesASet
                   .Include(x => x.EntitiesB)
                   .Include(x => c.EntitiesC)
                   .Where(x => x.FilialID == Global.FilialID);
    }
}

In this case, when you call the code: 

var dados = db.EntitiesA.ToList();

The data will already be filtered with the desired filter.

I've used approaches like this in EntityFramework 6.1.3, never tested with EF Core, but I believe it will work. >

    
22.05.2017 / 23:01
What is the difference between Semaphore and Monitor? Reserved word include or "use" in PHP?