Linq,Lambda and SQL Group by

SQL & Linq Lambda Group

Group By allows us to group rows together and additionally perform aggregate calculation which works on a group of rows. Let me first give you a simple example of what group by does using one field. I will use "SampleTable" table and group all customers by Class in other words I want to show all unique values that exist in Country Field, that is how group by works. See below example code with results (before and after)

Sql Table example

DataTable example

Group by example with Aggregate Function

You can group by fields in tables but this is rarely used on its own and very often that is combined with aggregate function. Aggregate function gets access to grouped rows. So if you group 3 rows into 1 then you can perform aggregate calculation like count which in this case would give result 3. You can also use other aggregate functions like SUM, MAX, MIN, AVG and several other ones. See below example that is the same as previous ones but now with Count aggregate function.

select class,COUNT(Class) from SampleTable Group by Class

Linq Output:

 var query = from p in dt.AsEnumerable()

                        group p by p.Field<string>("Class") into g

                        select new { ClassType = g.Key.ToString(), CCount = g.Count() };

Lambda Output:

 var query = dt.AsEnumerable().GroupBy(n => n.Field<string>("Class")).
                Select(y => new
                {
                    ClassType = y.Key.ToString(),
                    CCount = y.Count()
                });

LinQ Query Where Condition

Introduction

Imagine a scenario in which we have a collection of objects and want to allow the user to filter the collection by filtering on combination of properties. To make the scenario concrete, let's assume that our object is declared as follows:

namespace SampleLinq

{

    public class Genericuselinq

    {

        public int Id { get; set; }

        public string Name { get; set; }

    }

}

Now suppose we have a collection of Genericuselinq like this (this is just for explanation purposes, in real life you would get a much bigger collection from database):

List<Genericuselinq> objGL = new List<Genericuselinq>();

            Genericuselinq obj = new Genericuselinq();

            obj.Id = 1;

            obj.Name = "sriram";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 1;

            obj.Name = "sri";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 1;

            obj.Name = "sree";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 1;

            obj.Name = "srithar";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 1;

            obj.Name = "sridevi";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 6;

            obj.Name = "arun";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 5;

            obj.Name = "malar";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 14;

            obj.Name = "peter";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 13;

            obj.Name = "kumar";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 123;

            obj.Name = "John";

            objGL.Add(obj);

Suppose we want to allow the user to filter the collection on any property or any combination of properties (on our UI form). One way would be to have a function for each property and each combination of properties, something like:

Ex 1:

 List<Genericuselinq> objGL = GenericValues();

            var output = (from t in objGL

                          where t.Name == "sriram"

                          select t).FirstOrDefault();

            Console.WriteLine("Id  "+output.Id+"    Name  "+ output.Name);

FirstOrDefault is almost the same as First. The difference is how it handles empty collections. If a collection is empty, it returns the default value for the type. This method simplifies code. 

Ex 2:

List<Genericuselinq> objGL = GenericValues();

            var output = (from t in objGL

                          where t.Name.StartsWith("s")

                          select t).ToList();

            foreach (var w in output)

            {

                Console.WriteLine("Id : " + w.Id + "    Name  :  " + w.Name);

            }

            Console.Read();

ToList converts collections to List instances. It is a fast and easy-to-remember method. It returns a List instance with the appropriate elements. It creates a new List internally, with the List constructor

Generics With lambda expression and Linq in C#

Generics:

As we know C# is a strongly typed language, programmers have to specify the type of an object at the time of writing the program.

"Generics introduce to the .NET Framework the concept of type parameters, which make it possible to design classes and methods that defer the specification of one or more types until the class or method is declared and instantiated by client code."

We can write a generic method for sorting an array of objects, then invoke the generic method separately with an int array, a double array, a string array and so on, to sort each different type of array.

Generic methods enable us to specify, with a single method declaration, a set of related methods. Generic classes enable us to specify, with a single class declaration, a set of related classes. Similarly, generic interfaces enable us to specify, with a single interface declaration, a set of related interfaces. Generics provide compile-time type safety.

They are equivalent. The compiler changes the query expression into the equivalent Lambda expression before compiling it, so the generated IL is exactly the same.

Example

var result = (from p in objGL

               orderby p.Name

               select p).ToList()

is exactly the same as

var result = IEnumerable<Genericuselinq> query = objGL.OrderBy(x => x.Name.ToLower()).ToList();

Note that if you write the query expression like this:

var result = (from objGL

             orderby p.Name
             select p).ToList()

It's converted to:

var result = objGL.orderby ( p => p.Name);

The final call to Select is omitted because it's redundant.

namespace SampleLinq

{

    public class Genericuselinq

    {

        public int Id { get; set; }

        public string Name { get; set; }

    }

}

private static void LambdaAndLinq()

        {

            List<Genericuselinq> objGL = new List<Genericuselinq>();

            Genericuselinq obj = new Genericuselinq();

            obj.Id = 1;

            obj.Name = "sriram";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 6;

            obj.Name = "arun";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 5;

            obj.Name = "malar";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 14;

            obj.Name = "peter";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 13;

            obj.Name = "kumar";

            objGL.Add(obj);

            obj = new Genericuselinq();

            obj.Id = 123;

            obj.Name = "John";

            objGL.Add(obj);

            IEnumerable<Genericuselinq> query = objGL.OrderBy(x => x.Name.ToLower()).ToList();

            Console.WriteLine("lambda expression in c#");

            foreach (Genericuselinq word in query)

            {

                Console.WriteLine("Id = " + word.Id + "  Name  =  " + word.Name);

            }

            Console.WriteLine("Linq in C#");

            List<Genericuselinq> sortedProducts = (from p in objGL

                                                   orderby p.Name

                                                   select p).ToList();

            foreach (var w in sortedProducts)

            {

                Console.WriteLine("Id : " + w.Id + "    Name  :  " + w.Name);

            }

            Console.Read();

        }