AutoQueryable ·
AutoQueryable add auto querying functionality like OData on top of IQueryable with best url practices. It help you to make requests like http://baseurl/api/products?nameContains=frame&color=red,black with no effort.
Installing / Getting started
Other web framework? You could made your own attribute, see Use AutoQueryable without attribute section.
Basic usage
[Route("api/[controller]")]
public class ProductsController : Controller
{
[HttpGet]
[AutoQueryable]
public IQueryable<Product> Get([FromServices] myDbContext dbContext)
{
return dbContext.Product;
}
}
With this url: /products?select=productId,name,color,productCategory.name,salesOrderDetail,salesOrderDetail.product
You will get result like:
[
{
"productId": 1,
"name": "Product 0",
"color": "red",
"productCategory": {
"name": "red"
},
"salesOrderDetail": [
{
"product": {
"productId": 1,
"name": "Product 0",
"productNumber": "24bb9446-d540-4513-a3c6-be4323984112",
"color": "red",
"standardCost": 1,
"listPrice": 0,
"size": "L",
"weight": 0,
"productCategoryId": 1,
"productModelId": 1,
"sellStartDate": "2017-07-14T00:00:00+02:00",
"rowguid": "f100e986-adeb-46b0-91de-9a1f8f3a4d11",
"modifiedDate": "0001-01-01T00:00:00",
"salesOrderDetail": []
},
"salesOrderId": 0,
"salesOrderDetailId": 1,
"orderQty": 5,
"productId": 1,
"unitPrice": 0,
"unitPriceDiscount": 0,
"lineTotal": 0,
"rowguid": "00000000-0000-0000-0000-000000000000",
"modifiedDate": "0001-01-01T00:00:00"
},
...
]
},
...
]
Api URL usage
- Selectable columns: /products?select=name,color,toto
- Top/Take, Skip: /products?take=5&skip=5
- First, Last: /products?first=true
- OrderBy, OrderByDesc: /products?orderby=price,id
- Wrap with: /products?wrapwith=count,total-count,next-link
- Filtering: /products?nameContains=frame&color=red,black
- Paging: /products?page=2&pagesize=10
Existing filters
By default filters are separated by AND (eg: color=red&color=black is translated by color == red AND color == black)
In a filter, comma separator is used for OR (eg: color=red,black is translated by color == red OR black)
- Equals '=': /products?color=red,black
- Not Equals '!=': /products?color!=green,blue
- Less Than, Greater Than '<', '>': /products?productCount<5
- Less Than or Equals, Greater Than or equals '<=' /products?productCount<=5
- Contains 'contains': /products?colorContains=bla,ed
- StartsWith, EndsWith 'startswith', 'endswith': /products?colorStartsWith=bla,re
String filters have a negate (NOT) variant:
- NotContains 'notcontains': /products?colorContains!=bla,ed
- NotStartsWith, NotEndsWith 'notstartswith', 'notendswith': /products?colorStartsWith!=bla,ed
String filters have the following modifiers:
- :i (case insensitivity), ex. 'contains:i', 'startsWith:i!=': /products?colorContains:i=bla,ed
Filter aliases are not case sensitive:
- Contains 'contains': /products?colorcontains=bla,ed
Note that filters works with primitive types, string, datetime & guid
Filter modifiers
AQ provide property modifiers to modify property before filter, modifiers are denoted with :
- DateInYear :year=: /products?selldate:year=1989
- NotDateInYear :year!=: /products?selldate:year!=2000
Selection
- Select all properties of level zero without relations: /products?select=_
- Select all properties of level zero with relations: /products?select=*
- Select an object with all its value types: /products?select=productcategory
- Select an object with all its values including navigation properties on the first level: /products?select=productcategory.*
Projection
You can use projection in select & filters clauses with navigation properties (objects or collection of object)
- Select projection: /products?select=name,color,toto,productcategory.name
- Filter projection: /products?salesorderdetail.product.productid=1
Dto projection
You can still use dto projection and query over your dto with defined type:
[HttpGet]
[AutoQueryable]
public IQueryable Get([FromServices] AdventureWorksContext adventureWorksContext)
{
return adventureWorksContext.Product.Select(p => new ProductProjection
{
Name = p.Name,
ProductColor = p.Color,
FinalPrice = p.price
});
}
Or anonymous type:
[HttpGet]
[AutoQueryable]
public IQueryable Get([FromServices] AdventureWorksContext adventureWorksContext)
{
return adventureWorksContext.Product.Select(p => new
{
p.Name,
p.Color,
FinalPrice = p.Price
});
}
Unselectable properties
If you want some properties to be unselectable (eg: Id, Password, ...)
[Route("api/[controller]")]
public class UsersController : Controller
{
[HttpGet]
[AutoQueryable(UnselectableProperties = new []{ "Password", "Id" })]
public IQueryable<User> Get([FromServices] myDbContext dbContext)
{
return dbContext.User;
}
}
Note that Dto projection is the best way to limit selectable properties
Use base type instead of dynamic types
AQ uses dynamic types per default to ensure that the final flow will be as small as possible. In some cases you may not want to use dynamic types, there is a property for this:
[Route("api/[controller]")]
public class UsersController : Controller
{
[HttpGet]
[AutoQueryable(UseBaseType = true)]
public IQueryable<User> Get([FromServices] myDbContext dbContext)
{
return dbContext.User;
}
}
Note that final flow will include null and default values but you can escape them with serializer settings for exemple
Use AutoQueryable without attribute
If you don't want to use autoqueryable attribute you could use AQ directry in your code by passing it the querystring. First install the Autoqueryable package
Install-Package AutoQueryable
[Route("api/[controller]")]
public class UsersController
{
[HttpGet]
public IActionResult Get([FromServices] myDbContext dbContext)
{
var resultSet = dbContext.Product.Select(p => new { ... }).AutoQueryable(queryString);
return new OkObjectResult(new {
foo = "",
....,
Values = resultSet;
})
}
}
Add Swagger parameters
If you want to add AQ parameters to your swagger docs, just add AutoQueryable to the swagger conf.
Install-Package AutoQueryable.AspNetCore.Swagger
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new Info { Title = "My API", Version = "v1" });
c.AddAutoQueryable(); // add this line
});
AutoMapper
ProjectTo()
If you are getting the error "Sequence contains no elements", you should use the package
Install-Package AutoMapper.Extensions.ExpressionMapping
and replace the
ProjectTo<T>()
with
.UseAsDataSource().For<T>()
Map relations
AutoQueryable and AutoMapper do not work together when it comes to mapping entity relations from an Entity to a Dto. To fix this, use the AutoQueryable profile setting
profile.ToListBeforeSelect = true
This performs a ToList() using the AutoMapper mapping before using the select query parameters and selecting only the properties asked by the Querystring