Code - Highly efficient LINQ to SQL data layer using C# and SQL Server 2008 – Part 1

We all know that a data layer done in LINQ to SQL can be developed very quickly and it is also considered a rapid development tool. However, LINQ to SQL is usually subjected to criticism for following shortcomings:

1. Data Context or DBML file may become cluttered for a big database. 
2. Entities are directly mapped to SQL Server schemas, hence; mapping a single entity to multiple tables is not possible. 
3. Data fetched by dynamic SQL statement is slower if you query on entities directly and try to fetch related data for objects in hand. 

In this blog post, I will walk you through the steps of creating a very basic LINQ to SQL data layer which will demonstrate how to overcome the above mentioned shortcomings of LINQ to SQL leading to create a cost effective and efficient data layer.

Ok, let’s get started.

• Create a new database in SQL Server and create tables and their relationships as shown in following figure:

• After you are done creating tables create a database view as follows:

CREATEVIEW DomainEntView
AS
SELECT
 MT.ID,
 MT.LookupTableID,
 MT.Name,
 MT.Description,
 MT.Created,
      LT.Name AS LookupTableName,
''AS LinkedData
FROM  MainTable MT
 INNERJOIN LookupTable LT ON MT.LookupTableID = LT.ID

Add 3 records in LookupTable and 100000 records in MainTable and LinkedTable through a script.

Now we are going to create four stored procedures as follows:

CREATEPROCEDURE GetMainTableDataOnly
AS
SELECT
  ID,
  LookupTableID,
  Name,
  [Description],
  Created
FROM    MainTable

CREATE PROCEDURE GetLinkedDataByID
(
 @LinkedTableID int
)
AS
SELECT
  LT.ID,
  LT.MainTableID,
  LT.Address,
  LT.EmailAddress,
  LT.Phone
FROM    dbo.LinkedTable LT
WHERE   LT.MainTableID = @LinkedTableID

CREATE PROCEDURE GetDataByID
(
 @ID int
)
AS
SELECT
  MT.ID,
  MT.LookupTableID,
  MT.Name,
  MT.Description,
  MT.Created,
        LT.Name AS LookupTableName,
CAST((
    SELECT
      LT1.ID,
      LT1.MainTableID,
      LT1.Address,
      LT1.EmailAddress,
      LT1.Phone
    FROM    LinkedTable LT1
    WHERE   LT1.MainTableID = MT.ID
    FOR XML PATH('LinkedTable'), ROOT('LinkedTables')
   )
   AS XML
  ) LinkedData
FROM    MainTable MT
  INNERJOIN LookupTable LT ON MT.LookupTableID = LT.ID
WHERE   MT.ID = @ID

CREATE PROCEDURE GetAllData
AS
SELECT
  MT.ID,
  MT.LookupTableID,
  MT.Name,
  MT.Description,
  MT.Created,
        LT.Name AS LookupTableName,
CAST((
    SELECT
      LT1.ID,
      LT1.MainTableID,
      LT1.Address,
      LT1.EmailAddress,
      LT1.Phone
    FROM    LinkedTable LT1
    WHERE   LT1.MainTableID = MT.ID
    FOR XML PATH('LinkedTable'), ROOT('LinkedTables')
   )
   AS XML
  ) LinkedData
FROM    MainTable MT
  INNERJOIN LookupTable LT ON MT.LookupTableID = LT.ID

At the end your database should look like this:

proceed to part 2 >>