Five New Optimizely Certifications are Here! Validate your expertise and advance your career with our latest certification exams. Click here to find out more

AI OnAI Off
Navigation [hide] [expand]
Area: Optimizely CMS
ARCHIVED This content is retired and no longer maintained. See the latest version here.

Supporting LINQ

The Dynamic Data Store has extensive support for Microsoft’s Language Integrated Query (LINQ). The LINQ support is the same for both typed stores and for property bags.

Conditions

Where is supported on inline types, independent if the inline types are directly on the queried object or nestled inside another object.

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

Ordering

Order by and Then by are supported for inline types, independent if the inline types are directly on the queried object or nestled inside another object.

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

Selecting

To receive the whole object in the store, the select object can be used.

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

It is also possible to receive an anonym type from the store by using the new keyword.

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

Enumerations

If an object contains an enumeration of an inline type, the following operations on the enumeration are supported; Max(), Min(), and Average() are supported without predicates, Count() is supported both with and without predicates, and Contains() is  supported with 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.Countains(p => p == "testar"));

It also supports Contains() for the opposite scenario, querying an inline property against a .NET enumeration, that is not stored in the DDS. This can be used to get all 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();

Grouping

Group by is supported for inline types. If the query has been grouped, the following operations are supported for the grouped data; Sum(), Max(), Min(), and Average() are supported with predicates, and Count() is supported without 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.Avergage(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

Skip(x), Take(y) and Reverse() are also supported methods. Those can be helpful when developing paging.

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

String operations

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

The following DateTime operations are supported:

  • AddYear
  • AddMonths
  • AddDays
  • AddMinutes
  • AddSeconds
  • AddMilliseconds
  • AddSubtract
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.AddMonth(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 string properties are supported:

  • Year
  • Month
  • Day
  • 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 });

Executing queries

The queries are not executed until an deferrer (execution method) gets called. This means that it is possible to work with the query until an execution method gets called without the overhead of going to the database all the time.

The following deferred methods are supported:

  • ToList()
  • ToArray()
  • ToDictionary()
  • ToLookup()
  • Count()
  • First()
  • FirstOrDefault()
  • Single()
  • SingleOrDefault()
  • Last()
  • LastOrDefault()
List<People> 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");

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

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

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

Sometimes it is necessary to have a condition in the code that renders the query in different ways depending on the condition. The following example shows how to work with a query:

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

See the UsingLinq class in the DDS sample project for examples of LINQ support.

Last updated: Jan 05, 2016