Wednesday, November 28, 2012

LINQ and equivalent LAMDA expression


This discussion I will try to provide some common LINQ statement and the equivalent LAMDA expression. This is purely personal preference to choose LINQ or Lamda. I am not going to describe which one is the best way considering performance as parameter. What I noticed both emit the same IL code after compilation. I used Entity framework 5.0 and Northwind database for this sample.

Retrieve all records
LINQ
from c in dbContext.Categories
       select c;
LAMDA
dbContext.Categories.Select(c => c);

Specific fields
LINQ
from c in dbContext.Categories
       select new { c.CategoryName, c.CategoryID };
LAMDA
dbContext.Categories.Select(c => new { c.CategoryName, c.CategoryID })

Orderby asc
LINQ
from c in dbContext.Categories
       orderby c.CategoryName
       select c;
LAMDA
dbContext.Categories.OrderBy(c => c.CategoryName);

Orderby dsc
LINQ
from c in dbContext.Categories
       orderby c.CategoryName descending
       select c;
LAMDA
      dbContext.Categories.OrderByDescending(c => c.CategoryName);

Multiple orderby
LINQ
from c in dbContext.Categories
       orderby c.CategoryName , c.CategoryID
       select c;
LAMDA
      dbContext.Categories.OrderBy(c => c.CategoryName).ThenBy(c => c.CategoryID);

Orderby asc and dec
LINQ
from c in dbContext.Categories
       orderby c.CategoryName, c.CategoryID descending
       select c;
LAMDA
dbContext.Categories.OrderBy(c => c.CategoryName)
.ThenByDescending(c => c.CategoryID);

Filter
LINQ
from c in dbContext.Categories
       where c.CategoryID.Equals(1)
       select c;
LAMDA
dbContext.Categories.Where(c => c.CategoryID.Equals(1));

Filter with multiple criteria
LINQ
from c in dbContext.Categories
       where c.CategoryID.Equals(1) && c.CategoryName.StartsWith("A")
       select c;
LAMDA
dbContext.Categories.Where(c => c.CategoryID.Equals(1) &&
       c.CategoryName.StartsWith(
"A"));

Group By
LINQ
from c in dbContext.Categories
       group c by c.CategoryName into g
       select g;
LAMDA
dbContext.Categories.GroupBy(c => c.CategoryName)

Multiple group By
LINQ
from c in dbContext.Categories
       group c by new { c.CategoryID, c.CategoryName } into g
       select g;
LAMDA
dbContext.Categories.GroupBy(c => new { c.CategoryID, c.CategoryName })


Record Count
LINQ
(from c in dbContext.Categories
        where c.CategoryName.StartsWith("B")
       select c).Count();
LAMDA
dbContext.Categories.Count(c => c.CategoryName.StartsWith("B"));

Join
LINQ
from c in dbContext.Categories
       join p in dbContext.Products1 on c.CategoryID equals p.CategoryID
       select new
              {
                 CategoryName,
                 p.ProductID,
                 p.ProductName
              };
LAMDA
dbContext.Categories.Join(dbContext.Products1, c => c.CategoryID, P =>
       P.CategoryID,((c,p)=>
new {c.CategoryName,p.ProductID,p.ProductName}));

Entity framework and data iteration



We can write following piece of code to retrieve all records using DBSet. If we look at the database pro-filer we could see “SELECT * FROM Category” statement issued in the database.

var dbContext = new NorthwindEntities()
foreach (Category cat in dbContext.Categories)
{
Console.WriteLine(cat.CategoryName);
}

Some time we may need to iterate the same set of result set multiple times in a logical unit like below.

private void LogicalUnit()
{
          var dbContext = new NorthwindEntities()

foreach (Category cat in dbContext.Categories)
{
Console.WriteLine(cat.CategoryName);
}
foreach (Category cat in dbContext.Categories)
{
Console.WriteLine(cat.CategoryName);
}
}
Apparently everything looks fine but I wondered when I looked into the pro-filer and saw above SQL statement issued twice.
To avoid this, we can perform LINQ operation such as ToList() to copy the result set into a list and iterate the list whenever required.
var categories = dbContext.Categories.ToList();

foreach (Category cat in categories)
{
Console.WriteLine(cat.CategoryName);
}

foreach (Category cat in categories)
{
Console.WriteLine(cat.CategoryName);
}

This will hit the database only once to execute the query. We can refer the local result set for further reference. This way we can bring down the repeated database hit.