DbSet.Find() in Entity Framework

DbSet.Find method in Entity framework provides some cool and awesome features.
Unlike other it doesn’t except lambda expression whereas it accept only primary key(s) value.

var cat = dbContext.Categories.Find(1);

Above will retrieve the category where category id is 1. “categoryid” is defined as primary key in the underlying table. Incase composite primary key we should pass values in the same sequence of keys.

var data = dbContext.Products.Find(1, 10);

So far, behavior is simple but nothing special. Wait ….following features will make it incredible.
Find() will always not make a database trip to fetch the data rather it supports following principles.

• Find the data in memory and returns it.

var cat = dbContext.Categories.Find(1);
cat.Description = “Modified”;
//Modified data in the memory will be returned
cat = dbContext.Categories.Find(1);

• Look at the newly added data in memory.

var cats = new Category
{
    CategoryID = 1001, CategoryName = “New Category”,
    Description = “New Category Description”
};
dbContext.Categories.Add(cats);
// newly added data in the memory will be returned
cats = dbContext.Categories.Find(1001);

• It makes a database trip only when 1 & 2 fails to return any data.

Wonderful isn’t it. I really liked it very much

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
{
c.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.

Business Entity as XML

This article I have posted in dotnetfunda.com on 13th-Feb-2010. This is all about how to convert a business entity to XML. Refer below the link for details discussion.
Convert Business Entity or Entity collection as XML

ADO.NET DataTable as XML parameter to an Oracle/SQL Server Database Stored Procedure

I have posted this article in codeproject.com on 27th-Jan-2010. This article is all about how to convert ado.net datatable to XML and pass it as a parameter to backend. To get the detail please refer belo the link

ADO.NET DataTable as XML parameter to an Oracle/SQL Server Database Stored Procedure