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

Question

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?

Answer 1

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.

Answer 2

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>