How to mock the limitations of EntityFramework’s implementation of IQueryable

by

I think it is very very hard, if impossible, to mock Entity Framework behaviour. First and foremost because it would require profound knowledge of all peculiarities and edge cases where linq-to-entites differs from linq-to-objects. As you say: the real challenge is finding them. Let me point out three main areas without claiming to be even nearly exhaustive:

Cases where Linq-to-Objects succeeds and Linq-to-Entities fails:

  • .Select(x => x.Property1.ToString(). LINQ to Entities does not recognize the method ‘System.String ToString()’ method… This applies to nearly all methods in native .Net classes and of course to own methods. Only a few .Net methods will be translated into SQL. See CLR Method to Canonical Function Mapping. As of EF 6.1, ToString is supported by the way. But only the parameterless overload.
  • Skip() without preceding OrderBy.
  • Except and Intersect: can produce monstrous queries that throw Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
  • Select(x => x.Date1 - x.Date2): DbArithmeticExpression arguments must have a numeric common type.
  • (your case) .Where(p => p.Category == category): Only primitive types or enumeration types are supported in this context.
  • Nodes.Where(n => n.ParentNodes.First().Id == 1): The method ‘First’ can only be used as a final query operation.
  • context.Nodes.Last(): LINQ to Entities does not recognize the method ‘…Last…’. This applies to many other IQueryable extension methods. See Supported and Unsupported LINQ Methods.
  • (See Slauma’s comment below): .Select(x => new A { Property1 = (x.BoolProperty ? new B { BProp1 = x.Prop1, BProp2 = x.Prop2 } : new B { BProp1 = x.Prop1 }) }): The type ‘B’ appears in two structurally incompatible initializations within a single LINQ to Entities query… from here.
  • context.Entities.Cast<IEntity>(): Unable to cast the type ‘Entity’ to type ‘IEntity’. LINQ to Entities only supports casting EDM primitive or enumeration types.
  • .Select(p => p.Category?.Name). Using null propagation in an expression throws CS8072 An expression tree lambda may not contain a null propagating operator. This may get fixed one day.
  • This question: Why does this combination of Select, Where and GroupBy cause an exception? made me aware of the fact that there are even entire query constructions that are not supported by EF, while L2O wouldn’t have any trouble with them.

Cases where Linq-to-Objects fails and Linq-to-Entities succeeds:

  • .Select(p => p.Category.Name): when p.Category is null L2E returns null, but L2O throws Object reference not set to an instance of an object. This can’t be fixed by using null propagation (see above).
  • Nodes.Max(n => n.ParentId.Value) with some null values for n.ParentId. L2E returns a max value, L2O throws Nullable object must have a value.
  • Using EntityFunctions (DbFunctions as of EF 6) or SqlFunctions.

Cases where both succeed/fail but behave differently:

  • Nodes.Include("ParentNodes"): L2O has no implementation of include. It will run and return nodes (if Nodes is IQueryable), but without parent nodes.
  • Nodes.Select(n => n.ParentNodes.Max(p => p.Id)) with some empty ParentNodes collections: both fail but with different exceptions.
  • Nodes.Where(n => n.Name.Contains("par")): L2O is case sensitive, L2E depends on the database collation (often not case sensitive).
  • node.ParentNode = parentNode: with a bidirectional relationship, in L2E this will also add the node to the nodes collection of the parent (relationship fixup). Not in L2O. (See Unit testing a two way EF relationship).
  • Work-around for failing null propagation: .Select(p => p.Category == null ? string.Empty : p.Category.Name): the result is the same, but the generated SQL query also contains the null check and may be harder to optimize.
  • Nodes.AsNoTracking().Select(n => n.ParentNode. This one is very tricky!. With AsNoTracking EF creates new ParentNode objects for each Node, so there can be duplicates. Without AsNoTracking EF reuses existing ParentNodes, because now the entity state manager and entity keys are involved. AsNoTracking() can be called in L2O, but it doesn’t do anything, so there will never be a difference with or without it.

And what about mocking lazy/eager loading and the effect of context life cycle on lazy loading exceptions? Or the effect of some query constructs on performance (like constructs that trigger N+1 SQL queries). Or exceptions due to duplicate or missing entity keys? Or relationship fixup?

My opinion: nobody is going to fake that. The most alarming area is where L2O succeeds and L2E fails. Now what’s the value of green unit tests? It has been said before that EF can only reliably be tested in integration tests (e.g. here) and I tend to agree.

However, that does not mean that we should forget about unit tests in projects with EF as data layer. There are ways to do it, but, I think, not without integration tests.

More Related Contents:

  1. ASP.NET MVC 2.0 Implementation of searching in jqgrid
  2. Code First: Independent associations vs. Foreign key associations?
  3. Code-first vs Model/Database-first [closed]
  4. Entity Framework: “Store update, insert, or delete statement affected an unexpected number of rows (0).” [closed]
  5. Unique Key constraints for multiple columns in Entity Framework
  6. Entity Framework 6 Code first Default value
  7. Disable lazy loading by default in Entity Framework 4
  8. Best way to check if object exists in Entity Framework? [closed]
  9. Improve navigation property names when reverse engineering a database
  10. Create a Dropdown List for MVC3 using Entity Framework (.edmx Model) & Razor Views && Insert A Database Record to Multiple Tables
  11. How to configure ProviderManifestToken for EF Code First
  12. Entity Framework One-To-One Mapping Issues
  13. Can I specify global mapping rules in Entity Framework Code First?
  14. The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid
  15. Entity Framework loading child collection with sort order
  16. Equivalent for .HasOptional in Entity Framework Core 1 (EF7)
  17. How to exclude one table from automatic code first migrations in the Entity Framework?
  18. How to pass parameters to the DbContext.Database.ExecuteSqlCommand method?
  19. Entity framework code first creates “discriminator” column
  20. EF Migrations: Rollback last applied migration?
  21. Using EF Core ThenInclude() on Junction tables
  22. How to update not every fields of an object using Entity Framework and EntityState.Modified
  23. Keyword not supported: “data source” initializing Entity Framework Context
  24. Entity Framework Data Annotations Set StringLength VarChar
  25. What is the best practice in EF Core for using parallel async calls with an Injected DbContext?
  26. EF 4.1 loading filtered child collections not working for many-to-many
  27. How to map Table per Type (TPT) inheritance in Entity designer?
  28. DbContext AutoDetectChangesEnabled set to false detecting changes
  29. Using the Entry().CurrentValues.SetValues() is not updating collections
  30. An object with the same key already exists in the ObjectStateManager. The ObjectStateManager cannot track multiple objects with the same key

Leave a Comment