Working with Anonymous Types After Closing DB Connection

I have code similar to the following.

using (MyEntities context = new MyEntities())
{
    var activities = from act in context.Activities
                     where act.ActTwittered == false
                     select new { act.ActID, act.ActTitle, act.Category, act.ActDateTime, act.Location };

    foreach (var activity in activities)
    {
        /* ... */
    }
}

This seems to work fine but my loop has a lot of processing. I'm concerned that I'm leaving a database connection or other resources open during this processing.

I tried declaring var activities before the using statement so I could process the data after the using statement, but this variable must be initialized where it is declared.

Could someone who understands the inner workings of EF tell me if开发者_运维知识库 there are problems doing lengthy processing while an EF context is "alive", and how I make alleviate these problems.

While I'm at it, perhaps you could also comment on the fact that I use act.Category.CatName within the loop. This is a value from a related table. Am I better to use a join in my EF query so I get the data all at once rather than forcing another (?) database access to get the related data?

---

You can have the compiler infer the result type by calling a generic method on it:

    public static T CallFunc<T>( Func<T> theFunc )
    {
        return theFunc();
    }

    ... 

    var activities = CallFunc( () =>
    {
        using( var context = new MyEntities() )
        {
            return 
                (
                    from act in context.Activities
                    where act.ActTwittered == false
                    select new { act.ActID, act.ActTitle, act.Category, act.ActDateTime, act.Location };
                )
                .ToList();
        }
    } );

    foreach( var a in activities ) ...

Don't forget the .ToList() at the end, or your query won't be actually executed until you enumerate over it, which will happen after the context is closed.

---

Your problem is in wanting the anonymous type to be accessible outside of its context. You can use a specific type for your results and get the flexibility to access the values anywhere.

public class ActivitySummary
{
    public int ActID { get; set; }
    public string ActTitle { get; set; }
    public string Category { get; set; }
    public DateTime ActDateTime { get; set; }
    public string Location { get; set; }
}

. . .

List<ActivitySummary> activities;

using (MyEntities context = new MyEntities())
{
    activities = from act in context.Activities
                 where act.ActTwittered == false
                 select new ActivitySummary { act.ActID, act.ActTitle, act.Category, act.ActDateTime, act.Location }.ToList();

}

foreach (var activity in activities)
{
    /* ... */
}

---

The biggest problem with separating the reading and processing, is that your code doesn't do that at all.

The first statement only creates an expression that is capable of fetching the data, but it doesn't actually fetch anything at all. It's not until you start reading from it that it actually gets any data.

There are two things that you have to do to be able to close the database connection before processing the data with that code. You have to actually get the data instead of just setting up an expression, by using the ToList method, and you have to use Dispose instead of using so that you don't isolate the anonymous type inside it's scope:

MyEntities context = new MyEntities();

var activities = (
  from act in context.Activities
  where act.ActTwittered == false
  select new { act.ActID, act.ActTitle, act.Category, act.ActDateTime, act.Location }
).ToList();

context.Dispose();

foreach (var activity in activities) {
    /* ... */
}

One drawback with this approach is that you don't get the implicit try...finally around the code that the using provides, so if there is an error when fetching the data, the context won't be disposed.

Another alternative is to declare a class that can hold the data that you read, so that you can declare the variable using a known class, outside the using block:

List<Activity> activities;
using (MyEntities context = new MyEntities()) {
  activities = (
    from act in context.Activities
    where act.ActTwittered == false
    select new Activity(act.ActID, act.ActTitle, act.Category, act.ActDateTime, act.Location)
  ).ToList();
}

---

In cases when I need to work with result from DB for a long time I usually create that specific type, just declare a class with needed fields, not using anonymous types, after I get data from database, I spawn a thread where I do the needed work. This way the connection to database gets closed and application remains responsive.

---

You could manually dispose your DataContext.

In addition, you will need to call ToArray on the query so that it gets executed immidiately (and not after disposing the context):

MyEntities context = new MyEntities();

var activities = (from act in context.Activities
                  where act.ActTwittered == false
                  select new { act.ActID, act.ActTitle, act.Category, act.ActDateTime, act.Location })
                 .ToArray();

context.Dispose();

//Do something with activities

However, I would recommend switching to non-anonymous types so you can continue using using to dispose the Context.
For example, using takes care to call Dispose even if an exception occurs.