//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>();
I am having an issue with the above code when using NHibernate QueryOver. The database has both items setup with relationships to the User table. They are setup identically and both Map classes are setup as so.
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");
}
}
and
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");
}
}
and User is a class built from this mapping.
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");
}
}
The entities are:
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
};
}
}
The problem is when I try to get the list of "Characters" from the table using the User class as the comparison, I get a syntax error but as you can see the first query comes back with results fine and the two are pretty much identical.
Does anyone have an idea why this could be happening? I can not figure it out for the life of me.
The error I am receiving is:
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
Finally here is the entire Session/transaction if it helps. Everything comes back correct until I hit the QueryOver() even though I was able to get the UserProfile using the same QueryOver syntax.
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.
}
}
Apparently this is due to the fact that character is a reserved word in mySql. Refer to this question for more information.