LINQ to SQL : Understanding Mapping

LINQ to SQL : Understanding Mapping

  • Comments 6

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!!!

Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post
  • In LINQ to SQL you play with pure .NET objects and write your LINQ on top of that. But question might

  • Can you chime in about this:

    http://shrinkster.com/u2i

    Is there a way to query a table dynmically?

  • @B,

    Yes you can run simple Select statements (T-SQL) from LINQ to SQL but that will not be strongly typed.

  • I understand that stongly typed is out of the question, because I don't know of the columns until runtime.  I want to take advantage of the deferred execution and some of the other features..

    I have a dream...my dream is to do something like this using Dynamic LINQ:

    var q = db.CustomTable.Where("Age = @0", 21);

    q = q.Where("Gender = @0", "M");

    foreach(var a in q.Skip(10).Take(10){}

    Then it would be my responsibility to make sure that "Age" and "Gender" where valid columns..

    This would allow me to enable our customers to make a lot of filters, reports, etc... while taking advantage of the SQL Generated..that is my dream.

    I've expiremented a lot and can't figure out how to make this work.  Any pointers would be GREATLY appreciated.

  • 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.aspx

  • English would be a good start.

Page 1 of 1 (6 items)