LINQ support for DDS

13 min

Dynamic Data Store (DDS) supports Language Integrated Query (LINQ) for both typed stores and property bags. LINQ lets you filter, sort, group, and project data stored in DDS using familiar C# query syntax.

The LINQ support is the same for typed stores and for property bags.

Conditions

Filter query results by applying Where clauses to inline types.

Where is supported on inline types, regardless of whether the inline types are directly on the queried object or nested inside another object.

var query = (from person in _personStore.Items<Person>() where person.Address.City == "Stockholm"
  select person);

Order by

Sort query results using Order by and Then by on inline types.

Order by and Then by are supported for inline types, regardless of whether the inline types are directly on the queried object or nested inside another object.

var query = (from person in _personStore.Items<Person>() orderby person.Address.City
  select person);

Select

Project query results to return full objects or specific properties.

To receive the whole object in the store, use the select keyword.

var query = (from person in _personStore.Items<Person>() select person);

Return an anonymous type from the store by using the new keyword.

var query = (from person in _personStore.Items<Person>() select new {
  person.FirstName, person.LastName
});

Enumerations

Perform aggregate and containment operations on enumeration properties within stored objects.

If an object contains an enumeration of an inline type, DDS supports the following operations on the enumeration:

Max(), Min(), and Average() are supported without predicates.

Count() is supported with and without predicates.

Contains() is supported with a predicate.

The predicates are only supported when the predicate queries an inline type.

var queryMax = (from person in _personStore.Items<Person>() select person.List.Max());
var queryMin = (from person in _personStore.Items<Person>() where person.List.Min() < 10 select person);
var queryAverage = (from person in _personStore.Items<Person>() select person.List.Average());
var queryCount = (from person in _personStore.Items<Person>() where person.List.Count == 2 select person);
var queryCountWithPredicate = (from person in _personStore.Items<Person>() select person.Address.Count(p => p.Street == "testar"));
var queryContains = (from person in _personStore.Items<Person>() select person.List.Contains(p => p == "testar"));

DDS also supports Contains() for the opposite scenario: querying an inline property against a .NET enumeration that is not stored in DDS. For example, retrieve people with the last names “Smith,” “Anderson,” or “Svensson”.

var lastNames = new List<string>();
  lastNames.Add("Smith");
  lastNames.Add("Anderson");
  lastNames.Add("Svensson");
    
var query = _personStore.Items<Person>().Where(p => 
  lastNames.Contains(p.LastName).ToList();

GroupBy

Group query results by one or more inline properties and apply aggregate functions.

GroupBy is supported for inline types. If the query is grouped, DDS supports the following operations for the grouped data:

Sum(), Max(), Min(), and Average() are supported with predicates.

Count() is supported without a predicate.

The predicates are only supported when the predicate queries an inline type.

var query = _personStore.Items<Person>().GroupBy(p => p.Age).Select(m =>
  new {
    Count = m.Count(), Sum = m.Sum(s => s.Friends.ShoeSize), Max = m.Max(s =>
        s.Friends.ShoeSize), Min = m.Min(s => s.Friends.ShoeSize), Average =
      m.Average(s => s.Friends.ShoeSize)
  });

Multiple groupings are also supported.

var query = _personStore.Items<Person>().GroupBy(p => new {
  FirstName =
    p.FirstName, Age = p.Age
}).Select(m => new {
  m.Key.FirstName, m.Key.Age
});

Skip, take, and reverse

Control result pagination and ordering with Skip(x), Take(y), and Reverse().

Use these methods to implement paging for large result sets.

query.Reverse();
query.Skip(10).Take(20);

String operations

Filter and transform string properties within LINQ queries.

The following string operations are supported:

StartsWith

Contains

EndsWith

Substring(x)

Trim

IsNullOrEmpty

ToUpper

ToLower

Length

var startsWith = (from person in _personStore.Items<Person>() where 
  person.Address.City.StartsWith("St") select person);
var contains = (from person in _personStore.Items<Person>() where 
  person.Address.City.Contains("St") select person);
var EndsWith = (from person in _personStore.Items<Person>() where 
  person.Address.City.EndsWith("holm") select person);
var Substring = (from person in _personStore.Items<Person>() where 
  person.Address.City.Substring(2) == "ockholm" select person);
var trim = (from person in _personStore.Items<Person>() where 
  person.Address.City.Trim() == "Stockholm" select person);
var isNullOrEmpty = (from person in _personStore.Items<Person>() where 
  string.IsNullOrEmpty(person.Address.City) select person);
var toUpper = (from person in _personStore.Items<Person>() where 
  person.Address.City.ToUpper() == "STOCKHOLM" select person);
var toLower = (from person in _personStore.Items<Person>() where 
  person.Address.City.ToLower() == "stockholm" select person);

DateTime properties and operations

Perform date arithmetic and extract date components in LINQ queries.

The following DateTime operations are supported:

AddYears

AddMonths

AddDays

AddMinutes

AddSeconds

AddMilliseconds

Add or Subtract

var addYears = (from person in _personStore.Items<Person>() where 
  person.DateOfBirth.AddYears(4) < DateTime.Now select person);
var addMonths = (from person in _personStore.Items<Person>() where 
  person.DateOfBirth.AddMonths(4) < DateTime.Now select person);
var addDays = (from person in _personStore.Items<Person>() where 
  person.DateOfBirth.AddDays(4) < DateTime.Now select person);
var addMinutes = (from person in _personStore.Items<Person>() where 
  person.DateOfBirth.AddMinutes(4) < DateTime.Now select person);
var addSeconds = (from person in _personStore.Items<Person>() where 
  person.DateOfBirth.AddSeconds(4) < DateTime.Now select person);
var addMilliseconds = (from person in _personStore.Items<Person>() where 
  person.DateOfBirth.AddMilliseconds(4) < DateTime.Now select person);
var add = (from person in _personStore.Items<Person>() where 
  person.DateOfBirth.Add(new TimeSpan(1,2,3,4,5) < DateTime.Now select person);
var subtract = (from person in _personStore.Items<Person>() where 
  person.DateOfBirth.Subtract (DateTime.Now) < new TimeSpan(1,2,3,4,5);

The following DateTime properties are supported:

Year

Month

Hour

Minute

Second

DayOfYear

var year = (from person in _personStore.Items<Person>() select new { 
  person.DateOfBirth.Year });
var year = (from person in _personStore.Items<Person>() select new { 
  person.DateOfBirth.Month });
var year = (from person in _personStore.Items<Person>() select new { 
  person.DateOfBirth.Day });
var year = (from person in _personStore.Items<Person>() select new { 
  person.DateOfBirth.Hour });
var year = (from person in _personStore.Items<Person>() select new { 
  person.DateOfBirth.Minute });
var year = (from person in _personStore.Items<Person>() select new { 
  person.DateOfBirth.Second });
var year = (from person in _personStore.Items<Person>() select new { 
  person.DateOfBirth.DayOfYear });

Execute queries

Trigger query execution with deferred execution methods to control when DDS accesses the database.

Queries do not execute until a deferred execution method is called. Build and modify the query without database overhead until an execution method triggers the query.

The following deferred execution methods are supported:

ToList()

ToArray()

ToDictionary()

ToLookup()

Count()

First()

FirstOrDefault()

Single()

SingleOrDefault()

Last()

LastOrDefault()

List<Person>peopleList = _personStore.Items<Person>().ToList();
Dictionary<Guid, Person> peopleDictionary =
  _personStore.Items<Person>().ToDictionary(p => p.GuidId);

ILookup<Guid,Person> peopleLookups = _personStore.Items<Person>().ToLookup(p => p.GuidId);

int count = _personStore.Items<Person>().Count();
int countWithPredicate = _personStore.Items<Person>().Count(p => p.LastName == "Svensson");

Person firstPerson = _personStore.Items<Person>().OrderBy(p => p.FirstName).First();
Person firstPersonWithPredicate = _personStore.Items<Person>().OrderBy(p => p.FirstName).First(p => p.FirstName == "Svensson");

Person singlePerson = _personStore.Items<Person>().Where(p => p.FirstName == "Svensson").OrderBy(p => p.FirstName).Single();
Person singlePersonWithPredicate = _personStore.Items<Person>().OrderBy(p => p.FirstName).Single(p => p.FirstName == "Svensson");

Person lastPerson = _personStore.Items<Person>().OrderBy(p => p.FirstName).Last();
Person lastPersonWithPredicate = _personStore.Items<Person>().OrderBy(p => p.FirstName).Last(p => p.FirstName == "Svensson");

Conditional logic lets you modify the query before execution. The following example adds a Where clause based on a runtime condition:

var query = (from person in _personStore.Items<Person>() select person);
if (myCondition) {
  query = query.Where(person.LastName.StartsWith("a");
  }
  var result = query.ToList();