CODEWITHSUNDEEP
nhibernatefluent-nhibernate
Robert
Robert

Reputation: 4406

Converter from SQL to Linq to Fluent Nhibernate

I am wondering if there is a converter where I can type an SQL statement and receive its fluent Nhibernate equivalent?

I have this SQL statement:

 SELECT F.* 
 From Foo F
      INNER JOIN HamSandwich HS
           ON F.HamSandwichId = HS.HamSandwichId
 Where 
      HS.MustardId = 
           (Select MustardId
            From HamSandwich
            Where HamSandwichId = 1)

And I want to turn it into a fluent Nhibernate query. 

session.QueryOver<Foo>()
       .JoinQueryOver(foo => foo.HamSandwich)
       .Where() // this is where I am still drawing a blank

Upvotes: 1

Views: 911

Answers (1)

Andrew Whitaker
Andrew Whitaker

Reputation: 126042

Assuming some things about your entites and mappings, this is the equivalent QueryOver:

IList<Foo> foos = session.QueryOver<Foo>()
    .JoinQueryOver(foo => foo.HamSandwich)
    .WithSubquery.WhereProperty(hs => hs.Mustard.Id).Eq(
        QueryOver.Of<HamSandwich>()
            .Where(hs => hs.Id == 1)
            .Select(hs => hs.Mustard.Id))
    .TransformUsing(Transformers.DistinctRootEntity)
    .List<Foo>();

This generates the following SQL:

SELECT this_.*
FROM   [Foo] this_
       inner join [HamSandwich] hs1_
         on this_.HamSandwichId = hs1_.Id
WHERE  hs1_.MustardId = (SELECT this_0_.MustardId as y0_
                                   FROM   [HamSandwich] this_0_
                                   WHERE  this_0_.Id = 1 /* @p0 */)

Upvotes: 2

Related Questions