Fluent NHibernate dynamic DTO query

1
votes

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?

1
c#nhibernatefluent-nhibernatequeryover
How your filters should be connected? Using && or ||? – Roman Koliada
All filters will be && at the same level. – Chris Pickford

1 Answers

0
votes

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)