Queries

Querying the data using LINQ

LINQ is the standard on querying the data on .NET platform. DataObjects.Net provides full-featured LINQ API for querying persistent objects.

Single endpoint: Session.Query property

Session.Query property provides a set of members allowing you to query the database using LINQ.

Session.Query.All<T>() method is the most important one. It creates and returns a new IQueryable<T> instance that allows you to get all entities assignable to type T.

var products = session.Query.All<Product>();
foreach (var product in products) {
  ...
}

Note : In this chapter we’ll often use term query as a synonym for IQueryable instance.

Unless session uses client profile you’ll need an active transaction to enumerate query results.

using (var session = domain.OpenSession()) {
  using (var t = session.OpenTransaction()) {
    var articles = from a in session.Query.All<Article>() select a;
    foreach (var article in articles) {
    ...
    }
  }
}

Each query is implicitly bound to a session that created it. To enumerate query result this session should not be disposed at the moment of query execution. The following example demostrates incorrect usage of query.

IQueryable<Product> products;
using (var session = domain.OpenSession()) {
  using (var t = session.OpenTransaction()) {
    products = session.Query.All<Product>();
  }
}
// Wrong! Session that created products queryable is already disposed here.
foreach (var p in products) {
}

The object returned by Session.Query.All() method implements IQueryable interface. This means you could use standard LINQ facilities to construct more complex queries.

var result =
  from e in session.Query.All<Employee>()
  where e.FullName!=null
  select e;

from p in session.Query.All<Product>()
select p.UnitsInStock * p.UnitPrice

Multiple IQueryable<T> instances could be used in the same query expression:

var result =
  from c in session.Query.All<Customer>()
  join o in session.Query.All<Order>()
    on c equals o.Customer into ords
  join e in session.Query.All<Employee>()
    on c.Address.City equals e.Address.City into emps
  select new {ords = ords.Count(), emps = emps.Count()};

Fetching a single entity

Session.Query provides two method groups allowing fetch a single entity:

• Single(...)

• SingleOrDefault(...)

These methods are pseudo-LINQ. They generally follow semantics of the standard Single and SingleOrDefault extension methods. However some differences exist. Regular LINQ queries always perform a database query. Pseudo-LINQ methods first try to find entity in session cache. If entity is found there it is returned immediately. Otherwise database query is performed.

Base class library methods and operators in queries

DataObjects.Net allows using many of base class library operators and methods in queries. Generally you can expect most of the members of the supported persistent types to work. Please see persistent fields section for information about supported types. All provided members are supported only for the specified types.

• Standard C# operators: == != < <= > >= || && ! ?? ?: + - * /

  IQueryable<Product> calculableFilter =
    session.Query.All<Product>()
      .Where(p => 100 + p.UnitPrice * p.UnitsInStock/100 >= 100);
  
  IQueryable<decimal> calculableColumn =
    session.Query.All<Product>()
      .Select(p => 100 + p.UnitPrice * p.UnitsInStock/100);
  

  IQueryable<Product> calculableFilter =
    session.Query.All<Product>().Where(p =>
      p.UnitPrice >= 100 && p.UnitPrice<=200 || !(p.ProductName=="Name"));
  
  IQueryable<bool> calculableColumn =
    session.Query.All<Product>().Select(p =>
      p.UnitPrice >= 100 && p.UnitPrice<=200 || !(p.ProductName=="Name"));
  

• Standard methods: Equals, ToString, IEquatable<T>.Equals, IComparable<T>.CompareTo.

• Decimal methods: Add, Compare, Divide, Parse, Remainder, Subtract

• Math type methods:

  • Arithmetics: Abs, Sign, Min, Max, BigMul, Exp, Log, Log10, PI, Pow, Sqrt

  • Roundings: Floor, Ceiling, Round, Truncate

  • Trigonometrics: Acos, Asin, Atan, Atan2, Sinh, Tan, Tanh, Cos, Cosh

• Nullable<T> properties: Value, HasValue

• String methods:

  • Methods: StartsWith, EndsWith, Contains, Substring, ToUpper, ToLower, Trim, TrimStart, TrimEnd, Length, ToString, Replace, Insert, Remove, IsNullOrEmpty, Concat, PadLeft, PadRight, Compare, CompareTo, IndexOf, Chars, Contains

  • Operators: [] +

  • StringExtensions extension methods: LessThan, LessThanOrEqual, GreaterThan, GreaterThanOrEqual, Like

• DateTime:

  • Extractors: Year, Month, Day, Hour, Minute, Second, Millisecond, TimeOfDay, Date, DayOfWeek, DayOfYear

  • Constructors: new DateTime(year, month, day), new DateTime(year, month, day, hour, minute, second, millisecond)

  • Methods: Add, Subtract, Now, Today, IsLeapYear, DaysInMonth,

• DateTimeOffset (By now, only MS Sql Server and Oracle providers have full support

  for the type due to existance of native type; PostgreSQL and SQLite have limited support as long as they lack native type which can store datetime with timezone):

  • Extractors: Year, Month, Day, Hour, Minute, Second, Millisecond, TimeOfDay, Date, DayOfWeek, DayOfYear, DateTime, UtcDateTime LocalDateTime

  • Constructors: new DateTime(year, month, day, hour, minute, second, millisecond, offset), new DateTime(year, month, day, hour, minute, second, offset), new DateTime(dateTime), new DateTime(dateTime, offset)

  • Methods: Add, Subtract, Now

• TimeSpan:

  • Constructors: new TimeSpan(days, hours, minutes, seconds, milliseconds), new TimeSpan(ticks), new TimeSpan(hours, minutes, seconds), new TimeSpan(days, hours, minutes, seconds)

  • Static construction methods: FromDays, FromHours, FromMinutes, FromSeconds, FromMilliseconds, FromTicks

  • Extractors: Milliseconds, Seconds, Minutes, Hours, Days

  • Converters: Ticks, TotalMilliseconds, TotalSeconds, TotalMinutes, TotalHours, TotalDays

  • Methods: Add, Subtract, Negate, Duration

EntitySet<T> operations

EntitySet<T> implements IQueryable<T>, so it is possible to use it as query root:

Customer customer = GetCustomer();
EntitySet<Order> orders = customer.Orders;
var result =
  from order in orders
  where order.Freight > 100
  select order;

GroupBy method returns IQueryable<IGrouping<TGroupingKey, TElement>>. Each IGrouping<TGroupingKey, TElement> implements IQueryable<TElement> as well, so it can be used as query root after explicit cast to IQueryable<TElement>. An alternative to explicit cast here is standard AsQueryable method – if source is queryable, it simply makes the cast we need:

IQueryable<IGrouping<decimal, Product>> groupings =
  from product in session.Query.All<Product>()
  group product by product.UnitPrice;

foreach (IGrouping<decimal, Product> grouping in groupings) {
  Log.Info("Unit Price {0}", grouping.Key);

  // Use IGrouping<TGroupingKey, TElement> as IQueryable<TElement>
  var categories =
    from product in grouping.AsQueryable()
    select product.Category.CategoryName;

  foreach (string category in categories)
    Log.Info("Category {0}", category);
}

Subquery result implements IQueryable<T> as well:

IQueryable<IQueryable<Order>> result =
  from customer in session.Query.All<Customer>()
  select // Create subquery
    from order in session.Query.All<Order>()
    where order.Customer==customer
    select order;

foreach (IQueryable<Order> orders in result) {
  var subQueryCount = orders.Count(); // Execute subquery.
  // ....
}

Projections to custom types

DataObjects.Net can materialize not just the objects of built-in types (Entity, Structure, etc.), but objects of generally any type:

var orders = from customer in session.Query.All<Customer>()
select new CustomOrder(customer.Id) {
  Country = customer.Address.Country,
  City = customer.Address.City
};

As you see, here we create a custom, non-persistent CustomerOrder type and set its two properties – in fact, by doing this we’re mapping these properties to data participating in query. DataObjects.Net maintains such mapping information for the whole query, so it is possible to use properties of custom materialized objects further in the query:

var filteredOrders =
  from order in
    from customer in session.Query.All<Customer>()
    select new CustomOrder(customer.Id) {
      Country = customer.Address.Country,
      City = customer.Address.City
    }
  where order.Country=="Russia"
  select order;

Local collections in queries

DataObjects.Net allow you to mix operations on local collections and server-side data in queries. Local collections are objects of IEnumerable<T>. While executing such a query, DataObjects.Net either creates a temporary table, persists the data extracted from local objects into it and uses it in SQL statement as any other table, or injects local collection content into generated SQL (e.g. into in IN (...)). The last case is used if collection is small enough and usage of IN or boolean expression with collection elements is possible.

// Local collection of decimals.
var freights = new List<decimal> {1, 234, 334, 554, 343, 753};

var result = from storageOrders in session.Query.All<Order>()
join freight in freights on storageOrders.Freight equals freight
select storageOrders;

Moreover, DataObjects.Net provides Session.Query.Store<T> method – it converts IEnumerable<T> to IQueryable<T>, so it allows use local collections as query roots. Such queryables support all the operations you can do with any other DataObjects.Net queryable.

As it was mentioned, underlying IEnumerable<T> will be persisted to storage as temporary table on execution of such query.

var freights = new List<decimal> {1, 234, 554, 343};
var localCollectionQuery = session.Query.Store<decimal>(freights);

// It's possible to use params T[]:
// var localCollectionQuery = session.Query.Store<decimal>(1, 234, 554, 343);

var result = from freight in localCollectionQuery
join storageOrders in session.Query.All<Order>()
  on freight equals storageOrders.Freight
select storageOrders;

IQueryable<T> extensions

DataObjects.Net extends IQueryable<T> type with a set of useful methods:

LeftJoin simplifies writing of left joins. The syntax is exactly the same as of standard Join method, the only difference is the way query is executed.

var territories = session.Query.All<Territory>();
var regions = session.Query.All<Region>();
var result = territories.LeftJoin(
  regions,
  territory => territory.Region.Id,
  region => region.Id,
  (territory, region) => new {
    territory.TerritoryDescription,
    region.RegionDescription
  });

Note that there is another way to write left join using standard LINQ syntax:

var result =
   from order in session.Query.All<Order>()
  join customer in session.Query.All<Customer>()
    on order.Customer equals customer into oc
  from joinedCustomer in oc.DefaultIfEmpty()
  select new {
    CustomerId = joinedCustomer.Id,
    joinedCustomer.CompanyName,
    joinedCustomer.Address.Country
  };

DataObjects.Net LINQ translator recognizes this way. But we decided to add LeftJoin to make the syntax more explicit.

Lock adds locking hints to the underlying query:

var customers = session.Query.All<Customer>()
 .Where(c => c.Key == key)
 .Lock(LockMode.Update, LockBehavior.Skip);

In checks if value is contained in specified list of values. In fact, it is an alternative syntax for Contains. The following two queries are equal:

var customers = from c in session.Query.All<Customer>()
where c.Id.In("FISSA", "PARIS")
select c.Orders;

var customers = from c in session.Query.All<Customer>()
where new List<string> {"FISSA", "PARIS"}.Contains(c.Id)
select c.Orders;

AsAsync runs query to database asynchronously. For IQueryable<T> which are not created by DataObjects.Net (e.g. Enumerable.Range(0, 12).AsQueryable()) it returns completed task.

var customers = await session.Query.All<Customer>()
  .Where(c => c.Key == key).AsAsync();

Note that though it returns Task<IEnumerable<Customer>> it should not be executed in parallel with another action with persistent objects (creation, getting or setting field values, removing, executing another synchronous or asynchronous query, etc.), so we strongly recommend you to await results right away. A wrong usage example:

var customersTask = session.Query.All<Customer>()
  .Where(c => c.Key == key).AsAsync();

var products = await session.Query.All<Product>().
  .Where(p => p.Name.Contains("Beans")).AsAsync();

var customers = await customers;

As an exception you can postpone awaiting to do some work which is not connected to DataObjects.Net, for instance:

var customersTask = session.Query.All<Customer>()
  .Where(c => c.Key == key).AsAsync();

using(var streamReader = new StreamReader(fileName, System.Text.Encoding.UTF8)) {
  var fileContent = await streamReader.ReadToEndAsync();
  streamReader.Close();
  await ProcessContent(fileContent);
}

var customers = await customersTask;

Compiled queries

Query compilation takes time. If performance is really important, it is possible to compile the query once and execute it multiple times with different parameters afterward. This could decrease average execution time of a query by up to 10 times.

Session.Query.Execute method allows to compile and run such compiled query:

private IEnumerable<Employee> GetEmployees(DateTime date)
{
  return session.Query.Execute(q =>
    from employee in q.All<Employee>()
    where employee.BirthDate < date
    select employee);
}

Only the first call to GetEmployees method will cause query compilation. Subsequent execution of this method will lead to execution of previously compiled query, but with new parameters.

There is asynchronous version as well:

private async Task<IEnumerable<Employee>> GetEmployeesAsync(DateTime date)
{
  return await session.Query.ExecuteAsync(q =>
    from employee in q.All<Employee>()
    where employee.BirthDate < date
    select employee);
}

Unfortunately, LINQ has some limitation related to Take, Skip, ElementAt, ElementAtOrDefault in such scenarios: all of them allow to pass only an integer number as parameter, but not an expression. This number is always “encoded” as constant in LINQ expression our translator gets, thus there is no chance to change the value after the query has been compiled.

But DataObjects.Net provides its own Take, Skip, ElementAt, ElementAtOrDefault extension methods with parameter of type Expression<Func<int>> instead of int. This solution allows to work around this issue.

The first method of the following example throws an exception, but the second one works properly.

private IEnumerable<Customer> TakeCustomersIncorrect(int amount)
{
  return session.Query.Execute(q => q.All<Customer>().Take(amount));
}

private IEnumerable<Customer> TakeCustomersCorrect(int amount)
{
  return session.Query.Execute(q => q.All<Customer>().Take(() => amount));
}

IQueryable surface support map

The following IQueryable extension methods are fully supported by DataObjects.Net LINQ translator except the overloads that take IComparer or IEqualityComparer as an argument:

All Any AsQueryable Average Concat Contains Count Distinct
Except First FirstOrDefault GroupBy GroupJoin Intersect Join
LongCount Max Min OfType OrderBy OrderByDescending Reverse
Select SelectMany Single SingleOrDefault Sum
ThenBy ThenByDescending Union Where

Paging operations (ElementAt ElementAtOrDefault Take Skip) are supported in regular queries only. These methods are not supported in compiled queries. DataObjects.Net-specific overloads that work in compiled queries are available. These overloads require Func<int> argument instead of int and could be used in regular queries as well as compiled queries.

DefaultIfEmpty is supported in special scenario when it is used with SelectMany:

from customer in session.Query.All<Customer>()
from order in session.Query.All<Order>()
  .Where(o => o.Customer==customer)
  .DefaultIfEmpty()
select {customer, order}

DataObjects.Net does not support any LINQ method that takes IComparer or IEqualityComparer as an argument. Also the following methods are not supported:

Aggregate Cast TakeWhile SkipWhile SequenceEqual Last LastOrDefault

Joins, casts, references, inheritance and subqueries in LINQ

Joins

LINQ provides standard Join extension method that normally translates to SQL as inner join. The following query:

var result =
  from product in session.Query.All<Product>()
  join supplier in session.Query.All<Supplier>() on product.Supplier.Id
    equals supplier.Id
  select new {product.ProductName, supplier.ContactName, supplier.Phone};

will be translated to the following SQL:

SELECT [a].[ProductId],
       [a].[TypeId],
       [a].[ProductName],
       [b].[SupplierId]  AS [#a.SupplierId],
       [b].[TypeId]      AS [#a.TypeId],
       [b].[ContactName] AS [#a.ContactName],
       [b].[Phone]       AS [#a.Phone]
FROM   [dbo].[Products] [a]
       INNER JOIN [dbo].[Suppliers] [b]
         ON ([a].[Seller.SupplierId] = [b].[SupplierId])

LeftJoin method

LINQ does not provide standard method for left join, although it is possible to describe it using other query operations. But, as you’ll see below, the sequence you must write to describe left join is pretty complicated. DataObjects.Net provides its own LeftJoin extension method for IQueryable<T> to make left join syntax more clear.

The syntax of LeftJoin is the same as syntax for standard Join, but obviously, it can be used as extension method only:

var result = session.Query.All<Product>()
  .LeftJoin(session.Query.All<Supplier>(),
    product => product.Supplier.Id,
    supplier => supplier.Id,
    (product, supplier) => new {
      product.ProductName,
      supplier.ContactName,
      supplier.Phone
    });

And the result of the LeftJoin is the similar to standard Join but join is left instead of inner:

SELECT [a].[ProductId],
       [a].[TypeId],
       [a].[ProductName],
       [b].[SupplierId]  AS [#a.SupplierId],
       [b].[TypeId]      AS [#a.TypeId],
       [b].[ContactName] AS [#a.ContactName],
       [b].[Phone]       AS [#a.Phone]
FROM   [dbo].[Products] [a]
       LEFT OUTER JOIN [dbo].[Suppliers] [b]
         ON ([a].[Seller.SupplierId] = [b].[SupplierId])

GroupJoin+SelectMany+DefaultIfEmpty pattern translation

As it was mentioned, LINQ does not provide standard LeftJoin method, but it is possible to define left join using GroupJoin, SelectMany and DefaultIfEmpty methods:

var result = from order in session.Query.All<Order>()
             join customer in session.Query.All<Customer>()
               on order.Customer equals customer into oc
             from joinedCustomer in oc.DefaultIfEmpty()
             select new {
               CustomerId = joinedCustomer.Id,
               joinedCustomer.CompanyName,
               joinedCustomer.Address.Country
             };

Translation result for this query is the same as for query with LeftJoin:

SELECT [a].[CustomerId]      AS [#c.CustomerId],
       [a].[TypeId]          AS [#c.TypeId],
       [a].[CompanyName]     AS [#c.CompanyName],
       [a].[Address.Country] AS [#c.Address.Country]
FROM   [DBO].[ORDER] [b]
       LEFT OUTER JOIN [DBO].[Customers] [a]
         ON ([b].[Customer.CustomerId] = [a].[CustomerId])

Type casts

Entity safe cast (as operator for Entity descendants) is translated with use of left join:

var result = session.Query.All<Product>()
  .Select(product => product as DiscontinuedProduct);

SELECT [a].[ProductId]           AS [#a.ProductId],
       [a].[TypeId]              AS [#a.TypeId],
       [a].[ProductName]         AS [#a.ProductName],
       [a].[Seller.SupplierId]   AS [#a.Seller.SupplierId],
       [a].[Category.CategoryId] AS [#a.Category.CategoryId],
       [a].[ProductType]         AS [#a.ProductType],
       [a].[UnitPrice]           AS [#a.UnitPrice],
       [a].[UnitsInStock]        AS [#a.UnitsInStock],
       [a].[UnitsOnOrder]        AS [#a.UnitsOnOrder],
       [a].[ReorderLevel]        AS [#a.ReorderLevel],
       [a].[QuantityPerUnit]     AS [#a.QuantityPerUnit]
FROM   [dbo].[Products] [b]
       LEFT OUTER JOIN [dbo].[Products] [a]
         ON ([b].[ProductId] = [a].[ProductId])
WHERE  ([a].[TypeId] IN (107))

References

DataObjects.NET uses left join to provide access to fields of referenced Entity:

var result = session.Query.All<Product>().Select(p => p.Category.CategoryName);

SELECT [a].[CategoryId]   AS [#a.CategoryId],
       [a].[TypeId]       AS [#a.TypeId],
       [a].[CategoryName] AS [#a.CategoryName]
FROM   [dbo].[Products] [b]
       LEFT OUTER JOIN [dbo].[Categories] [a]
         ON ([b].[Category.CategoryId] = [a].[CategoryId])

Inheritance joins

Joins are also used to gather the date from inheritance hierarchies. For example, if InheritanceSchema.ClassTable is used in a particular hierarchy, fields of any hierarchy root descendant are spanned over multiple tables there. So to gather the whole field set for a particular type, DataObjects.Net will use joins.

Let’s assume Employee is inherited form Person in hierarchy with InheritanceSchema.ClassTable. So the following query:

var employees = session.Query.All<Employee>();

will be translated with inner join of two tables:

SELECT [a].[Id],
       [a].[TypeId],
       [a].[Name],
       [b].[Salary]
FROM   (SELECT [c].[Id],
               [c].[TypeId],
               [c].[Salary]
        FROM   [dbo].[Employee] [c]) [b]
        INNER JOIN (SELECT [d].[Id],
                           [d].[TypeId],
                           [d].[Name]
                    FROM   [dbo].[Person] [d]) [a]
         ON ([a].[Id] = [b].[Id]);

Subqueries

Subqueries are either expressions of IQueryable<T> type inside projections of primary query, or aggregates calculated over such expressions.

If subquery is a part of final projection (final .Select() call), it is executed separately for each query result item during enumeration of result of original query. So in this case:

var query = session.Query.All<Person>().Select(
   employee => new {
     employee,
     Namesakes = session.Query.All<Person>()
       .Where(person => person.Name==employee.Name)
   });

// Enumerate query
foreach (var employeeData in query) {
  // Enumerate each subquery element
  foreach (Person namesake in employeeData.Namesakes) {
    // Do something with employee, namesake
  }
}

SELECT [a].[Id],
       [a].[TypeId],
       [a].[Name]
FROM   [dbo].[Person] [a];

SELECT [a].[Id],
       [a].[TypeId],
       [a].[Name]
FROM   [dbo].[Person] [a]
WHERE  ([a].[Name] = 'John');

SELECT [a].[Id],
       [a].[TypeId],
       [a].[Name]
FROM   [dbo].[Person] [a]
WHERE  ([a].[Name] = 'Susan');

Highlighted part is SQL corresponding to the main query; a set of SELECTs below are subqueries performed for each item returned by the main query.

Similar subqueries are produced for GroupBy method results: IGrouping<TKey, TElement> is also treated as a subquery.

If subquery returns scalar result, it is evaluated as part of original SQL query. In particular, this happen if subquery method chain ends with: Contains Any All Sum Average Min Max First FirstOrDefault Single SingleOrDefault

The following example illustrates subquery with All method:

var result = session.Query.All<Customer>()
  .Where(c => session.Query.All<Order>()
    .Where(o => o.Customer==c)
    .All(o => o.ShippingAddress.City==c.Address.City));

SELECT [a].[CustomerId],
       [a].[TypeId],
       [a].[CompanyName],
       [a].[ContactName],
       [a].[ContactTitle],
       [a].[Address.StreetAddress],
       [a].[Address.City],
       [a].[Address.Region],
       [a].[Address.PostalCode],
       [a].[Address.Country],
       [a].[Phone],
       [a].[Fax]
FROM   [dbo].[Customers] [a]
WHERE  (NOT EXISTS(
  SELECT *
  FROM   [dbo].[Order] [b]
  WHERE  (
     ([b].[Customer.Id] = [a].[CustomerId])
     AND
     (NOT ([b].[ShippingAddress.City] = [a].[Address.City])))
  ));

Full-text queries

DataObjects.Net allows to build full-text indexes for domain entities and execute full-text queries against database. As of DataObjects.Net 4.6, the feature is implemented for SQL Server and PostgreSQL.

Configuring full-text indexes

DataObjects.Net simplifies the task of creating full-text indexes by providing the corresponding FullText attribute, that should be applied to indexed fields. It requires one mandatory argument - the language which will be used to instruct database server to choose the appropriate resource for word breaking, stemming, and thesaurus and stopword removal as part of the query.

public class Beverage : Entity
{
  [Field, Key]
  public int Id { get; private set; }

  [Field]
  [FullText("English")]
  public string Name { get; set; }
}

Querying against full-text data

Endpoint for full-text queries is Session.Query.FreeText<TEntity>(string searchCriteria)` method. But instead of returning ``IQueryable<TEntity> it returns IQueryable<FullTextMatch<TEntity>>. FullTextMatch<TEntity> is a simple class containing 2 fields: Entity and its Rank according to full-text index and search criteria.

      // Create some beverages
      new Beverage(session) {
        Name = "Sherbet Hawiian Punch"
      };
      new Beverage(session) {
        Name = "Miami-Fusion Virgin Mojito"
      };

      new Beverage(session) {
        Name = "Guava Ginger Punch"
      };

      // Query for 'punch guava' and order by rank desc
      var matches = session.Query.FreeText<Beverage>("punch guava")
        .OrderByDescending(i => i.Rank);
      foreach (var match in matches) {
        Console.WriteLine(match.Rank);
        Console.WriteLine(match.Entity.Name);
      }

      // Limit the number of matches by some rank
      var matches = session.Query.FreeText<Beverage>("punch guava")
        .Where(i => i.Rank > 0.15)
        .OrderByDescending(i => i.Rank);