Demystifying DLINQ: Part2 - Setting up a DataContext Class

Posted on 6/30/2006 @ 8:55 PM in #Vanilla .NET by | Feedback | 10731 views

Welcome to post #2 of Demystifying DLINQ. This serves as a perfect background for my upcoming BoF on DLINQ at TechED with Bob Beauchemin, and various articles that I will be blogging about over here.

In my last post, Demystifying DLINQ: Part1 - An introduction to DLINQ, I talked about setting up a strongly typed DataContext, so you can instead of executing a SQL Query like -

Select * from Customers

.. you could instead execute C# LINQ code that gives you a business object "Customer" back as shown below - 

Customer c =
      (from c in db.Customers
      where c.CustomerID == "ALFKI"
      select c).First<Customer>();


With such an object available, you work with your relational data, in a much more intuitive object-oriented (no pun intended, really!!) way.

In this blogpost we will dive deeper on how to create such a strongly typed DataContext. Assume that you have a sample database setup, here is the script that created 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

And here is a database diagram representing the above tables.

Very cool, so now the Q is, how do I map the above db diagram, into a DataContext class?

Well, first of all, you don't have to. You can use the base/underlying DataContext class. But in many cases, you probably will want to inherit from System.Data.Dlinq.DataContext, and create your own DataContext class. I'm guessin' this is the DataSet killer we had been waiting for - but lets wait and see.

So the TestDataContext class can be represented as below -

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

So you do need to create two classes, representing "Entity" and "EntityDetail". Well lets start with "Entity". It's class definition is pretty easy -

[Table(Name="Entity")]
public class Entity
{
...
}

The "Name" property is really not necessary, because it matches the DB, but I specified it anyway, for two reasons - a) To explicitly map to a table, which I could then put the name in a resource/sattelite assembly, so if the structure changes, I can update it later. b) To be cool.

Of course in most scenarios, you won't have to handwrite this code, but I think it is 100% necessary to understand what the autogen tools will do atleast for generation 1.

The next thing u gotta do is, create definition for the two columns inside the class. This is done using the code below -

[Column(Id=true,Name="EntityID")]
public Int32
EntityID;

[Column]
public string EntityName;

Allrighty. So Entity (EntityID, EntityName) is setup .... ehh not quite !! :-). See in a database, when you reference other tables, you use "JOINS". But in objects, you do Entity.EntityDetails. So we gotta specify a property called "EntityDetails".

This is where, "EntitySet" and "EntityRef" come in handy. (Also the Association attribute). The implementation of Entity.EntityDetails is shown as below -

private EntitySet<EntityDetail> _entityDetails ;
[
Association(Storage="_entityDetails", OtherKey="EntityID"
)]
public EntitySet<EntityDetail
> EntityDetails
{
 get
 {
   
return this
._entityDetails;
 }
 set
 {
   
this._entityDetails.Assign(value
) ;
 }
}

And in comparison (the "many side" in one to many), the EntityDetail's Entity property implementation is as below.

private EntityRef<Entity> _entity;
[
Association(Storage="_entity", ThisKey="EntityID")]
public Entity Entity
{
  get
  {
    return this._entity.Entity ;
  }

  set
  {
   this._entity.Entity = value ;
  }
}

Note the slight difference between the two? :-). (See bolded parts)

Thats it !!! Your DataContext class is now setup !!! Here's the full code fer ya -

public partial class TestDataContext : DataContext

{

public Table<Entity> Entity;

public Table<EntityDetail> EntityDetail ;

public TestDataContext(string connStr) : base(connStr) {}

}

 

[Table(Name="Entity")]

public class Entity

{

[Column(Id=true,Name="EntityID")]

public Int32 EntityID;

[Column]

public string EntityName;

private EntitySet<EntityDetail> _entityDetails ;

[Association(Storage="_entityDetails", OtherKey="EntityID")]

public EntitySet<EntityDetail> EntityDetails

{

get

{

return this._entityDetails;

}

set

{

this._entityDetails.Assign(value) ;

}

}

}

 

[Table(Name="EntityDetail")]

public class EntityDetail

{

[Column(Id=true)]

public Int32 EntityDetailID;

 

[Column]

public string EntityDetailName;

 

[Column]

public Int32 EntityID;

private EntityRef<Entity> _entity;

[Association(Storage="_entity", ThisKey="EntityID")]

public Entity Entity

{

get

{

return this._entity.Entity ;

}

set

{

this._entity.Entity = value ;

}

}

}

Note that primary keys are marked with [Column(Id=true)]. What if you had multiple columns acting as the primary key? (John Wood - this is an answer to your Q). Well, you simply mark multiple properties with that attribute - simple as that.

Obviously, for more complex databases, you are not going to hand-write such a class. You could instead alternatively use a tool called SqlMetal to generate a strongly typed DataContext for you. In fact SqlMetal lets you do a lot of other cool things as well (I will be blogging about those in the near future).

If you had a test database setup already, you could simply execute the following at commandline -

C:\>"c:\Program Files\LINQ Preview\bin\sqlmetal" /server:. /pluralize /database:Test  /code:TestDataContext.cs

This would setup a file called TestDataContext.cs, which would contain a strongly typed data context for you - ready to eat.

 

Sound off but keep it civil:

Older comments..


On 1/9/2008 8:37:50 AM Elan said ..
LINQ was a dark cave (to me). This article should me light of the day.


Hat's off to u to the great work.


On 4/3/2008 12:40:00 PM DataContext Transactions said ..
DataContext Transactions


On 4/25/2008 6:01:31 AM Sreenath said ..
As The LINQ ia almost new concept. It will be great if you provide some testproject download in your website. I think Business class creations(eg: Business Layer,Document Layer, Data access Layer) is enhancement of LINQ usage. Will you please send me a documentation about businessclass creation.


On 6/10/2008 10:19:44 AM Steve said ..
I always find it usefull to mention the 'Using statements'


ie


using System.Data.Linq;


using System.Data.Linq.Mapping;


.. 'cause as a new user to linq it took me ages to figure out how to do the [Table(Name=...


On 6/10/2008 10:27:40 AM Sahil Malik said ..
Steve - how about right click, refactor, add using?


On 10/9/2008 10:54:23 AM Vladimir said ..
If I have a set of tables "Entity" that I create dinamically, Entity_Day1, Entity_Day2 etc.


How dinamically to re-map class Entity to read data from Day1, Day2 tables?


Thanks


Vladimir