1

I got a problem with lazy loading in combination with a foreach loop.

I'm working on a code first asp.net backend for a website, and sometimes I want to gather information spanning multiple tables/models.

For example, take this:

public class Foo
{
    [Key]
    public Int32 Id { get; set; }
    public virtual Bar Bar { get; set; }
    public Int32 someInt { get; set; }
}

public class Bar
{
    [Key]
    public Int32 FooId { get; set; }

    [ForeignKey("FooId")]
    public virtual Foo Foo { get; set; }

    public String someString { get; set; }
}

public async Task<IHttpActionResult> getSomeInformation
{
    using(WhateverContext context = new WhateverContext())
    {
        var Foos = context.Foos;

        List<FrontendFriendlyModel> ForTheFrontend = new List<FrontendFriendlyModel>();

        foreach (var Foo in Foos)
        {
            var entry = new FrontendFriendlyModel();
            entry.Id = Foo.Id; //Works fine
            entry.someInt = Foo.someInt; //Works also fine
            entry.someString = Foo.Bar.someString; //Crashes, "There is already an open DataReader associated with this Command"

            ForTheFrontend.Add(entry);
        }

        return Ok(ForTheFrontend); //I convert it to a Json String with Json.net, but thats not relevant I guess
    }
}

I know I could activate MARS in my connection String.

I could also eager load Bar on the spot, and prevent the problem.

var Foos = context.Foos.Include(f => f.Bar);

From everything I gathered, using MARS is generally not considered good practice, and I don't like that I have to eager load everything in the at the start .

Things I could find on google about this, don't really help me here (Maybe I just suck at googling), and I feel like I'm missing something really basic.

So I'm wondering, whats the correct way here. Activating MARS? Eager Load everything I need? Cast the IQueryable to a List?

        var Foos = context.Foos.ToList();
        foreach (var Foo in Foos)
        {
            var entry = new FrontendFriendlyModel();
            entry.Id = Foo.Id; //Works fine
            entry.someInt = Foo.someInt; //Works also fine                
            entry.someString = Foo.Bar.someString; //Now that works, but I don't get why it doesnt work as IQueryable.
            ForTheFrontend.Add(entry);
        }

I don't understand why I can't work with an IQueryable here, so maybe someone can enlighten me.

6
  • 1
    Why don't you want to eager-load Bar if you always immediately fetch it in the loop anyway? Lazy load is much slower since it requires separate requests for each object. – JacquesB Feb 14 '17 at 14:21
  • @JacquesB: That's a bit of a mischaracterization. In some scenarios lazy loading is actually going to be "faster" overall, because it doesn't block. – Robert Harvey Feb 14 '17 at 22:27
  • Can you provide examples of articles on the Internet that state the use of MARS is a bad practice, please? – Robert Harvey Feb 14 '17 at 22:29
  • JacquesB had a good question. Why are you using lazing loading? Is the loop just an example? – Frank Hileman Feb 14 '17 at 23:40
  • @JacquesB, its just because of lazyness. Sometimes I have to gather informations across several tables, which makes the query quite long. I just thought, since most programming languages/frameworks are quite favourable in terms of lazyness, that there is some basic thing I just don't know. – user3021443 Feb 15 '17 at 1:23
2

You have two choices:

  1. You can eager load, or
  2. You can enable MARS.

Presumably, you want to use an IQueryable to obtain deferred execution. I assume you want to call getSomeInformation() with await. If you want deferred lookup from the database, you must use MARS; you don't have a choice.

However, note that you can still get most of the benefits of deferred execution by eager-loading the related entity. Your method will still be async. The only real difference will be that you are pre-loading the entity into memory instead of retrieving it from the database on-demand later on, which is probably what you want anyway.

As to whether or not the use of MARS is a "good" or "bad" practice, I think it comes down to understanding how to use it properly. It's easy to do eager loading correctly; it's a bit harder with MARS. Consider these errors that can occur with MARS:

The transaction operation cannot be performed because there are pending requests working on this transaction.

The server failed to resume the transaction. The transaction active in this session has been committed or aborted by another session.

The underlying provider failed on Open. The connection was not closed. The connection's current state is "connecting."

ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

The server failed to resume the transaction.

All of these errors demonstrate fundamental misunderstandings about how MARS works, and how the underlying connection and transaction model should be used. Microsoft has adequate guidance (linked below) for using MARS in ways that won't cause these errors.

Further Reading
Using Multiple Active Result Sets
Enabling Multiple Active Result Sets
Exceptions when Using MARS

1
  • Thanks for the Answer. I think I rather eager load than use MARS. (Or cast the IQueryable to a list, when possible). I was more or less looking for a lazy approach, that wouldn't need me to write out every table/model im going to need furtherer down the line. – user3021443 Feb 15 '17 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.