Beginning LINQ-Part II

Beginning LINQ-Part II

In Part 2, am going to explain the remaining options of select method. SQL queries allows arithmetic operations in select statement , we can do this in linq select method also.

As you can in the above code am multiplying unitprice with discount and storing it in a new field totalprice which is equivalent to giving alias name for columns in sql queries.
Select method also supports addition, subtraction, division,

Using MAX, MIN, SUM

The code shows the use of max() method of linq. It selects the max ordered, in the same way min() and sum() can also be used.

Using Where Clause
Most of the time we need to select data based on certain conditions. Linq supports conditional selecting. It has where() method which works same as where clause of query. Where method supports multiple conditions. Multiple conditions are specified using logical operators (&&,||).

Using OrderBy
The select data can be ordered ascending or descending by using orderby() and orderbydescending() method respectively.
Example,

Using GroupBy
In certain situations we need to group selected data to get the count of individual type of data. Linq have groupby() method which can be used to achieve this. It groups data and returns an IQueryable<IGrouping<key,element>> type which can be used for further querying.
Example,

In the above code, the products are grouped according to categoryid, the first foreach loop iterates through each of the igrouping objects and gets the key, Key is nothing but the field used to group values, in this case it is “categoryid” if the table is grouped on multiple conditions then the no of keys increases.
In IGrouping, the first object is the key, multiple keys are placed using comma. It must be similar to the datatype of the field used to group values.

The nested foreach loop iterates through the actual product table values and displays productname.
Since the return type of groupby() is IQueryable, it can be further queried to get the more specific results. This is shown in the line of codes after comment. 

The function produces the output

Beginning LINQ – Part I

Beginning LINQ – Part I

LINQ(Language Integrated Query) is one of the features of .NET 3.5. linq avoids the use of traditional sql queries in programming. It follows object oriented approach and linq Is type safe. Any object of type IEnumerable can be used as a datasource to perform linq operations.

There are two approaches in using linq

·         Linq Query: This is more flexible and resembles sql queries.

·         Linq Methods: Here methods from linq api are used to query a datasource.

In both the case, during execution they are converted back to sql queries to communicate with the datasource. This can be seen by placing a break point near the linq query.

In this article I’ll be dealing with Linq Methods.

To deal with database you should give reference to “System.Data.Linq” namespace.

When you add “.dbml” file to a project it automatically adds reference to set of namespace required for communicating with the database and to use Linq methods in project.

Once you add “.dbml” file drag and drop the tables needed for your project from server explorer. This will creates class files for each table and also add relationship for the table if exists. The relationship is two way (parent table can be accessed via child table and vice versa ). This feature adds to the ease of development.

Dbml file creates a datacontext class in designer.cs file which is important in establishing connection with the database.

Datacontext class is the key class required for committing all the changes back to database. Each table data can be accessed by creating an object of datacontext class.

Other than Datacontext in designer.cs file, there exists other classes which corresponds to tables added to dbml file and this class file contains properties corresponding to fields in table. Properties are of datatype similar to fields in table.

Here is a picture mapping to categories table in northwnd database and displaying its field as properties. Each properties map to corresponding fields in table. Attributes specified at the top of class and properties are responsible for mapping.

Now create an object of datacontext class in your project. Using this object we can access the table objects of respective tables which contains table data and linq methods are applied on this objects for selecting data based on conditions if any.

Note: Throughout this article I’ll be using Microsoft’s famous sample database “NorthWnd.dbo”. if you don’t have the one you can download it from here.

In the above code sample I have created a datacontext object and am using the categories table data for binding it to repeater which will display all the data from categories table.

The intellisense shows the tables that are present in the dbml file. These tables can be directly accesed through datacontext object.

In the page_load event am just binding all the data from categories to repeater.

In the categoryselect() method am using select method of linq to select only required field to display data. Here, in this case it is categoryname and description. You can question what is “=>” next to ‘c’ and why am using “new” keyword inside select method. “=>” symbol represents lambda expression similar to dynamic delegate  and “new” is because am selecting it into a new table with only 2 fields. It creates a virtual table. The return type is IEnumerable or IQueriable.

Enacapsulation Using Classes

Encapsulation in its simple terms refers to information hiding. In software development, encapsulating all the methods and classes into a module presents an interface which can be used for further development and can also be re-used. If any change requires, the change is restricted to a small subset of entire program thus saving time and cost of development.

Here am going to provide a small example of how classes are used in c# for encapsulation.

 class Program
    {
        public void method1()
        {
            Console.Write("method1 called ");
        }
        public void method2()
        { }
        static void Main(string[] args)
        {
            Class2 c2 = new Class2();
            c2.method3();
        }
    }
    class Class1
    {
        public Program prog()
        {
            return new Program();
        }
    }
    class Class2
    {
        public void method3()
        {
            Class1 c = new Class1();
            c.prog().method1();
        }
    }

In this small example, method3() of class2 is used to call method1() of  class Program through class1's method prog() which in turn returns a Program instance.