Posted by: Sahil Malik [MVP], on 05 Apr 2006 | View original | Bookmarked: 0 time(s)

Allrighty, that's the best way I can describe DLinq. It's a lot of power, but if you're not careful, you'll definitely cut yourself.

So here goes.

Lets begin by setting up a database, lets keep things simple, and create two tables, Entity and EntityDetail. This kind of One to Many kind of relationship aptly lends itself in describing the interesting stuff I am about to elucidate.

So here's a script to setup the DB.

Create Database Test
Go

Use Test
Go

Create Table Entity
(
 EntityID INT IDENTITY PRIMARY KEY,
 EntityName Varchar(100) NOT NULL
)
Go

Create Table EntityDetail
(
 EntityDetailID INT IDENTITY PRIMARY KEY,
 EntityID INT REFERENCES Entity(EntityID),
 EntityDetailName Varchar(100) NOT NULL
)
Go

Okay awesome. Running this script should setup a database depicted by the following database diagram.

Allright, with the tables setup, lets go ahead and insert some data. The following T-SQL Script will insert one entity and two related EntityDetails.

BEGIN
DECLARE @LASTID INT
 Insert Into Entity (EntityName) Values ('TestEntity')
 Select @LastID = SCOPE_IDENTITY()
 Insert Into EntityDetail(EntityID, EntityDetailName) Values (@LastID, 'Detail 1') ;
 Insert Into EntityDetail(EntityID, EntityDetailName) Values (@LastID, 'Detail 2') ;
END
Go

Great, so our database is setup. Now lets go ahead and setup a strongly typed DataContext class. For those new to DLinq, a DataContext class is what sits between your objects, and your database. You can either use System.Data.Dlinq.DataContext directly, or in most scenarios you would probably want to create a class that inherits from DataContext.

In most scenarios, you probably want a tool to do this for you, but I'm going to hand-write a DataContext class. For more explanation of how this DataContext class is structured, please see this post. (I can't seem to override the relative linking, so if this doesn't work in an RSS Aggregator, open this post in your browser).

For this example, the DataContext class is rather simple as shown below.

public partial class TestDataContext : DataContext
{
  public Table<Entity> Entity;
  public Table<EntityDetail> EntityDetail ;
  public TestDataContext(string connStr) : base(connStr) {}
}

Again, for a detailed explanation of what "Entity" is, and what "EntityDetail" is (as far as their class implementations), look at this post. (I can't seem to override the relative linking, so if this doesn't work in an RSS Aggregator, open this post in your browser).

Wonderful. With our DataContext setup, we can now go ahead, and start usin' it to execute some Linq queries.

The first thing to do obviously is to setup an instance of the DataContext as shown below -

string connStr = "Data Source=(local);Initial Catalog=Test;Integrated Security=True" ;
TestDataContext db = new TestDataContext(connStr) ;

Great, now we have the sentry, translater, or what I like to call "StarGate" between my objects, and my database. Lets go ahead and execute a Linq Query.

var q =
    from e in db.Entity
    select e ;

This query executes a query similar to

Select EntityID, EntityName from Entity

(well almost like that).

But that is not what I'm after. :-)

Lets write a query with Joins.

var q =
   from e in db.Entity, ed in e.EntityDetails
   where e.EntityName == "TestEntity"
   select new {e.EntityName, ed.EntityDetailName} ;

foreach (object qPart in q)
{
   Console.WriteLine(qPart.ToString()) ;
}

This query will produce two rows, as below

{EntityName=TestEntity, EntityDetailName=Detail 1}
{EntityName=TestEntity, EntityDetailName=Detail 2}

.. now .. (and here is where we get sneaky) ..  The above query could be rewritten as --

var p =
  from ed in db.EntityDetail
  where ed.Entity.EntityName == "TestEntity"
  select new { ed.Entity.EntityName, ed.EntityDetailName} ;

foreach (object pPart in p)
{
  Console.WriteLine(pPart.ToString()) ;
}

This query again produces the same result as above -

{EntityName=TestEntity, EntityDetailName=Detail 1}
{EntityName=TestEntity, EntityDetailName=Detail 2}

Well so the above two queries are the same .. right? RONG !!! DAMRONG !!!

See the difference lies in the queries that are actually sent to the database. Look at the query sent in the first case (well, it was parameterized, but I removed that for clarity).

SELECT
[t0].[EntityID], [t0].[EntityName], [t1].[EntityID]
AS [EntityID1], [t1].[EntityDetailID], [t1].[EntityDetailName]
FROM
[Entity] AS [t0], [EntityDetail] AS [t1]
WHERE
([t0].[EntityName] = 'TestEntity') AND
([t1].[EntityID] = [t0].[EntityID])

and then, compare it with the query that was sent in Case #2 (again, I removed the parameterization for clarity):

Category: XLinq | Other Posts: View all posts by this blogger | Report as irrelevant | View bloggers stats | Views: 1815 | Hits: 82