DLinq: Mapping a Database to a DataContext class.
Posted by: Sahil Malik [MVP],
on 05 Apr 2006 |
View original | Bookmarked: 0 time(s)
Disclaimer: This is based on the LINQ Preview.
I just wrote up, what I thought was a cool thing that I should share about - Dlinq: Playing with Knives. (PS: If you're viewing this in an RSS Aggregator such as bloglines, and the links don't work - hey man, my apologies but the RSS produced by CS2.0 (atleast what we have here) uses relative links, which blow up in bloglines, so view this post on my blog instead)
So In that blogpost, I talked about a simple Entity->EntityDetail mapping, and how you'd query that in Linq. What I didn't gloss over was, how could such a DataContext class be created, and the basics around that. Just to jog your memory, 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 ; }
}
}
With the class setup, you are now ready to use it in your code, as described in this post here.
Share this post: Email it! |
bookmark it! |
digg it! |
reddit!