Data Access usind DLinq

DLinq is so much fun. It's so amazingly simple to write data access layer that generates really optimized SQL. If you have not used DLinq before, brace for impact!

When you use DLinq, you just design the database and then use SqlMetal.exe (comes with Linq May CTP) in order to generate a Data Access class which contains all the data access codes and entity classes. Think about the dark age when you had to hand code all entity classes following the database design and hand code data access classes. Whenever your database design changed, you had to modify the entity classes and modify the insert, update, delete, get methods in data access layer. Of course you could use third party ORM tools or use some kind of code generators which generates entity classes from database schema and generates data access layer codes. But do no more, DLinq does it all for you!

The best thing about DLinq is it can generate something called Projection which contains only the necessary fields and not the whole object. There's no ORM tool or Object Oriented Database library which can do this now because it really needs a custom compiler in order to support this. The benefit of projection is pure performance. You do not SELECT fields which you don't need, nor do you contruct a jumbo object which has all the fields. DLinq only selects the required fields and creates objects which contains only the selected fields.

Let's see how easy it is to create a new object in database called "Page": 

var db = new DashboardData(ConnectionString);

var newPage = new Page();
newPage.UserId = UserId;
newPage.Title = Title;
newPage.CreatedDate = DateTime.Now;
newPage.LastUpdate = DateTime.Now;

db.Pages.Add(newPage);
db.SubmitChanges();
NewPageId = newPage.ID;

Here, DashboardData is the class which SqlMetal.exe generated.

Say, you want to change a Page's name:

var page = db.Pages.Single( p => p.ID == PageId );
page.Title = PageName;
db.SubmitChanges();

Here only one row is selected.

You can also select a single value:

var UserGuid = (from u in db.AspnetUsers
where u.LoweredUserName == UserName && u.ApplicationId == DatabaseHelper.ApplicationGuid
select u.UserId).Single();

And here's the Projection I was talking about:

var users = from u in db.AspnetUsers
select { UserId = u.UserId, UserName = u.LoweredUserName };

foreach( var user in users )
{
    Debug.WriteLine( user.UserName );
}

If you want to do some paging like select 20 rows from 100th rows:

var users = (from u in db.AspnetUsers
select { UserId = u.UserId, UserName = u.LoweredUserName }).Skip(100).Take(20);

foreach( var user in users )
{
    Debug.WriteLine( user.UserName );
}

If you are looking for transaction, see how simple it is:

using( TransactionScope ts = new TransactionScope() )
{
    List<Page> pages = db.Pages.Where( p => p.UserId == oldGuid ).ToList();
    foreach( Page page in pages )
        page.UserId = newGuid;
    
    // Change setting ownership
    UserSetting setting = db.UserSettings.Single( u => u.UserId == oldGuid );
    db.UserSettings.Remove(setting);
    
    setting.UserId = newGuid;
    db.UserSettings.Add(setting);
    db.SubmitChanges();

    ts.Complete();
}

Unbelievable? Believe it.

You may have some mixed feelings about DLinq performance. Believe me, it generates exactly the right SQL that I wanted it to do. Use SqlProfiler and see the queries it sends to the database. You might also think all these "var" stuffs sounds like late binding in old COM era. It will not be as fast as strongly typed code or your own hand written super optimal code which does exactly what you want. You will be surprised to know that all these DLinq code actually gets transformed into pure and simple .NET 2.0 IL by the Linq compiler. There's no magic stuff or no additional libraries in order to run these codes in your existing .NET 2.0 project. Unlike many ORM tools, DLinq also does not heavily depend on Reflection.

Published Thu, Dec 28 2006 7:47 by omar
Filed under: