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

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!

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();
            }
        }
    }

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

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

var ids2 = _unitOfWork.Repository<Table2>()
    .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:

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>netcoreapp2.1</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.App" Version="2.1.5" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.1.5" />
  </ItemGroup>
.........
</Project>

Business Logic library .csproj is:

<Project Sdk="Microsoft.NET.Sdk">
 <PropertyGroup>
    <TargetFramework>netstandard2.0</TargetFramework>
  </PropertyGroup>
 <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.1.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.1.4" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.1.4" />
  </ItemGroup>
.....
</Project>

@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!

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!

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!`

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