Lambda Expressions In Entity Framework

From the two options I mentioned in my last post, I chose lambda expression for this post for those programmers who are not much comfortable with SQL like LINQ query. Microsoft gives us very good option called “Lambda Expression”. In this post I am going to demonstrate below operations:

1. Simple select query using LINQ & Simple select query using lambda expression
2. Select query with where condition using LINQ & select query with where condition using lambda expression
3. Inner Join using LINQ & Inner Join using lambda expression
4. Left Join using LINQ & Left Join using lambda expression
5. Group by using LINQ & Group by using lambda expression
6. Sorting using LINQ & Sorting using lambda expression

1. Simple select:

   // Using LINQ
   Employee[] allEmployees1 = (from E in NorthwindEntity.Employees
   select E).ToArray();
   // Using lambda expression
   Employee[] allEmployees2 = NorthwindEntity.Employees.ToArray();
   
   // Where condition using LINQ
   Employee[] allEmployees1 = (from E in NorthwindEntity.Employees
   where E.ReportsTo == 1
   select E).ToArray();
   // Where condition using lambda expression
   Employee[] allEmployees2 = NorthwindEntity.Employees.Where(w => w.ReportsTo == 1).ToArray();

   
   
2. Join using:

   // JOIN using LINQ
   var allData1 = from E in NorthwindEntity.Employees
   from O in NorthwindEntity.Orders
   where E.EmployeeID == O.EmployeeID
   select new { Employees = E, Orders = O };
   
   // JOIN using lambda expressions
   var allData2 = NorthwindEntity.Employees // Source table
   .Join(NorthwindEntity.Orders // Target table
   , E => E.EmployeeID // EmployeeID of Employees table
   , O => O.EmployeeID // EmployeeID of Orders table
   , (E, O) => new { Employees = E, Orders = O })
   .Select(s => new { s.Employees, s.Orders }); // .Select() method can be ignored as it's already been selected in .join() method

   
   
3. Left join:

   // LEFT JOIN using LINQ
   var allProductsWithCategory1 = from p in NorthwindEntity.Products
   let c = (from c in NorthwindEntity.Categories
   where c.CategoryID == p.CategoryID
   select c).FirstOrDefault()
   select new { Productname = p.ProductName, CategoryName = c.CategoryName };
   
   // LEFT JOIN using lambda expression
   var allProductsWithCategory2 = NorthwindEntity.Products // Source table
   .GroupJoin(NorthwindEntity.Categories // Target table
   , p => p.CategoryID // Product's CategoryID
   , c => c.CategoryID // CategoryID
   , (p, c) => new { products = p, categories = c })
   .SelectMany(s => s.categories.DefaultIfEmpty()
   , (s, categories) => new { ProductName = s.products.ProductName, CategoryName = categories.CategoryName });

   
   
4. Group by:

// Group by using LINQ
var AllEmployee1 = from E in NorthwindEntity.Employees
group E by E.LastName into G
select new { LastName = G.Key, Group = G };

// Group by using lambda expression
var AllEmployee2 = NorthwindEntity.Employees.GroupBy(g => g.LastName)
.Select(s => new { LastName = s.Key, Group = s });

foreach (var employees in AllEmployee2) // OR in AllEmployee1
{
// employees.Group is the current group 
// Here you will find each group ( using employees.Group ) for employees.LastName

foreach (var employee in employees.Group)
{
// Here you will find each employee in particular group
// You can access each property of Employee object here.
}
}

5. Sorting:

// Order by using LINQ
var allEmployeesASCE1 = from E in NorthwindEntity.Employees
orderby E.LastName ascending
select E;

var allEmployeesDESC1 = from E in NorthwindEntity.Employees
orderby E.LastName descending
select E;

// Order by using lambda expressions
var allEmployeesASCE2 = NorthwindEntity.Employees.OrderBy(o => o.LastName);

var allEmployeesDESC2 = NorthwindEntity.Employees.OrderByDescending(o => o.LastName);

So, that’s all for today. My next post will be for bulk insert / update / delete operations using entity framework. Will try to post it by tomorrow. Bye for now…