Querying with Linq

Description and code samples
Table of Contents
Summary

The DataServiceQuery class implements the IQueryable<T> interface defined by LINQ and therefore the WCF Data Services client library is able to transform LINQ queries against an entity set obtained from the DataServiceContext into a URI that represents a query expression evaluated against a data service resource.

An exception of type NotSupportedException is raised when the query cannot be mapped to a URI in the target data service.

Operations
Create the DataServiceContext using the service URI
How to do it
TrainingEntities context = new traningEntities(new Uri("http://localhost/TrainingService1/"));
Queries with no options applied
How to do it
var query = from c in context.Customers
            select c;

Trabslated URI

http://localhost:12345/TrainingService.svc/Customers()
Queries with filter options
How to do it
// Define a query for orders with a Freight value greater than 30.
var query = from o in context.Orders
            where o.Freight > 30
            select o;

Translated URI

http://localhost:12345/TrainingService.svc/Orders()?$filter=Freight gt 30M
Queries with sorting options
How to do it
var query = from c in context.Customers
            orderby c.CompanyName ascending, c.PostalCode descending
            select c;

Translated URI

http://localhost:12345/TrainingService.svc/Customers()?$orderby=CompanyName,PostalCode descx
Queries with projections options
How to do it

Projection provides a mechanism to reduce the amount of data returned by a query by specifying that only certain properties of an entity are returned in the response.

Considerations for querying:

  • Constructors are not supported when projecting into entity types;
  • Constructors are not supported when projecting into non-entity types;
  • Constructors are not supported when projecting into anonymous types, but the data is read-only because anonymous types are treated as non-entity types;
  • When a projection includes a navigation property, the related objects are loaded implicitly without having to call the Expand method

Considerations for updating:

  • When inserts are made to a projected type that does not contain all of the properties of the entity in the data model of the data service, the properties not included in the projection at the client are set to their default values;
  • When updates are made to a projected type that does not contain all of the properties of the entity in the data model of the data service, existing values not included in the projection on the client will be overwritten with uninitialized default values.
How to use it

Sample raising an unsupported-type exception due to the use of the constructor on a entity type:

var query = from c in context.Customers
            where c.Country == "Portugal"
            select new CustomerAddress(
              c.CustomerID,
              c.Address,
              c.City,
              c.Region,
              c.PostalCode,
              c.Country);

Sample using object initializer pattern to create a new instance of the CustomerAddress type:

var query = from c in context.Customers
            where c.Country == "Portugal"
            select new CustomerAddress
                    {
                        CustomerID = c.CustomerID,
                        Address = c.Address,
                        City = c.City,
                        Region = c.Region,
                        PostalCode = c.PostalCode,
                        Country = c.Country
                   };

[DataServiceKey("CustomerID")]
public partial class CustomerAddress
{
    private string _customerID;
    private string _address;
    private string _city;
    private string _region;
    private string _postalCode;
    private string _country;

    public string CustomerID
    {
        get
        {
            return this._customerID;
        }

        set
        {
            this._customerID = value;
        }
    }
    public string Address
    {
        get
        {
            return this._address;
        }
        set
        {
            this._address = value;
        }
    }
    public string City
    {
        get
        {
            return this._city;
        }
        set
        {
            this._city = value;
        }
    }
    public string Region
    {
        get
        {
            return this._region;
        }
        set
        {
            this._region = value;
        }
    }
    public string PostalCode
    {
        get
        {
            return this._postalCode;
        }
        set
        {
            this._postalCode = value;
        }
    }
    public string Country
    {
        get
        {
            return this._country;
        }
        set
        {
            this._country = value;
        }
    }
}

Translated URI

http://localhost:12345/TrainingService.svc/Customers()?$filter=Country eq 'Portugal'&$select=CustomerID,Address,City,Region,PostalCode,Country

 

Preventing unsupported exception due to NULL values

To consider when the projection includes navigation properties with high probability of contaning NULL values

How to do it

Example of an query in error:

var query =
    from book in context.Books
    select new {Title = book.Title, PublisherName = book.Publisher.Name};
An entry returned by the navigation property 'Publisher' is null and cannot be initialized. You should check for a null value before accessing this property.
How to use it

In order to avoid the prior error scenario reformulate query as described next:

var query =
    from book in context.Books
    select new
    {
        Title = book.Title,
        PublisherName = book.Publisher != null ? book.Publisher.Name : null,
        AgentName = book.Publisher != null ? (book.Publisher.Agent != null ? book.Publisher.Agent.Name : null) : null
    };
Preventing unsupported exception due to NULL values (II)
How to do it

Example of an query in error:

var query = from e in context.Employees
        select new Employee()
        {
            EmployeeID = e.EmployeeID,
            LastName = e.LastName,
            Manager = new Employee()
            {
                EmployeeID = e.Manager.EmployeeID,
                LastName = e.Manager.LastName,
            }
        };
An entry returned by the navigation property 'Manager' is null and cannot be initialized. You should check for a null value before accessing this property.

 

 

 

How to use it

In order to avoid the prior error scenario reformulate query as described next:

var query = from e in service.Employees
        select new Employee()
        {
            EmployeeID = e.EmployeeID,
            LastName = e.LastName,
            Manager = (e.Manager == null) ? null : new Employee()
            {
                EmployeeID = e.Manager.EmployeeID,
                LastName = e.Manager.LastName,
            }
        };
Queries with paging options
How to do it
var query = (from o in context.Orders
             orderby o.OrderDate descending
             select o).Skip(50).Take(25);

Translated URI:

http://localhost:12345/TrainingService.svc/Orders()?$orderby=OrderDate desc&$skip=50&$top=25

 

 

Queries with deferred selection options
How to do it
var query = from o in context.Orders.Expand("Order_Details")
            where o.CustomerID == "jseixas"
            select o;

Translated URI:

http://localhost:12345/TrainingService.svc/Orders()?$filter=CustomerID eq 'jseixas'&$expand=Order_Details
References

[1] Refer to here for the list of unsupported Linq methods
[2] Refer to here for OData supporting query options