Error in sql generation

I am using hte lasest nightly.  This release gives an error when doing a count on one of my unit tests.

The test is

        [Test]
        public void GetActiveUsers()
        {
            UseSampleDatabase();

            var users = _AccRepository.GetActiveUsers(29);

            Assert.Greater(users.Count(), 0); // error in lightspeed sql
            foreach (var user in users)
            {
                Assert.IsTrue(user.Active);
                Assert.IsTrue(user.InRole(29));
            }
        }

and the linq is

        public static IQueryable<User> GetActiveUsers(this IBaseRepository helper, int permissionId, int? userId = null)
        {
            return from u in helper.GetAll<UserPermission>()
                   where u.PermissionId == permissionId && (u.User.Active || u.UserId == userId)                      
                   orderby u.User.FullName
                   select u.User;           
        }

The table definitions are

        public static IQueryable<User> GetActiveUsers(this IBaseRepository helper, int permissionId, int? userId = null)
        {
            return from u in helper.GetAll<UserPermission>()
                   where u.PermissionId == permissionId && (u.User.Active || u.UserId == userId)                      
                   orderby u.User.FullName
                   select u.User;           
        }

UserPermission object

CREATE TABLE [dbo].[UserPermission](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [PermissionId] [int] NOT NULL,
)

User object

CREATE TABLE [dbo].[Permissn](
    [UserID] [int] NOT NULL,
    [UserName] [varchar](15) NOT NULL,
    [FullName] [varchar](30) NOT NULL,
...
)

The generated sql is

SELECT
  COUNT(UserId)
FROM
  UserPermission
INNER JOIN
  Permissn
ON
  UserPermission.UserId = Permissn.UserId
WHERE
  (UserPermission.PermissionId = @p0 AND (Permissn.ActiveUser = @p1 OR UserPermission.UserId IS NULL))

and is failing as userId in   COUNT(UserId) is ambiguous.  It should either be UserPermission.UserId or *

Sean

Can you remember which build you were using before (when the test was passing)?  No need to go digging around if you don't have the info to hand -- just might be helpful for us to track down the source of the regression.

I suspect it was LightSpeed31Professional-20110316.

Sean

Hi Sean,

We're having trouble reproducing the SQL you're seeing -- we see an EXISTS subquery instead of an INNER JOIN -- and we suspect we're not seeing part of the problem.  Is there something in the GetAll method that could be causing this, perhaps explicitly performing a join?  Are you using the IQueryable<User> returned from GetActiveUsers in a projection (select) when you do the Count()?  Could you post the query that causes the error?  Thanks!

The problem has something to with the property Active on the user object. 

It's defined using an Expression as per the blog post http://www.mindscapehq.com/blog/index.php/2010/09/14/ninja-domain-properties-in-lightspeed/ .

        public bool Active
        {
            get { return (ActiveUser == "Y") || (ActiveUser == "y"); }
            set { ActiveUser = value ? "Y" :"N"; }
        }

        private static readonly Expression<Func<User, bool>> ActiveExpression = u => u.ActiveUser == "Y";

If I change my method from;

        public static IQueryable<User> GetActiveUsers(this IBaseRepository helper, int permissionId, int? userId = null)
        {
            return from u in helper.GetAll<UserPermission>()
                   where u.PermissionId == permissionId && (u.User.Active || u.UserId == userId)                      
                   orderby u.User.FullName
                   select u.User;           
        }

to

        public static IQueryable<User> GetActiveUsers(this IBaseRepository helper, int permissionId, int? userId = null)
        {
            return from u in helper.GetAll<UserPermission>()
                   where u.PermissionId == permissionId && (u.User.ActiveUser == "Y" || u.UserId == userId)                      
                   orderby u.User.FullName
                   select u.User;           
        }

Then everything is sweetness and light.

All the way from the top to the bottom, my code is

Unit test

        [Test]
        public void GetActiveUsers()
        {
            UseSampleDatabase();

            var users = _AccRepository.GetActiveUsers(29);

            Assert.Greater(users.Count(), 0); // error in lightspeed sql here
            foreach (var user in users)
            {
                Assert.IsTrue(user.Active);
                Assert.IsTrue(user.InRole(29));
            }
        }

Repository helper

        public static IQueryable<User> GetActiveUsers(this IBaseRepository helper, int permissionId, int? userId = null)
        {
            return from u in helper.GetAll<UserPermission>()
                   where u.PermissionId == permissionId && (u.User.Active || u.UserId == userId)                      
                   orderby u.User.FullName
                   select u.User;           
        }

Repository

        public IQueryable<TEntity> GetAll<TEntity>() where TEntity : Entity
        {
            return UnitOfWorkScope.Current.Query<TEntity>();
        }

Aha, that was it!  Thanks, Sean, reproduced and investigating.

Thanks for alerting us to this.  The problem was with the cocktail of ninja domain members, traversals, performing a Count on the traversal entity, the PK of the traversed-to table having the same name as the FK in the origin table and finally the traversal-plus-ninja-member being within a composite (&&/||) expression.

There will be a candidate fix for this in the next nightly build.  It covers the reported case but we have not yet tested it with more complex combinations, so do let us know if you still see problems.

Works fine now in all my tests.  Thanks