Apply in SQL Server

The apply keyword was introduced mainly for the use of selecting data by combining a select query with a table valued function, which is nothing but select query on another table (or result of any business logic in the function). To understand it better, let’s call our main table as the left table and the second table (in the table valued function) as the right table.

Let’s start by creating sample data. We will have two tables Employee and Department. Here, our Employee table will be the left table and the Department table will be the right table. Each employee can only belong to one department.

Cross apply in SQL , Outer apply in sql

Our aim will be to join these two tables to get the required data. One solution which immediately comes to mind is use of  LEFT, RIGHT or INNER JOIN, which will depend on our requirement. Let’s see the results with the use of JOINS.

Cross apply in SQL , Outer apply in sqlThe same results can be achieved using the apply keyword. It works the same way as the co-related sub query, with the fact that the select query on which apply is used i.e. the right table, will be executed for each and every record of the main table or the left table. It can be applied in two different ways:

  • CROSS APPLY: This works same as the INNER JOIN on two queries.
  • OUTER APPLY: This works same as the LEFT JOIN on two queries.

Let’s change the queries using these two forms of apply and see the results.

Cross apply in SQL , Outer apply in sqlAs we can see above, CROSS APPLY gives the same result as the INNER JOIN and OUTER APPLY gives the same result as the LEFT OUTER JOIN. The difference with the JOIN is that APPLY results in execution of the Select statement of Department query, for each record of Employee record (same as that of a co-related sub-query).

Next, suppose we were using the co-related sub query. But we need to view the rest of the columns of the second table i.e. the Department table. Can we do that ? Doesn’t seems to be, unless we add some twist in the query. But this can be easily done with APPLY keyword. Simply add name of the columns we want to view, in the select statement of Department and we are done. Let’s change our queries and see the results:

Cross apply in SQL , Outer apply in sql

Another possible and big use of APPLY is with the table valued function. We create a simple function which returns Department details by Id. Next we replace our Select statement for Department, with call to the user defined function. See the query below:

Cross apply in SQL , Outer apply in sqlSo, depending on the requirements, we can add or remove the names of the columns in the SELECT statement of the function call. To summarize, we can use the apply keyword as :

  • A co-related sub query with the advantage of selecting multiple columns
  • A join with the table valued user defined function to select multiple columns from the second table

So this was about the use of the apply keyword.

Posted in SQL Server | Tagged | Leave a comment

delegates, anonymous methods & lamda expressions

In this article, we will discuss about the basic concept of the delegates, anonymous methods and lamda expressions. We will use one single code example of a add method, which takes two input parameters and return their sum. So we have a simple function with the code as:

public static Int32 Add(Int32 a, Int32 b)
return a + b;

Delegate: Same definition you might have seen on the other sources, a delegate acts as a function pointer and can be used to execute a function, by simply passing its name(to register that method) and then invoke it, by calling the delegate instance. But the condition is that the signature of the delegate must be same as that of the function that we need to call through this delegate.
So now we will use a delegate to call the above method. For this, we need to first create a delegate.

public delegate Int32 Calculate(Int32 a, Int32 b);
Calculate _calculate = new Calculate(Add);
Console.Write("Sum of numbers using Delegate : " + _calculate(21, 22));

Anonymous methods: Anonymous methods were introduced in C# 2.0, as a shorter syntax for passing the function definition directly to the delegate parameter, instead of creating a method and passing its name explicitly to it. We can simply pass the logic of our function inside the delegate call, instead of creating a function for it.
So now we will convert the above code to calculate the sum using anonymous method.

Calculate _calculate = delegate(Int32 a, Int32 b)
return a + b;
Console.Write("Sum of numbers using Anonymous method : " + _calculate(50, 50));

As we can see above, we replaced the original function name with the actual function logic, in the delegate initialization. Run the code and see the results.

Lamda expressions:
Finally we have the concept of lamda expressions. Introduced in C# 3.5, they further shorten the syntax of creating anonymous methods. It uses the => or lamda operator, to declare the anonymous methods. Let’s convert our code to use the lamda expression.

Calculate _calculate2 = (a, b) => a + b;
Console.Write("Sum of numbers using Lamda expressions : " + _calculate2(10, 10));

To specify any input parameters, we set them on the left of the => operator, in the brackets, as we did above (a, b) . If we do not have any input parameter, we can simply use empty brackets as (). On the other hand, the statements on the right side of the => operator are called as Expression lamda. If we need to have multiple statements in the expression lamda, we need to use the {} brackets to enclose them and are called Statement lamda (Source MSDN)

The procedure to call the delegate remains the same. What changes is, how we use the actual function with the delegate created for it. The complete code is listed below:

delegates, anonymous methods & lamda expressions

Lamda expressions in LINQ query: lamda expressions are used extensively in the linq queries. You must have written the queries like below:

List<Int32> _lst = new List<Int32>();
_lst.Where(x => x > 10).ToList();

Let’s try to see why we can use the lamda expressions here. See the code below:

delegates, anonymous methods & lamda expressions

As we can see, it expects a Func<int, bool> delegate, which takes an input parameter and returns a boolean value. Since we can directly provide lamda expression based definition to the delegate, like we did above in our Calculate example, we can directly write the lamda expressions with these methods. In-fact we can write the anonymous method directly in this method or could have declared the Func delegate explicitly, with anonymous method syntax and then passed it to this method. So we can have the code as:

delegates, anonymous methods & lamda expressions

So this was about the basic concept of delegates, anonymous methods and lamda expressions. Hope you enjoyed reading it…!!!

Posted in C#, Linq | Tagged | Leave a comment

Dynamic query in LINQ

In this article, we will try to understand how we can create a dynamic query in LINQ. In order to do this, we first need to understand the concept of expression trees.

What is Expression tree ?

As per MSDN : Expression trees represent code in a tree-like data structure, where each node is an expression, for example, a method call or a binary operation such as x < y

In simple language, an expression tree is a tree data structure, which can be used to represent some executable piece of code, in the form of nodes of this tree. This code is then converted into actual executable code. This kind of property allows you to generate the dynamic linq query. So let’s start with the code :

We have the following linq query which returns data of a Customer, based on the CustomerId.

_dbEntities.Customers.Where(cust => cust.CustomerId == 10).FirstOrDefault();

We will now convert this query into dynamic query, by creating an expression tree for it. First, we need to add the namespace System.Linq.Expressions. This class contains various static methods, which we will use to generate the query. These methods include Parameter, Constant, Property etc. We will be using these methods to generate the query, in multiple steps. So let’s start

Step 1 : We need to first generate the input parameter which is represented by code on the left of the lamda operator i.e. cust =>. For this, we use the Expression.Parameter method, which will be passed 2 parameters Type and String name. Here

  • Type is the type of entity we are using i.e. Customer entity.
  • String is the any name we use to represent an input to the parameter, which in our case is cust. So our code will be :

ParameterExpression pe = Expression.Parameter(Customer, "cust");

Step 2: Next, we need to use the CustomerId property to make the comparison with a value which is represented by cust.CustomerId in our initial query. So we need to get that property first and we use the Expression.Property method for it. So our code will be:

var _prpToUse = Expression.Property(pe, "CustomerId");

Step 3: Now we need to compare our CustomerId with some value say 10 in our case. So we generate our expression for this using the Expression.Constant method as :

var _cnstToUse = Expression.Constant(10);

Step 4: Next we need to combine the above two expressions, to generate expression cust.CustomerId == 10. For this purpose, we will use the Expression.Equal method. So our code becomes:

var qry = Expression.Equal(_prpToUse, _cnstToUse);

Step 5: Now, we have the expression of the form cust => cust.CustomerId == 10 and need to combine it with the Where extension method, to complete the query. So our following code will pass the above expression to tit, using the MethodCallExpression method.

MethodCallExpression whereExpression = Expression.Call(
new Type[] { lst.ElementType },
Expression.Lambda<Func<Customer,                                                            bool>>(qry, new ParameterExpression[] {                                                    pe }));

Step 6: Finally, we need to execute the query it using CreateQuery method in the provider on which we need to execute the query.


Here, lstData is the list of customers which we have from the database. Execute the code and see the results. It will be the same that we had at the start of the article.
In all the above steps, except step 6, we have created expressions of code that are combined to create an Expression tree, as a whole, which is nothing but the query that we executed at the start of the article.

Below is the generic implementation of the code that we created

Dynamic query using LINQ

So this was about the use of Expression trees to generate the dynamic linq query. Hope you enjoyed reading it.

Posted in Design Patterns | Leave a comment

Getting started with Repository pattern using C#

In this article, we will discuss about the repository pattern and will create a simple example to get the basic working of this pattern.

To understand why we should be using this pattern, let’s take example of an MVC application, using the entity framework. As long as we are getting the correct results, we are not concerned with how the application code is structured, what are different layers defined, how they interact, how they pass the required data between them etc. and in most of the cases, we use the database context directly inside the controllers. This creates tight coupling between the controller and the data access layer, and if we closely look into this type of code, it violates the Single Responsibility Principle as well as Open close principle. Any change related to data-access layer can break the controller code. In such a case, our code structure have the following layers :

Repository pattern using C#

As seen from the above structure, layer 1 is directly using the database context and connects with the sql server. This is where the repository pattern can be implemented, to separate the layers. Our aim will be to separate the controller and the data access layer(database context) by using an intermediate layer i.e. repository layer, for communication between the two. So our code structure now changes to the following :

Repository pattern using C#

Example to use Repository pattern using C# :

To implement this pattern, we can either use the approach of having one-repository per model, which means, each entity of the database will have its own repository, or we can use a generic repository, which can be used by all the entities of the database.

For our example, we will be using the generic repository, with database first approach along with entity framework 5.0 and .Net framework 4.0. So let’s start with the example.

What is generic repository ?

A generic repository is a generic class, with basic CRUD methods in it (and of-course other methods can be added as per the need). This class and its member functions can be used for any entity of the database. This means, if we have entities Customers and Orders, this single generic class can be used for both of them.

To start with, we will create a sample database with a table to store the Customer data. We will be creating a simple form in mvc to add basic details in the table we created. Next, we will create a generic repository with our required operations to get all records, add/update/delete records. So our repository would look like the following.

Repository pattern using C#

Here, TEntity is the generic type which can be any entity of the database i.e. it could be Customer or it can be Orders. All the operations will be applied on this TEntity type only.

Next, our task will be to create a generic unit of work class. If you are not aware of the Unit of work in repository pattern, then I would suggest you to read this previous article to get an idea about this. Just to re-iterate about unit of work, it will act as the mediator between the mvc controller and the generic repository, to perform all database transactions in a single go (will discuss later in the article). This is because SaveChanges will be called by the instance of unit of work class, only after it has performed operations for the entities involved. So our generic unit of work code will be like following:

Getting started with Repository pattern using C#

Here, we have a method named GetRepoInstance, which returns an instance of the type GenericRepository<TEntity>, where, TEntity will represents the entity for which we are going to perform the database operations. For example, if we are going to work with Customer entity, it will return instance GenericRepository<Customer> and if we are going to work with Order entity, it will return GenericRepository<Order>. Next we have the SaveChanges method to commit all the database transactions in one go, by the current instance of unit of work we are using.

So we are done with setting up the unit of work class. Now we move in to the controller and will write the code for get/add/update/remove data. Let’s start with the listing of the records. We will discuss the GetAllRecords repository method and on the same lines, rest of the methods will be called by the controller. To start with, we will create the Customer repository instance, via use of the unit of work class. So we instantiate the unit of work class and call GetRepoInstance method to get the instance of the Customer repository. Now, when we call any method of the generic repository, it will call that method for the Customer entity, as it holds the reference to TEntity type at that time. This is because, when we initialized the generic repository, we created a DbSet of the type TEntity, in its constructor. So any type of CRUD operation is performed on the DbSet. See the code below:

Getting started with Repository pattern using C#

And, our controller method to fetch the records will look like the following:

Repository_3That’s it. We are done with the code to get the records. Run the application and see the results. Similarly, we can write the code for the other operations of update/add/delete. Just instantiate the unit of work and get the instance for the entity repository. Call the methods and it’s all we have to do. Rest of our code will look like the following:

Getting started with Repository pattern using C#

Getting started with Repository pattern using C#

Why unit of work ?

Earlier in our article, we discussed a point about the preferred use of unit of work class. The advantage of using this class is the fact that when we are performing multiple operations in one single controller method, we will be committing all the changes in the database by calling the SaveChanges only once and that is through the unit of work instance.
For example, in our above delete method, we might also want to delete the Address related to that Customer(assuming it is in a separate table). In that case, we find the associated address record using the GetFirstOrDefault() method and call the delete operation for it. Finally, we call the SaveChanges. In such a situation, if our first transaction fails, the other one will be also blocked, as the SaveChanges is never executed and thus maintains the database consistency.

So this was a basic article to get started with the repository pattern. Hope you enjoyed reading it…!!!

Posted in Design and Architecture, Design Patterns | Tagged | Leave a comment

Unit of Work in Repository Pattern

Unit of Work is the concept related to the effective implementation of the repository pattern. To understand this concept in better way, it is important to know the concept of repository pattern. We will not get into the details of the repository pattern in this discussion. But a small idea of this concept is necessary to proceed further.

What is Repository pattern ?

A repository is nothing but a class defined for an entity, with all the operations possible on that particular entity. For example, a repository for an entity Customer, will have basic CRUD operations and any other possible operation related to it. A repository pattern can be implemented in following ways :

  • One repository per entity (non generic) : This type of implementation involves the use of one repository class for each entity. For example, if you have two entities Order and Customer, each entity will have its own repository.
  • Generic repository : A generic repository is the one which can be used for all the entities i.e. it can be either used for Order or Customer or any other entity.

What is Unit of Work in Repository pattern ?

Unit of Work is referred to as a single transaction, which involves multiple operations of kind insert/update/delete etc. To say it in simple words, it means that on particular user action(say registration on a website), all the transactions like insert/update/delete etc. are done in one single transaction, rather then doing multiple database transactions. This means, one unit of work here involves insert/update/delete operations, all in one single transaction.

To understand this concept, consider the following implementation of repository pattern using non-generic repository, for a Customer entity.

Unit of Work

The above code seems to be fine. The issue arises when we add repository for another entity, say Order. In that case, both repositories will generate and maintain their own instance of the DbContext. This may lead to issues in future, as each DbContext will have its own in-memory list of changes of the records, of the entities, which are getting added/updated/modified, in a single transaction/operation. In such a case, if the SaveChanges of one of the repository fails and other one succeeds, it will result in database in-consistency. This is where the concept of UnitOfWork comes in the the role.

To avoid this, we will add another layer or intermediate between the controller and the Customer repository. This layer will act as a centralized store for the all repositories to receive the instance of the DbContext. This will make sure that, for a unit of transaction, which spans across multiple repositories, should either complete for all entities or should fail entirely, as all of them will share the same instance of the DbContext. In our above example, while adding data for the Order and Customer entities, in a single transaction, both will use the same DbContext instance. This situation, without and with Unit of work, can be represented like following :

Unit of work in repository pattern

In above representation, during a single operation, which involves Customer & Order entities, both of them use the same DbContext instance. This will make sure that even if one of them breaks, the other one is also not saved, thus maintaining the database consistency. So when SaveChanges is executed, it will be done for both the repositories.

Let us implement this concept in our example. We add a new class called UnitOfWork and this class will receive the instance of the DbContext. The same class will further generate the required repository instances i.e. repository instances for Order & Customer and pass the same DbContext to both the repositories. So our UnitOfWork will be like the following :


And, our Customer Repository will be changed, to receive the instance of DbContext from the unit of work class. See the code below :

Unit of Work in repository pattern

Similarly, we can have the code for the Order repository. Finally, our controller code will be like the following :

Unit of Work in repository pattern

Here, both the Order and Customer repository use the same instance of DbContext and we are executing the save changes using the instance unit of work class. So the changes of a single transaction are either done for both or none. Run the code and see the results.

So this was about the concept of unit of work in repository pattern. Any suggestions are welcome.

Posted in Design and Architecture | Leave a comment