combining raw sql with IQueryable

[Daniel Thaller]

I am creating queries from raw sql like this: ()

var query = "SELECT * from ORDERS o where o.Year in (:orderYear));";
 
var session = sessionFactory.OpenSession();
var result =session.CreateSQLQuery(query)
                .AddEntity(typeof(Order))
                .SetInt32("orderYear",2012)
                .List<Order>();

The query shown above should be restricted further dynamically by the user. 
What i normally do is get an IQueryable and add my where-clauses. 
But how can i combine the query shown above with IQueryable WITHOUT getting the list of elements from the above query first?

what i want (pseudocode):  CurrentSession.Query<Order>().Where(x => query.Contains(x)).ToList()
=> so i want to execute both queries in the same statement, because if the first query yields many results i get in real performance-troubles if i have to retrieve the list of the first query beforehand.

Is there a way to archive my goal?

[Fran Knebels]

Could you use filters?

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com.
To post to this group, send email to nhu...@googlegroups.com.
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.

[Daniel Thaller]

can you give an example of what you mean, please?

[Fran Knebels]

http://ayende.com/blog/3993/nhibernate-filters

[Gunnar Liljas]

AFAIK, there is currently no way to do this. What is it about the first query that makes this necessary?

[Daniel Thaller]

its built by the user (some sort of query-editor which results in a querystring)

[Gunnar Liljas]

Ah...I suggest you build the Linq query dynamically instead.

/G

[Ricardo Peres]

Or Dynamic LINQ?

http://dynamiclinq.codeplex.com/

[Daniel Thaller]

i think dynamic linq doesnt really fit in here for me, because what the query contains is a REAL db-statement (column names from db, not from an entity that is mapped within nhibernate - in fact the entity is not mapped at all. the query is generated from fetching db-column-names and types from db...)

[Daniel Thaller]

This seems to be the only real alternative here. If i choose to go down this path, there is no way around creating a nhibernate-mapping for every entity, right? (because my queries can be created on random db-views, there doesnt have to be a mapping for that entity in our code for now...)

One other approach i was trying was to extend the linq-provider:

public class QueryContainsGenerator : BaseHqlGeneratorForMethod

    {

        public QueryContainsGenerator()

        {

            SupportedMethods = new[]

                {

                    ReflectionHelper.GetMethodDefinition(() => default(IQuery).QueryContains(default(Guid)))

                };

        }

        public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject,

                                             ReadOnlyCollection<Expression> arguments, HqlTreeBuilder treeBuilder,

                                             IHqlExpressionVisitor visitor)

        {

            var node = visitor.Visit(arguments[1]).AsExpression();

            var source = visitor.Visit(arguments[0]).AsExpression();

            HqlTreeNode inClause = treeBuilder.In(node, source);

            return inClause;

        }

    }

i could then call the query like this:

IQuery query; //the user-construced query containing the pure sql (S)

queryable.Where(x => query.QueryContains(x.IdCompany));

this seemed to work at the first peek, but unfortunately i cant get the plain sql to execute as its interpreted as a string... this is the sql that is generated by my approach:

SELECT * FROM companies WHERE id_company IN ('SELECT id_company FROM companies where name = :parameterName') //note the quotes around the subselect within the IN-clause