Question

Hi,

We are having an issue in Linq2SQL, querying objects on a Nullable Enum Field. Version: Dataobjects V4.4.2.8482

Description: If an entity has a NULL value for its nullable enum field, this entity won't appear in a query retrieving all values different from one of the enum value.

How to reproduce : - Create an entity with a nullable enum field ([Field] public Category? Category {get;set;}), with its value set to null - Query all entities where Category != Category.MyCategory : the query doesn't return the entity with null category (session.Query.All<myentity>.Where(e=>e.Category != Category.MyCategory).

Source code to reproduce : File: MyEntity.cs

  public enum Category
  {
    MyCategory = 0,
  }

  [Serializable]
  [HierarchyRoot]
  public class MyEntity : Entity
  {
    [Field, Key]
    public int Id { get; private set; }

    [Field]
    public Category? Category { get; set; }
    public MyEntity(Session session): base(session)
    {
    }
  }

File: Program.cs

static void Main(string[] args)
    {
      var config = DomainConfiguration.Load("sqlexpress");
      var domain = Domain.Build(config);

      using (var session = domain.OpenSession())
        using (var transactionScope = session.OpenTransaction())
        {
          var instance = new MyEntity(session) { Category = null };
          transactionScope.Complete();
        }

      // Reading all persisted objects from another Session
      using (var session = domain.OpenSession())
        using (var transactionScope = session.OpenTransaction())
        {
          // BUG, no entities will be found with Linq2SQL
          var query = session.Query.All<MyEntity>()
            .Where(e => e.Category != Category.MyCategory);

          Console.WriteLine("Linq2SQL");
          foreach (var myEntity in query)
            Console.WriteLine("  " + myEntity);

          var query2 = session.Query.All<MyEntity>().ToList().
            Where(e => e.Category != Category.MyCategory);

          Console.WriteLine("Linq2Object");
          foreach (var myEntity in query2)
            Console.WriteLine("  " + myEntity);

          transactionScope.Complete();
        }
      Console.ReadKey();
    }

Is this a bug in Dataobjects Linq2SQL ?

Thanks, Benoit

Accepted Answer

Hello Benoit,

This is a tri-logic feature of SQL. Each logical expression can be either true, false or undefined. When you compare anything to null the result is undefined. SQL where clause accepts row if and only if filter expression has true value.

When implementing any LINQ -> SQL translator implementor has to deal with such logic. DataObjects.Net follows LINQ To SQL (System.Data.Linq) and EF apporach to handle such cases "as is".

Adding Column IS NULL to any generated comparison with Column over complicates generated SQL and could yield index scans instead of index seeks in the resulting query plan.

However comparing column with parameter that can have null value is supported.

 MyCategory? cat = null;
 session.Query.All<MyEntity>().Where(e => e.Category==cat);

This will be translated to where Category IS NULL because actual parameter value is known at C# side.

For your case I suggest a workaround by adding Undefined category instead of using nullable type. It should have integer value 0 so any newly created entities have their categories undefined.

Bonus chatter:

Almost all logical operations with undefined SQL value will again yield undefined. The only cases that allow you to "escape" undefined result at SQL side are false && value and true || value. If you rewrite your query to the following it will produce expected result:

var query = session.Query.All<MyEntity>()
  .Where(e => e.Category==null || e.Category != Category.MyCategory);

Answer 2

0	Thanks it makes sense. So this is not a bug but a SQL feature. -> Our plan is going to be to remove Nullable<enum> everywhere and add the 'undefined'-like enum entry where necessary. answered Mar 14 '12 at 12:14 Benoit Nesme 43●20●20●24

Subscription:

Related questions