NHibernate Query to json results are unexpected

Question

I'm building a webapplication with .Net Core using web api, angular 2 and NHibernate. I have the following tables in my database:

Todo

ID
Name
Priority

Priority

ID
Name

And the following mapping for these tables:

[Class(NameType = typeof(Todo), Table = "Todo")]
public class Todo
{
    [ID(-2, Name = "ID")]
    [Generator(-1, Class = "native")]
    public virtual long ID { get; set; }

    [Property]
    public virtual string Name { get; set; }

    [ManyToOne]
    public virtual Priority Priority { get; set; }
}

[Class (NameType = typeof(Priority), Table = "Priority")]
public class Priority
{
    [ID(-2, Name = "ID")]
    [Generator(-1, Class = "native")]
    public virtual long ID { get; set; }

    [Property]
    public virtual string Name { get; set; }
}

I also have the following DTO that I want to create a list of and send it to the client in json: For the purpose of this example I have stripped some other properties from it.

public class TodoDTO
{
    public long ID { get; set; }

    public string Name { get; set; }

    public Priority Priority { get; set; }
}

When I run the query below:

var session = SessionFactoryConfigurationBase.GetSessionFactory().GetCurrentSession();

var query = session.QueryOver<Todo>();

TodoDTO todoSummary = null;

query.SelectList(list => list
    .Select(t => t.ID).WithAlias(() => todoSummary.ID)
    .Select(t => t.Name).WithAlias(() => todoSummary.Name)
    .Select(t => t.Priority).WithAlias(() => todoSummary.Priority))
.TransformUsing(Transformers.AliasToBean<TodoDTO>());

the resulting json doesn't show the ID and Name properties of Priority but it shows the following:

[{
    "id":1,
    "name":"TEST",
    "priority":
    {
        "__interceptor":
        {
            "persistentClass":"Todo, ApplicationName, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null",
            "identifier":2,
            "isUninitialized":true,
            "unwrap":false,
            "session":null,
            "entityName": "Priority",
            "isReadOnlySettingAvailable":false
        }
    }
}]

Why is it not showing the ID and Name properties but the class definition instead? When i create a separate list from the above query I get the result I expected in the fist place but that seems rather cumbersome.

--------------------- EDIT -------------------------

As requested the code that does give me the result i expect:

public IList<Todo> GetTodos()
{
        var session = SessionFactoryConfigurationBase.GetSessionFactory().GetCurrentSession();

    var query = session.QueryOver<Todo>()
    .Fetch(t => t.Priority).Eager
    .List<Todo>();

    if(!query.Any())
    {
        return null;
    }

    var result = (
        from t in query
        select new TodoDTO
        {
            ID = t.ID,
            Name = t.Name,
            Priority = t.Priority
        }
    ).ToList();        

    return result;
}

The result is return to the client with the following code:

public JsonResult GetTodos()
{
    var todos = GetTodos();

    return new JsonResult(todos);
}

Which line is generating the JSON? Can you show us the alternative you wrote that did work? — mjwills
– mjwills, Commented Jun 22, 2017 at 8:35
I don't think you've 'materialised' your results by using the List() method on the resulting query. — David Osborne
– David Osborne, Commented Jun 22, 2017 at 8:40
@David Osborne: I'm actually quite sure that is the problem but I don't know how to fix that exactly. — Bunnynut
– Bunnynut, Commented Jun 22, 2017 at 8:49
Can you talk us through why you don't want to use the way you already have that works? — mjwills
– mjwills, Commented Jun 22, 2017 at 10:00
I have been using Linq to SQL for years and with that I can shape my results within the query itself. With QueryOver I need more code to do the same thing it seems. I just want to make sure that I'm doing it right. — Bunnynut
– Bunnynut, Commented Jun 22, 2017 at 10:06

David Osborne · Accepted Answer · 2017-06-22 11:27:14Z

2

This is probably a neater way to accomplish what you want:

var todos = 
    session
        .Query<Todo>()
        .Fetch(t => t.Priority)
        .Select(t => 
            new {
               t.ID,
               t.Name,
               Priority = new {
                   t.Priority.Id,
                   t.Prioriry.Name}
            })
        .ToList();

return new JsonResult(todos);

I've omitted the DTO to demonstrate that it's not strictly necessary. However, the Select() call could be changed to create a new DTO instead.

I just want to make sure that I'm doing it right.

When it comes to QueryOver() and Query(), there is no 'right way'. I find Query() is often more readable and neater but sometimes lacks the control I might need. I generally start with Query() and 'escalate' to QueryOver() if I get stuck or I am not happy the SQL Query() is generating.

edited Jun 22, 2017 at 11:27

answered Jun 22, 2017 at 11:14

David Osborne

6,8461 gold badge26 silver badges39 bronze badges

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

Comments

mjwills · Accepted Answer · 2017-06-22 21:07:34Z

1

query.SelectList(list => list
    .Select(t => t.ID).WithAlias(() => todoSummary.ID)
    .Select(t => t.Name).WithAlias(() => todoSummary.Name)
    .Select(t => t.Priority).WithAlias(() => todoSummary.Priority))
.TransformUsing(Transformers.AliasToBean<TodoDTO>());

The likely cause of your issue is that you aren't assigning the result of this line of code to a variable.

[{
    "id":1,
    "name":"TEST",
    "priority":
    {
        "__interceptor":
        {
            "persistentClass":"Todo, ApplicationName, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null",
            "identifier":2,
            "isUninitialized":true,
            "unwrap":false,
            "session":null,
            "entityName": "Priority",
            "isReadOnlySettingAvailable":false
        }
    }
}]

The above JSON that you are receiving is likely because you are converting the query variable to JSON rather than the result of the SelectList call.

answered Jun 22, 2017 at 21:07

mjwills

24.1k6 gold badges45 silver badges74 bronze badges

4 Comments

Bunnynut Over a year ago

Ok, I can understand that but how can I do this properly then?

Bunnynut Over a year ago

According the following article it's not possible to populate entities to a property: andrewwhitaker.com/blog/2014/06/19/…

Bunnynut Over a year ago

"You cannot populate full entities (e.g., .Select(() => productReview.Product).WithAlias(() => result.Product))" In the paragraph before the last one of the AliasToBean section

Bunnynut Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

NHibernate Query to json results are unexpected

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related