Calling a SQL query in Orchard service

Question

In my Orchard Module (herein referred to as "MyModule"), I need to query for a dataset that consists of several joins looping back to an original table to build a list of friends for a specified member.

I have two ContentItems defined with the appropriate parts (among others omitted for clarity)

Member item => MemberPart, etc.
Friend item => FriendPart, CommonPart

CommonPart on the Friend item is used to hold a reference to the Member to whom this Friend belongs. FriendPart also contains a pointer reference back to the Member's record.

I am able to express this query in a service using three ContentManager.GetMany() calls, but it requires a bit of code to accomplish this in an Orchard service.

The dataset I am looking for is easiest stated as SQL:

SELECT DISTINCT memberFriend.Id
  FROM MyModule_MemberPartRecord member
    INNER JOIN Common_CommonPartRecord common
        ON member.Id = common.Container_id
    INNER JOIN MyModule_FriendPartRecord friend
        ON common.Id = friend.Id
    INNER JOIN MyModule_MemberPartRecord memberFriend
        ON friend.Member_Id = memberFriend.Id
    WHERE member.Id = {parameter}

Works great and tighter coding.

My question: how do I call a SQL query in Orchard? Do I save the SQL as a database View and call that? Or do I build a command and call that?

What is the appropriate way to execute a SQL query in a service in Orchard 1.8.1?

Answer 1

private readonly ISessionLocator _sessionLocator;
public Class(ISessionLocator sessionLocator) {
    _sessionLocator = sessionLocator;
}

public void SQL(string sql) {
    var session = _sessionLocator.For(typeof(object));
    session.CreateSQLQuery(sql);
    var list = session.List();
}

You shall also need to add a reference to NHibernate from Orchard's lib folder.

Have you looked at .Query<> or .HqlQuery<> on the ContentManager API? You can do all these joins there, should be okay. Perhaps. Or use HQL itself (good intro here). This has some pretty sweet syntax, can join content very easily so long as the relationship is explicitly mapped within your models. But yeah, sometimes SQL is just the convenient option ^_^