Support OData Functions/Actions #28
Comments
|
Update on the 2 checkboxes above Passing function parametersReading the OData spec regarding Complex and Collection Literals:
Since these values can always be treated as JSON (either a JSON array or JSON string, etc), I was able to handle the list of strings mentioned above using: [HttpGet("WithRoles(roleCodes={roleCodes})")]
public IActionResult WithRoles(string roleCodes = "")
{
var roleCodesAsArray = JArray.Parse(roleCodes).ToObject<IEnumerable<string>>();
// ...
}when passed as a JSON-escaped string (ex. [HttpGet("SomeFunc(someProp={someProp})")]
public virtual IActionResult SomeFunc(string someProp)
{
var parsedValue = JValue.Parse(someProp).Value<string>();
// ...
}There is supposedly a performance difference between I haven't tested if this will handle an collection parameters with a complex type (which is then aliased). See this odata-query test for an example, but basically passing Not sure how it would work when also passing OData query via the query string as well. For example: I also came across AspNetCoreJTokenModelBinder which appears to allow you to bind to a Resolve issue when calling parser.ExecuteReader(query)This is still a big issue for me and not sure I can find a way to work around it. Hoping you have a good idea :). One thought was to register the function in the EdmModel we could expose: [ODataFunction("WithRoles(roleCodes={roleCodes})")]
public IActionResult WithRoles(string roleCodes = "")
{
// ...
}or [ODataAction("WithRoles(roleCodes={roleCodes})")]
public IActionResult WithRoles(string roleCodes = "")
{
// ...
}depending on the use case (Actions can have side effects (typically Anyways, just being able to take an OData query string and apply it anymore is really my use case (including applying to OData query to a custom |
techniq
changed the title
|
After more research, it looks like only To support
Here is a rough example of how I think it might look: [OeFunction]
[HttpGet("Default.HasOperatingStandard(roleCode={roleCode})")]
public virtual ODataResult<Department> HasOperatingStandard(string roleCode)
{
var parsedValue = JValue.Parse(roleCode).Value<string>();
var departmentIds = IdentityContext.GetEntityIdsForRole(RoleEntityType.Department, parsedValue);
var departmentsWithOperatingStandards = DbContext.Set<OperatingStandard>()
.Where(op => op.EntityTypeId == EntityTypeConstants.Department && departmentIds.Contains(op.EntityId))
.Select(d => d.EntityId);
var query = DbContext.Set<Department>().Where(d => departmentsWithOperatingStandards.Contains(d.Id));
var parser = new OeAspQueryParser(HttpContext);
var result = parser.ExecuteReader<User>(query);
return parser.OData(result);
}Specs for Operations (Actions/Functions)Here is also a good good description of the 3 types of functions in OData - https://stackoverflow.com/a/29023704/191902. Plan is to add support for option 1. We currently support option 3 by adding functions on the |
|
provider specific implementation because add sql server data adapter |
|
@voronov-maxim hmm, this mostly looks like support for passing an array to a Table Value Function as a My request is to expose a function bounded to an entity/collection. Pulled from StackOverflow link above...
We currently support My hope is to support [HttpGet("WithItems(itemIds={itemIds}"]
public async Task<ODataResult<Model.Order>> WithItems(IEnumerable<int> itemIds)
{
var parser = new OeAspQueryParser(_httpContextAccessor.HttpContext);
Model.OrderContext orderContext = parser.GetDbContext<Model.OrderContext>();
IAsyncEnumerable<Model.Order> orders = parser.ExecuteReader<Model.Order>(
orderContext.Orders.Where(o => o.Items.Any(i => itemIds.Contains(i.Id))
);
List<Model.Order> orderList = await orders.OrderBy(o => o.Id).ToList();
return parser.OData(orderList);
}This is a contrived example (and might not even compile). Instead of a simple Does this explain it any better? And thank for you for for this project, I've been very impressed with how it's designed and written (it's exactly what I've been looking for). |
|
DbContext function public static LambdaExpression WithItems(IEnumerable<Order> orders, IEnumerable<int> itemIds)
{
Expression<Func<IEnumerable<Order>, IEnumerable<Order>>> e = z => z.Where(o => o.Items.Any(i => itemIds.Contains(i.Id)));
return e;
} |
|
@voronov-maxim Are you saying that should work now... or suggesting how it might work? |
|
this is suggesting how it might work. |
|
@voronov-maxim Hmm, something like that might work (I guess you would know from the There is also a single function bound to a single entity (went a Single entity functioncalled via // in DepartmentsController
[HttpGet("({key})/Users(roleCodes={roleCodes})")]
public virtual ODataResult<User> Users(int key, IEnumerable<string> roleCodes)
{
var query =
from cu in DbContext.ContainedUsers
join u in DbContext.Users on cu.UserId equals u.Id
join g in DbContext.Groups on cu.GroupId equals g.Id
join r in DbContext.Roles on g.Id equals r.GroupId
join ua in DbContext.UserAssociations on u.Id equals ua.UserId into uaGroup
from ua in uaGroup.DefaultIfEmpty()
join e in DbContext.Employees on ua.EmployeeId equals e.Id into eGroup
from e in eGroup.DefaultIfEmpty()
join p in DbContext.Positions on e.PositionId equals p.Id into pGroup
from p in pGroup.DefaultIfEmpty()
where r.DepartmentId == key
select new { User = u, Role = r, Position = p };
if (roleCodes != null && roleCodes.Any())
{
query = from q in query
where roleCodes.Contains(q.Role.Code)
select q;
}
var parser = new OeAspQueryParser(_httpContextAccessor.HttpContext);
IAsyncEnumerable<User> users = parser.ExecuteReader<User>(query);
return parser.OData(users);
}Produced CSDL <Function Name="Users" IsBound="true">
<Parameter Name="bindingParameter" Type="Finance.Data.Organizations.Taxonomy.Department"/>
<Parameter Name="roleCodes" Type="Collection(Edm.String)"/>
<ReturnType Type="Collection(Finance.Web.Controllers.Organizations.DepartmentUserDto)"/>
</Function>Entity collection functioncalled via // in UsersController
[HttpGet("WithRoles(roleCodes={roleCodes}")]
public IActionResult WithRoles(IEnumerable<string> roleCodes)
{
var userIdsWithRoleCodes = from ur in DbContext.UserRoles
where roleCodes.Contains(ur.Role.Code)
select ur.UserId;
var query = from u in DbContext.Users
where userIdsWithRoleCodes.Contains(u.Id)
select u;
var parser = new OeAspQueryParser(_httpContextAccessor.HttpContext);
IAsyncEnumerable<User> users = parser.ExecuteReader<User>(query);
return parser.OData(users);
}Produced CSDL <Function Name="WithRoles" IsBound="true">
<Parameter Name="bindingParameter" Type="Collection(Finance.Data.Security.Users.User)"/>
<Parameter Name="roleCodes" Type="Collection(Edm.String)"/>
<ReturnType Type="Finance.Data.Security.Users.User"/>
</Function> |
|
One feature (semi-related to this) is it would be nice if you could get an expression tree from the parser, and then apply it later. Similar to how the tests are structured... OdataToEntity/test/OdataToEntity.Test/Common/SelectTest.cs Lines 45 to 46 in b55608a You could pass in a the OData query string and get the expression back, and then apply the expression later (I guess via an IQueryableProvider). Maybe something like
var parser = new OeParse(baseUri, edmModel);
var expression = parser.GetExpression<Order>("$apply=filter(Status eq OdataToEntity.Test.Model.OrderStatus'Unknown')/groupby((Name), aggregate(Id with countdistinct as cnt))");
// t => t.Where(o => o.Status == OrderStatus.Unknown).GroupBy(o => o.Name).Select(g => new { Name = g.Key, cnt = g.Count() }),
var results = DbContext.Orders.AsQueryable().Provider.Execute(expression)A lot of times I just want the OData query string parsed as an expression tree, and then let me worry about the execution (or further refinement of the query). |
|
Get expression tree will work for simplest cases, query "$apply=filter(Status eq OdataToEntity.Test.Model.OrderStatus'Unknown')/groupby((Name), aggregate(Id with countdistinct as cnt))" return expression type Tuple<IGrouping, Tuple> |
|
Understood. Usually if the query is complex I create is manually using EFCore (or make a DB View) but like to leverage Odata for simple filters, expands, pagonation, maybe a group by/roll-up, etc. |
|
expand query instead anonymous type new { o.Order, Customer = c } use Tuple<Order, Customer> |
|
@voronov-maxim 👍 . Shouldn't be an issue. When we handle inline (?$count=true this would be a special case as well since it would issue 2 queries/expressions. Might be useful to be able to get them individually as well. var url = "...";
parser.GetFilterExpression<Order>(url);
parser.GetExpandExpression<Order>(url);
parser.GetApplyExpression<Order>(url);but a single parser.GetExpression<Order>(url);would still be useful. |
|
Implement this feature after bound function. |
|
@voronov-maxim thanks! Here is some feedback from my observations / needs for my project: Support for OData query parametersSee this PR but also tried using new bounded function: which threw this exception More control over endpointI understand the need to allow non-MVC setup, but exposing the fully qualified method name as the function is a bit of an issue any way we could support "custom" endpoints to allow for example: I guess one solution might be to somehow re-write the OData Path before the parser/function resolver goes to look up the method. Customize query before executionIn your Bound function example, it looks like you are issuing multiple DB requests ( For example, this is one of my current OData functions I need to port [HttpGet]
public IActionResult WithRoles(ODataQueryOptions<User> queryOptions, [FromODataUri] IEnumerable<string> roleCodes)
{
var userIdsWithRoleCodes = from ur in DbContext.UserRoles
where roleCodes.Contains(ur.Role.Code)
select ur.UserId;
var query = from u in GetQuery()
where userIdsWithRoleCodes.Contains(u.Id)
select u;
var result = queryOptions.ApplyTo(query);
return Ok(result);
}As always, thanks for all your hard work on this project. |
|
@techniq
It will not work, result bound function entities NOT expression tree, $select can only be applied to the expression tree |
Could something like this be supported? ("WithItems(itemIds={itemIds})")]
public async Task<ODataResult<Model.OrderItem>> WithItems(String itemIds)
{
List<int> ids = JArray.Parse(itemIds).Select(j => j.Value<int>()).ToList();
var parser = new OeAspQueryParser(_httpContextAccessor.HttpContext);
Model.OrderContext orderContext = parser.GetDbContext<Model.OrderContext>();
var query = orderContext.OrderItems.Where(i => ids.Contains(i.Id));
var orderItems = await parser.ExecuteReader<Model.OrderItem>(query).ToList();
return parser.OData(orderItems);
}(currently throws this stack) |
|
Function without namespace not valid odata query. Dont use OeAspQueryPaser, see my sample. |
|
@voronov-maxim Currently WebApi/OData exposes all functions and actions using the http://odata.github.io/WebApi/#04-21-Set-namespaces-for-operations You used to be able to remove the namespace completely, but this may have changed. http://odata.github.io/WebApi/#12-02-WebApiV7newDefaultSettings The
The |
|
hi @techniq support $select, $top, $skip, $expand |
|
@voronov-maxim thanks! I'll try to take a look at it tonight or tomorrow. Looking at the commit changes, it looks good. It looks like. It looks like I need to register a bound function in the DbContext using So instead of (as it shows currently...) [HttpGet("OdataToEntity.Test.Model.BoundFunctionCollection(customerNames={customerNames})")]
public ODataResult<Model.OrderItem> BoundFunctionCollection(String customerNames)
{
var parser = new OeAspQueryParser(_httpContextAccessor.HttpContext);
IAsyncEnumerable<Model.OrderItem> orderItems = parser.ExecuteReader<Model.OrderItem>();
return parser.OData(orderItems);
}
[HttpGet("{id}/OdataToEntity.Test.Model.BoundFunctionSingle(customerNames={customerNames})")]
public ODataResult<Model.OrderItem> BoundFunctionSingle(int id, String customerNames)
{
var parser = new OeAspQueryParser(_httpContextAccessor.HttpContext);
IAsyncEnumerable<Model.OrderItem> orderItems = parser.ExecuteReader<Model.OrderItem>();
return parser.OData(orderItems);
}it would be... [HttpGet("BoundFunctionCollection(customerNames={customerNames})")]
public ODataResult<Model.OrderItem> BoundFunctionCollection(String customerNames)
{
var parser = new OeAspQueryParser(_httpContextAccessor.HttpContext);
IAsyncEnumerable<Model.OrderItem> orderItems = parser.ExecuteReader<Model.OrderItem>();
return parser.OData(orderItems);
}
[HttpGet("{id}/BoundFunctionSingle(customerNames={customerNames})")]
public ODataResult<Model.OrderItem> BoundFunctionSingle(int id, String customerNames)
{
var parser = new OeAspQueryParser(_httpContextAccessor.HttpContext);
IAsyncEnumerable<Model.OrderItem> orderItems = parser.ExecuteReader<Model.OrderItem>();
return parser.OData(orderItems);
} |
|
add support bound function 9fd4767 |
|
@techniq |
|
@voronov-maxim thanks! |
Currently with OData/WebApi I am able to register an OData function in the EdmModel...
..and then expose it in the Controller
I can then call it using
where you pass the function parameters via
(roleCodes=['Foo','Bar','Baz'])as well as process the OData query string$select,$top, etc.I attempted something similar using pure ASP.NET Core routing and ODataToEntity...
...but am running into the following issues
roleCodesparam as an array (ex.WithRoles(roleCodes=['Foo','Bar','Baz']))parser.ExecuteReader<User>(query)callI think to support this use case, the following needs to be supported:
odata-querytests.FromODataUri):/products?sizes=s,m,land not/products(sizes=['s','m','l'])parser.ExecuteReader<User>(query)EdmModellike in OData/WebApi (although this isn't the case with stored procedures in ODataToEntity, so I dunno).The text was updated successfully, but these errors were encountered: