Using Results Outside of dbContext in VB.NET

Question

Lets say I have code like this:

Using dbContext as mydb_entities = New mydb_entities
   Dim qperson = (From p in dbContext.People _
                  Where p.name = "John" _
                  Select p)
End Using
Using dbContext as yourdb_entities = New yourdb_entities
   Dim qyou = (From p in dbContext.Customer _
               Where p.name = "John" _
               Select p)
End Using

How can I compare the results of qperson to qyou? Since the results "disappear" once End Using is executed?

score 2 · Answer 1 · answered Mar 23 '12 at 17:58

2

You will need to declare both variables outside the using statements

Dim qperson As IQueryable(Of Person)
Dim qyou As IQueryable(Of Customer)

Using dbContext as mydb_entities = New mydb_entities
   qperson = (From p in dbContext.People _
              Where p.name = "John" _
              Select p)
End Using
Using dbContext as yourdb_entities = New yourdb_entities
   qyou = (From p in dbContext.Customer _
           Where p.name = "John" _
           Select p)
End Using

answered Mar 23 '12 at 17:58

Aducci

26,101
8
63
67

This is helpful...but I don't think it can be IQueryable...I am using Entity Framework? – Dave Mackey Mar 23 '12 at 18:29
@davemackey - Yes it returns an IQueryable(Of T). See this link: http://msdn.microsoft.com/en-us/library/bb548743.aspx#Y0 – Aducci Mar 23 '12 at 18:32

score 2 · Answer 2 · answered Mar 24 '12 at 01:46

The key thing here is to know when your LINQ queries are going to get run. When this line of code is executed:

qperson = (From p in dbContext.People _ 
          Where p.name = "John" _ 
          Select p)

no query is sent to the server. Instead you get back an object that implements the IQueryable(Of T) interface that describes what the query is. The query isn't actually sent to the server and executed until you start to use the results, such as in a For Each loop. This is called delayed execution and is fundamental to LINQ.

So what does this mean to you? Well, it means that the context must not be disposed before you execute the query. In the examples so far this is not necessarily always true. (The nested answer might do so, depending what is actually happening inside the nested usings.)

The typical way to deal with this is to force execution of the query to produce an in-memory collection of the results before the context is disposed. The ToList() extension method is a common way to do this. So, for example:

Dim qperson As IList(Of Person)           
Dim qyou As IList(Of Customer)           

Using dbContext as mydb_entities = New mydb_entities           
   qperson = (From p in dbContext.People _           
              Where p.name = "John" _           
              Select p).ToList()
End Using           

Using dbContext as yourdb_entities = New yourdb_entities           
   qyou = (From p in dbContext.Customer _           
           Where p.name = "John" _           
           Select p).ToList()
End Using

Now you have executed the queries and got the results into memory before the contexts are disposed and you can happily do what you want with them.

The first thing that came to my mind after reading the question was "use .ToList() to enforce the query to be executed". — Pawel, Mar 25 '12 at 05:55

score 1 · Answer 3 · edited May 23 '17 at 11:48

1

In C# I just typically nest the usings...

using (var context blahentities())
{
  using (var context2 blahentities())
  {

  }
}

Check this out for nesting usings in vb...

Nested using statements

edited May 23 '17 at 11:48

Community

1
1

answered Mar 23 '12 at 17:57

Paul

441
7
14

Thanks Paul. That is what I do also, but I'm getting a transport level error and it seems like maybe the connection is being ended, so I wanted to see if I could parse them out into separate unnested calls, and thereby avoid this possibility. – Dave Mackey Mar 23 '12 at 17:58

Using Results Outside of dbContext in VB.NET

3 Answers3