Table with a foreign key

Question

how can I build a table of "orders" containing "IdOrder", "Description" and "User"?... the "User" field is a reference to the table "Users", which has "IdUser" and "Name". I'm using repositories.

I have this repository:

Repository<Orders> ordersRepo = new OrderRepo<Orders>(unitOfWork.Session);

to return all Orders to View, I just do:

return View(ordersRepo.All());

But this will result in something like:

IdOrder:1 -- Description: SomeTest -- User: UserProxy123ih12i3123ih12i3uh123

-

When the expected result was:

IdOrder:1 -- Description: SomeTest -- User: Thiago.

PS: I don't know why it returns this "UserProxy123ih12i3123ih12i3uh123". In Db there is a valid value.

The View:

It is showed in a foreach (var item in Model).

@item.Description
@item.User //--> If it is @item.User.Name doesn't work.

What I have to do to put the Name on this list? May I have to do a query using LINQ - NHibernate?

Tks.

What result do you when you have "@item.User.Name"? – Serhiy Mar 23 '11 at 17:07 — Serhiy, Mar 23 '11 at 17:07

akmad · Answer 1 · 2011-03-22T15:06:44.413

What type of ORM are you using? You mention "repositories" but does that mean LinqToSql, Entity Framework, NHibernate, or other?

It looks like you are getting an error because the User field is not loaded as part of the original query. This is likely done to reduce the size of the result set by excluding the related fields from the original query for Orders.

There are a couple of options to work around this:

Set up the repository (or context, depending on the ORM) to include the User property in the result set.
Explicitly load the User property before you access it. Note that this would be an additional round-trip to the database and should not be done in a loop.

In cases where you know that you need the User information it would make sense to ensure that this data in returned from the original query. If you are using LinqToSql take a look at the DataLoadOptions type. You can use this type to specify which relationships you want to retrieve with the query:

var options = new DataLoadOptions();
options.LoadWith<Orders>(o => o.User);

DataContext context = ...;
context.LoadOptions = options;

var query = from o in context.Orders
            select o;

There should be similar methods to achive the same thing whatever ORM you are using.

In NHibernate you can do the following:

using (ISession session = SessionFactory.OpenSession())
{
    var orders = session.Get<Order>(someId);
    NHibernateUtil.Initialize(orders.User);
}

This will result in only two database trips (regardless of the number of orders returned). More information on this can be found here.

score 0 · Answer 2 · answered Mar 21 '11 at 20:10

In asp.net MVC the foreign key doesn't work the way you are using it. I believe you have to set the user to a variable like this:

User user = @item.User;

Or you have to load the reference sometimes. I don't know why this is but in my experience if I put this line before doing something with a foreign key it works

@item.UserReference.load();

score 0 · Answer 3 · answered Mar 23 '11 at 17:12

Maybe when you access item.User.Name the session is already closed so NHib cannot load appropriate user from the DB. You can create some model and initialize it with proper values at the controller. Also you can disable lazy loading for Orders.User in your mapping.

But maybe it is an other problem. What do you have when accessing "@item.User.Name" from your View?

Table with a foreign key

3 Answers3