Views: 6651
Number of votes: 3
Average rating:

Dynamic Data Store Linq extension

I have to admit I’m in love with Dynamic Data Store, but the DDS is not perfect and there is some things that can be better. For example, when you make linq queries against it, how do you know what’s supported and not? Well, of course you could read about it, but you will probably forget and write linq queries not supported by Dynamic Data Store.

So what can we do about this problem? What I have done is an query builder class that only contains the supported methods in Dynamic Data Store (plus some methods that hacks the query so the methods get supported, or almost anyway). Beside the query builder class, I have also 3 extension methods that will generate “Link” and “LinqAsPropertyBag” methods on the instance of DynamicDataStore objects. It’s through this method you now will be able to write linq queries.

public static class QueryBuilderExtensions
{
public static QueryBuilder<TResult> Linq<TResult>(this EPiServer.Data.Dynamic.DynamicDataStore store)
{
return new QueryBuilder<TResult>(store.Items<TResult>());
}

public static QueryBuilder<Object> Linq(this EPiServer.Data.Dynamic.DynamicDataStore store)
{
return new QueryBuilder<Object>(store.Items<Object>());
}

public static QueryBuilder<PropertyBag> LinqAsPropertyBag(this EPiServer.Data.Dynamic.DynamicDataStore store)
{
return new QueryBuilder<PropertyBag>(store.Items<PropertyBag>());
}
}


QueryBuilder

So what’s in the QueryBuilder class? Well, it’s more or less a wrapper that contains the methods we support. It contains the methods select, where, order by, order by descending, group by, take, skip, reverse and the executing methods “AsEnumerable” and “Count”. With executing methods I mean methods that will create sql from the linq query and get the result from the source. This is important to know because when you are using one of those methods you will pull the result from the source (database). So if you do “AsEnumberable().First() you will receive everything from the database and then do an “LinqToObject” on the result. When I wrote the class I thought, why not throw in a little hack that makes it look like we support “First”, “FirstOrDefault”, “Last”, and “LastOrDefault”, and only receive the data from the source needed for those methods? The result of the hack is:

public TSource First()
{
return _query.Take(1).AsEnumerable().First();
}

public TSource FirstOrDefault()
{
return _query.Take(1).AsEnumerable().FirstOrDefault();
}

public TSource Last()
{
return _query.Reverse().Take(1).AsEnumerable().First();
}

public TSource LastOrDefault()
{
return _query.Reverse().Take(1).AsEnumerable().FirstOrDefault();
}

 

Use the Linq extension

To use the query builder, create an instance of Dynamic Data Store.

private void CreateQuery()
{
EPiServer.Data.Dynamic.DynamicDataStore store = GetStore("People", typeof(Person));
}

static EPiServer.Data.Dynamic.DynamicDataStore GetStore(string storeName, Type t)
{
return EPiServer.Data.Dynamic.DynamicDataStoreFactory.Instance.GetStore(storeName) ??
EPiServer.Data.Dynamic.DynamicDataStoreFactory.Instance.CreateStore(storeName, t);
}


Then, create an instance of the QueryBuilder:
private void CreateQuery()
{
EPiServer.Data.Dynamic.DynamicDataStore store = GetStore(typeof(Person));
QueryBuilder<Person> query = store.Linq<Person>();
}


Simple methods

And now you can play around as much as you want. When using “Where”, OrderBy” “OrderByDecending”, “Take”, “Skip”, and “Reverse” the query builder will add expressions to the class every time you call the methods, so you do not need to create a new instance of the object when calling those methods.

private void CreateQuery()
{
EPiServer.Data.Dynamic.DynamicDataStore store = GetStore("People", typeof(Person));
QueryBuilder<Person> query = store.Linq<Person>();

query.Where(p => p.FirstName.StartsWith("a"));
query.Where(p => p.LastName.Contains("a"));

query.OrderBy(p => p.LastName);
query.OrderByDescending(p => p.FirstName);
}


Now we have a query but no result. To execute the query I will now call “AsEnumerable” on the instance.
private void CreateQuery()
{
EPiServer.Data.Dynamic.DynamicDataStore store = GetStore("People", typeof(Person));
QueryBuilder<Person> query = store.Linq<Person>();

query.Where(p => p.FirstName.StartsWith("a"));
query.Where(p => p.LastName.Contains("a"));

query.OrderBy(p => p.LastName);
query.OrderByDescending(p => p.FirstName);

query.Skip(5);
query.Take(3);

IEnumerable<Person> persons = query.AsEnumerable();
int personCount = query.Count();
}


Now I executed the query twice, one time when getting the persons and one time when getting the count. A better way would have been doing int personCount = persons.Count() to avoid two executions.
 
We should also be able to use the “First” and “Last” methods.
private void CreateQuery()
{
EPiServer.Data.Dynamic.DynamicDataStore store = GetStore("People", typeof(Person));
QueryBuilder<Person> query = store.Linq<Person>();

query.Where(p => p.FirstName.StartsWith("a"));
query.Where(p => p.LastName.Contains("a"));

query.OrderBy(p => p.LastName);
query.OrderByDescending(p => p.FirstName);

Person person1 = query.FirstOrDefault();
Person person2 = query.LastOrDefault();
}

 
If you are more of an one liner programmer, you can write like this:
private void CreateQuery()
{
EPiServer.Data.Dynamic.DynamicDataStore store = GetStore("People", typeof(Person));
IEnumerable<Person> persons = store.Linq<Person>()
.Where(p => p.LastName.StartsWith("a"))
.OrderBy(p => p.FirstName)
.AsEnumerable();
}

 

Complex methods

Where, order by, order by descending, skip, take, and reverse was easy to work with. Select and group by are more complex, because whey have to create a new instance of the “QueryBuilder” class. So, when using those methods do like this:

private void CreateQuery()
{
EPiServer.Data.Dynamic.DynamicDataStore store = GetStore("People", typeof(Person));
QueryBuilder<Person> query = store.Linq<Person>();

var peronGroup = query.GroupBy(p => p.ShoeSize);
var personSelect = peronGroup.Select(m => new { Key = m.Key, Count = m.Count() });

var result = personSelect.AsEnumerable();
}

Or:
private void CreateQuery()
{
EPiServer.Data.Dynamic.DynamicDataStore store = GetStore("People", typeof(Person));

var result = store.Linq<Person>()
.GroupBy(p => p.ShoeSize)
.Select(m => new { Key = m.Key, Count = m.Count() })
.AsEnumerable();
}

If you think this is something you can use, download the project and play around with it. Download

Hi! do you have this on GitHub?
Added ToList() to this code :D

QueryBuilder.cs

 public List ToList()
        {
            return _query.ToList();
        }



Please login to comment.