Export (0) Print
Expand All
1 out of 7 rated this helpful - Rate this topic

Writing LINQ Queries Against the Table Service

Updated: February 10, 2014

You can write queries against the Table service using LINQ syntax. The following examples show how to write sample queries similar to the sample queries shown in Querying Tables and Entities, but using LINQ instead of the REST protocol.

The Table service supports executing simple queries that retrieve all properties of an entity; it's not possible to select a subset of an entity's properties. The Table service also supports filtering query results using the Where operator, and specifying how many entities to return using the Take operator.

For details about which LINQ operators are supported by the Table service, see Query Operators Supported for the Table Service.

Projecting Entity Properties

The LINQ select clause can be used to project a subset of properties from an entity or entities. The maximum number of properties that can be projected is 255, which is also the maximum number of properties in an entity.

To project an entity’s properties, the client must support OData Data Service Version 2.0, indicated by specifying either the DataServiceVersion or MaxDataServiceVersion headers as follows:

DataServiceVersion: 2.0;NetFx
MaxDataServiceVersion: 2.0;NetFx

The following example demonstrates how to project properties from a single entity, using the required object initializer:

var query = from entity in dataServiceContext.CreateQuery<SampleEntity>(tableName)
                 where entity.PartitionKey == "MyPartitionKey"
                 select new { entity.RowKey };

The following example projects 3 properties from an entity that has 10 properties. In this example, SampleEntity’s 10 properties are letters from A through J:

IEnumerable<SampleEntity> query = from entity in
                                       dataServiceContext.CreateQuery<SampleEntity>(tableName)
                                       where entity.PartitionKey == "MyPartitionKey"
                                       select new SampleEntity
                                      {
                                          PartitionKey = entity.PartitionKey,
                                          RowKey = entity.RowKey,
                                          A = entity.A,
                                          D = entity.D,
                                          I = entity.I
                                      };

Single entity projection is not supported. The REST request produced by the following code is invalid:

var query = from entity in dataServiceContext.CreateQuery<SampleEntity>(tableName)
                 where entity.PartitionKey == "MyPartitionKey"
                 select { entity.RowKey }; // this code is invalid!

You can also project entity properties by using the $select query option in a standard REST request. For more information, see Query Entities (REST API).

For more information on entity projections and transformations, see Select System Query Option ($select) in the OData documentation.

Returning the Top n Entities

To return n entities, use the LINQ Take operator. Note that the maximum number of entities that may be returned in a single query is 1,000. Specifying a value greater than 1,000 for the Take operator results in error code 400 (Bad Request).

The following example returns the top 10 entities from a Customers table:

var query = (from entity in context.CreateQuery<Customer>("Top10Customers")
                 select entity).Take(10);

Filtering on String Properties

The following example filters on two String properties:

var query = from entity in context.CreateQuery<Customer>("SpecificCustomer")
                 where entity.LastName.Equals("Smith")
                 && entity.FirstName.Equals("John")
                 select entity;

The following example performs prefix matching using comparison operators to return entities with a LastName property beginning with the letter 'A':

var query = from entity in context.CreateQuery<Customer>("CustomersA")
                 where entity.LastName.CompareTo("A") >= 0
                 && entity.LastName.CompareTo("B") < 0
                 select entity;

Filtering on Numeric Properties

The following example returns all entities with an Age property whose value is greater than 30:

var query = from entity in context.CreateQuery<Customer>("CustomersOver30")
                 where entity.Age > 30
                 select entity;

This example returns all entities with an AmountDue property whose value is less than or equal to 100.25:

var query = from entity in context.CreateQuery<Customer>("CustomersAmountDue")
                 where entity.AmountDue <= 100.25
                 select entity;

Filtering on Boolean Properties

The following example returns all entities where the IsActive property is set to true:

var query = from entity in context.CreateQuery<Customer>("ActiveCustomers")
                 where entity.IsActive == true
                 select entity;

Filtering on DateTime Properties

The following example returns entities where the CustomerSince property is equal to July 10, 2008:

DateTime dt = new DateTime(2008, 7, 10);
var query = from entity in context.CreateQuery<Customer>("CustomerSince")
                 where entity.CustomerSince.Equals(dt)
                 select entity;

See Also

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.