Astoria query Cheat Sheet

Samples Data Model:


Query Samples:

Below are some samples of using Astoria to query against the model.  I tried to hit the most popular that will hit most of the features, but far from complete.  Please let me know others you would like to see and I will keep adding.

var db = SocialEntities.GetDB();  // Get the DataContext.


public void Test()
    var q = from u in db.Users
            where u.UserID == 2
            from en in u.Entries
            select en;


public void Test()
    var q3 = (from u in db.Users
              where u.UserID == 2
              from e in u.Entries
              where e.EntryID > 1
              select e).Take(10);
    //http://localhost:7777/RestService.svc/Users(2)/Entries()?$filter=EntryID gt 1&$top=10

String filter

public void Test()
    var q2 = from u in db.Users
             where u.Tag.Contains("will") || u.Tag.EndsWith("ne")
             select u;
    //http://localhost:7777/RestService.svc/Users()?$filter=substringof('will',Tag) or endswith(Tag,'ne')

Reverse Navigation

public void Test()
    var q4 = from e in db.Entries
             where e.EntryID == 85 // Must be 1 primary key. Range not supported.
             select e.Users1; // Get the user that owns this entry.
    // http://localhost:7777/RestService.svc/Entries(85)/Users1

Date filter

public void Test()
    var q5 = (from u in db.Users
              where u.Created >= DateTime.Parse("10/1/05") && u.Created <= DateTime.Parse("11/1/09")
              select u).Take(5);
    //http://localhost:7777/RestService.svc/Users()?$filter=(Created ge datetime'2005-10-01T00:00:00') and (Created le datetime'2009-11-01T00:00:00')&$top=5

Using Expand on Service operation

This example shows a couple concepts. It shows how one can use Expand on a service operation, and it shows how we can continue to "compose" over query.  Here we use a service operation that takes 1 parameter, but we could use more or none.  Linq is used to "build" the uri query, which is then executed by the .Net client stack, which is sent to the server.  The servers gets the IQueryable from the method and composes over it (i.e. adds the filters and the Expand) using the client’s query and returns the results.

private void Test()
    // Using Expand on a service operation proxy.
    var db = SocialEntities.GetDB();
    var q3 = from u in db.GetUsers("").Expand("Entries")
             where u.Tag == "william"
             select u;
    // URI: http://localhost:7777/RestService.svc/GetUsers()?$filter=Tag eq 'william'&$expand=Entries&pattern=''
    var user = q3.First();
    Console.WriteLine("{0} {1}", user.Tag, user.Entries.Count() );

// Client proxy method.
public DataServiceQuery<Users> GetUsers(string pattern)
    var q = this.CreateQuery<Users>("/GetUsers").AddQueryOption("pattern", "'" + pattern + "'");
    return q;

// Service operation
public IQueryable<Users> GetUsers(string userPattern)
    if (userPattern == null || userPattern.Length == 0)
        return this.CurrentDataSource.Users;
    userPattern = userPattern.Replace('*', '%');
    userPattern = userPattern.Replace('?', '_');
    string filter = string.Format("it.Tag LIKE '{0}'", userPattern);
    var q = this.CurrentDataSource.Users.Where(filter).Select(u => u);
    return q;

Limit number of "Expanded" items on the many side (rSide)

TMK, you can’t use Expand to limit the number items on the many side.  However, you can reverse the query to get the same effect.

public void Test()
    // Take X from many side and expand the 1 side.
    var q2 = (from en in db.Entries.Expand("Users")
              where en.Users.Tag == "william"
              orderby en.Created descending
              select en).Take(1);
    foreach (var en in q2)
        Console.WriteLine("{0} {1} {2}", en.EntryID, en.Created, en.Users.Tag);


Return "partial" types from server using Service Operation and EFExtentions to "hide" data:

Using the Entity Framework Extentions (, you can materialize entities using all or some of the fields. Here we just return ID and Firstname from the database. One drawback here is you can not use Expand on the client side when using Materialize this way. Not sure why yet, but I assume it has something to do with the way it creates Entity Collections (or not).

public IQueryable<Employees> GetEmployee(string empID)
    // Note we create a partial employee with only selected columns, so we can return
    // just the data we need.  The rest will be the default values of the types.
    string sql = string.Format("select EmployeeID, FirstName from Employees where EmployeeID='{0}'", empID);
    var cmd = this.CurrentDataSource.CreateStoreCommand(sql);
    var q = cmd.Materialize<Employees>();
    return q.AsQueryable();


Update entity without query first:

You can skip the initial query to download the object just to update it by attaching a client-side object and setting the key property.

MyDataServiceContext ctx = new MyDataServiceContext(myServiceRoot);
Customer cust = new Customer();
cust.ID = 1;                    // Set primary key for object tracking.
ctx.AttachTo("Customer", cust); // Attache customer to context.
cust.Name = "Bill";             // Update members of object.
ctx.UpdateObject( cust );       // Set for update.

This entry was posted in C#. Bookmark the permalink.

One Response to Astoria query Cheat Sheet

  1. fast says: HP pavilion dv9000 Battery HP pavilion dv9600 Battery HP pavilion dv2000 Battery HP pavilion dv6000 Battery laptop battery laptop batteries dell laptop battery dell laptop batteries hp laptop battery hp laptop batteries dell laptop battery dell laptop batteries dell laptop ac adapter dell laptop ac adapters dell laptop ac adapter dell laptop ac adapters dell laptop ac adapter dell laptop ac adapters laptop ac adapter laptop ac adapters Hp 500 Battery Mitac mim2120 Battery Acer Aspire 1520 1620 19V 7.1A 135W 5.5MM*2.5MM Adapter Liteon nx9500 Adapter Compaq presario v4000 Battery Toshiba pa3356u-3bas Battery Toshiba pa3400u-1brs Battery Toshiba pa3400u-1bas Battery Apple a1148 Battery Asus a32-f3 Battery Asus f3 Battery Toshiba pa3285u-3bas Battery Toshiba pa3285u-3brs Battery Dell 1501 Battery Dell 6400 Battery Dell d620 Battery Dell d630 Battery Dell d820 Battery Dell d830 Battery Dell inspiron 1520 Battery Dell inspiron 1720 Battery Hp nc6000 Battery Hp nc6100 Battery Hp nc6120 Battery Hp nc6200 Battery

Comments are closed.