Efcore: Union: new transaction is not allowed because there are other threads running in the session

[shaher]

I have the same problem after upgrading to EF Core 2.1, at least for now in two different places in the code, the same code was running with no problems before upgrade from 2.0

The issue seems to be related to union, after changing the code as below the error disappeared.
In BOTH places the problem fixed by dividing the union to multi selects and do union in memory.

This code failed

var uids = _db.Table1.Where(x => x.AccountUId == accountUId).Select(x => x.UId)
    .Union(_db.Table2.Where(x => x.AccountUId == accountUId).Select(x => x.UId))
    .ToList();

This code succeeded

var uids1 = _db.Table1.Where(x => x.AccountUId == accountUId).Select(x => x.UId).ToList();
var uids2 = _db.Table2.Where(x => x.AccountUId == accountUId).Select(x => x.UId).ToList();
var uids = uids1.Union(uids2);  //UNION in memeory

But now there is an extra database request!
It seems EF stuck with other internal selects in the union!

Note:
I was getting the Exception later in SaveChanges. In Sql profiler, SaveChanges was not running any Sql command, but after the error, I saw the "stuck" Select in Sql profiler.

[ajcvickers]

@shaher I have not been able to reproduce what you are seeing--my repro attempt is below. Can you post a runnable project/solution or complete code listing that demonstrates the behavior you are seeing?

```C#
public class Table1
{
public Guid UId { get; set; }
public Guid AccountUId { get; set; }
}

public class Table2
{
public Guid UId { get; set; }
public Guid AccountUId { get; set; }
}

public class BloggingContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Table1>().HasKey(e => e.UId);
    modelBuilder.Entity<Table2>().HasKey(e => e.UId);
}

}

public class Program
{
public static void Main()
{
var accountUId = Guid.NewGuid();

    using (var context = new BloggingContext())
    {
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        context.AddRange(
            new Table1 {AccountUId = accountUId},
            new Table2 {AccountUId = accountUId});

        context.SaveChanges();
    }

    using (var context = new BloggingContext())
    {
        var uids = context.Set<Table1>().Where(x => x.AccountUId == accountUId).Select(x => x.UId)
            .Union(context.Set<Table2>().Where(x => x.AccountUId == accountUId).Select(x => x.UId))
            .ToList();
    }
}

}
```

[divega]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

[divega]

Reopening to give us a chance to investigate this further, even if we didn't get an answer about the repro. Here are a couple of possible things to try:

1. Make the queries async
2. Having multiple concurrent requests

[ajcvickers]

We will investigate this more if we get a runnable repro, either from the original poster or from someone else.

[shaher]

@ajcvickers I'm facing the same problem again on other location and used the same workaround to pass that exception!

EF Core 2.1.4

Not working!
```c#
var paymentMethodIds = _db.BankAccount
.Where(x => x.AccountId == billingAccountId)
.Select(x => x.Id)
.Union(_db.CreditCard.Where(x => x.AccountId == billingAccountId).Select(x => x.Id))
.ToList();

**Working!**
```c#
var bankIds = _db.BankAccount
    .Where(x => x.AccountId == billingAccountId)
    .Select(x => x.Id)
    .ToList();

var creditIds = _db.CreditCard
    .Where(x => x.AccountId == billingAccountId)
    .Select(x => x.Id)
    .ToList();

var paymentMethodIds = bankIds.Union(creditIds); // TODO: this should be one db request, due to EF bug it was splitted and union done in memory!`

[divega]

@shaher thanks for letting us know about this. Last time, when we tried to repro, we were not able to do it. It would really help if you could provide a repro project, or at least if you could give us more information, e.g. about what kind of application it is, what version of .NET is the application using? is it using async? Is it possible that there are concurrent threads working with the same DbContext? Does the failure occur consistently every time this code executes or is it intermitent or maybe under stress?

[mike2212]

I can confirm that I also have found this issue and it seems to also be centered around unions. It's not an open project and I'm not sure what can be shared.

Example
``c# var ids1 = _unitOfWork.Repository<Table1>() .Queryable() .Where(x => x.Id== id1) .Select(x => x.Id) .ToList(); // ThisToList()` was added because of a bug in EF Core 2.1.

var ids2 = _unitOfWork.Repository()
.Queryable()
.Where(x => x.Id == id2)
.Select(x => x.Id)
.ToList(); // This ToList() was added because of a bug in EF Core 2.1.

return ids1.Union(ids2).ToList();

Without the commented `ToList()'s` I also get a `"new transaction is not allowed because there are other threads running in the session"` when calling a `context.SaveChanges()` (in my case `_unitOfWork.Save()`) later in in the calling function - after an insert.

This happens consistently; it isn't load effected.  The calls in this request are not async.  This is a WebApi application.  There are no concurrent threads working with the same DbContext?


**WebApi .csproj project is:** 

netcoreapp2.1





.........

**Business Logic library .csproj is:** 

netstandard2.0






.....

```

[ajcvickers]

@mike2212 I attempted to reproduce what you are seeing--see below--but it is working for me. Can you post a runnable project/solution or a complete code listing like that below that demonstrates the behavior you are seeing?

```C#
public class BloggingContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Table1>();
    modelBuilder.Entity<Table2>();
}

}

public class Table1
{
public int Id { get; set; }
}

public class Table2
{
public int Id { get; set; }
}

public class Program
{
public static void Main()
{
using (var context = new BloggingContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();

        context.AddRange(new Table1(), new Table2(), new Table1(), new Table2());

        context.SaveChanges();
    }

    using (var context = new BloggingContext())
    {
        var id1 = 1;
        var id2 = 2;

        var ids1 = context.Set<Table1>()
            .Where(x => x.Id == id1)
            .Select(x => x.Id);

        var ids2 = context.Set<Table2>()
            .Where(x => x.Id == id2)
            .Select(x => x.Id);

        var results = ids1.Union(ids2).ToList();
    }
}

}
```

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

[rotem925]

Hi @ajaybhargavb
I managed to reproduce it with your example and few changes.
You should call saveChanges after you create the list.
Bellow is an example using the latest EFCore version 2.2.1
can you please reopen that issue?

  public class BloggingContext : DbContext
    {
        public BloggingContext()
        {

        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=Test;Connect Timeout=5");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Table1>();
            modelBuilder.Entity<Table2>();
        }
    }

    public class Table1
    {
        public int Id { get; set; }
    }

    public class Table2
    {
        public int Id { get; set; }
    }

    public class Program
    {
        public static void Main()
        {


            using (var context = new BloggingContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                var id1 = 1;
                var id2 = 2;

                var ids1 = context.Set<Table1>()
                    .Where(x => x.Id == id1)
                    .Select(x => x.Id);

                var ids2 = context.Set<Table2>()
                    .Where(x => x.Id == id2)
                    .Select(x => x.Id);

                var results = ids1.Union(ids2).ToList();


                context.AddRange(new Table1(), new Table2(), new Table1(), new Table2());

                context.SaveChanges();
            }
        }
    }

[ajcvickers]

@rotem925 Thanks!

[mike2212]

@rotem925 Thanks for putting the legwork in here, I've unfortunately been unable to spare enough time for this.

@ajcvickers if I am able to assist, I will.

[ajcvickers]

Verify this works after 3.0 query changes and set operations are translated.

[smitpatel]

Related #6812
Assigning to @roji

[roji]

This should be more or less a dup of #6812 but will keep this issue open to test that particular scenario.

[hubert17]

Reopening to give us a chance to investigate this further, even if we didn't get an answer about the repro. Here are a couple of possible things to try:

1. Make the queries async
2. Having multiple concurrent requests

This works for me. IQueryable Union is doing good now. Thanks!