SQL query with inner join to NHibernate with QueryOver

Question

I have this SQL query :

select pr.*, det.Description, det.Name 
from Product pr 
inner join ProductDetail det on det.Product_id = pr.id
where pr.Id = XX and det.IsDefault = yy

How can I do this with QueryOver ?

Thanks,

Update :

public ProductMap()
{
    Id(x => x.Id).GeneratedBy.Native();
    Map(x => x.Code)
        .Length(20)
        .Not.Nullable();
    Map(x => x.CreationDate).Not.Nullable();
    Map(x => x.IsDeleted);
    Map(x => x.Price);
    HasManyToMany(x => x.Categories)
        .AsSet()
        .Cascade
        .SaveUpdate()
        .Table("ProductsCategories");
}

public class ProductDetailMap : ClassMap<ProductDetail>
{
    public ProductDetailMap()
    {
        Id(x => x.Id).GeneratedBy.Native();
        Map(x => x.Name)
            .Length(50)
            .Not.Nullable();
        Map(x => x.Description)
            .Length(250);
        Map(x => x.IsDefault);
        References(x => x.Language);
        References(x => x.Product);
    }
}

What type are you trying to get out?

Andrew Whitaker
– Andrew Whitaker

2012-07-18 22:32:09 +00:00
Commented Jul 18, 2012 at 22:32 — Andrew Whitaker
– Andrew Whitaker, Commented Jul 18, 2012 at 22:32
@AndrewWhitaker An anonymous type.

TheBoubou
– TheBoubou

2012-07-19 03:42:08 +00:00
Commented Jul 19, 2012 at 3:42 — TheBoubou
– TheBoubou, Commented Jul 19, 2012 at 3:42

Surya Pratap · Accepted Answer · 2014-12-09 16:39:48Z

2

As far as I know QueryOver has no equivalent of the "pr.*" part, which would be nice. Which means you would have to manually specify each property of pr in your query.

See the section "Projections" on the document here

But it would be something like:

Product productAlias = null;
ProductDetail productDetail = null;
var query = session.QueryOver(() => productAlias)
                     .Inner.JoinAlias(() => productAlias.ProductDetails, () => productDetail)
                     .Where(() => productAlias.Id == XX && productDetail.IsDefault == YY)
                     .SelectList(list => list
                         .Select(() => productAlias.Id)
                         .Select(() => productAlias.Property1)
                         .Select(() => productAlias.Property2) 
                         // and so on...
                         .Select(() => productDetail.Description)
                         .Select(() => productDetail.Name)
                      );

// One way of doing it... 
// Will give you a list of object arrays matching the result
var results1 = query.List<object[]>();

// Another way...
// You need to define a class that has all the properties your are querying for
// If we create a class like that called "MySummaryClass" you can do:
var results2 = query.TransformUsing(Transformers.AliasToBean<MySummaryClass>()).List<MySummaryClass>();

edited Dec 9, 2014 at 16:39

Surya Pratap

4959 silver badges18 bronze badges

answered Jul 19, 2012 at 9:13

NeoDarque

3,4823 gold badges23 silver badges21 bronze badges

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

1 Comment

TheBoubou Over a year ago

Well well, It's easier to do two queries and then use linq after to merge both, that's work fine

Collectives™ on Stack Overflow

SQL query with inner join to NHibernate with QueryOver

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related