Operation CRUD with edmx and objectdatasource error Idisposable

Question

I have 3 tiers apps.

In the Persistance layer the is just the drag 'n' drop Entities.edmx file.

In the Presentation layer :

<asp:ObjectDataSource ID="ObjectDataSource_Tva" runat="server" 
        DeleteMethod="del_Tva" InsertMethod="Inst_Tva" 
        SelectMethod="Get_All_Tva" 
        TypeName="FaExped_BackEnd_WebApp_Business.Le_T.LeTVA_Entite_BL" 
        UpdateMethod="Updt_Tva">
    <DeleteParameters>
        <asp:Parameter Name="Id" Type="Int32" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="Id" Type="Int32" />
        <asp:Parameter Name="Libelle" Type="String" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="Id" Type="Int32" />
        <asp:Parameter Name="Libelle" Type="String" />
    </UpdateParameters>
</asp:ObjectDataSource>

And a gridview that connects to this objectdatasource.

In my busines logic layer, when I use it like this:

public IEnumerable<T_TVA> Get_All_Tva()
{
    FaExpedEntities oEntite_T = new FaExpedEntities();
    var query = from o in oEntite_T.T_TVA select o;
    IEnumerable<T_TVA> LesTva = query;
    return LesTva;       
}

It works, but when I use like this:

public IEnumerable<T_TVA> Get_All_Tva()
{
    using (FaExpedEntities oEntite_T = new FaExpedEntities())
    {
        var query = from o in oEntite_T.T_TVA select o;
        IEnumerable<T_TVA> LesTva = query;
        return LesTva;
    }         
}

It does not work. It said that the instance of ObjectContext has been deleted and cannot be used for operations that require a connection.

Why?

What is the difference such that using "using" syntax fails and not using "using" works?

Which is the better approach, with or without the "using"?

Answer 1

I suspect your error with the code that has the using statement is due to deferred execution. Essentially it means that the database connection has been closed before the LINQ query is executed to retrieve the results.

One common work around is to 'force' the LINQ query to be immediately executed using a non deferred operator like ToList().

There are plenty of good articles on MSDN regarding deferred execution which applies to all types of LINQ, not just using LINQ with Entity Framework.