Minimal and Correct Way to Map One-To-Many with Nhibernate

Minimal and correct way to map one-to-many with NHibernate

Just few hints, summarizing the most suitable standards I found out when working with NHibernate.

1) If there is a bi-directional reference in persitence (DB column), express it in C# code bi-directional as well.

Other words, if a child has reference to parent, parent should have reference to child.

public class Employee
{
    ...
    public virtual IList<Address> { get; set; }
}
public class Address
{
    ...
    public virtual Employee Employee { get; set; }
}

This represents Business Domain as is. Address belongs to Employee and Employee belongs to Address.

If we for some reasons really want to restrict that, we should rather protected modifier, but still keep the reference in C#

2) Use inverse="true". This could be used only if we mapped both sides (as above), and will lead to more "expected and optimized" INSERT and UPDATE scritps

Read more here:

inverse = “true” example and explanation by mkyong

3) Use batch fetching mapping almost everwhere. That will avoid 1 + N issues during querying. Read more:

few details about batch fetching

4) In case, that one object (in our case Employee) is root (the other does not make so much sense without it) - use cascading. Read more:

nhibernate - Create child by updating parent, or create explicitly?

Rules 2,3,4 in a mapping snippets:

<class name="Employee" ... batch-size="25">
  ...
  <bag name="Addresses"
       lazy="true" 
       inverse="true" 
       batch-size="25" 
       cascade="all-delete-orphan" >
    // wrong! This columns is the same as for many-to-one
    //<key column="AddressId" />
    // it is the one column expressing the relation
    <key column="EmployeeId" />
    <one-to-many class="Address" />
  </bag>

<class name="Address" ... batch-size="25">
  ...
  <many-to-one not-null="true" name="Employee" column="EmployeeID" />

3) if we use inverse="true do not forget to assign both sides of relation (mostly critical during creation)

The reason is:

we instruct NHibernate - the other side (Address) is responsible for persisting relation. But to do that properly, that Address needs to have reference to Employee - to be able to persist its ID into its column in Address table.

So this should be the standard code to create new Address

Employee employee = ... // load or create new
Address address = new Address 
{
    ...
    Employee = employee, // this is important
};
Employee.Addresses.Add(address);
session.SaveOrUpdate(employee);  // cascade will trigger the rest

We can also introduce some method like AddAddress() which will hide this complexity, but setting both sides is a good prectice.

Is there a way to map a one-to-one with a where clause?

It sounds to me like you have a candidate for inheritance. If you have an abstract base type of BinContents, have a derived class of VideoBinContents, which contains just a Video mapping, and and a ClipBinContents which contains just a Clip mapping. Your itemtype is your discriminator. Have a look here for more: http://www.hibernate.org/hib_docs/reference/en/html/inheritance.html

NHibernate with mapping by code and a SQLite database: saving many-to-one parent-child entities, child gets a null foreign key

Hehe, there is something wrong with your PaymentItemMapping , the correct mapping should be like this:

public class PaymentItemMapping : ClassMapping<PaymentItem> {

    public PaymentItemMapping() {
        Table("PaymentItems");
        Lazy(true);
        Id(x => x.ID, map => map.Generator(Generators.Identity));

        ManyToOne(x => x.OwningBillingItem, m => {
            //Do not map to m.Column("ID");
            m.Column("BillingItemID");
            // BillingItemID can be insert and update
            m.Update(true);
            m.Insert(true);
            m.Cascade(Cascade.None);
            m.Fetch(FetchKind.Join);
            m.NotFound(NotFoundMode.Exception);
            m.Lazy(LazyRelation.Proxy);
            m.ForeignKey("BillingItemID");
        });

        Property(x => x.DueDate, map => map.NotNullable(true));
        // ... other properties.
    }
}

Duplicate collection role mapping nHibernate - When trying to make one - many mapping

The first issue seems to be related to wrong setting of the .hbm.xml file.. which always must have (see e.g. MappingException: No persister for - NHibernate - Persisting an Adapter)

• xml mapping file is NOT makred as Embedded Resource
• xml file is not part of .dll which is configured as the mapping source <mapping assembly="MyProject.Data" /> (see configuration)
• xml file does not have the default suffix .hbm.xml

The second question (in comment)

Here I insert a product. I want to insert some ProductType to the
database like prod.ProductTypes = new .... How can I do that

Product prod = new Product(); 
prod.Name = "Q3"; prod.Category = "Audi"; 
prod.Discontinued = false; 
session.Save(prod);  
...

solution is to adjust the mapping of the collection to be using cascading:

<class name="Product">
    <id name="Id">
      <generator class="guid" />
    </id>
    <property name="Name" />
    <property name="Category" />
    <property name="Discontinued" />

    <bag name="ProductTypes" 
      lazy="true" inverse="true" batch-size="25" cascade="all-delete-orphan"
     >
      <key column="ProductID" />
      <one-to-many class="NHibernateSample.Models.ProductType,NHibernateSample" />
    </bag>

</class>

(what are all these settings on the <bag> - check here)

I would adjust this POCO defintion

public class Product
{
    public virtual Guid Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Category { get; set; }
    public virtual bool Discontinued { get; set; }

    //public virtual IList<ProductType> ProductTypes { get; set; }
    IList<ProductType> _productTypes;
    public virtual IList<ProductType> ProductTypes
    {
        get { return _productTypes ?? (_productTypes = new List<ProductType>()); }
        set { _productTypes = value; }
    }
}

(that is just to be sure that list is initiated either by NHibernate on load or by us in other cases)

and then we just have to assign both sides

 // product
 Product prod = new Product(); 
 prod.Name = "Q3"; prod.Category = "Audi"; 
 prod.Discontinued = false; 

 // product type
 ProductType productType = new ProudctType();
 ...

 // both sides assigned
 prod.ProductTypes.Add(productType);
 productType.Product = prod;

 session.Save(prod);  

And we also should adjust the mapping to be readonly for value type property

<property name="ProductID" insert="false" update="false" />
<many-to-one name="Product" class="Product">
  <column name="ProductID" sql-type="int" not-null="true"/>
</many-to-one>

To get more details I would not miss this:

• Minimal and correct way to map one-to-many with NHibernate

How to map one-to-one relation as part of composite key in Fluent NHibernate

I think your mapping for TaskClassMap needs to be the following:

public class TaskClassMap : ClassMap<Task>
{
    public TaskClassMap()
    {
        Table("Task");

        Id(task => task.Id, "taskid");
        HasMany(c => c.Applications)
            .KeyColumn("task_id");
    }
}

If you don't specify a specific column name (.KeyColumn) nhibernate tries to use conventions which would be TaskId in this case.

Also the reason you were getting the infamous error below is because you were trying to map the same column (task_id) twice in the same mapping (ApplicationMap):

Index was out of range. Must be non-negative and less than the size of the collection.

    CompositeId()
        .KeyProperty(app => app.UserId, "user_id")
        .KeyReference(app => app.Task, "task_id")
        .KeyProperty(app => app.TransactionId, "transaction_id");

    Map(app => app.TaskId, "task_id");

The TaskId property is going to be access a lot, and I'd like to avoid a JOIN query on the application and tasks table just to get a value already in the application table.

Also to comment on the above statement made I will say that nhibernate will not query the database if you are only accessing Application.Task.Id. When doing lazy loading nhibernate creates a proxy object for this type of relationship where the only field that is stored in memory is the primary key (Task.Id). So if you were to access this field it's actually not hitting the database. If you access any other field outside of the id it will trigger a query to the database to fetch the remaining values. It's like you say in the comment this value is already stored in the Application table and as such nhibernate won't query the Task table until you try to access a value that is only in that table.

NHibernate: Filter result on one-to-many join

Ok so I managed to find a fairly good solution this. The sql query I need to run is:

    SELECT RecipeId
  FROM Ingredient
GROUP BY RecipeId 
HAVING SUM(CASE WHEN Ingredient = 'Ingredient1'  AND Amount = 200 THEN 1 ELSE 0 END) = 1
   AND SUM(CASE WHEN Ingredient = 'Ingredient2'  AND Amount = 300 THEN 1 ELSE 0 END) = 1
   AND ...

I had a couple more problems converting it into something nhibernate would construct correctly and ended up with this:

ICriteria criteria = Session.CreateCriteria<Ingredients>()
                .SetProjection(Projections.GroupProperty(Projections.Property<Ingredient>(i => i.Recipe.Id)));

foreach (Ingredient ingredient in recipe.Ingredients)
{
    criteria.Add(
        Restrictions.Eq(
            Projections.Sum(
                Projections.Conditional(
                    Restrictions.Where<Ingredient>(i => i.Ingredient == ingredient.Ingredient && i.Amount == ingredient.Amount)
                    Projections.Constant(1),
                    Projections.Constant(0)
                 )
             ),
         1));
}

Which returns the above query. I tried doing it with Restrictions.Conjuntction, or using QueryOver, but the conditional query ended up in a WHERE condition before the GROUP BY rather than the HAVING condition after the GROUP BY resulting in an incorrect sql query. It might be a bug in Nhibernate (similar to NH-2863), but I'm not sure.
If anyone finds a more efficient way to solve this I'd me glad to update it.
Also the answer is based on these answers on SO:

https://stackoverflow.com/a/24334034/5426336

https://stackoverflow.com/a/3556840/5426336

NHibernate: mapping single column from many-to-one to a primitive type

Personally I would create a Role class like Yassir

But If you want to use the structure that you have at the moment then create a view that contains the foriegn Key to your Person Table and the Role Description.

Modify the Set mapping table to point at your new view
Then modify your Role mapping so that it is a property instead of the many to one mapping.

However taking this approach I think will mean that you will not be able to update your role as it is reerencing a view.

Edit: To update the role you could add <sql-insert>,<sql-update> and <sql-delete> to your mapping file so that the cascade-all will work

Fluent NHibernate Many to Many with extra column does not insert

The cause of your issue is that NHibernate is trying to insert the Inventory record before the Warehouse record. This is because the order of insertions is governed by the order in which session.Save is called. Based on this information I tried a number of code variations that will prevent the Foreign Key Constraint error. I have posted my nicest solution below.

using (var session = sessionFactory.OpenSession())
using (var transaction = session.BeginTransaction())
{
    var warehouse = new Warehouse() { Id = 1, Name = "warehouse" };
    session.Save(warehouse);

    var product = new Product() {Id = 1, Name = "product"};
    var inventory = new Inventory 
                     { StockInHand = true, Product = product, Warehouse = warehouse};

    product.Inventory.Add(inventory);
    warehouse.Inventory.Add(inventory);

    session.Save(product);

    transaction.Commit();
}

One thing I discovered, which surprised me quite a bit, is that if you put the session.Save(warehouse) after warehouse.Inventory.Add(inventory) then NHibernate doesn't insert the Warehouse record first and the Foreign Key error is thrown.

As a final note, to obtain the three insert statements as listed below the Inverse() has to be re-instated in the ProductMap mapping class. Otherwise an additional update statement will be emitted by NHibernate.

INSERT INTO Warehouse (Name, Id) VALUES (@p0, @p1);@p0 = 'warehouse' 
[Type: String (4000)], @p1 = 1 [Type: Int32 (0)]

INSERT INTO Product (Name, Id) VALUES (@p0, @p1);
@p0 = 'product' [Type: String (4000)], @p1 = 1 [Type: Int32 (0)]

INSERT INTO Inventory (StockInHand, Product_id, Warehouse_id) VALUES (@p0, @p1, @p2);
@p0 = True [Type: Boolean (0)], @p1 = 1 [Type: Int32 (0)], @p2 = 1 [Type: Int32 (0)]

---

Related Topics