๐ $select Enhancement in ASP.NET Core OData
๐ก Newskategorie: Programmierung
๐ Quelle: devblogs.microsoft.com
The release of ASP.NET Core OData v7.3 brings a ton of improvements to $select functionality. In this article, Iโd like to introduce some of the new features of $select and its usages in combination with other query options like $filter, $top, $skip, $orderby, $count and $expand.
This tutorial assumes that you already have the knowledge to build an ASP.NET Core Web Application service using ASP.NET Core OData NuGget package. If not, start by reading ASP.NET Core OData now Available and refer to the sample project used in this article.
Letโs get started.
Data Model
As mentioned, we are going to skip the steps to create an ASP.NET Core Web Application with OData functionalities enabled. However, to get a good understanding of the scenarios listed in this article, it is important for us to see the model types used in this sample project.
Below are the CLR class types used in sample project:
// Entity type public class Customer { public int Id { get; set; } public string Name { get; set; } public IList<string> Emails { get; set; } public Address HomeAddress { get; set; } public IList<Address> FavoriteAddresses { get; set; } public Order PersonOrder { get; set; } public Order[] Orders { get; set; } } // Complex type public class Address { public string Street { get; set; } public string City { get; set; } public ZipCode ZipCode { get; set; } } // Complex type public class BillAddress : Address { public string FirstName { get; set; } public string LastName { get; set; } } // Entity type public class Order { public int Id { get; set; } public string Title { get; set; } } // Entity Type public class ZipCode { public int Id { get; set; } public string DisplayName { get; set; } }
Where,
- Type โCustomerโ, โOrderโ, โZipCodeโ serve as Edm entity types.
- Type โAddressโ and โBillAddressโ serve as Edm complex types, and โBillAddressโ is derived from โAddressโ.
- โAddressโ has a navigation property named โZipCodeโ.
In the corresponding Edm model, I have โCustomersโ, โOrdersโ and โZipCodesโ as the Edm entity sets related to the above Edm types.
Besides, I have two real โCustomersโ named โBalmyโ and โChillyโ in the sample project. For other propertiesโ information, you can refer the source code or build, run, and send a http://localhost:5000/odata/Customers to get.
$select
$select is one of OData supported query options which allows the clients to select specific properties from the server. The biggest advantage of using $select is that the heavy lifting is done by the server before the data is returned, which leads to better performance. Hassan Habitโs Optimizing Web Applications with OData $Select shares some advantages to use $select.
For example, we can select a complex property using $select like:
http://localhost:5000/odata/Customers(1)?$select=HomeAddress
We can get:
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(HomeAddress)/$entity", "HomeAddress": { "Street": "145TH AVE", "City": "Redonse" } }
In this way, we can get data performance by limiting the result only including the properties wanted. That is, server doesnโt need to return all properties belong to โCustomer(1)โ, meanwhile the client doesnโt need to trim the result.
Select path in $select
The above example is a very basic usage of $select. With the release of ASP.NET Core OData v7.3.0, we now have support to use select path in $select.
For example:
http://localhost:5000/odata/Customers(1)?$select=HomeAddress/Street
In summary, the select path should follow up the following basic rules:
- If only one segment exists, it could be โ*โ, โNS.*โ, โStructural propertyโ segment or โNavigation propertyโ segment.
- Otherwise, the last segment in a select path could be โStructural propertyโ segment or โNavigation propertyโ segment, and the other segments could be โComplex propertyโ segment or โComplex type castโ segment.
For the above request, we can get the following result:
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(HomeAddress/Street)/$entity", "HomeAddress": { "Street": "145TH AVE" } }
Note: The result only includes the Street in HomeAddress.
Type cast select path in $select
It also supports the type cast in the select path, for example:
http://localhost:5000/odata/Customers?$select=HomeAddress/SelectImprovement.Models.BillAddress/FirstName
We can get:
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(HomeAddress/SelectImprovement.Models.BillAddress/FirstName)", "value": [ { "HomeAddress": {} }, { "HomeAddress": { "@odata.type": "#SelectImprovement.Models.BillAddress", "FirstName": "Peter" } } ] }
Note:
- The first customerโs HomeAddress is not a BillAddress, so the entity for this customer only includes the HomeAddress property with empty object.
- The second customerโs HomeAddress is a BillAddress, so it includes the selected property named FirstName and a control metadata property named @odata.type.
Nested $select in $select
We can use the nested $select to replace the above select path. In fact, Itโs more understandable to use nested $select.
A simplified example should look as follows:
http://localhost:5000/odata/Customers?$select=HomeAddress($select=Street)
We can get:
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(HomeAddress)", "value": [ { "HomeAddress": { "Street": "145TH AVE" } }, { "HomeAddress": { "@odata.type": "#SelectImprovement.Models.BillAddress", "Street": "Main ST" } } ] }
Note, the context URI in this scenario is not correct. It should be same as the context URI in select path scenario. Itโs a known issue and will be fixed in the future release.
Select sub navigation property in $select
It also supports to select the sub navigation property in a complex property. For example:
http://localhost:5000/odata/Customers?$select=HomeAddress/ZipCode
We can get:
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(HomeAddress/ZipCode)", "value": [ { "HomeAddress": {} }, { "HomeAddress": { "@odata.type": "#SelectImprovement.Models.BillAddress" } } ] }
You may be wondering โWhy the HomeAddress is empty object here?โ Itโs empty not because itโs not a BillAddress, but because the navigation link control information is omitted by default in the โMinimalโ metadata level. In OData, if we donโt set the metadata level, by default itโs โMinimalโ metadata level. So, if we want to get all control metadata information, we can use $format to set the โFullโ metadata level as below:
http://localhost:5000/odata/Customers(1)?$select=HomeAddress/ZipCode&$format=application/json;odata.metadata=full
We can get:
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(HomeAddress/ZipCode)/$entity", "@odata.type": "#SelectImprovement.Models.Customer", "@odata.id": "http://localhost:5000/odata/Customers(1)", "@odata.editLink": "http://localhost:5000/odata/Customers(1)", "HomeAddress": { "@odata.type": "#SelectImprovement.Models.Address", "[email protected]": "http://localhost:5000/odata/Customers(1)/HomeAddress/ZipCode/$ref", "[email protected]": "http://localhost:5000/odata/Customers(1)/HomeAddress/ZipCode" } }
Again, we can use nested $select to get the same payload result as below (except the context URI):
http://localhost:5000/odata/Customers(1)?$select=HomeAddress($select=ZipCode)&$format=application/json;odata.metadata=full
Selection on collection property
Now, thereโs support to select path and nested select on collection complex property. For example:
http://localhost:5000/odata/Customers(2)?$select=FavoriteAddresses/Street or http://localhost:5000/odata/Customers(2)?$select=FavoriteAddresses($select=Street)
We can get:
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(FavoriteAddresses/Street)/$entity", "FavoriteAddresses": [ { "Street": "145TH AVE" }, { "@odata.type": "#SelectImprovement.Models.BillAddress", "Street": "Main ST" }, { "Street": "32ST NE" } ] }
Note: The result in select path and nested select is almost same except the context URI.
Nested $filter, $top, $skip, $orderby, $count
Besides the nested $select, there is support for nested $filter, $top, $skip, $orderby and $count on collection property selection.
For example, we can select the collection property of string as:
http://localhost:5000/odata/Customers(2)?$select=Emails
We can get:
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(Emails)/$entity", "Emails": [ "E8", "E7", "E9" ] }
- Now, we can add nested $filter as:
http://localhost:5000/odata/Customers(2)?$select=Emails($filter=$it eq 'E7')
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(Emails)/$entity", "Emails": [ "E7" ] }
- We can add nested $top, $skip as:
http://localhost:5000/odata/Customers(2)?$select=Emails($top=1;$skip=1)
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(Emails)/$entity", "Emails": [ "E7" ] }
- Also, we can add $orderby as:
http://localhost:5000/odata/Customers(2)?$select=Emails($top=2;$skip=1;$orderby=$it)
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(Emails)/$entity", "Emails": [ "E8", "E9" ] }
Or order by in descending order as:
http://localhost:5000/odata/Customers(2)?$select=Emails($top=2;$skip=1;$orderby=$it desc)
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(Emails)/$entity", "Emails": [ "E8", "E7" ] }
The above query options can also apply to complex type collection property, for example:
- $filter on collection complex property
http://localhost:5000/odata/Customers(2)?$select=FavoriteAddresses($filter=Street eq '32ST NE')
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(FavoriteAddresses)/$entity", "FavoriteAddresses": [ { "Street": "32ST NE", "City": "Bellewe" } ] }
- $top, $skip, $count, $orderby on collection complex property
http://localhost:5000/odata/Customers(2)?$select=FavoriteAddresses($top=2;$skip=1;$count=true;$orderby=City desc)
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(FavoriteAddresses)/$entity", "[email protected]": 3, "FavoriteAddresses": [ { "@odata.type": "#SelectImprovement.Models.BillAddress", "Street": "Main ST", "City": "Issaue", "FirstName": "Peter", "LastName": "Jok" }, { "Street": "32ST NE", "City": "Bellewe" } ] }
Note: So far, $filter, $top, $skip and $orderby work for โallโ type collection structural property, such as primitive type, Enum type and complex type. However, $count only works for complex type collection property.
Nested $expand in $select (?)
It also supports to expand the navigation property under a complex property. For example, we can get a customer with โHomeAddressโ selected meanwhile โZipCodeโ is included under HomeAddress property.
It seems that we can use the โnested $expandโ in selection, same as nested $filter as above, like:
~/Customers(2)?$select=HomeAddress($expand=ZipCode)
However, itโs not allowed, not supported by design. Even though the OData spec says that you may use โ$select with nested $expandโ, but there is no compelling use case for such a scenario. Thereโs active discussion around this topic and this might change in the near future. So, at this time, we decided not to provide support for nested $expand in $select.
However, it doesnโt mean that we cannot accomplish the above goal. We can combine $select and $expand together to get the result.
Letโs start from the expand path. Simply put, we can use the expand path to expand a navigation property under a complex property as below:
http://localhost:5000/odata/Customers(1)?$expand=HomeAddress/ZipCode
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(HomeAddress/ZipCode())/$entity", "Id": 1, "Name": "Balmy", "Emails": [ "E1", "E3", "E2" ], "HomeAddress": { "Street": "145TH AVE", "City": "Redonse", "ZipCode": { "Id": 71, "DisplayName": "aebc" } }, "FavoriteAddresses": [ { "Street": "145TH AVE", "City": "Redonse" }, { "@odata.type": "#SelectImprovement.Models.BillAddress", "Street": "Main ST", "City": "Issaue", "FirstName": "Peter", "LastName": "Jok" }, { "Street": "32ST NE", "City": "Bellewe" } ] }
We can see โZipCodeโ navigation property included under โHomeAddressโ.
We can also use the same pattern on the navigation property of collection complex property as:
http://localhost:5000/odata/Customers(1)?$expand=FavoriteAddresses/ZipCode
We can get the following result:
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(FavoriteAddresses/ZipCode())/$entity", "Id": 1, "Name": "Balmy", "Emails": [ "E1", "E3", "E2" ], "HomeAddress": { "Street": "145TH AVE", "City": "Redonse" }, "FavoriteAddresses": [ { "Street": "145TH AVE", "City": "Redonse", "ZipCode": { "Id": 71, "DisplayName": "aebc" } }, { "@odata.type": "#SelectImprovement.Models.BillAddress", "Street": "Main ST", "City": "Issaue", "FirstName": "Peter", "LastName": "Jok", "ZipCode": { "Id": 61, "DisplayName": "yxbc" } }, { "Street": "32ST NE", "City": "Bellewe", "ZipCode": { "Id": 81, "DisplayName": "bexc" } } ] }
However, we cannot use nested $expand in this scenario as below:
~/Customers(1)?$expand=FavoriteAddresses($expand=ZipCode)
A $expand query as such will get an error message stating that โFavoriteAddressesโ is not a navigation property and you canโt expand on a non-navigation property.
To summarize, the expand path should follow up the following basic rules:
- The last segment in the expand path should be navigation property segment.
- The other segment could be complex property segment or type cast segment.
Combine $select and $expand
Even though we cannot use $expand in $select, we can combine them together to get more interesting results, for example:
http://localhost:5000/odata/Customers(1)?$select=Name&$expand=HomeAddress/ZipCode
We can get:
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(Name,HomeAddress/ZipCode())/$entity", "Name": "Balmy", "HomeAddress": { "Street": "145TH AVE", "City": "Redonse", "ZipCode": { "Id": 71, "DisplayName": "aebc" } } }
You may notice that in this scenario, I only select โNameโ explicitly, without select โHomeAddressโ, and only expand โZipCodeโ in โHomeAddressโ, the payload should only include โNameโ property and โZipCodeโ of โHomeAddressโ. The above payload does not seem correct. Itโs a known issue and will be fixed in the future release.
To get the result only including โZipCodeโ of โHomeAddressโ, we can construct a query like:
http://localhost:5000/odata/Customers(1)?$select=HomeAddress/ZipCode&$expand=HomeAddress/ZipCode
which gets:
{ "@odata.context": "http://localhost:5000/odata/$metadata#Customers(HomeAddress/ZipCode,HomeAddress/ZipCode())/$entity", "HomeAddress": { "ZipCode": { "Id": 71, "DisplayName": "aebc" } } }
Note, in โFullโ metadata level, the payload includes the navigation link metadata.
Summary
Thanks for reading this article. I hope you enjoy the new $select usages released in new ASP.NET Core OData. If you have any questions, comments, concerns, please feel free send email to [email protected]. ย If you find any issues or have a feature request, please go to odata@github.
For the sample project used in this article, you can find it here.ย And big thanks for reviewing from Saurahb Madan.
The post $select Enhancement in ASP.NET Core OData appeared first on OData.
...