Azka.LinqExtensions 0.0.1-Beta5

Azka.LinqExtensions

Model Example

public class ItemCategory {
    public int Id { get; set; }
    public String Name { get; set; }
}

public class Item {
    public int Id { get; set; }
    public String BarCode { get; set; }
    public String Name { get; set; }
    public Double Price { get; set; }
    public int ItemCategoryId { get; set; }
    public virtual ItemCategory ItemCategory { get; set; }
}

public class PurchaseOrder {
    public int Id { get; set; }
    public String PONumber { get; set; }
    public DateTime PODate { get; set; }
    public virtual IEnumerable<PurchaseOrderDetail> PurchaseOrderDetails { get; set; }
}

public class PurchaseOrderDetail {
    public int Id { get; set; }
    public int ItemId { get; set; }
    public virtual Item Item { get; set; }
    public int PurchaseOrderId { get; set; }
    public virtual PurchaseOrder PurchaseOrder { get; set; }
    public int Qty { get; set; }
    public Double Price { get; set; }
}

DBContext Example

public class OnlineStoreContext : DbContext {
    public OnlineStoreContext(DbContextOptions<OnlineStoreContext> options) : base(options) { }

    public DbSet<ItemCategory> ItemCategories { get; set; }
    public DbSet<Item> Items { get; set; }
    public DbSet<PurchaseOrder> PurchaseOrders { get; set; }
    public DbSet<PurchaseOrderDetail> PurchaseOrderDetails { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Item>()
            .HasOne(p => p.ItemCategory).WithMany()
            .OnDelete(DeleteBehavior.Restrict).HasForeignKey(p => p.ItemCategoryId);
        modelBuilder.Entity<PurchaseOrderDetail>()
            .HasOne(p => p.Item).WithMany()
            .OnDelete(DeleteBehavior.Restrict).HasForeignKey(p => p.ItemId);
        modelBuilder.Entity<PurchaseOrderDetail>()
            .HasOne(p => p.PurchaseOrder).WithMany(p => p.PurchaseOrderDetails)
            .OnDelete(DeleteBehavior.Restrict).HasForeignKey(p => p.PurchaseOrderId);

        modelBuilder.Entity<ItemCategory>()
            .HasData(
                new ItemCategory() {
                    Id = 1,
                    Name = "Food"
                },
                new ItemCategory() {
                    Id = 2,
                    Name = "Electronic"
                }
            );
        modelBuilder.Entity<Item>()
            .HasData(
                new Item()
                {
                    Id = 1,
                    Name = "Oreo",
                    BarCode = "87726267",
                    Price = 1000,
                    ItemCategoryId = 1
                },
                new Item()
                {
                    Id = 2,
                    Name = "Chitos",
                    BarCode = "77728272",
                    Price = 2000,
                    ItemCategoryId = 1
                }
            );
        modelBuilder.Entity<PurchaseOrder>()
            .HasData(
                new PurchaseOrder() {
                    Id = 1,
                    PONumber = "PO0001",
                    PODate = new DateTime(2020, 1, 1)
                },
                new PurchaseOrder() {
                    Id = 2,
                    PONumber = "PO0002",
                    PODate = new DateTime(2020, 1, 2)
                },
            );
        modelBuilder.Entity<PurchaseOrderDetail>()
            .HasData(
                new PurchaseOrderDetail() {
                    Id = 1,
                    ItemId = 1,
                    PurchaseOrderId = 1,
                    Qty = 1,
                    Price = 1000
                },
                new PurchaseOrderDetail() {
                    Id = 2,
                    ItemId = 2,
                    PurchaseOrderId = 1,
                    Qty = 2,
                    Price = 2000
                },
                new PurchaseOrderDetail() {
                    Id = 3,
                    ItemId = 1,
                    PurchaseOrderId = 2,
                    Qty = 4,
                    Price = 1000
                }
            );
    }
}

Query From DbContext

IQueryable<T> QueryUrl<T>(this IQueryable<T> queryable, string queryUrl)

This Query URL will result object Item Oreo search by property BarCode with operation EQUAL

IQueryable<Item> queryable = dbContext.Set<Item>();
queryable = queryable.QueryUrl("filter[BarCode][equal]=87726267");

This Query URL will result object Item Chitos search by property Price with operation BETWEEN

IQueryable<Item> queryable = dbContext.Set<Item>();
queryable = queryable.QueryUrl("filter[Price][between]=2000,3000");

This Query URL will apply paging, get page 1, data per page 10 rows data, paging with key page.

IQueryable<Item> queryable = dbContext.Set<Item>();
queryable = queryable.QueryUrl("filter[Price][between]=1000,3000&page[number]=1&page[size]=10");

This Query URL will apply sorting data, sort with key sort, ascending and descending sorting differentiated by "-", "-" indicate descending. page.

IQueryable<Item> queryable = dbContext.Set<Item>();
queryable = queryable.QueryUrl("sort=-Name"); // Sort By Name Descending
queryable = queryable.QueryUrl("sort=Name"); // Sort By Name Ascending

This Query URL will include sub object "PurchaseOrderDetails" and "PurchaseOrderDetails.Item", include with key include

IQueryable<PurchaseOrder> queryable = dbContext.Set<PurchaseOrder>();
queryable = queryable.QueryUrl("include=PurchaseOrderDetails,PurchaseOrderDetails.Item");

IQueryable<T> Query<T>(this IQueryable<T> queryable, QueryFilter queryFilter)

Example using QueryFilter:

QueryFilter queryFilter = new QueryFilter() {
    Filter = new TreeFilter() {
        Composition = TreeComposition.And,
        Left = new TreeFilter() {
            PropertyName = "BarCode",
            Operation = TreeOperation.Equal,
            Value = "87726267"
        },
        Right = new TreeFilter() {
            PropertyName = "Price",
            Operation = TreeOperation.Between,
            Value = new List<double>() { 1000, 3000 }
        }
    },
    OrderBy = new List<OrderBy>() {
        new OrderBy() { 
            Property = "Name",
            OrderType = OrderType.Desc
        }
    },
    Pagination = new Pagination() {
        Page = 1,
        Size = 10
    },
    Include = new List<String>() { "ItemCategory" }
};

IQueryable<Item> queryable = dbContext.Set<Item>();
queryable = queryable.Query(queryFilter);

That's QueryFilter equal with this operation:

IQueryable<Item> queryable = dbContext.Set<Item>();
queryable = queryable.QueryUrl("filter[BarCode][equal]=87726267&filter[Price][Between]=1000,3000&sort=-Name&include=ItemCategory");

Count Row Data

Counting row data can be execute with Count or LongCount, data count will ignore sort, include, and pagination.

IQueryable<Item> queryable = dbContext.Set<Item>();
int count = queryable.Count("filter[BarCode][equal]=87726267&filter[Price][Between]=1000,3000&sort=-Name&include=ItemCategory");
long longCount = queryable.LongCount("filter[BarCode][equal]=87726267&filter[Price][Between]=1000,3000&sort=-Name&include=ItemCategory");

Current Supported Filter Operation

1. Equal
2. Contains
3. Like
4. GreaterThan
5. GreaterThanOrEqual
6. LessThan
7. LessThanOrEqual
8. Between