2

I'm trying to write a dynamic query using Fluent NHibernate and struggling to work out how to apply the dynamic where criteria.

This is for an ASP.NET MVC5 project, where I wish to display a table of DTOs in a view, with the ability for the user to apply a filter on any of the rows. The filter must be passed to the database for performance reasons.

Say I have the following class definitions:

// Entity in database
public class EntityA
{
    public int Id {get; set;}
    public string Name {get; set;}
    public IEnumerable<EntityB> Children {get; set;}
}

// Entity in database
public class EntityB
{
    public int Id {get; set;}
    public string Name {get; set;}
    public EntityA Parent {get; set;}
}

// DTO that is displayed in view / filter criteria is based on
public class Dto
{
    public int Id {get; set;}
    public string AName {get; set;}
    public string BName {get; set;}
}

I am receiving the user's filter criteria in an IEnumerable<FilterProperty>, which looks like:

public class FilterProperty
{
    public string Name {get; set;}
    public dynamic Value {get; set;}
}

My NHibernate query is as follows:

EntityA aliasA = null;
EntityB aliasB = null;
Dto aliasDto = null;

var query = QueryOver.Of(() => aliasB)
    .JoinAlias(() => aliasA.Parent, () => aliasA)
    .SelectList(l => l
        .Select(() => aliasB.Id).WithAlias(() => aliasDto.Id)
        .Select(() => aliasA.Name).WithAlias(() => aliasDto.AName)
        .Select(() => aliasB.Name).WithAlias(() => aliasDto.BName)
    )
    .TransformUsing(Transformers.AliasToBean<Dto>());

Where I'm stumped is how do I translate the flat list of filters against the DTO, into expression trees against the entity objects that I can pass to the .Where() NHibernate method?

Is it possible to apply a restriction criteria post transformation?

Chris Pickford
  • 8,642
  • 5
  • 42
  • 73

1 Answers1

0

1. Create criteria for filtering.

You might want to create separate method/helper/convertor for this.

    Dictionary<string, string> mappings = new Dictionary<string, string>(){
       {"Id",    "aliasB.Id"},
       {"AName", "aliasA.Name"},
       {"BName", "aliasB.Name"}
    };
    List<FilterProperty> filters = new List<FilterProperty>();
    Junction filterCreteria = Restrictions.Conjunction();
    foreach (var filter in filters)
    {
        var mappedPropertyName = mappings[filter.Name];
        filterCreteria.Add(Restrictions.Eq(mappedPropertyName, filter.Value));
    }

2. Use it in your query:

 .Where(filterCreteria)
Roman Koliada
  • 4,286
  • 2
  • 30
  • 59
  • Doing this throws a `NHibernate.QueryException` as the property names of the DTO don't match the underlying entity objects. – Chris Pickford Nov 06 '17 at 09:31
  • In this case you'll need to implement converting from your DTO properties to Entity properties somehow(it can be done by simple dictionary, removing prefixes or something more complex). – Roman Koliada Nov 06 '17 at 09:52
  • Thanks - is mapping the DTO properties manually like this the only way? If possible, I'd like to filter on the projected result set using the DTO property names. This is certainly possible using Entity Framework. – Chris Pickford Nov 06 '17 at 10:37
  • I'm afraid that it's not possible with NHibernate. You can feed only entity properties for filtering(if you want to filter data on the DB level). Aliases in the select statements are used to project raw values(after filtering) to your DTO model. – Roman Koliada Nov 06 '17 at 13:08
  • Ok thanks - if you update your answer to say it's not possible to do this dynamically I'll mark as accepted. – Chris Pickford Nov 06 '17 at 13:13