Contents tagged with Pitfalls

  • Entity Framework Pitfalls – Date/Time Operations

    When using Entity Framework, you cannot perform date/time operations as you would normally do in .NET/LINQ to Objects. For instance, the following query throws an exception:

       1: ctx.Projects.Select(x => new { ElapsedTime = DateTime.Now - x.Start }).ToList();

    Instead, you need to use the static methods in SqlFunctions, like DateDiff:

       1: ctx.Projects.Select(x => new { ElapsedTime = SqlFunctions.DateDiff("HOUR", x.Start, DateTime.Now) }).ToList();

    For a full description of DateDiff’s first parameter, refer to

    If you want to achieve the same result, that is, return a TimeSpan, you need to use LINQ to Objects at the end:

       1: ctx.Projects.Select(x => new { ElapsedTime = SqlFunctions.DateDiff("HOUR", x.Start, DateTime.Now) }).ToList().Select(x => new { ElapsedTime = TimeSpan.FromHours((Double)x.ElapsedTime) }).ToList();


  • NHibernate Pitfalls: XML Mappings

    This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

    If you are still using XML mappings – .hbm.xml files –, there’s nothing wrong with that, but you may run into problems.

    First, you will need to add these files as embedded resources in your project, if you are calling Configuration.AddAssembly(), Configuration.AddClass() or you are specifying the name of the assembly in the .config file like this:

       1: <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
       2:     <session-factory>
       3:         <property name="connection.driver_class">NHibernate.Driver.Sql2008ClientDriver</property>
       4:         <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
       5:         <property name="connection.connection_string_name">MyConnection</property>
       6:         <mapping assembly="MyAssembly.MyModel" />
       7:     </session-factory>
       8: </hibernate-configuration>

    These methods will look for .hbm.xml files as embedded resources in that assembly:


    Alternatively, you can have the files on the filesystem, but you will have to call Configuration.AddDirectory(), Configuration.AddFile() or Configuration.AddInputStream(), passing the appropriate parameters.

    In either case, the name of each .hbm.xml must end with .hbm.xml (of course!) and must be composed of the name of the associated class including its namespace.

    If you don’t do this, NHibernate will not find your mappings, which will result in runtime errors.


  • NHibernate Pitfalls: Fetch and Paging

    This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

    NHibernate allows you to force loading additional references (many to one, one to one) or collections (one to many, many to many) in a query. You must know, however, that this is incompatible with paging. It’s easy to see why.

    Let’s say you want to get 5 products starting on the fifth, you can issue the following LINQ query:

       1: session.Query<Product>().Take(5).Skip(5).ToList();

    Will product this SQL in SQL Server:

       1: SELECT
       2:     TOP (@p0) product1_4_,
       3:     name4_,
       4:     price4_
       5: FROM
       6:     (select
       7:         product0_.product_id as product1_4_,
       8: as name4_,
       9:         product0_.price as price4_,        
      10:         ROW_NUMBER() OVER(
      11:     ORDER BY
      12:         CURRENT_TIMESTAMP) as __hibernate_sort_row
      13:     from
      14:         product product0_) as query
      15:     WHERE
      16:         query.__hibernate_sort_row > @p1
      17:     ORDER BY

    If, however, you wanted to bring as well the associated order details, you might be tempted to try this:

       1: session.Query<Product>().Fetch(x => x.OrderDetails).Take(5).Skip(5).ToList();

    Which, in turn, will produce this SQL:

       1: SELECT
       2:     TOP (@p0) product1_4_0_,
       3:     order1_3_1_,
       4:     name4_0_,
       5:     price4_0_,
       6:     order2_3_1_,
       7:     product3_3_1_,
       8:     quantity3_1_,
       9:     product3_0__,
      10:     order1_0__
      11: FROM
      12:     (select
      13:         product0_.product_id as product1_4_0_,
      14:         orderdetai1_.order_detail_id as order1_3_1_,
      15: as name4_0_,
      16:         product0_.price as price4_0_,
      17:         orderdetai1_.order_id as order2_3_1_,
      18:         orderdetai1_.product_id as product3_3_1_,
      19:         orderdetai1_.quantity as quantity3_1_,
      20:         orderdetai1_.product_id as product3_0__,
      21:         orderdetai1_.order_detail_id as order1_0__,
      22:         ROW_NUMBER() OVER(
      23:     ORDER BY
      24:         CURRENT_TIMESTAMP) as __hibernate_sort_row
      25:     from
      26:         product product0_
      27:     left outer join
      28:         order_detail orderdetai1_
      29:             on product0_.product_id=orderdetai1_.product_id
      30:         ) as query
      31: WHERE
      32:     query.__hibernate_sort_row > @p1
      33: ORDER BY
      34:     query.__hibernate_sort_row;

    However, because of the JOIN, what happens is that, if your products have more than one order details, you will get several records – one per order detail – per product, which means that pagination will be broken.

    There is an workaround, which forces you to write your LINQ query in another way:

       1: session.Query<OrderDetail>().Where(x => session.Query<Product>().Select(y => y.ProductId).Take(5).Skip(5).Contains(x.Product.ProductId)).Select(x => x.Product).ToList()

    Or, using HQL:

       1: session.CreateQuery("select od.Product from OrderDetail od where od.Product.ProductId in (select p.ProductId from Product p skip 5 take 5)").List<Product>();

    The generated SQL will then be:

       1: select
       2:     product1_.product_id as product1_4_,
       3: as name4_,
       4:     product1_.price as price4_
       5: from
       6:     order_detail orderdetai0_
       7: left outer join
       8:     product product1_
       9:         on orderdetai0_.product_id=product1_.product_id
      10: where
      11:     orderdetai0_.product_id in (
      12:         SELECT
      13:             TOP (@p0) product_id
      14:         FROM
      15:             (select
      16:                 product2_.product_id,
      17:                 ROW_NUMBER() OVER(
      18:             ORDER BY
      19:                 CURRENT_TIMESTAMP) as __hibernate_sort_row
      20:             from
      21:                 product product2_) as query
      22:         WHERE
      23:             query.__hibernate_sort_row > @p1
      24:         ORDER BY
      25:             query.__hibernate_sort_row);

    Which will get you what you want: for 5 products, all of their order details.


  • Entity Framework Pitfalls Index

    Updated on August 25th

    These are the posts on Entity Framework pitfalls I’ve written so far. This post will be updated whenever there are more.


  • Entity Framework Pitfalls – Concrete Table Inheritance and Identity Keys

    When using the Concrete Table Inheritance / Table Per Concrete Type pattern for mapping entity inheritances, you cannot use IDENTITYs as primary keys. It is easy to understand why: because each entity of a concrete type is stored in its own table, and if these tables would be using IDENTITYs for generating the primary key, if we would issue a query on their base class looking for a record by its primary key, Entity Framework would generate lots of UNIONs, one for each table, where only one could possibly return a record.

    For example, say we have this model:


    A query such as:

       1: var tool = ctx.Tools.Where(x => x.ToolId == new Guid("32C1BACF-5814-48DD-95E7-31855C7849CB")).SingleOrDefault();

    Would generate the following SQL:

       1: SELECT 
       2: CASE WHEN ([UnionAll2].[C6] = 1) THEN '0X0X' WHEN ([UnionAll2].[C7] = 1) THEN '0X1X' ELSE '0X2X' END AS [C1], 
       3: [UnionAll2].[C1] AS [C2], 
       4: [UnionAll2].[C2] AS [C3], 
       5: CASE WHEN ([UnionAll2].[C6] = 1) THEN [UnionAll2].[C3] WHEN ([UnionAll2].[C7] = 1) THEN CAST(NULL AS varchar(1)) END AS [C4], 
       6: CASE WHEN ([UnionAll2].[C6] = 1) THEN CAST(NULL AS bit) WHEN ([UnionAll2].[C7] = 1) THEN [UnionAll2].[C4] END AS [C5], 
       7: CASE WHEN ([UnionAll2].[C6] = 1) THEN CAST(NULL AS bit) WHEN ([UnionAll2].[C7] = 1) THEN CAST(NULL AS bit) ELSE [UnionAll2].[C5] END AS [C6]
       8: FROM  (SELECT 
       9:     [UnionAll1].[ToolId] AS [C1], 
      10:     [UnionAll1].[Name] AS [C2], 
      11:     [UnionAll1].[C1] AS [C3], 
      12:     [UnionAll1].[C2] AS [C4], 
      13:     [UnionAll1].[Automated] AS [C5], 
      14:     [UnionAll1].[C3] AS [C6], 
      15:     [UnionAll1].[C4] AS [C7]
      16:     FROM  (SELECT 
      17:         [Extent1].[ToolId] AS [ToolId], 
      18:         [Extent1].[Name] AS [Name], 
      19:         CAST(NULL AS varchar(1)) AS [C1], 
      20:         CAST(NULL AS bit) AS [C2], 
      21:         [Extent1].[Automated] AS [Automated], 
      22:         cast(0 as bit) AS [C3], 
      23:         cast(0 as bit) AS [C4]
      24:         FROM [dbo].[TestingTool] AS [Extent1]
      25:         WHERE cast('32c1bacf-5814-48dd-95e7-31855c7849cb' as uniqueidentifier) = [Extent1].[ToolId]
      26:     UNION ALL
      27:         SELECT 
      28:         [Extent2].[ToolId] AS [ToolId], 
      29:         [Extent2].[Name] AS [Name], 
      30:         CAST(NULL AS varchar(1)) AS [C1], 
      31:         [Extent2].[CompatibleWithProject] AS [CompatibleWithProject], 
      32:         CAST(NULL AS bit) AS [C2], 
      33:         cast(0 as bit) AS [C3], 
      34:         cast(1 as bit) AS [C4]
      35:         FROM [dbo].[ManagementTool] AS [Extent2]
      36:         WHERE cast('32c1bacf-5814-48dd-95e7-31855c7849cb' as uniqueidentifier) = [Extent2].[ToolId]) AS [UnionAll1]
      37: UNION ALL
      38:     SELECT 
      39:     [Extent3].[ToolId] AS [ToolId], 
      40:     [Extent3].[Name] AS [Name], 
      41:     [Extent3].[Language] AS [Language], 
      42:     CAST(NULL AS bit) AS [C1], 
      43:     CAST(NULL AS bit) AS [C2], 
      44:     cast(1 as bit) AS [C3], 
      45:     cast(0 as bit) AS [C4]
      46:     FROM [dbo].[DevelopmentTool] AS [Extent3]
      47:     WHERE cast('32c1bacf-5814-48dd-95e7-31855c7849cb' as uniqueidentifier) = [Extent3].[ToolId]) AS [UnionAll2]

    If all tables used IDENTITY, there was no way to make all primary keys unique to each table, at least, not in a easy way.

    The solution is to use a different identifier generation algorithm, such as Guids:

       1: public abstract class Tool
       2: {
       3:     public Tool()
       4:     {
       5:         this.ToolId = Guid.NewGuid();
       6:     }
       8:     public String Name
       9:     {
      10:         get;
      11:         set;
      12:     }
      14:     public Guid ToolId
      15:     {
      16:         get;
      17:         set;
      18:     }
      19: }

    For a complete mapping solution, please read this post.


  • Entity Framework Pitfalls – Validation Does Not Load Lazy Properties

    In a nutshell: Entity Framework Code First (EFCF) validation does not load lazy properties. If any of these properties is marked as required, and it is not loaded, a validation error will occur.

    While I understand the reason – imagine you are saving lots of entities where a required property is not loaded, this will cause lots of accesses to the database just for checking that the required entity exists – I think the way things are is not very productive. I hope the Entity Framework team can come up with a solution, I’ll probably propose something myself.

    Imagine you have a class model such as:

       1: public class Parent
       2: {
       3:     public Int32 ParentId { get; set; }
       4:     public virtual ICollection<Child> Children { get; set; }
       5: }
       7: public class Child
       8: {
       9:     public Int32 ChildId { get; set; }
      10:     [Required]
      11:     public virtual Parent Parent { get; set; }
      12: }

    Say you load a child and make some change:

       1: Child c = ctx.Children.Find(1);
       2: c.SomeProperty = "...";
       4: ctx.SaveChanges();

    This will throw a DbEntityValidationException, because EFCF will think that the Child instance does not have its Parent property set. This is really annoying and is the source of great frustration.

    Of course, there are some workarounds:

    • Explicitly force loading all lazy loaded properties before calling SaveChanges;
       1: var p = c.Parent;
    • Include all lazy loaded properties in the query (best option), such as:
       1: var c = ctx.Children.Where(x => x.ChildId == 1).Include(x => x.Parent).Single();
    • Disable validation at all (not recommended):
       1: ctx.Configuration.ValidateOnSaveEnabled = false;
    • Disable lazy loading globally (also not recommended):
       1: ctx.Configuration.LazyLoadingEnabled = false;
       2: ctx.Configuration.ProxyCreationEnabled = false;
    • Finally, you can remove the required constraint (not good as well):
       1: public virtual Parent Parent { get; set; }