LINQ to SQL : Understanding Mapping

In LINQ to SQL you play with pure .NET objects and write your LINQ on top of that. But question might arise how this magic happens. Though we say that it is object, the question might arise in our mind that there has to be a mechanism through which it connects. Yes, there is. That is DataContext which controls the background transaction. But again how it understands that your class is actually representing a database object, is only though Attribute magic.

There could be different database components,

Database Æ DataContect

Table Æ Class

Column Æ Property/Field

Relationship Æ Property/Field

View Æ Class

Stored Procedure Æ Function

Now when we write code for that how we define them?

Let us have a database TestDB and a table Emp.

CREATE TABLE [dbo].[Emp](

      [Id] [int] IDENTITY(1,1) NOT NULL,

      [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

  CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED

(

      [Id] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

Step 1:

++++

Create a table representation,

public class Employee

{

    public int Id { get; set; }

    public string Name { get; set; }

}

Now map this class for dbo.Emp table through attribute.

[Table(Name="dbo.Emp")]

public class Employee

{

public int Id { get; set; }

public string Name { get; set; }

}

Step 2:

++++

Now you need to map columns,

[Column(IsDbGenerated=true, IsPrimaryKey=true)]

public int Id { get; set; }

Now this is because the Id column is Primary Key and automatically generated column.

[Column]

public string Name { get; set; }

Notice here I am not specifying the “Name=” is because the property name and the column names are identical.

Step 3

++++

You have Database which ideally DataContext should be able to handle, so if you do not want to create class for that, you can very well do that. Like

DataContext db = new DataContext(@"Connection String");

var query = db.GetTable<Employee>();

But if you really would like to get the strongly typed behavior with statement completion then the approach would be little different.

public class TestDB : DataContext

{

    public TestDB(string sConn): base(sConn){}

    public Table<Employee> Emps;

}

Now if you start writing the same code this will give you the itellisense and statement completion,

TestDB db = new TestDB(@"Connection String");

var query = db.Emps;

var query1 = from e in db.Emps

             where e.Id == 1

             select e;

Hope you liked this.

Namoskar!!!