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!!!
Comments
Anonymous
January 22, 2008
In LINQ to SQL you play with pure .NET objects and write your LINQ on top of that. But question mightAnonymous
January 22, 2008
Can you chime in about this: http://shrinkster.com/u2i Is there a way to query a table dynmically?Anonymous
January 23, 2008
@B, Yes you can run simple Select statements (T-SQL) from LINQ to SQL but that will not be strongly typed.Anonymous
January 23, 2008
The comment has been removedAnonymous
January 31, 2008
B, have a look at dynamic Linq: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspxAnonymous
April 28, 2008
English would be a good start.