Wortzel's blog

Introducing to DLINQ - Entities declaration - part 1

One of the major parts in LINQ project is DLINQ – .Net Language Integrated Query for relational Data. DLINQ is a smart data access layer framework (component of the ADO.NET family), with an advanced runtime infrastructure and design time tools for managing data (queries and data manipulations) from a relational db as an object.

The usage of this framework is very easy: The basic class in DLINQ is call "Entity class", each class represent a single database table. The properties in this class will represent the table columns.

For example:

Employees table:

Departments table:

 The tables diagram look like this:

Now let's create the entity classes for these tables.

We will create new classes which are called "Employee" and "Department". We have to declare the classes as a DLINQ table by adding the "Table" attribute (This attribute found in the System.Data.Linq namespace), and declare the class properties as table columns by adding the Column attribute.

Our classes should look like this:

[Table(Name="Employees")]
class Employee
{
      [Column(IsPrimaryKey = true)]
      public int Id;

      [Column]
      public string Name;
} 
 
[Table(Name="Employees")]
class Department
{
      [Column(IsPrimaryKey = true)]
      public int Id;

      [Column]
      public string Name;
} 

Notes:

1. The Table attribute has Name parameter, it's good if you want to call your object in a different name from the name in the database table.
2. The Column attribute has several parameters for details declaration of the database column. In this example I used the IsPrimaryKey parameter that specifies if this column is the table primary key.

And now let add the relationship between those tables:

[Table(Name="Employees")]
class Employee
{
       …
    
      private EntityRef<Department> _Department;

      [Association(ThisKey = "DepartmentCode", OtherKey="Id")]
      public Department Department
      {
          get
          {
              return this._Department.Entity;
          }
          set
          {
              return this._Department.Entity = value;
          }
      }    
}


[Table(Name="Employees")]
class Department
{
         …

        private EntitySet<Employee> _Employees;
        
        [Association(ThisKey="Id",  OtherKey="DepartmentCode")]
        public EntitySet<Employee> Employees
        {
            get
            {
                return this._Employees;
            }
            set
            {
                this._Employees.Assign(value);
            }
        }
}

Note:

In the father's table of the relationship (In department table) we used the EntitySet class to hold a list of "sons". In the son's table we used the EntityRef struct in order to give us the ability to see the father object when we holding the son (if we want to get the department name for example when we holding an employee object).

Before we are going to use our new class lets talk about the DataContext engine.

The DataContext is the smart brain which works behind the scene. You just need to initialize it with the connection and use his sophisticated functionality. This engine is responsible to translate the queries and manipulation that you made on the object to a SQL statements (And in a case there is a return value it will create an appropriate object with this data). In addition, it implements the SQO (Standard Query Operators) and gives us the ability to use the standard familiar SQL syntax.

Now after we know what DataContxt is good for, let's use it. In the following example I'll create an instance of the DataContext class (Initialized by the connection string). I'll request from it to retrieve the object that manage my employees table. After I'll get that object I'll use some SQL to retrieve all employees that their names start with the string "Y". Then I'll print the results to console.

string connectioString = "...";
DataContext myDb = new DataContext(connectioString);
Table<Employee> employees = myDb.GetTable<Employee>();
var query = from c in employees
                  where c.Name.StartsWith("Y")
                  select c;
            
foreach (var var in query)
{
       Console.Write(var.Name);
} 

Note:

In this example I used the weakly-type for the table name. The best practice here is to inherit from the DataContext class, and to create collection for each table that managed by the DataContext.

Published Thursday, August 02, 2007 11:16 AM by Avi Wortzel