CODEWITHSUNDEEP
c#.netsql-server-2008datacontext
sharptooth
sharptooth

Reputation: 170539

Can I access more than one table via the same DataContext object simultaneously?

Suppose I have a DataContext object and access two tables at the same time:

using( var context = new DataContext( connectionString ) ) {
     foreach( firstTableEntry in context.GetTable<FirstTable>() ) {
         switch( firstTableEntry.Type ) {
         case RequiresSecondTableAccess:
         {
             var secondTable = context.GetTable<SecondTable>();
             var items = secondTable.Where( item => item.Id = firstTableEntry.SecondId );
             foreach( var item in items ) {
                handleItem( item );
             }
         }
         default:
           // not accessing the second table
     }
}

Note that I don't stop using the first query results while making queries to the other table and use the same DataContext object all the time.

Is such usage legal? Should I expect any problems with this approach?

Upvotes: 9

Views: 287

Answers (2)

phil soady
phil soady

Reputation: 11348

AS commented by David. I would add to his info... See WEB.CONFIG as follows, MARS=True in connection string

<connectionStrings>
<add name="ContextNameXYZ" connectionString="Data Source=ServerName;Initial Catalog=DBName;Integrated Security=True;MultipleActiveResultSets=True;App=EntityFramework"
   providerName="System.Data.SqlClient" />
  </connectionStrings>

Upvotes: 1

David Pfeffer
David Pfeffer

Reputation: 39872

This will only work if you have support for Multiple Active Recordsets (MARS) in your database engine. If not, you will generate an exception. Otherwise, you can definitely do this.

If you do not have MARS support, you will need to use .AsEnumerable to download all records from the database at one time and convert to an in-memory enumerable.

Here's another great resource on what in Entity Framework uses MARS and what you can't do if you don't have it.

Upvotes: 3

Related Questions