Training
Module
Work with queries in Dynamics 365 Business Central - Training
Learn how to work with the Query object in Dynamics 365 Business Central to join, filter, and aggregate data.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies To: # OData client v7 supported OData Client V7
There are mainly 2 ways to add Query Options to a DataServiceQuery
The following example shows how to use to the AddQueryOption
method to create a DataServiceQuery<TElement> class, which implements the IQueryable<T>
DataServiceQuery peopleQuery = dsc.People
.AddQueryOption("$filter", "Gender eq Microsoft.OData.SampleService.Models.TripPin.PersonGender'Female'")
.AddQueryOption("$skip", "3")
.AddQueryOption("$orderby", "UserName desc");
foreach (Person person in peopleQuery)
{
Console.WriteLine($"Username: {person.UserName} First Name: {person.FirstName} Gender: {person.Gender}");
}
In the case above, the URI that is generated by the OData client includes the requested entity set together with the added query options.
In the example below, we are creating a Linq query expression that returns only females, ordered by UserName
. We skip the first 3 results and also specify which columns should be returned.
DefaultContainer dsc = new DefaultContainer(new Uri("https://services.odata.org/V4/(S(uvf1y321yx031rnxmcbqmlxw))/TripPinServiceRW/"));
var query = dsc.People.Where(p => p.Gender == PersonGender.Female)
.OrderByDescending(p => p.UserName)
.Skip(3)
.Select(p => new { p.FirstName, p.LastName, p.UserName, p.Gender });
foreach(var person in query)
{
Console.WriteLine($"Username: {person.UserName} First Name: {person.FirstName} Gender: {person.Gender}");
}
Since the DataServiceQuery<TElement> class implements the IQueryable<T> interface (System.Linq), the OData client library is able to translate Linq queries against entity sets into URIs executed against a data service resource.
The examples below demonstrate the kinds of LINQ queries that can be used to create various query options.
For GET https://host/service/People?$filter=FirstName eq 'Peter'
:
var people = context.People.Where(c => c.FirstName == "Peter");
For GET https://host/service/People?$filter=endswith(FirstName, 'Peter')
:
var people = context.People.Where(c => c.FirstName.EndsWith("Peter"));
For GET https://host/service/People?$filter=Trips/$count eq 2
:
var people = context.People.Where(c => c.Trips.Count == 2);
For GET https://host/service/People?$filter=Trips/any(d:d/Budget gt 6000)
:
var people = context.People.Where(c => c.Trips.Any(d => d.Budget > 6000));
For GET https://host/service/People?$filter=UserName in ('russellwhyte', 'scottketchum', 'ronaldmundy')
:
var userNames = new List<string> { "russellwhyte", "scottketchum", "ronaldmundy" };
var people = context.People.Where(p => userNames.Contains(p.UserName));
For GET https://host/service/People/$count
:
var count = context.People.Count();
For GET https://host/service/People?$count=true
:
We have two ways of making this request.
var people = context.People.IncludeCount();
var people = context.People.IncludeCount(true);
For GET https://host/service/People?$count=false
:
var people = context.People.IncludeCount(false);
For GET https://host/service/People?$orderby=FirstName
:
var people = context.People.OrderBy(c => c.FirstName);
For GET https://host/service/People?$orderby=FirstName desc
:
var people = context.People.OrderByDescending(c => c.FirstName);
For GET https://host/service/People?$orderby=Trips/$count
:
var people = context.People.OrderBy(c => c.Trips.Count);
For GET https://host/service/People?$skip=3
:
var people = context.People.Skip(3);
For GET https://host/service/People?$top=3
:
var people = context.People.Take(3);
For GET https://host/service/People?$expand=Trips
:
var people = context.People.Expand(c => c.Trips);
For GET https://host/service/People
Selects all columns by default
OR
For GET https://host/service/People?$select=*
Same as above
var people = context.People;
For GET https://host/service/People?$select=FirstName,LastName
:
var people = context.People.Select(c => new {c.FirstName, c.LastName});
For GET https://host/service/People?$expand=Trips&$filter=FirstName eq 'Peter'&$orderby=Firstname asc&$skip=3&$top=3
var people =
context.People
.Expand(c => c.Trips)
.Where(c => c.FirstName == "Peter")
.OrderBy(c => c.FirstName)
.Skip(3)
.Take(3);
The order of the query options matters.
Training
Module
Work with queries in Dynamics 365 Business Central - Training
Learn how to work with the Query object in Dynamics 365 Business Central to join, filter, and aggregate data.