Filtrowanie w Entity Framework Plus

BaseModel.cs

public class BaseModel
{
    public BaseModel()
    {
        IsActive = true;
    }

    public int Id { get; set; }

    public bool IsActive { get; set; }
}

Category.cs

public class Category : BaseModel
{
    public string Name { get; set; }

    public virtual ICollection<Product> Products { get; set; }
}

DataContext.cs

public class DataContext : DbContext
{
    public DataContext()
        : base("Name=DefaultConnection")
    {
    }

    public DbSet<Category> Categories { get; set; }

    public DbSet<Product> Products { get; set; }
}

GlobalFilter.cs

public class FilterDemo
{
    public void Run()
    {
        GlobalFilter();
    }

    private void GlobalFilter()
    {
        QueryFilterManager.Filter<Product>(q => q.Where(p => p.IsActive));

        using (var db = new DataContext())
        {
            var count = db.Products.Count();

            Console.WriteLine($"Products count: {count}");
        }

        using (var db = new DataContext())
        {
            var category = db.Categories.FirstOrDefault();

            var count = category.Products.Count;

            Console.WriteLine($"Products count in category {category.Name}: {count}");
        }
    }
}

Include.cs

public class FilterDemo
{
    public void Run()
    {
        IncludeWithoutFilter();
        IncludeWithFilter();
    }

    private void IncludeWithoutFilter()
        {
            using (var db = new DataContext())
            {
                var categories = db.Categories.Include(c => c.Products).ToList();

                Console.WriteLine($"Products count: {categories.Count}");
            }
        }

    private void IncludeWithFilter()
        {
            using (var db = new DataContext())
            {
                db.Filter<Product>(q => q.Where(p => p.IsActive));

                var categories = db.Categories.Include(c => c.Products).ToList();

                Console.WriteLine($"Products count: {categories.Count}");
            }
        }
}

Include.sql

SELECT [Project1].[Id]         AS [Id],
       [Project1].[Name]       AS [Name],
       [Project1].[IsActive]   AS [IsActive],
       [Project1].[C1]         AS [C1],
       [Project1].[Id1]        AS [Id1],
       [Project1].[Name1]      AS [Name1],
       [Project1].[CategoryId] AS [CategoryId],
       [Project1].[IsActive1]  AS [IsActive1]
FROM   (SELECT [Extent1].[Id]         AS [Id],
               [Extent1].[Name]       AS [Name],
               [Extent1].[IsActive]   AS [IsActive],
               [Extent2].[Id]         AS [Id1],
               [Extent2].[Name]       AS [Name1],
               [Extent2].[CategoryId] AS [CategoryId],
               [Extent2].[IsActive]   AS [IsActive1],
               CASE
                 WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int)
                 ELSE 1
               END                    AS [C1]
        FROM   [dbo].[Categories] AS [Extent1]
               LEFT OUTER JOIN [dbo].[Products] AS [Extent2]
                 ON [Extent1].[Id] = [Extent2].[CategoryId]) AS [Project1]
ORDER  BY [Project1].[Id] ASC,
          [Project1].[C1] ASC
                                                
                                                
SELECT [Project1].[Id]         AS [Id],
       [Project1].[Name]       AS [Name],
       [Project1].[IsActive]   AS [IsActive],
       [Project1].[C1]         AS [C1],
       [Project1].[Id1]        AS [Id1],
       [Project1].[Name1]      AS [Name1],
       [Project1].[CategoryId] AS [CategoryId],
       [Project1].[IsActive1]  AS [IsActive1]
FROM   (SELECT [Extent1].[Id]         AS [Id],
               [Extent1].[Name]       AS [Name],
               [Extent1].[IsActive]   AS [IsActive],
               [Extent2].[Id]         AS [Id1],
               [Extent2].[Name]       AS [Name1],
               [Extent2].[CategoryId] AS [CategoryId],
               [Extent2].[IsActive]   AS [IsActive1],
               CASE
                 WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int)
                 ELSE 1
               END                    AS [C1]
        FROM   [dbo].[Categories] AS [Extent1]
               LEFT OUTER JOIN [dbo].[Products] AS [Extent2]
                 ON ([Extent1].[Id] = [Extent2].[CategoryId])
                    AND ([Extent2].[IsActive] = 1)) AS [Project1]
ORDER  BY [Project1].[Id] ASC,
          [Project1].[C1] ASC                                  

LocalFilter.cs

public class FilterDemo
{
    public void Run()
    {
        LocalFilter();
    }

    private void LocalFilter()
    {
        using (var db = new DataContext())
        {
            db.Filter<Product>(q => q.Where(p => p.IsActive));

            var count = db.Products.Count();

            Console.WriteLine($"Products count: {count}");

            var category = db.Categories.FirstOrDefault();

            count = category.Products.Count;

            Console.WriteLine($"Products count in category {category.Name}: {count}");
        }
    }
}

LocalFilter.sql

SELECT [GroupBy1].[A1] AS [C1]
FROM   (SELECT COUNT(1) AS [A1]
        FROM   [dbo].[Products] AS [Extent1]
        WHERE  [Extent1].[IsActive] = 1) AS [GroupBy1]
        
        
SELECT [Extent1].[Id]         AS [Id],
       [Extent1].[Name]       AS [Name],
       [Extent1].[CategoryId] AS [CategoryId],
       [Extent1].[IsActive]   AS [IsActive]
FROM   [dbo].[Products] AS [Extent1]
WHERE  ([Extent1].[IsActive] = 1)
       AND ([Extent1].[CategoryId] = 1 /* @EntityKeyValue1 - [CategoryId] */)

NoFilter.cs

public class FilterDemo
{
    public void Run()
    {
        NoFilter();
    }
    
    private void NoFilter()
    {
        QueryFilterManager.Filter<Product>(q => q.Where(p => p.IsActive));

        using (var db = new DataContext())
        {
            var count = db.Products.AsNoFilter().Count();

            Console.WriteLine($"Products count: {count}");
        }
    }
}

NoFilter.sql

SELECT [GroupBy1].[A1] AS [C1]
FROM   (SELECT COUNT(1) AS [A1]
        FROM   [dbo].[Products] AS [Extent1]) AS [GroupBy1]

Product.cs

public class Product : BaseModel
{
    public string Name { get; set; }

    public int CategoryId { get; set; }

    public virtual Category Category { get; set; }
}