Adding dynamic ‘where’ clauses to a linq query using Builder and Specification pattern

On-the-fly building on where clauses of query statements to fire against a data store is a common problem in most of the applications. Linq is a concept of querying data a first class programming concept in .NET, and enables you to efficiently express queries in your programming language of choice. Linq allows you to write type-safe queries, most of the times you need to construct queries on the fly based on some filter conditions.

The Dynamic query library provided by Microsoft helps you to construct linq queries by passing a string value for where condition or an expression that can be evaluated at runtime. In this post we'll see how to construct expressions for the where clause dynamically using a combination of builder and specification pattern.

Creating a class for filter options

public class UsersFilterOption

{

    public string Name { get; set; }

    public string Country { get; set; }

    public string Role { get; set; }

    public string Email { get; set; }

    public string Telephone { get; set; }

}

The condition builder class is used to add expressions as specifications to the Specification based on the filter options.

public class UserWhereConditionExpressionBuilder

{

    private readonly UsersFilterOption _filterOptions;

    private Specification<User> _result = new TrueSpecification<User>();

    public UserWhereConditionExpressionBuilder(UsersFilterOption usersFilterOptions)

    {

        _filterOptions = usersFilterOptions;

    }

    public UserWhereConditionExpressionBuilder Name()

    {

        var shouldBuild = !string.IsNullOrEmpty(_filterOptions.Name);

        if (!shouldBuild) return this;

        _result &= new DirectSpecification<User>(u => u.Name.ToUpper().Contains(_filterOptions.Name.ToUpper()));

            

        return this;

    }

    public UserWhereConditionExpressionBuilder Email()

    {

        var shouldBuild = !string.IsNullOrEmpty(_filterOptions.Email);

        if (!shouldBuild) return this;

        _result &= new DirectSpecification<User>(u => u.Email.ToUpper().Contains(_filterOptions.Email.ToUpper()));

        return this;

    }

    public UserWhereConditionExpressionBuilder Country()

    {

        var shouldBuild = !string.IsNullOrEmpty(_filterOptions.Country) &&

                                        System.String.CompareOrdinal(_filterOptions.Country, "ALL") != 0;

        if (!shouldBuild) return this;

        _result &= new DirectSpecification<User>(u => System.String.Compare(u.Country, _filterOptions.Country, System.StringComparison.OrdinalIgnoreCase) == 0);

        return this;

    }

    public UserWhereConditionExpressionBuilder Role()

    {

        var shouldBuild = !string.IsNullOrEmpty(_filterOptions.Role) &&

                                        System.String.CompareOrdinal(_filterOptions.Role, "ALL") != 0;

        if (!shouldBuild) return this;

        _result &= new DirectSpecification<User>(u => System.String.Compare(u.Role, _filterOptions.Role, System.StringComparison.OrdinalIgnoreCase) == 0);

        return this;

    }

       

    public Specification<User> GetWhereCondition()

    {

        return _result;

    }

}

The construct method on the builder class constructs the expression from the condition builder class. I've implemented the condition builder class as fluent interfaces for easy use.

public class UsersWhereCondition

{

    public static ISpecification<User> Construct(UsersFilterOption filterOptions)

    {

        var builder = new UserWhereConditionExpressionBuilder(filterOptions);

        return

            builder

            .Name()

            .Email()

            .Country()

            .Role()

            .GetWhereCondition();

    }

}

Using these classes you can construct the query condition for the dynamic linq clause as given below

var whereCondition = UsersWhereCondition.Construct(filterOptions);

               

var data = from user in userFactory.GetAll()

            .Where(whereCondition.SatisfiedBy());

Reactive Extensions - Much simpler asynchronous and event based programming

Reactive Extensions helps developers write asynchronous and event based operations in a much simpler way. The user must specify a SynchronizationContext to which the Rx messages are sent. For e.g: If you are using a console app, you should specify the Context as

Observable.Context = SynchronizationContexts.ReactiveEventLoop

For WPF applications your Context will be

Observable.Context = SynchronizationContexts.CurrentDispatcher;

You can write subscriptions to your control events using Rx like

Observable.FromEvent<RoutedEventArgs>(button1, "Click").Subscribe(x => this.Close());

Or

var __StartTimer = button2.GetClickEvents().Subscribe(x => MessageBox.Show("Starting timer..."));

where GetClickEvents is an extension method on UIElements to track clicks on them

public static IObservable<IEvent<RoutedEventArgs>> GetClickEvents(this UIElement uiElement)

{

     return Observable.FromEvent<RoutedEventArgs>(uiElement, "Click");

}

Reactive Extensions – First Look

Rx is a library for composing asynchronous and event based programs using observable collections. The library revolves around the concept of “Reactive programming”. Reactive programming or reactive programs is not a new concept, we have been using reactive programming before in our applications by registering to an event handler or a call back on an asynchronous operation.

All these operations were performed by specifying delegates or methods that are called at unpredictable times. i.e. the functions were called based on an action or a change in the existing data type or code. But we can also think that these operations are performed when data is passed to them as an item in a sequence. For e.g. a sequence of button click events are passed to the method OnButtonClick(). Every time the function gets an input it performs some operation.

Rx is a superset of the standard LINQ sequence operators that exposes asynchronous and event based computations as push based observable collections via the .NET 4.0 interfaces IObservable and IObserver. These interfaces form the heart of Rx.

IObservable interface represents the class that sends notifications (the provider); the IObserver interface represents the class that receives them (the observer). T represents the class that provides the notification information.

public interface IObservable<out T>

{       

    IDisposable Subscribe(IObserver observer);

}

The provider object implements the IObservable  that has a single method Subscribe, which indicates that an observer wants to receive push-based notifications. Callers to the method pass an instance of the observer.

public interface IObserver<in T>

{       

    void OnCompleted();       

    void OnError(Exception error);       

    void OnNext(T value);

}

The provider sends the following three kinds of notifications to the observer by calling IObserver<(Of <(T>)>) methods:

The current data. The provider can call the IObserver , OnNext method to pass the observer a T object that has current data, changed data, or fresh data.

An error condition. The provider can call the IObserver, OnError method to notify the observer that some error condition has occurred.

No further data. The provider can call the IObserver, OnCompleted method to notify the observer that it has finished sending notifications.

I have created a simple example to demonstrate the usage of IObservable and IObserver interfaces and the observer pattern implementation.

public class Employee

{

    public string Name { get; set; }

    public string Email { get; set; }

    public decimal Bonus { get; set; }

    public DateTime DateOfJoining { get; set; }

}

public class EmployeeSubject : IObservable<Employee>

{

    public EmployeeSubject ()

       {

        ___Employee = new Employee { Name = "Prajeesh", Bonus=90.90M, DateOfJoining= new DateTime(2005, 08, 01), Email = "prajeesh.prathap@gmail.com" };

       }

    public Employee GetEmployee() { return ___Employee; }

   

    public void UpdateEmployee(Employee employee)

    {

        ___Employee.Name = employee.Name;

        ___Employee.Email = employee.Email;

        ___Employee.Bonus = employee.Bonus;

        ___Employee.DateOfJoining = employee.DateOfJoining;

       

        //Notify the observers by pushing the updated employee details.

        ___Observers.ForEach(observer => observer.OnNext(___Employee));           

    }

    public IDisposable Subscribe(IObserver<Employee> observer)

    {

        ___Observers.Add(observer);

        observer.OnNext(___Employee);

        return observer as IDisposable;

    }

    Employee ___Employee;

    List<IObserver<Employee>> ___Observers = new List<IObserver<Employee>>();

}

public class EmployeeObserver : IObserver<Employee>

{

    public void  OnCompleted()

    {

       Console.WriteLine("Subscription completed");

    }

    public void  OnError(Exception error)

    {

       Console.WriteLine("Unexpected error occurred {0}", error.Message);

    }

    public void  OnNext(Employee value)

    {

       Console.WriteLine("Employee Details");

        Console.WriteLine("Name : " + value.Name);

        Console.WriteLine("Date of joining : " + value.DateOfJoining.ToShortDateString());

        Console.WriteLine("Email : " + value.Email);

        Console.WriteLine("Bonus : " + value.Bonus.ToString());

        Console.WriteLine();

    }

}

In the console application, I have the code written as

var __EmployeeSubject = new EmployeeSubject();

var __Observer = new EmployeeObserver();

__EmployeeSubject.Subscribe(__Observer);

var __Employee = __EmployeeSubject.GetEmployee();

__Employee.Bonus = 55.55M;

__EmployeeSubject.UpdateEmployee(__Employee);

Output:

Employee Details

Name : Prajeesh

Date of joining : 8/1/2005

Email : prajeesh.prathap@gmail.com

Bonus : 90.90

Employee Details

Name : Prajeesh

Date of joining : 8/1/2005

Email : prajeesh.prathap@gmail.com

Bonus : 55.55

In the next post, I’ll show some samples using the Rx framework and how it can be used in our applications.

ADO.NET Entity Framework - Part 3

ADO.NET Entity framework - update changes to the storage layer.

ADO.NET Entity framework helps you in creating, updating and deleting objects by using Object Services. Object Services keep track of the changes and then update them to the persisted storage by executing the corresponding T-SQL statements. The SaveChanges() method of the ObjectContext class is used for saving the changes to the persistence storage.

In this post I will explain how to apply changes on a detached object to the storage layer.

When a query is executed within an object context, the returned objects are automatically attached to the context. Object Services also lets you attach existing objects to an object context. Once you attach an object to the context. You can use the SaveChanges() method to update the changes in the database.

The ApplyPropertyChanges method can be used when the object already exists in the store and the detached object has property updates that you want to persist.

In the following example an updated Customer object is passed to the Save method. The Save method retrieves the original object by using the EntityKey and then updates the original object by the ApplyPropertyChanges() method. Later the changes are updated by using the SaveChanges() method of the ObjectContext class.

public int Save(Customer myCustomer)

{

EntityKey key;

object originalItem;

using (TwoWayDataBindingEntities entites = new TwoWayDataBindingEntities())

{

try

{

// Get the detached object's entity key.

if (myCustomer.EntityKey == null)

{

// Get the entity key of the updated object.

key = entites.GetEntityKey("Customers", myCustomer);

}

else

{

key = myCustomer.EntityKey;

}

// Get the original item based on the entity key from the context

// or from the database.

if (entites.TryGetObjectByKey(key, out originalItem))

{

// Call the ApplyPropertyChanges method to apply changes

// from the updated item to the original version.

entites.ApplyPropertyChanges(key.EntitySetName, myCustomer);

}

return entites.SaveChanges();

}

catch (InvalidOperationException ex)

{

//Exception handling logic

}

return 0;

}

}

ADO.NET Entity Framework - Part 2

Working with ADO.NET Entity framework

After looking into the architecture of the Entity Framework, this article I will show the how to create an EDM using the Entity Data Model Wizard.

The Entity Framework compiles a set of conceptual and storage schemas, together with the mappings between them, into bidirectional pairs of Entity SQL statements called client views. These views drive query and update processing in the runtime engine.

Generating an Entity Data model

To generate an EDM you need to first add an ADO.NET Entity Data Model item template to the project. Follow the below given steps to add and EDM to the project

1. Right-click the project, point to Add, and then click New Item.
2. Select ADO.NET Entity Data Model in the Templates pane.
3. Click Add after you enter the name for EDM. This will present the Entity Data Model Wizard.
4. Select Generate from database in the Choose Model Contents dialog box. Then click Next.
5. Click the New Connection button. The Connection Properties dialog box is displayed.
6. Enter your server name, select the authentication method.
7. Select the database name, and then click OK.
8. The Choose Your Data Connections dialog box is updated with your database connection settings.
9. Select all the tables from the dialog box and click Finish.

The Entity Framework tools generate a class derived from ObjectContext that represents the entity container defined in the conceptual model. The ObjectContext class supports queries against an EDM that return entities as objects, as well as creating, updating, and deleting entity objects. The Entity Framework supports object queries against an EDM. Queries can be composed using Entity SQL, LINQ to entities and object query builder methods.

Once the entity data model is generated you can use any of the above given methods to query your ObjectContext class.

For example :

using (BlogEntities entities = new BlogEntities())

{

var query = from blogentry in entities.blogentries

.Skip(StartRow).Take(PageSize)

orderby blogentry.datepublished descending

select blogentry;

return query;

}

Accessing anonymous types from a different assembly

Anonymous types are one of the new features introduced in C# 3.0 which helps you to encapsulate a set of properties into an object without having to define a type. The type will not be available at the source code level (This is generated by the compiler).

In this article I will explain how to access anonymous types exposed from another assembly in the caller method. There are some very nice articles on the topic by Tom and Clark .

I have a GetContactList method which returns an anonymous type collection that is a result of the LINQ query.

public class Contact

{

public Int32 ContactID { get; set; }

public String ContactName { get; set; }

public Int32 CompanyID { get; set; }

}

public class Company

{

public Int32 CompanyID { get; set; }

public String CompanyName { get; set; }

public String CompanyURL { get; set; }

}

public Object[] GetContactList()

{

var contacts = from contact in GetContacts()

join company in GetCompanies()

on contact.CompanyID equals company.CompanyID

select new { contact.ContactName, company.CompanyName, company.CompanyURL };

return contacts.ToArray();

}

The properties of this return type cannot be accessed before casting it to an object.

The type returned from the above method looks something like this to the external caller.

<>f__AnonymousType0`3[System.String,System.String,System.String] b__3(BusinessCoreLayer.Contact, BusinessCoreLayer.Company)

I will go through some interesting parts of the above type

• The 0 after the <>f__AnonymousType is a zero based index of each anonymous type definition. (If you have another anonymous type in the code that will have the index 1)
• The 3 after the 0 represents the number of properties in the anonymous type (We have 3 properties – ContactName, CompanyName, CompanyURL)
• The rest of the portions represent the data types and method name

I have created an Entity converter that will convert this anonymous type to the respective type in the caller function. The below given code will convert the anonymous type collection returned to the ContactDetails object.

public class ContactDetails

{

public String ContactName { get; set; }

public String CompanyName { get; set; }

public String CompanyURL { get; set; }

}

private ContactDetails EntityTranslator(Collection<KeyValuePair<String, Object>> propertyCollection)

{

ContactDetails cd = new ContactDetails();

foreach (KeyValuePair<String, Object> propertyInfo in propertyCollection)

{

switch (propertyInfo.Key)

{

case "ContactName":

cd.ContactName = propertyInfo.Value.ToString();

break;

case "CompanyName":

cd.CompanyName = propertyInfo.Value.ToString();

break;

case "CompanyURL":

cd.CompanyURL = propertyInfo.Value.ToString();

break;

}

}

return cd;

}

Now in the caller method I have to call the EntityTranslator like

Object[] contacts = Assembly2.GetContactList();

Collection<ContactDetails> details = new Collection<ContactDetails>();

foreach (Object contact in contacts)

{

ContactDetails cd = new ContactDetails();

Type t = contact.GetType();

PropertyInfo[] pInfo = t.GetProperties();

Collection<KeyValuePair<String, Object>> propertyCollection = new Collection<KeyValuePair<String, Object>>();

foreach(PropertyInfo pi in pInfo)

{

propertyCollection.Add(new KeyValuePair<String, Object>(pi.Name, pi.GetValue(contact, null)));

}

cd = ContactTranslator(propertyCollection);

details.Add(cd);

}

As you have seen by using Reflection we can access the properties of anonymous type returned from another assembly.

ADO.NET Entity Framework - Introduction

I have been using LINQ to SQL as an OR-mapper for my application since the release of Visual studio 2008. As a light weight OR-mapper and for RAD scenarios LINQ to SQL serves my purpose as a framework for persisting data. But when thinking about Enterprise Application development and real scenarios, I don’t find LINQ to SQL as powerful tool with all the features I require for Application development.

LINQ to SQL as present was not a competitor for NHibernate or LLBLGen which are the common OR-mappers available. If you are looking for a better OR-mapper from Microsoft, ADO.NET Entity framework is the answer. This one gives life to the conceptual modeling and has a lot of features compared to LINQ to SQL. The primary benefit of the ADO.NET Entity Framework is to elevate the level of abstraction at which developers can work when they deal with data and to decrease the amount of code that is required to create and maintain data-oriented applications.

Some of the advantages of using Entity framework:

• Entity framework maps relational tables, columns, and foreign key constraints to entities and relationships in conceptual models. Instances of these classes are populated with data and data changes are saved based on defined mappings between the conceptual and storage model.
• Supports Entity SQL and LINQ to Entities two powerful query languages to query the data.
• Provides a rich and intuitive view to the data model from the application perspective as opposed to the normalized view of data.
• Last but not least, comes with a code generation wizard that is integrated with Visual Studio that will create less and more quality code much faster.

In this article series I will be explaining the underlining technologies in the Entity Framework by some tutorials that help you to get hands on experience on the topic.

The architecture

The ADO.NET Entity Framework is a layered framework which abstracts the relational schema of a database and presents a conceptual model.

• Object Services: Object Services is a component of the Entity Framework that enables you to query, insert, update, and delete data, expressed as strongly-typed CLR objects that are instances of entity types. Object Services supports both Language-Integrated Query (LINQ) and Entity SQL queries against types defined in an Entity Data Model (EDM). Object Services materializes returned data as objects, and propagates object changes back to the persisted data store. It also provides facilities for tracking changes, binding objects to controls, and handling concurrency. Object Services is implemented by classes in the System.Data.Objects and System.Data.Objects.DataClasses namespaces.
• Entity SQL: Entity SQL is a SQL-like language provided by the ADO.NET Entity Framework to support the Entity Data Model (EDM). The EDM is an extended relational model that supports basic relational concepts, rich types with inheritance, and relationships. Entity SQL supports EDM constructs, enabling users to effectively query the data represented by an entity model.
• Entity Data Model: The Entity Data Model (EDM) is a specification for defining the data used by applications built on the Entity Framework. Applications using the EDM define entities and relationships in the domain of the application in a design schema. The design schema is used to build programmable classes used by application code. The entity data model contains of 3 parts

1. Conceptual Schema Definition Language (CSDL): The conceptual schema is a design template for the object model that will be used by applications built on the Entity Data Model (EDM). Conceptual schema is used to declare and define entities, associations, inheritance etc. It is from this schema the Entity classes are generated.
2. Store schema definition language (SSDL): The SSDL is a formal description of the database that persist data for an application built on the Entity Data Model (EDM). The entities and associations declared in this schema are the basis for mapping entities and associations in the conceptual schema to the corresponding entities in the storage model. This contains the metadata describing the database that persist data.
3. Mapping specification language (MSL): In the Entity Data Model (EDM), the mapping specification uses mapping specification language (MSL) to connect the types declared in conceptual schema definition language (CSDL) to database metadata that persists data for applications that use the object model being defined.

• LINQ to entities: LINQ to Entities enables developers to write queries against the database from the same language used to build the business logic

In In the next part of this series we will look into more details of the Entity Framework and see how Entity Framework will assist in creating data centric applications more easily.

Sorting and Grouping in LINQ to Objects

As a continuation to the articles on LINQ, in this post I will explain how to write queries and use the query operators to perform common operations such as sorting, dealing with nested data, and grouping.

For the examples used in this article I will use the following data model.

public class Customer

{

public String Name { get; set; }

public Int32 Age { get; set; }

public Collection<Address> Addresses { get; set; }

public String Industry { get; set; }

}

public class Address

{

public String AddressLine { get; set; }

public String Country { get; set; }

}

And the method to return the data as

private static Collection<Customer> GetCustomers()

{

return new Collection<Customer> {

new Customer { Name = "John Samuels", Age = 40 , Addresses = new Collection<Address>

{ new Address { AddressLine = "New York", Country = "USA" },

new Address { AddressLine = "California, 21st Main Road", Country = "USA" },

new Address { AddressLine = "London", Country= "UK" }

}, Industry = "Computers"},

new Customer { Name = "Sreejith P.R", Age = 30 , Addresses = new Collection<Address>

{ new Address { AddressLine = "Bangalore", Country = "India" },

new Address { AddressLine = "Redmond", Country = "USA" }

}, Industry = "Telecom"},

new Customer { Name = "Kim David", Age = 35 , Addresses = new Collection<Address>

{ new Address { AddressLine = "Shangai", Country = "China" }

}, Industry = "Telecom"},

new Customer { Name = "Rahul Bose", Age = 50 , Addresses = new Collection<Address>

{ new Address { AddressLine = "New York", Country = "USA" },

new Address { AddressLine = "Bangalore", Country = "India" },

new Address { AddressLine = "Mysore", Country= "India" },

new Address { AddressLine = "Kolkatta", Country= "India" }

}, Industry = "Information Technology"},

new Customer { Name = "Micheal Johnson", Age = 37 , Addresses = new Collection<Address>

{ new Address { AddressLine = "New York", Country = "USA" },

new Address { AddressLine = "London", Country= "UK" }

}, Industry = "Computers"}

};

}

Now let’s say we want to see the customers in USA, sorted by their names in descending order.

The query I wrote to achieve this result is

var customers = (from customer in GetCustomers()

from address in customer.Addresses

where address.Country == "USA"

orderby customer.Name descending

select customer.Name).Distinct();

The operator that we are interested here is the orderby operator which sorts the collection in ascending order by default. You can also use the descending, ThenByDescending, ThenBy, OrderByDescending operators also for sorting.

The groupby operator helps you to group the query result based on any grouping condition.

For example to display all the customers grouped by industry we write the query like.

var industryCustomers =

from customer in GetCustomers()

group customer by customer.Industry into customerIndustries

select new { Industry = customerIndustries.Key, Customers = customerIndustries };

You can also have the advantage of using aggregation operators after grouping the results.

For example to find the minimum age of each customer by each industry we can write the query like.

var industryCustomers =

from customer in GetCustomers()

group customer by customer.Industry into customerIndustries

select new { Industry = customerIndustries.Key, Customers = customerIndustries, MinAge = customerIndustries.Min(cust => cust.Age)};