While experimenting with the Beta 3 of the ADO.NET Entity Framework, I came across an issue of storing a LINQ Query result set in session so that I can retrieve it latter in the application for use, instead of querying the entity every time.  I typically did this in ASP.NET with DataSets.  When the application started, I would query for a bunch of data, store it in a session object and then when the application needed it, I would retrieve the DataSet from session and use it.

But because LINQ and ADO.NET query on demand, and not all at once, you need to use the “ToList” method to enable LINQ to query the entire set of data at once.  We need to do this, because the goal is to store this in session, and to do that we need to have the entire result set.

Another issue that I found was that the “ToList” method, when used with the ADO.NET Entity Set loses its relationships.  For example, if you have a Customer table linked with and Order Table and create an ADO.NET Entity Object that reference these two tables, then call the ToList after Joining these table together, you will lose the relationships.

Below is an example of this issue.

var qCustomers = (from Customers in Context.Customers join Orders in Context.Orders on Customers.Orders.OrderID equals Orders.OrderID select Customers).ToList();

If you execute this query then you will get all of the Customers fields, you will see the Orders field, but it will be null?  I am not sure why, but it probably has to do with some flattening of the entity when it dumps it using the “ToList”.

The other problem is that Customers can not be added to session and then retrieved from session in the proper type because it is Anonymous.  You cannot reverse cast an Anonymous type.

To get around these issues is not that hard, you simple need to create a serializable class to store the data you want from the LINQ Query.  Then in the LINQ Select method, select this new class and populate the classes properties.  This way you will be able add the data to session and then retrieve it using a known type.

Below is an example of this.

- Class Definition –

[Serializable]

public class CustomerOrders

{

      Public int CustomerID {get; set;}

      Public int OrderID {get; set;}

}

- LINQ Query –

var qCustomers = (from Customers in Context.Customers join Orders in Context.Orders on Customers.Orders.OrderID equals Orders.OrderID select new CustomerOrders()

{

      CustomerID = Customers.CustomerID,

      OrderID = Customers.Orders.OrderID

}

// Store the list in session

Session[“Customer_Orders”] = qCustomers

// Get the list collection out of session (Cast it to the custom class structure so that we can query it again.)

System.Collections.Generic.List<CustomerOrders> objCustomerOrders = (System.Collections.Generic.List< CustomerOrders >)Session["Customer_Orders"];

 And that’s it.  Hope this demonstrates how you can use LINQ to query an ADO.NET Entity and store the results in session.