LINQ to NHibernate: could not resolve property

Question

Fluent nHibernate using nHibernate v. 2.1.2.4, nHibernate.Linq v. 1.1.0.1001, and Fluent NHibernate v. 1.1.0.685.

I have a domain object with a DateTime field called DateOfEvent. This is an actual date/time field in the database. I've also added 2 new properties YearOfEvent which grabs the Year from the DateOfEvent field as well as MontOfEvent which grabs the Month from the DateOfEvent field.

public class Event
{
    public Event() { }
    public virtual Int32 Id { get; set; }
    public virtual String Title { get; set; }
    public virtual DateTime DateOfEvent { get; set; }
    public virtual Int32 YearOfEvent
    {
        get { return DateOfEvent.Year; }
    }
    public virtual Int32 MonthOfEvent
    {
        get { return DateOfEvent.Month; }
    }
    public virtual String Location { get; set; }
    public virtual String City { get; set; }
    public virtual String State { get; set; }
    public virtual String Zip { get; set; }
    public virtual String Description { get; set; }
    public virtual Boolean HasImage { get; set; }
    public virtual Boolean IsActive { get; set; }
}

However, when running this method:

public IList<Event> GetActiveEvents(int pageNumber, int pageSize, out int totalItems)
    {
        Int32 skip = Misc.NumberToSkip(pageNumber, pageSize);
        totalItems = (from e in _session.Linq<Event>()
                      where e.IsActive
                      select e).Count();

        return (from e in _session.Linq<Event>()
                where e.IsActive
                select e)
            .Skip(skip)
            .Take(pageSize)
            .ToList();
    }

NHibernate.QueryException: could not resolve property: YearOfEvent of: ....Event

FWIW, I've blurred out the name of the project. ;)

Any idea how to get this query working?

Thanks! -Steve

EDIT: Here's my mapping class:

public class EventMap : ClassMap<Event>
{
    public EventMap()
    {
        Table("tbl_event");
        Id(x => x.Id, "eventId");
        Map(x => x.DateOfEvent, "dateOfEvent");
        Map(x => x.Description, "description");
        Map(x => x.Title, "title");
        Map(x => x.Location, "location");
        Map(x => x.State, "state");
        Map(x => x.City, "city");
        Map(x => x.Zip, "zip");
        Map(x => x.HasImage, "hasImage");
        Map(x => x.IsActive, "isActive");
    }
}

...and my config:

public Configuration GetConfiguration()
    {
        Configuration configuration;
        var assembly = Assembly.Load(".....Data");
        var fluentConfig = Fluently.Configure()
                            .Database(MySQLConfiguration.Standard.ConnectionString(
                                          c => c.FromConnectionStringWithKey("......")
                                          ))
                            .Mappings(m => m.FluentMappings.AddFromAssembly(assembly));
        configuration = fluentConfig.BuildConfiguration();
        configuration.Properties.Add("hbm2ddl.keywords", "none");
        return configuration;
    }

I also just tried the Criteria API:

return _session.CreateCriteria(typeof (Event))
            .Add(Expression.Eq("IsActive", true))
            .Add(Expression.Eq("YearOfEvent", year))
            .List<Event>();

No workie either.

Answer 1

Here is how you can fix that using the formulas. In your Event class convert those 2 properties into regular properties:

public class Event
{
    public Event() { }
    ...
    public virtual DateTime DateOfEvent { get; set; }
    public virtual Int32 YearOfEvent{ get; set; }
    public virtual Int32 MonthOfEvent { get; set; }
    ...
    public virtual Boolean IsActive { get; set; }
}

Then in the mapping add formulas to populate their values.. I'm not very positive about the MySQL syntax for retrieving the year & month but you've got an idea (I used this reference):

public class EventMap : ClassMap<Event>
{
    public EventMap()
    {
        Table("tbl_event");
        Id(x => x.Id, "eventId");
        Map(x => x.DateOfEvent, "dateOfEvent");
        Map(x => x.YearOfEvent).Formula("SELECT YEAR(dateOfEvent)");
        Map(x => x.MonthOfEvent).Formula("SELECT MONTH(dateOfEvent)");            
        ...
        Map(x => x.IsActive, "isActive");
    }
}

Now you can query them just like any other fields using either LINQ or ICriteria:

return _session.CreateCriteria(typeof (Event))
            .Add(Expression.Eq("IsActive", true))
            .Add(Expression.Eq("YearOfEvent", year))
            .List<Event>();

Answer 2

You can query over properties that really exists in database or introduced in the mappings. As YearOfEvent just exists in your entity, you can not use it in your queries either in LINQ-to-NH or ICriteria.

Answer 3

The technique that I use is to force execution of NHLinq and then utilize LINQ to finish filtering. This is technically what needs to happen since NH is unaware of non-mapped properties like Event.YearOfEvent. It would look something like this:

_session.Linq<Event>()
.Where(e => e.IsActive)
.ToArray() // force evaluation of expression
.Where(e => e.YearOfEvent == DateTime.Now.Year); // evaluated by LINQ, not NHLinq

Note: You can utilize any of the other methods that force evaluation (e.g., ToList()). This technique is not dependent upon ToArray() specifically.

Answer 4

Are you auto mapping this? Because I would suspect it's attempted to map YearOfEvent and MonthOfEvent, and it's trying to pull those columns from the database, but they don't exist.

If you're manually mapping them, then this wouldn't be an issue.

Answer 5

Your criteria query is not working, because your "YearOfEvent" property is not listed in your mapping. NHibernate does not know this property, and cannot translate it into a column name.

Try querying on properties that are actually mapped instead of calculated properties, or calculate the property directly in your query...

However, your linq query looks like it should be working, I don't see how this error can occur with that query & mapping...