Skip to content
This repository has been archived by the owner on Oct 8, 2020. It is now read-only.

Querying

Jump to bottom
danielwertheim edited this page Dec 22, 2012 · 29 revisions

Result as Entities, JSON or Anonymous types

Most query functions in the API has as three versions of every query/get functions:

  • One returning JSON
  • One returning the entity
  • One returning an entity of T
  • One returning an anonymous type (useful for projection)

Since the structue is stored as JSON in the database the API with least overhead is the get/query functions returning JSON.

GetIds

If you just want to lookup StructureIds use session.GetIds or db.UseOnceTo().GetIds. That way you will not have to deserialize the structure to a certain entity and then extract the Id.

GetById, GetByIdAs & GetByIdAsJson

Straightforward functions for returning a single item by Id, where the Id can be either an Integer or a Guid. If you have enabled caching, the cache will be consumed.

using (var session = Db.BeginSession())
{
    var item = session.GetById<Order>(id);
    ...
    ...
}
using (var session = Db.BeginSession())
{
    var item = session.GetByIdAsJson<Order>([Guid|Integer]);
    ...
    ...
}

or using the UseOnceTo() API, which is intended for a single read only. Read more about it here.

var item = db.UseOnceTo().GetById<Order>(id);
var json = db.UseOnceTo().GetByIdAsJson<Order>(id);

GetByIds, GetByIdsAs & GetByIdsAsJson

This function takes an enumerable collection of either integers or Guids (depending on selected id) and returns the matching structures, either as entities or as JSON.

using (var session = Db.BeginSession())
{
    var result = session.GetByIds<Order>(new[] {1, 2, 3, 4, 5});
    ...
    ...
}

The example above, would return the structures having av value 1, 2, 3, 4 or 5 as their StructureId. If no matches are found, you will get an empty enumerable result.

Query

The Query API tries to mimic the normal IQueryable-interface and lets you pass in expressions that are turned in to SQL-queries against the [x]Indexes-table, e.g OrderIndexes. The generated SQL uses parameters to protect you from injection attacks.

Query supports:

  • Take
  • Where
  • OrderBy
  • Include
  • Page (for serverside paging)

Where - Qx - Extension methods for querying

Import the namespace SisoDb so that the supported query extension methods gets available. The methods that are supported starts with the prefix Qx.

Where - Operators

  • =
  • !=
  • <, <=

  • , >=

Where - String functions

Collations

Please note that string-functions taking casing into concern also gets affected on the setting of your database collation. E.g If Collation is case-insensitive and you use QxToLower or ToLower, that will not have an effect. In this case, performance wise, you would also be better of not using it.

  • QxAny(string|valuetype|memberexpression)
  • QxIn(string|valuetype|string[]|valuetype[])
  • QxNotIn(string|valuetype|string[]|valuetype[])
  • QxContains(string)
  • QxStartsWith(string)
  • QxEndsWith(string)
  • QxToLower() == "string"
  • QxToUpper() == "string"
  • QxLike(string) - supports like exression characters as: "%" and "?".
  • QxIsExactly(string) - performs a varbinary compare. Note! Read about collations above. This has negative effect on performance, hence use only when you really need exact compare.
  • Contains(string)
  • StartsWith(string)
  • EndsWith(string)
  • ToLower() == "string"
  • ToUpper() == "string"

Where - Entity member comparision

  • Same datatypes, e.g x => x.String1 == x.String2

Where - Other functions

On variables or members on params, not members in the database.

  • [param].Parse(value), e.g Int.Parse - evals as value
  • [param].Parse(value, IFormatProvider), e.g Int.Parse - evals as value
  • [param].ToString() - evals as value

Where - Other members

  • Static fields, e.g String.Empty - evals as value
  • Static properties, e.g. DateTime.Now - evals as value

Where - Examples

using (var session = Db.BeginSession())
{
    var result = session.Query<Order>()
        .Where(o => o.Amount >= 200 && o.Amount <= 300);
    ...
    ...
}

QxAny - To query nested arrays

var result = Database.UseOnceTo().Query<Order>()
    .Where(o => o.Lines.QxAny(l => l.ProductNo == "P123" && l.Quantity > 1));

QxIn - for matching member X in set Y

var result = Database.UseOnceTo().Query<Order>()
    .Where(o => o.Lines.QxAny(l => l.ProductNo.QxIn("P123", "P124)));

Skip & Take

Lets you limit the number of rows to return. Note! If you use Skip and/or Take in conjunction with Page, Page will take precedence.

OrderBy, OrderByDescending

You can pass in several sort expressions which will be turned into simple order by statements.

Page

You can make use of effective fetching against Sql-server to fetch only the interval of rows needed by letting it generate common table expressions (CTE) (for SQLCE, offset and fetch) and make use of row_number function. You do this by calling Page on the querycommand builder.

Examples of Query

using (var session = Database.BeginSession())
{
    var result = session.Query<Order>()
        .Take(10)
        .Where(o => o.Amount >= 200 && o.Amount <= 300)
        .OrderBy(o => o.Amount, o => o.OrderDate.Desc())
        .Page(pageIndex: 2, pageSize: 10);
    ...
    ...
}

GetByIdAs & Query.ToListOf - Store as X return as Y

In this case you can have stored an entity using type X and read it back to type Y with only members matching. Read more here: Store as X return as Y

Clone this wiki locally