Re: [re-motion-users] help with Contains -> where in

[Fabian Schmied]

Hi,

> Could anyone point me in the direction of any docs/examples/code that would
> give guidance on how to map a 'Contains' linq expression to "where ... in
> ..." sql?
>
> For example, the following additional test in the hql example i would want
> to produce sql of the form "select from PhoneNumber where AreaCode in
> ('2-111', '2-333')":
>
[...]
> var query = from pn in
> NHQueryFactory.Queryable<PhoneNumber>(session)
> where codes.Contains(pn.AreaCode)
> select pn;

[...]

> I'm currently unsure of the best way to handle the SubQueryExpression and
> ContainsResultOperator back in the main query.

As you said, re-linq will interpret your where condition as a subquery
that has one result operator (Contains). When you analyze that Where
condition using an expression visitor, you can detect the
corresponding SubQueryExpression by overriding the
VisitSubQueryExpression method. There you can see that the inner
QueryModel's last result operator is a ContainsResultOperator. At that
point, you know that this is going to be an "x IN y" query. Translate
the result operator's Item expression to your target representation
(e.g., HQL) for the "x" part. Translate the rest of the sub-QueryModel
for the "y" part.

In your case, the left side of the Contains operator is not a real
query (although it generally might be), it's just a constant
collection of values. When you translate the "y" part, you need to
special-case this in order to produce valid HQL (I assume). So, detect
that the MainFromClause.FromExpression of the inner QueryModel is a
ConstantExpression whose value implements ICollection and that the
rest of the QueryModel is an "identity query" (i.e., it just routes
through the values). See QueryModel.IsIdentityQuery (and check that
ResultOperators.Count == 1 - so there are no other filters, etc.). If
so, you can safely emit a list of constant values into your HQL based
on the ICollection within the FromExpression. I suggest you just throw
an exception when the inner QueryModel has a constant value collection
as its FromExpression but is more complex than that.

Note that the HQL sample (I guess you're referring to the CodeProject
article) is just a very basic example. Translating LINQ to HQL is
_very_ difficult, especially because LINQ allows so much more than HQL
does. I'd suggest translating to a tree data structure rather than
directly producing a string if you want to build a LINQ provider that
performs a complex transformation. You can take a look at NHibernate's
LINQ provider for an example of translating LINQ to an HQL tree, or
look at re-linq's own SQL backend
("https://svn.re-motion.org/svn/Remotion/trunk/Relinq/SqlBackend/")
for an example of translating LINQ into a SQL statement.

Best regards,
Fabian

On Tue, Jan 15, 2013 at 2:17 PM, Richard Szurgot <thumb...@gmail.com> wrote:
> Hi,
> Could anyone point me in the direction of any docs/examples/code that would
> give guidance on how to map a 'Contains' linq expression to "where ... in
> ..." sql?
>
> For example, the following additional test in the hql example i would want
> to produce sql of the form "select from PhoneNumber where AreaCode in
> ('2-111', '2-333')":
>
> [Test]
> public void SelectFromWhereContains()
> {
> IEnumerable<string> codes = new List<string> {"2-111", "2-333"};
>
> using (ISession session = _sessionFactory.OpenSession())
> {
> var query = from pn in
> NHQueryFactory.Queryable<PhoneNumber>(session)
> where codes.Contains(pn.AreaCode)
> select pn;
> }
> }
>
> I'm currently unsure of the best way to handle the SubQueryExpression and
> ContainsResultOperator back in the main query.
>
> Any help would be much appreciated!
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "re-motion Users" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/re-motion-users/-/hMFzaYrbxNcJ.
> To post to this group, send email to re-moti...@googlegroups.com.
> To unsubscribe from this group, send email to
> re-motion-use...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/re-motion-users?hl=en.