mysql - 奇怪的 NHibernate QueryOver 语法错误

Question

//This code works fine.
UserProfile profile = session.QueryOver<UserProfile>().Where(userProfile => userProfile.UserId == user).List().FirstOrDefault();

//This code throws an invalid Syntax Error
IList<Character> characters = session.QueryOver<Character>().Where(character => character.UserId == user).List<Character>();

我在使用 NHibernate QueryOver 时遇到上述代码的问题。数据库有两个项目设置与用户表的关系。它们的设置相同，两个 Map 类的设置也是如此。

    public class CharacterMap : ClassMap<Character>
{
    public CharacterMap()
    {
        Id(x => x.Id).Column("id");
        Map(x => x.Name).Column("name");
        Map(x => x.Class).Column("class");
        Map(x => x.Level).Column("level");
        Map(x => x.Sex).Column("sex");
        Map(x => x.Stats).Column("stats"); //Varchar 2048
        Map(x => x.Position).Column("position"); //Varchar 1024

        References(x => x.UserId).Column("user_id");
        Table("character");
    }
}

和

    public class UserProfileMap : ClassMap<UserProfile>
{
    public UserProfileMap()
    {
        Id(x => x.Id).Column("id");
        Map(x => x.CharacterSlots).Column("character_slots");
        References(x => x.UserId).Column("user_id");
        Table("user_profile");
    }
}

而 User 是从这个映射构建的类。

public class UserMap : ClassMap<User>
{
    public UserMap()
    {
        Id(x => x.Id).Column("id");
        Map(x => x.Username).Column("username");
        Map(x => x.Password).Column("password");
        Map(x => x.Salt).Column("salt");
        Map(x => x.Email).Column("email_address");
        Map(x => x.Algorithm).Column("algorithm");
        Map(x => x.Created).Column("created_at");
        Map(x => x.Updated).Column("updated_at");
        Table("user");
    }
}

实体是:

public class UserProfile
{
    public virtual int Id { get; set; }
    public virtual User UserId { get; set; }
    public virtual int CharacterSlots { get; set; }
}

public class User
{
    public virtual int Id { get; set; }
    public virtual string Username { get; set; }
    public virtual string Password { get; set; }
    public virtual string Salt { get; set; }
    public virtual string Email { get; set; }
    public virtual string Algorithm { get; set; }
    public virtual DateTime Created { get; set; }
    public virtual DateTime Updated { get; set; }
}

public class Character
{
    public virtual int Id { get; set; }
    public virtual User UserId { get; set; }
    public virtual string Name { get; set; }
    public virtual string Class { get; set; }
    public virtual string Sex { get; set; }
    public virtual int? Level { get; set; }
    public virtual string Stats { get; set; }
    public virtual string Position { get; set; }


    public virtual CharacterDetails BuildCharacterListItem()
    {
        return new CharacterDetails()
                    {
                        Id = Id,
                        Class = Class,
                        Name = Name,
                        Level = Level,
                        Sex = Sex
                    };
    }
}

问题是当我尝试使用 User 类作为比较从表中获取“字符”列表时，出现语法错误，但如您所见，第一个查询返回的结果很好，而这两个查询是几乎相同。

有谁知道为什么会发生这种情况？我一辈子都弄不明白。

我收到的错误是:

        Error: could not execute query
    [ SELECT this_.id as id0_0_, this_.name as name0_0_, this_.class as
     class0_0_, this_.level as level0_0_, this_.sex as sex0_0_, this_.stats 
    as stats0_0_, this_.position as position0_0_, this_.user_id as 
    user8_0_0_ FROM character this_ WHERE this_.user_id = ?p0 ]
      Name:cp0 - Value:PerilousServer.Data.NHibernate.User
    [SQL: SELECT this_.id as id0_0_, this_.name as name0_0_, this_.class 
    as class0_0_, this_.level as level0_0_, this_.sex as sex0_0_, this_.stats 
as stats0_0_, this_.position as position0_0_, this_.user_id as user8_0_0_ 
FROM character this_ WHERE this_.user_id = ?p0]

    Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'character this_ WHERE this_.user_id = 11' at line 1

如果有帮助，最后是整个 session /事务。一切都恢复正确，直到我点击 QueryOver()，即使我能够使用相同的 QueryOver 语法获取 UserProfile。

   try
    {
        using (var session = NHibernateHelper.OpenSession())
        {
            using (var transaction = session.BeginTransaction())
            {
                //Get userData from logged in connections for verification later..
                var userData = Server.Instance.ConnectedUsers[peerID];
                var user = session.QueryOver<User>().Where(u => u.Id == userID).List().FirstOrDefault();

                if (user != null)
                {
                    UserProfile profile = session.QueryOver<UserProfile>().Where(userProfile => userProfile.UserId == user).List().FirstOrDefault();
                    if (profile != null)
                    {
                        //Check to see if peerID is actually connected.
                        if (userData != null && userData.ClientData<UserData>().UserId == userID )
                        {
                            Console.Write("Found user: {0} and matching to character.", profile.UserId.Id);
                            IList<Character> characters = session.QueryOver<Character>().Where(character => character.UserId == user).List<Character>();

                            List<CharacterDetails> characterList = new List<CharacterDetails>();

                            foreach (var character in characters)
                            {
                                characterList.Add(character.BuildCharacterListItem());
                            }

                            response = new Message(MessageType.Response, MessageCode.Login, (byte)LoginCode.CharacterList);
                            response.AddParameter(MessageParameterCode.CharacterSlots, profile.CharacterSlots);
                            response.AddParameter(MessageParameterCode.CharacterList, characterList);

                            client.Send(response);
                            transaction.Commit();
                        }
                        else
                        {
                            //Send message saying the account was trying to access someone elses information.
                            client.OnLog("User ID's did not match. PeerID is incorrect.");
                        }
                    }
                    else
                    {
                        //Send message that profile was not found.
                        client.OnLog("User Profile was not found.");
                    }
                }
                else
                {
                    //Send message saying there was no user with that ID. NULL
                    client.OnLog("UserID was null.");
                }
            }
        }
    }
    catch (Exception e)
    {
        client.OnError(e.Message);
        client.OnError(e.InnerException.Message);

        //Send message we recieved an error.
    }
}

Answer 1

显然这是因为 character 是 mySql 中的保留字。引用this问题以获取更多信息。