How to incorporate ASP.NET Web API ODATA into ActiveRecord/NHibernate

Question

In ASP.NET Web API, it allows you to write ODATA queries in the url string to specify which data you want to return from a method. However the part that I'm having a hard time grasping is that ODATA works to filter an IQueryable collection of C# objects, not the database table itself.

This is impractical because really you would want to filter at the database level, as it would be horrible to return all objects from the database, load them into a C# IQueryable list, and then have the ODATA filter that list.

Here is the code, which uses NHibernate and Castle Active Record for data access:

public IQueryable<Message> GetAll()
{
    return from m in MessageData.FindAllQueryable()
           select ConvertToView(m);
}

public static IQueryable<Message> FindAllQueryable()
{
    var criteria = DetachedCriteria.For<Message>()
        .CreateAlias("MessageRecipients", "mr")
        .AddOrder(new Order("Id", false));
    return ActiveRecordMediator<Message>.FindAll(criteria).AsQueryable();
}

The end result of this code would be it returning all messages from the database. How do I allow the ODATA to perform its filters upon the database itself? Otherwise this whole concept of ODATA is completely impractical for real world situations.

Answer 1

Here is how I ended up getting it working. Much thanks to Andreas for leading me to NHibernate.OData.

In my controller action I get the odata from the url and pass it into my data access function:

public IQueryable<Message> GetAll(int authUserId, int userId, DateTime? startDate, DateTime? endDate)
        {
            LogWriter.Write(String.Format("Getting all messages for user {0}", userId));

            //get messages and convert to view.
            return from m in MessageData.FindAll(userId, startDate, endDate, GetOData())
                    select new Message(m);
        }

    protected string GetOData()
            {
                var odata = this.Request.RequestUri.Query;
                odata = odata.Substring(odata.IndexOf("$"), odata.Length - odata.IndexOf("$"));
                odata = odata.Replace("%20", " ");
                return odata;
            }

Inside the data access method, we get the NHibernate session and call session.ODataQuery:

 public static IQueryable<Message> FindAll(int userId, DateTime? startDate, DateTime? endDate, string odata)
            {
                ICriteria query = GetSession().ODataQuery<Message>(odata);
                var detachedCriteria = new ConvertedDetachedCriteria(query)
                    .CreateAlias("MessageRecipients", "mr")
                    .Add(Restrictions.Or(
                        Restrictions.Eq("SenderUserId", userId),
                        Restrictions.Eq("mr.Key.RecipientId", userId)
                    ));
                return FindAllQueryable(detachedCriteria);
            }

    public static ISession GetSession()
            {
                var factory = ActiveRecordMediator.GetSessionFactoryHolder().GetSessionFactories()[0];
                return factory.OpenSession();
            }

public static IQueryable<T> FindAllQueryable(DetachedCriteria criteria)
        {
            return ActiveRecordMediator<T>.FindAll(criteria).AsQueryable();
        }

Also, a simple ConvertedDetachedCriteria class is needed to convert from ICriteria to DetachedCriteria.

public class ConvertedDetachedCriteria : DetachedCriteria
    {
        public ConvertedDetachedCriteria(ICriteria criteria)
            : base((CriteriaImpl)criteria, criteria)
        {
            var impl = (CriteriaImpl)criteria;
            impl.Session = null;
        }
    }

Hopefully this will help someone else. I now can write odata queries against my asp.net web api methods and have them filter at the db level, which is much more useful than filtering iqueryable c# objects!!