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.

Maybe this is an option: github.com/pvginkel/NHibernate.OData — Andreas
– Andreas, Commented Mar 5, 2012 at 20:10
@Andreas - thanks for the suggestion, however I can't figure out how to integrate NHibernate.OData into Castle ActiveRecord. :( — Justin
– Justin, Commented Mar 5, 2012 at 21:46

Rich · Accepted Answer · 2012-03-05 22:44:14Z

2

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.

answered Mar 5, 2012 at 22:44

Rich

2,0961 gold badge15 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Justin Over a year ago

I'm aware of when it hits the database when dealing with an iqueryable, however in this case (with asp.net web api, odata, and active record) using the approach that you suggest it calls the database first and returns ALL records in the table, and then the odata performs upon the c# list and filters it down. It's not smart enough to know how to perform odata commands within nhibernate/active record i guess, hence the nhibernate.odata project. Thanks though for the suggestion.

Justin · Accepted Answer · 2012-03-05 22:23:15Z

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

Hi @Justin, I'm a little confused. Could you help me? In my case I use ODataController with webapi, posted this question on this link. Thank you!

Joel · Accepted Answer · 2013-02-15 12:38:41Z

0

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!

answered Feb 15, 2013 at 12:38

Joel

7,5894 gold badges56 silver badges61 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related