Data Access using DLinq Quite Unbelievable? Believe it.

I have used couple of times during my application development,and its really fantastic.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!

DLinq, a component of the LINQ Project, provides a run-time infrastructure for managing relational data as objects without giving up the ability to query. It does this by translating language integrated queries into SQL for execution by the database and then translating the tabular results back into objects you define. Your application is then free to manipulate the objects while DLinq stays in the background tracking your changes automatically.
DLinq is designed to be non intrusive to your application. It is possible to migrate current ADO.Net solutions to DLinq in a piecemeal fashion, sharing the same connections and transactions, since DLinq is simply another component in the ADO.Net family.
DLinq applications are easy to get started. Objects linked to relational data can be defined just like normal objects, only decorated with attributes to identify how properties correspond to columns. Of course, its not even necessary to do this by hand. A design-time tool is provided to automate translating pre-existing relational database schemas into object definitions for you.
Together, the DLinq run-time infrastructure and design-time tools significantly reduce the work load for the database application developer. The following chapters provide an overview of how DLinq can be used to perform common database related tasks. It is assumed that the reader is familiar with Language Integrated Query and the Standard Query Operators.

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.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s