Astoria query Cheat Sheet

Samples Data Model:

image

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.

Navigation1

public void Test()
{
    var q = from u in db.Users
            where u.UserID == 2
            from en in u.Entries
            select en;
    //http://localhost:7777/RestService.svc/Users(2)/Entries()
}

Navigation2

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;
    Console.WriteLine(q3.ToString());
    // 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
[WebGet]
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 (http://code.msdn.microsoft.com/EFExtensions), 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).

[WebGet]
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.
ctx.SaveChanges();

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

One Response to Astoria query Cheat Sheet

  1. fast says:

    http://www.batteryfast.com.au/hp/pavilion-dv9000.htm HP pavilion dv9000 Batteryhttp://www.batteryfast.com.au/hp/pavilion-dv9600.htm HP pavilion dv9600 Batteryhttp://www.batteryfast.com.au/hp/pavilion-dv2000.htm HP pavilion dv2000 Batteryhttp://www.batteryfast.com.au/hp/pavilion-dv6000.htm HP pavilion dv6000 Batteryhttp://www.batteryfast.com.au laptop batteryhttp://www.batteryfast.com.au laptop batterieshttp://www.batteryfast.com.au/dell/ dell laptop batteryhttp://www.batteryfast.com.au/dell/ dell laptop batterieshttp://www.batteryfast.com.au/hp/ hp laptop batteryhttp://www.batteryfast.com.au/hp/ hp laptop batterieshttp://www.batteryfast.com.au/dell/ dell laptop batteryhttp://www.batteryfast.com.au/dell/ dell laptop batterieshttp://www.batteryfast.com.au/laptop-ac-adapter/dell/ dell laptop ac adapterhttp://www.batteryfast.com.au/laptop-ac-adapter/ dell laptop ac adaptershttp://www.batteryfast.com.au/laptop-ac-adapter/dell/ dell laptop ac adapterhttp://www.batteryfast.com.au/laptop-ac-adapter/dell/ dell laptop ac adaptershttp://www.batteryfast.com.au/laptop-ac-adapter/dell/ dell laptop ac adapterhttp://www.batteryfast.com.au/laptop-ac-adapter/dell/ dell laptop ac adaptershttp://www.batteryfast.com.au/laptop-ac-adapter/ laptop ac adapterhttp://www.batteryfast.com.au/laptop-ac-adapter/ laptop ac adaptershttp://www.batteryfast.com.au/hp/500.htm Hp 500 Batteryhttp://www.batteryfast.com.au/mitac/mim2120.htm Mitac mim2120 Batteryhttp://www.batteryfast.com.au/laptop-ac-adapter/acer/acer-19V-7.1A-135w-5.5mm-2.5mm.php Acer Aspire 1520 1620 19V 7.1A 135W 5.5MM*2.5MM Adapter http://www.batteryfast.com.au/laptop-ac-adapter/liteon/nx9500.htm Liteon nx9500 Adapter http://www.batteryfast.com.au/compaq/presario-v4000.htm Compaq presario v4000 Battery http://www.batteryfast.com.au/toshiba/pa3356u-3bas.htm Toshiba pa3356u-3bas Battery http://www.batteryfast.com.au/toshiba/pa3400u-1brs.htm Toshiba pa3400u-1brs Battery http://www.batteryfast.com.au/toshiba/pa3400u-1bas.htm Toshiba pa3400u-1bas Battery http://www.batteryfast.com.au/apple/a1148.htm Apple a1148 Battery http://www.batteryfast.com.au/asus/a32-f3.htm Asus a32-f3 Battery http://www.batteryfast.com.au/asus/f3.htm Asus f3 Battery http://www.batteryfast.com.au/toshiba/pa3285u-3bas.htm Toshiba pa3285u-3bas Battery http://www.batteryfast.com.au/toshiba/pa3285u-3brs.htm Toshiba pa3285u-3brs Battery http://www.batteryfast.com.au/dell/1501.htm Dell 1501 Battery http://www.batteryfast.com.au/dell/6400.htm Dell 6400 Battery http://www.batteryfast.com.au/dell/d620.htm Dell d620 Battery http://www.batteryfast.com.au/dell/d630.htm Dell d630 Battery http://www.batteryfast.com.au/dell/d820.htm Dell d820 Battery http://www.batteryfast.com.au/dell/d830.htm Dell d830 Batteryhttp://www.batteryfast.com.au/dell/inspiron-1520.htm Dell inspiron 1520 Batteryhttp://www.batteryfast.com.au/dell/inspiron-1720.htm Dell inspiron 1720 Batteryhttp://www.batteryfast.com.au/hp/nc6000.htm Hp nc6000 Batteryhttp://www.batteryfast.com.au/hp/nc6100.htm Hp nc6100 Batteryhttp://www.batteryfast.com.au/hp/nc6120.htm Hp nc6120 Batteryhttp://www.batteryfast.com.au/hp/nc6120.htm Hp nc6200 Battery

Comments are closed.