Secondary Db Context in ASP.NET CORE EF

Question

I have a service that sends notifications, it requires a db connection to lookup subscriptions. I also have a controller (and may more) that does some logic, and sends notifications.

The problem with this is, because of DI it uses the same instance of the DbContext so I get a error thrown for re-using a DataReader in the same context (understandable).

I would really love to do this without enabling the MARS flag in DbConnectionString. Given that the controllers cannot use .ToList() or no tracking and the 'inner' NotificationService needs to lookup the database - is this even possible?

public class NotificationSystem
{
     private readonly DbContext context;
     public NotificationSystem(DbContext context) { this.context = context;}

     public void SendNotification(string username){
       var subscriptions = context.subscriptions.where(u => u.username == username); 
       // Do some notification stuff
     } 
}

And a simple controller

public class SendRemindersController : Controller
{
    private readonly DbContext _context;
    private readonly NotificationSystem _notificationSystem;

    public SendRemindersController(DbContext context, NotificationSystem notificationSystem)
    {
        this._context = context;
        this._notificationSystem = notificationSystem;
    }

    [HttpGet]
    public async Task<IActionResult> Get()
    {
        var reminders = _context.Reminders.Where(r => r.Sent == false && r.RemindAt < DateTime.UtcNow);

        foreach (var reminder in reminders)
        {
            await _notificationSystem.SendNotificationToUser(reminder.UserId);
            reminder.Sent = true;
        }

        await _context.SaveChangesAsync();
        return Ok();
    }
}

And startup.cs (yes I know I haven't used an interface, that will get refactored later).

services.AddDbContext<DbContext>(options => options.UseSqlServer(connection));
services.AddTransient<NotificationSystem, NotificationSystem>();

Update

This question is flawed since i was under the false impression that .ToList/.ToArray also detached the entities from the context. In-fact these do not detach and only execute the query.

This error would appear if you use lazy initialization for a reminder field, or if SendNotificationToUser is trying to execute a query in the middle of the iteration. Make sure you load all required data when the operation starts, eg by using an Include() statement. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 17, 2016 at 12:38
BTW you should have a relation from reminder to User or Subscription, not use an ID like the username to link them. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 17, 2016 at 12:38
@PanagiotisKanavos in real code there is :) I'm just trying to give an example. — nyxthulhu
– nyxthulhu, Commented Oct 17, 2016 at 12:40
In this case you wouldn't need a second query. You could walk the relations. In any case, you should try to load all the data you need at the start, to avoid both this error and multiple roundtrips to the database — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 17, 2016 at 12:41
That's a job for the database and eg hierarchyid. Finding parents of a node is trivial this way. EF does support hierarchyid through an extension. This will increase your performance 100x at least (1 query for all parents instead of 3-4 recursions per item) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 17, 2016 at 12:47

Igor · Accepted Answer · 2016-10-17 12:29:24Z

1

Its because you are using the same DbContext to execute multiple simultaneous transactions. If you add .ToListAsync() to this line of code like this

var reminders = await _context.Reminders
  .Where(r => r.Sent == false && r.RemindAt < DateTime.UtcNow)
  .ToListAsync();

It will retrieve all the reminders immediately and then the code inside the loop (after this statement) can use the DbContext without the DbContext throwing an exception because an active result set is still being iterated over.

edited Oct 17, 2016 at 12:29

answered Oct 17, 2016 at 12:27

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

nyxthulhu Over a year ago

I understand that but I cannot use a .ToList or a Without tracking ("Given that the controllers cannot use .ToList() or no tracking and the 'inner' NotificationService needs to lookup the database - is this even possible?") because some controllers that use the service will need to update their entities.

Igor Over a year ago

@JohnMitchell - ToList and AsNoTracking are 2 unrelated things. You can use ToList in this instance and still be able to update the entities in the same manner as normal.

Panagiotis Kanavos Over a year ago

@JohnMitchell why can't you use that? You are doing this already, without realizing it, and in the slowest way possible (ie synchronously). You are probably trying to lazily load data too, resulting in the N+1 query problem. The fix is to load all necessary data in the beginning (the lookups too). This will result in faster execution because you make only 1 call instead of N

Panagiotis Kanavos Over a year ago

@JohnMitchell no, it won't. It will execute the query and return the results in an IList. I'd prefer ToArrayAsync though, if you don't intend to add new items to the list

Igor Over a year ago

@JohnMitchell - good deal. Should you ever want to retrieve detached entities there is a named extension called .AsNoTracking() that would do that. AFAIK there are no other native extension methods that would implicitly do this.

|

Collectives™ on Stack Overflow

Secondary Db Context in ASP.NET CORE EF

Update

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related