none
Problem Projecting in ESQL Query RRS feed

  • Question

  • Ever since I started using POCO in my projects, I've been having problem querying data that references other entity on the query. The annoying part of it is that trying the same query on LINQPad works well.

    For example, this esql query below:

     

    SELECT VALUE TOP(1) a.AccountUrl FROM AppEntities.Accounts AS a WHERE EXISTS(SELECT VALUE u FROM a.Users AS u WHERE u.Username=@username)
    

     

    throws the following error when it tries to execute from my application.

    Users' is not a member of type 'DelightModel.Account' in the currently loaded schemas. Near simple identifier, line 1, column 104.

    I tried the same query on LINQPad with the same dll(Repository library) that my web application referenced, and it worked. Changing the query to return the full entity without projection (example below) also works with no problem.

     

    SELECT VALUE TOP(1) a FROM AppEntities.Accounts AS a WHERE EXISTS(SELECT VALUE u FROM a.Users AS u WHERE u.Username=@username)
    

     

    The above query works on my application.

    Is this a bug or am I doing something wrong?

    Please help point me towards the right direction. Thanks.

     


    • Edited by Emekus Wednesday, September 7, 2011 2:13 AM
    Tuesday, September 6, 2011 2:18 AM

All replies

  • Hello Emekus,

    Thanks for your post.

    Could you please show me some of your entities, especially the AppEntities.Accounts so that I can repro your issue on my side?

    looking forward to hearing from you.

     

    have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, September 8, 2011 9:05 AM
    Moderator
  • Hi Jackie-Sun, thanks for the reply.

    The code for the Account class is below

     

     

        public partial class Account
        {
            #region Primitive Properties
        
            public virtual int ID
            {
                get { return _iD; }
                set
                {
                    if (_iD != value)
                    {
                        if (User != null && User.UserId != value)
                        {
                            User = null;
                        }
                        _iD = value;
                    }
                }
            }
            private int _iD;
        
            public virtual int AccountTypeId
            {
                get { return _accountTypeId; }
                set
                {
                    if (_accountTypeId != value)
                    {
                        if (AccountType != null && AccountType.AccountTypeId != value)
                        {
                            AccountType = null;
                        }
                        _accountTypeId = value;
                    }
                }
            }
            private int _accountTypeId;
        
            public virtual string AccountUrl
            {
                get;
                set;
            }
        
            public virtual bool IsLockedOut
            {
                get;
                set;
            }
        
            public virtual Nullable<System.DateTime> LastUpdatedDate
            {
                get;
                set;
            }
        
            public virtual System.DateTime CreatedDate
            {
                get;
                set;
            }
        
            public virtual byte[] TimeStamp
            {
                get;
                set;
            }
        
            public virtual bool IsDeactivated
            {
                get;
                set;
            }
        
            public virtual string Name
            {
                get;
                set;
            }
    
            #endregion
            #region Navigation Properties
        
            public virtual AccountType AccountType
            {
                get { return _accountType; }
                set
                {
                    if (!ReferenceEquals(_accountType, value))
                    {
                        var previousValue = _accountType;
                        _accountType = value;
                        FixupAccountType(previousValue);
                    }
                }
            }
            private AccountType _accountType;
        
            public virtual User User
            {
                get { return _user; }
                set
                {
                    if (!ReferenceEquals(_user, value))
                    {
                        var previousValue = _user;
                        _user = value;
                        FixupUser(previousValue);
                    }
                }
            }
            private User _user;
        
        
            public virtual ICollection<User> Users
            {
                get
                {
                    if (_users == null)
                    {
                        var newCollection = new FixupCollection<User>();
                        newCollection.CollectionChanged += FixupUsers;
                        _users = newCollection;
                    }
                    return _users;
                }
                set
                {
                    if (!ReferenceEquals(_users, value))
                    {
                        var previousValue = _users as FixupCollection<User>;
                        if (previousValue != null)
                        {
                            previousValue.CollectionChanged -= FixupUsers;
                        }
                        _users = value;
                        var newValue = value as FixupCollection<User>;
                        if (newValue != null)
                        {
                            newValue.CollectionChanged += FixupUsers;
                        }
                    }
                }
            }
            private ICollection<User> _users;
    
    
            #endregion
            #region Association Fixup
    
        
            private void FixupUser(User previousValue)
            {
                if (previousValue != null && ReferenceEquals(previousValue.Account, this))
                {
                    previousValue.Account = null;
                }
        
                if (User != null)
                {
                    User.Account = this;
                    if (ID != User.UserId)
                    {
                        ID = User.UserId;
                    }
                }
            }
    
        
            private void FixupUsers(object sender, NotifyCollectionChangedEventArgs e)
            {
                if (e.NewItems != null)
                {
                    foreach (User item in e.NewItems)
                    {
                        if (!item.Accounts.Contains(this))
                        {
                            item.Accounts.Add(this);
                        }
                    }
                }
        
                if (e.OldItems != null)
                {
                    foreach (User item in e.OldItems)
                    {
                        if (item.Accounts.Contains(this))
                        {
                            item.Accounts.Remove(this);
                        }
                    }
                }
            }
    
            #endregion
        }
    

     


    I would have posted the code for the User class as well, but I think the code above is too long. Let me know if you need it, so I can post it as well.

    Note: The query with projection in my previous post works well on LINQPad but not in my application.  But if I change the code to return the entire entity without projecting, it works well on the application.

    Another strange thing is that I walked through the application using Breakpoint, and clicking the refresh like button to explicitly fetch the data worked without throwing any error in the application.



    • Edited by Emekus Thursday, September 8, 2011 9:29 PM
    Thursday, September 8, 2011 9:12 PM