"The LINQ expression could not be translated" with in-memory provider #24318
Comments
|
We experienced the same exact issue.
I tried to assemble the smallest reproduction I could: internal class Program
{
private static void Main(string[] args)
{
SqlServer();
InMemory();
}
/// <summary>
/// Run the query on an in-memory DbContext
/// </summary>
private static void InMemory()
{
var options = new DbContextOptionsBuilder<ExampleContext>()
.UseInMemoryDatabase("db")
.Options;
using var ctx = new ExampleContext(options);
Query(ctx);
}
/// <summary>
/// Run the query on an SQLServer DbContext
/// </summary>
private static void SqlServer()
{
var options = new DbContextOptionsBuilder<ExampleContext>()
.UseSqlServer("Server=.\\SQLEXPRESS01;Database=EFInMemoryQueryIssue;Trusted_Connection=True;")
.Options;
using var ctx = new ExampleContext(options);
ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();
Query(ctx);
}
/// <summary>
/// Actual query to run
/// </summary>
private static void Query(ExampleContext ctx)
{
try
{
var owner = ctx
.Owners
.Include(o => o.OwnedEntities)
.FirstOrDefault();
Console.WriteLine("Query successful.");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
/// <summary>
/// Example DbContext
/// </summary>
internal class ExampleContext : DbContext
{
public ExampleContext(DbContextOptions<ExampleContext> dbContextOptions) : base(dbContextOptions)
{
}
public DbSet<OwnerEntity> Owners => Set<OwnerEntity>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// strongly typed ID conversion setup
modelBuilder.Entity<OwnedEntity>().Property(o => o.Id)
.HasConversion(id => id.Id, id => new(id));
// many-to-many relationship
modelBuilder.Entity<OwnerEntity>()
.HasMany(o => o.OwnedEntities)
.WithMany(s => s.Owners);
}
}
record StronglyTypedId(Guid Id);
internal class OwnedEntity
{
public StronglyTypedId Id { get; set; } = new(Guid.NewGuid());
public ICollection<OwnerEntity> Owners { get; set; }
}
internal class OwnerEntity
{
public Guid Id { get; set; } = Guid.NewGuid();
public List<OwnedEntity> OwnedEntities { get; set; }
}Additionally, based on my observation, it is enough if the joined table has an ID with a conversion. Exception message: |
|
Can be reproduced in https://github.com/wallymathieu/entity-framework-studies/ |
|
I'm facing the same issue with class Program
{
static async Task Main()
{
var rootKey = new byte[8];
new Random().NextBytes(rootKey);
using var dbContext = new MyDbContext();
dbContext.Roots.Add(new RootEntity { Key = rootKey });
dbContext.Children.Add(new ChildEntity { RootKey = rootKey });
await dbContext.SaveChangesAsync();
var child = await dbContext.Children
.Where(a => a.RootKey == rootKey)
.Include(a => a.Root)
.FirstOrDefaultAsync();
}
}
public class RootEntity
{
public byte[] Key { get; set; }
public virtual ICollection<ChildEntity> Children { get; set; }
}
public class ChildEntity
{
public int Id { get; set; }
public byte[] RootKey { get; set; }
public virtual RootEntity Root { get; set; }
}
public class MyDbContext : DbContext
{
public DbSet<RootEntity> Roots { get; set; }
public DbSet<ChildEntity> Children { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseInMemoryDatabase("testdatabase");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<ChildEntity>(child =>
{
child.HasOne(a => a.Root)
.WithMany(n => n.Children)
.HasForeignKey(a => a.RootKey);
});
modelBuilder.Entity<RootEntity>(root =>
{
root.HasKey(n => n.Key);
});
}
}This throws the following Exception: |
smitpatel
added
the
priority-bug
|
Any news on this one? I can confirm this is related to the usage of strongly-typed ids. With int ids and Includes, it works fine. With strongly-typed ids and no Includes it works. With both strongly-typed ids and includes, it fails. Here's a short repro. This code fails: void Main()
{
var options = new DbContextOptionsBuilder<MyDbContext>()
.UseInMemoryDatabase("test")
.Options;
// Add to DB
{
using var dbContext = new MyDbContext(options);
var product = new Product
{
Id = new(1),
Name = "Toto / Titi",
Category = new Category { Id = new(1), Name = "blabla" }
};
dbContext.Add(product);
dbContext.SaveChanges();
}
// Load from DB with Include
{
using var dbContext = new MyDbContext(options);
var productId = new ProductId(1);
var product = dbContext.Products
.Include(a => a.Category)
.Single(a => a.Id == productId);
product.Dump();
}
}
public class MyDbContext : DbContext
{
public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
{
}
public DbSet<Product> Products { get; set; }
public DbSet<Category> Categories { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Product>(builder =>
{
builder.HasKey(e => e.Id);
builder.Property(e => e.Id).HasConversion(id => id.Value, value => new ProductId(value));
builder.Property(e => e.CategoryId).HasConversion(id => id.Value, value => new CategoryId(value));
builder.HasOne(e => e.Category).WithMany().HasForeignKey(e => e.CategoryId).IsRequired();
});
modelBuilder.Entity<Category>(builder =>
{
builder.HasKey(e => e.Id);
builder.Property(e => e.Id).HasConversion(id => id.Value, value => new CategoryId(value));
});
}
}
public record ProductId(int Value);
public record CategoryId(int Value);
public class Product
{
public ProductId Id { get; set; }
public string Name { get; set; }
public CategoryId CategoryId { get; set; }
public Category Category { get; set; }
}
public class Category
{
public CategoryId Id { get; set; }
public string Name { get; set; }
}The same code minus the strongly-typed ids works fine: void Main()
{
var options = new DbContextOptionsBuilder<MyDbContext>()
.UseInMemoryDatabase("test")
.Options;
// Add to DB
{
using var dbContext = new MyDbContext(options);
var product = new Product
{
Id = 1,
Name = "Toto / Titi",
Category = new Category { Id = 1, Name = "blabla" }
};
dbContext.Add(product);
dbContext.SaveChanges();
}
// Load from DB with Include
{
using var dbContext = new MyDbContext(options);
var productId = 1;
var product = dbContext.Products
.Include(a => a.Category)
.Single(a => a.Id == productId);
product.Dump();
}
}
public class MyDbContext : DbContext
{
public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
{
}
public DbSet<Product> Products { get; set; }
public DbSet<Category> Categories { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Product>(builder =>
{
builder.HasKey(e => e.Id);
builder.HasOne(e => e.Category).WithMany().HasForeignKey(e => e.CategoryId).IsRequired();
});
modelBuilder.Entity<Category>(builder =>
{
builder.HasKey(e => e.Id);
});
}
}
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public int CategoryId { get; set; }
public Category Category { get; set; }
}
public class Category
{
public int Id { get; set; }
public string Name { get; set; }
}(tested in LinqPad with EF 5.0.11) |
|
Using structs as PK is fine, the problem appears only if you use class as PK. |
|
I seem to be running into this same issue, or a similar one. In my situation I have the Include and am using the AsSplitQuery() extension method. The main query is also doing an explicit Join() to another table, so the Select() part of the query is receiving an anonymous object. This seemed to all work fine with the Sql Server provider, but my unit tests are failing using the InMemory provider. When I comment out the Join() and the areas out of the Select that were using the joined table, then the unit tests start passing. |
smitpatel
added
the
punted-for-7.0
ajcvickers
added
propose-close
closed-out-of-scope
|
We recommend against using the in-memory provider for testing--see Testing EF Core Applications. While we have no plans to remove the in-memory provider, we will not be adding any new features to this provider because we believe valuable development time is better spent in other areas. When feasible, we plan to still fix regressions in existing behavior. |
|
Thanks! Good to see that there are docs around what to use 🙂 |
|
As far as I could tell, the docs linked here currently do not recommend against the in-memory provider.
|
|
@NickStrupat we do recommend against using the in-memory provider. See the Choosing a testing strategy page, and specifically this section. |
|
It's worth noting that using Sqlite instead of the in-memory provider is only shifting the problem. My unit tests were implemented using sqlite from the beginning because of the guidance in the docs. It works fine if you enable foreign keys, which the in-memory provider can;t do, but it also cannot translate all linq queries properly (notably String.Contains()). And now switching the strongly typed ids just destroyed all the sqlite tests as sqlite, event though the integration tests with SQL Server work just fine. |
|
Yeah. At this point, the only reliable approach is to test against the real DB engine. You can easily spin up a SQL Server container using TestContainers. |
|
@yuominae what @thomaslevesque said. Testing against a fake database (in-memory, sqlite...) used to make more sense, but in today's world of easy containerization and testcontainers, you really should be testing against SQL Server. |
|
Thanks to both of you for confirming. I wish the guidance on tests would use stronger wording there. It's definitely something I will take into account in the future, but wanted to document the problems here for anybody who'll stumble across this issue :) |
The following query fails to translate using the in-memory provider
I get the following exception:
Stack trace:
I'm not sure what it should be "translated" to (does the in-memory provider use its own query language?), but it's a pretty basic query, and it's failing miserably.
The same query works fine with the SQL Server provider.
The same query without the
Includes works with the in-memory provider.It's worth noting that there's something unusual in my DbContext: I use strongly-typed ids. In other words, for each entity
Foo, there's aFooIdrecord that wraps a GUID. I suspect it's what causing the issue. Unless it's because the in-memory provider doesn't supportIncludes, but I'd be very surprised if it didn't.EF Core version: 5.0.3
Database provider: in-memory
Target framework: .NET 5.0
Operating system: Windows 10
IDE: JetBrains Rider
The text was updated successfully, but these errors were encountered: