CODEWITHSUNDEEP
nhibernatecastle-activerecordasp.net-mvc-4asp.net-web-api
Justin
Justin

Reputation: 18186

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

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.

Upvotes: 0

Views: 2940

Answers (3)

Joel
Joel

Reputation: 7569

Adding to @Justin answer, without ActiveRecord one could just return something like

public IQueryable<Message> Get()
{
    ICriteria query = _unitOfWork.CurrentSession.ODataQuery<Message>(GetOData());
    return query.Future<Location>().AsQueryable<Location>();
}

// Taken from @Justin's answer
protected string GetOData()
{
    var odata = this.Request.RequestUri.Query;
    odata = odata.Substring(odata.IndexOf("$"), odata.Length - odata.IndexOf("$"));
    odata = odata.Replace("%20", " ");
    return odata;
}

This should put you up for testing!

Upvotes: 0

Rich
Rich

Reputation: 2096

Keep in mind, you're dealing with an IQueryable, which is not a physical list of objects. It is a query which has the potential to be executed and result in a list of entities. These queries can also be chained together:

var query = customRepository.Where(x => x.CustomerName == "John"); //no results generated
var query2 = query.Where(x => x.Salary > 100000); // still no results generated
var results = query2.ToList();  // now results are generated

You should be able to just return an instance of Session.Query from NHibernate. The OData functionality will then chain the criteria additional based on the URL and then enumerate the results back out.

Upvotes: 2

Justin
Justin

Reputation: 18186

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!!

Upvotes: 1

Related Questions