Wriju's BLOG

.NET, Cloud and everything

LINQ to SQL : Deferred and Immediate Loading

LINQ to SQL has support for both deferred and immediate loading. By default it supports deferred loading but you can configure your code to load the data immediately. Let’s consider the Northwind database. As we know the there are two tables Customer and Orders and one customer can have multiple orders. These two tables have relationship defined in the database. If you use Linq to Sql (.dbml) designer which generates the background code for us. Once you drag both Customers and Orders you will get the following view in designer


 


Linq to Sql designer


 


Then in the console I will write the following code,


 


static void Main(string[] args)


{


    //This takes the connection string from file Settings.settings


    //which gets generated while creating Linq to Sql (.dbml) file


    NorthwindDBDataContext db = new NorthwindDBDataContext();


 


    //Get the Customers from database


    var query = from c in db.Customers


                where c.City == London


                select c;


 


    db.Log = Console.Out;


 


    foreach (var c in query)


    {


        Console.WriteLine(“++++++++++++++++++++++++++++++”);


        Console.WriteLine(“Customer Id : “ + c.CustomerID);


        Console.WriteLine(“++++++++++++++++++++++++++++++”);


        foreach (var o in c.Orders)


        {


            //Getting the orders value for


            //each Customers


            Console.WriteLine(o.OrderID);


        }


        Console.WriteLine();


    }


}


 


 


 


The output will show up


 


++++++++++++++++++++++++++++++


Customer Id : AROUT


++++++++++++++++++++++++++++++


SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],


t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[S


ipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPost


lCode], [t0].[ShipCountry]


FROM [dbo].[Orders] AS [t0]


WHERE [t0].[CustomerID] = @p0


— @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) NOT NULL [AROUT]


SqlProvider\AttributedMetaModel


 


10355


10383


10453


10558


10707


10741


10743


10768


10793


10864


10920


10953


11016


 


++++++++++++++++++++++++++++++


Customer Id : BSBEV


++++++++++++++++++++++++++++++


SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate],


t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[S


ipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPost


lCode], [t0].[ShipCountry]


FROM [dbo].[Orders] AS [t0]


WHERE [t0].[CustomerID] = @p0


— @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) NOT NULL [BSBEV]


SqlProvider\AttributedMetaModel


 


10289


10471


10484


10538


10539


10578


10599


10943


10947


11023


 


 


Every time for each customer this will run a query to fetch the corresponding orders. This is welcomed behavior in most web scenarios as we may need to perform some action like click to view the data.


 


But if you would like to execute the query at one shot and then display the order values (immediate loading) then you need to modify the above code


 


static void Main(string[] args)


{


    //This takes the connection string from file Settings.settings


    //which gets generated while creating Linq to Sql (.dml) file


    NorthwindDBDataContext db = new NorthwindDBDataContext();


 


    //This part is new


    var shape = new DataShape();


    //This indicates that I am loading Orders with Customer   


    shape.LoadWith<Customer>(c => c.Orders);


 


    db.Shape = shape;


 


    //end of new part


    


 


    //Get the Customers from database


    var query = from c in db.Customers


                where c.City == London


                select c;


 


    db.Log = Console.Out;


 


    foreach (var c in query)


    {


        Console.WriteLine(“++++++++++++++++++++++++++++++”);


        Console.WriteLine(“Customer Id : “ + c.CustomerID);


        Console.WriteLine(“++++++++++++++++++++++++++++++”);


        foreach (var o in c.Orders)


        {


            //Getting the orders value for


            //each Customers


            Console.WriteLine(o.OrderID);


        }


        Console.WriteLine();


    }


 


 


Now the generated query will look like,


 


SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[Contact


itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Cou


try], [t0].[Phone], [t0].[Fax], [t1].[OrderID], [t1].[CustomerID] AS [CustomerI


2], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate


, [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[Sh


pCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry], (


    SELECT COUNT(*)


    FROM [dbo].[Orders] AS [t2]


    WHERE [t2].[CustomerID] = [t0].[CustomerID]


    ) AS [count]


FROM [dbo].[Customers] AS [t0]


LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]


WHERE [t0].[City] = @p0


ORDER BY [t0].[CustomerID], [t1].[OrderID]


— @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]


SqlProvider\AttributedMetaModel


 


++++++++++++++++++++++++++++++


Customer Id : AROUT


++++++++++++++++++++++++++++++


10355


10383


10453


10558


10707


10741


10743


10768


10793


10864


10920


10953


11016


 


++++++++++++++++++++++++++++++


Customer Id : BSBEV


++++++++++++++++++++++++++++++


10289


10471


10484


10538


10539


10578


10599


10943


10947


11023


 


 


Namoskar!!!