Fluent NHibernate - DeleteById somehow deletes all records in DB table

Question

I'm new to Fluent NHibernate. I followed the example at https://github.com/jagregory/fluent-nhibernate/wiki/Getting-started

The example showed how to Create and Read entities. At run-time, it creates 2 stores.

I added a DeleteByStoreId() method to this sample code.

    public static void DeleteByStoreId(int StoreId)
    {
        var sessionFactory = CreateSessionFactory();

        using (var session = sessionFactory.OpenSession())
        {
            using (var transaction = session.BeginTransaction())
            {
                var store = session.Get<Store>(StoreId);

                if (store != null)
                {
                    session.Delete(store);
                }

                transaction.Commit();
            }
        }
    }

When I execute DeleteByStoreId by supplying one of the two existing StoreId, it somehow deletes BOTH stores. I would have expected it to delete ONLY the one store with the given StoreId. Can you tell me what I'm doing wrong?

Here is how the Entities are defined:

using System.Collections.Generic;

namespace FluentNHibernateDemo.Entities
{
    public class Employee
    {
        public virtual int Id { get; protected set; }

        public virtual string FirstName { get; set; }
        public virtual string LastName { get; set; }

        public virtual Store Store { get; set; }
    }

    public class Product
    {
        public virtual int Id { get; protected set; }
        public virtual string Name { get; set; }
        public virtual double Price { get; set; }

        public virtual Location Location { get; set; }
        public virtual IList<Store> StoresStockedIn { get; set; }

        public Product()
        {
            StoresStockedIn = new List<Store>();
        }
    }

    public class Location
    {
        public virtual int Aisle { get; set; }
        public virtual int Shelf { get; set; }
    }

    public class Store
    {
        public virtual int Id { get; protected set; }
        public virtual string Name { get; set; }
        public virtual IList<Product> Products { get; set; }
        public virtual IList<Employee> Staff { get; set; }

        public Store()
        {
            Products = new List<Product>();
            Staff = new List<Employee>();
        }

        public virtual void AddProduct(Product product)
        {
            product.StoresStockedIn.Add(this);
            Products.Add(product);
        }

        public virtual void AddEmployee(Employee employee)
        {
            employee.Store = this;
            Staff.Add(employee);
        }
    }
}

Here is how the Mappings are defined:

using FluentNHibernate.Mapping;
using FluentNHibernateDemo.Entities;

namespace FluentNHibernateDemo.Mappings
{
    public class EmployeeMap : ClassMap<Employee>
    {
        public EmployeeMap()
        {
            Id(x => x.Id);

            Map(x => x.FirstName);
            Map(x => x.LastName);

            References(x => x.Store);
        }
    }

    public class LocationMap : ComponentMap<Location>
    {
        public LocationMap()
        {
            Map(x => x.Aisle);
            Map(x => x.Shelf);
        }
    }

    public class ProductMap : ClassMap<Product>
    {
        public ProductMap()
        {
            Id(x => x.Id);

            Map(x => x.Name);
            Map(x => x.Price);

            HasManyToMany(x => x.StoresStockedIn)
                .Cascade.All()
                .Inverse()
                .Table("StoreProduct");

            Component(x => x.Location);
        }
    }

    public class StoreMap : ClassMap<Store>
    {
        public StoreMap()
        {
            Id(x => x.Id);

            Map(x => x.Name);

            HasManyToMany(x => x.Products)
                .Cascade.All()
                .Table("StoreProduct");

            HasMany(x => x.Staff)
                .Cascade.All()
                .Inverse();
        }
    }
}

Here is how the database tables are defined:

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](255) NULL,
    [FirstName] [varchar](255) NULL,
    [Store_id] [int] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Product](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Price] [money] NULL,
    [Name] [varchar](255) NULL,
    [Aisle] [int] NULL,
    [Shelf] [int] NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Store](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](255) NULL,
 CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[StoreProduct](
    [Product_id] [int] NULL,
    [Store_id] [int] NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Store] FOREIGN KEY([Store_id])
REFERENCES [dbo].[Store] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Store]
GO
ALTER TABLE [dbo].[StoreProduct]  WITH CHECK ADD  CONSTRAINT [FK_StoreProduct_Product] FOREIGN KEY([Product_id])
REFERENCES [dbo].[Product] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[StoreProduct] CHECK CONSTRAINT [FK_StoreProduct_Product]
GO
ALTER TABLE [dbo].[StoreProduct]  WITH CHECK ADD  CONSTRAINT [FK_StoreProduct_Store] FOREIGN KEY([Store_id])
REFERENCES [dbo].[Store] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[StoreProduct] CHECK CONSTRAINT [FK_StoreProduct_Store]
GO

More information. I used SQL Profiler to monitor the SQL calls. Assume that when I executed this sample program, Store_id=105 and 106 are created.

I've captured the sequence of SQL calls that took place after executing DeleteByStoreId(105):

********************************************************************************
SELECT store0_.Id as Id10_0_, store0_.Name as Name10_0_ FROM [Store] store0_ WHERE store0_.Id=@p0;
@p0 = 105 [Type: Int32 (0)]
********************************************************************************

SELECT products0_.Store_id as Store2_1_, products0_.Product_id as Product1_1_, product1_.Id as Id8_0_, product1_.Name as Name8_0_, product1_.Price as Price8_0_, product1_.Aisle as Aisle8_0_, product1_.Shelf as Shelf8_0_ FROM StoreProduct products0_ left outer join [Product] product1_ on products0_.Product_id=product1_.Id WHERE products0_.Store_id=@p0;
@p0 = 105 [Type: Int32 (0)]
********************************************************************************

SELECT storesstoc0_.Product_id as Product1_1_, storesstoc0_.Store_id as Store2_1_, store1_.Id as Id10_0_, store1_.Name as Name10_0_ FROM StoreProduct storesstoc0_ left outer join [Store] store1_ on storesstoc0_.Store_id=store1_.Id WHERE storesstoc0_.Product_id=@p0;
@p0 = 307 [Type: Int32 (0)]
********************************************************************************

SELECT storesstoc0_.Product_id as Product1_1_, storesstoc0_.Store_id as Store2_1_, store1_.Id as Id10_0_, store1_.Name as Name10_0_ FROM StoreProduct storesstoc0_ left outer join [Store] store1_ on storesstoc0_.Store_id=store1_.Id WHERE storesstoc0_.Product_id=@p0;
@p0 = 308 [Type: Int32 (0)]
********************************************************************************

SELECT storesstoc0_.Product_id as Product1_1_, storesstoc0_.Store_id as Store2_1_, store1_.Id as Id10_0_, store1_.Name as Name10_0_ FROM StoreProduct storesstoc0_ left outer join [Store] store1_ on storesstoc0_.Store_id=store1_.Id WHERE storesstoc0_.Product_id=@p0;
@p0 = 309 [Type: Int32 (0)]
********************************************************************************

SELECT storesstoc0_.Product_id as Product1_1_, storesstoc0_.Store_id as Store2_1_, store1_.Id as Id10_0_, store1_.Name as Name10_0_ FROM StoreProduct storesstoc0_ left outer join [Store] store1_ on storesstoc0_.Store_id=store1_.Id WHERE storesstoc0_.Product_id=@p0;
@p0 = 310 [Type: Int32 (0)]
********************************************************************************

SELECT products0_.Store_id as Store2_1_, products0_.Product_id as Product1_1_, product1_.Id as Id8_0_, product1_.Name as Name8_0_, product1_.Price as Price8_0_, product1_.Aisle as Aisle8_0_, product1_.Shelf as Shelf8_0_ FROM StoreProduct products0_ left outer join [Product] product1_ on products0_.Product_id=product1_.Id WHERE products0_.Store_id=@p0;
@p0 = 106 [Type: Int32 (0)]
********************************************************************************

SELECT storesstoc0_.Product_id as Product1_1_, storesstoc0_.Store_id as Store2_1_, store1_.Id as Id10_0_, store1_.Name as Name10_0_ FROM StoreProduct storesstoc0_ left outer join [Store] store1_ on storesstoc0_.Store_id=store1_.Id WHERE storesstoc0_.Product_id=@p0;
@p0 = 311 [Type: Int32 (0)]
********************************************************************************

SELECT storesstoc0_.Product_id as Product1_1_, storesstoc0_.Store_id as Store2_1_, store1_.Id as Id10_0_, store1_.Name as Name10_0_ FROM StoreProduct storesstoc0_ left outer join [Store] store1_ on storesstoc0_.Store_id=store1_.Id WHERE storesstoc0_.Product_id=@p0;
@p0 = 312 [Type: Int32 (0)]
********************************************************************************

SELECT staff0_.Store_id as Store4_1_, staff0_.Id as Id1_, staff0_.Id as Id7_0_, staff0_.FirstName as FirstName7_0_, staff0_.LastName as LastName7_0_, staff0_.Store_id as Store4_7_0_ FROM [Employee] staff0_ WHERE staff0_.Store_id=@p0;
@p0 = 106 [Type: Int32 (0)]
********************************************************************************

SELECT staff0_.Store_id as Store4_1_, staff0_.Id as Id1_, staff0_.Id as Id7_0_, staff0_.FirstName as FirstName7_0_, staff0_.LastName as LastName7_0_, staff0_.Store_id as Store4_7_0_ FROM [Employee] staff0_ WHERE staff0_.Store_id=@p0;
@p0 = 105 [Type: Int32 (0)]
********************************************************************************

DELETE FROM StoreProduct WHERE Store_id = @p0;
@p0 = 105 [Type: Int32 (0)]
********************************************************************************

DELETE FROM StoreProduct WHERE Store_id = @p0;
@p0 = 106 [Type: Int32 (0)]
********************************************************************************

Batch commands:
command 0:DELETE FROM [Product] WHERE Id = @p0;
@p0 = 307 [Type: Int32 (0)]
command 1:DELETE FROM [Product] WHERE Id = @p0;
@p0 = 308 [Type: Int32 (0)]
command 2:DELETE FROM [Product] WHERE Id = @p0;
@p0 = 309 [Type: Int32 (0)]
command 3:DELETE FROM [Product] WHERE Id = @p0;
@p0 = 311 [Type: Int32 (0)]
command 4:DELETE FROM [Product] WHERE Id = @p0;
@p0 = 312 [Type: Int32 (0)]

********************************************************************************

Batch commands:
command 0:DELETE FROM [Employee] WHERE Id = @p0;
@p0 = 256 [Type: Int32 (0)]
command 1:DELETE FROM [Employee] WHERE Id = @p0;
@p0 = 257 [Type: Int32 (0)]

********************************************************************************

Batch commands:
command 0:DELETE FROM [Store] WHERE Id = @p0;
@p0 = 106 [Type: Int32 (0)]

********************************************************************************

Batch commands:
command 0:DELETE FROM [Product] WHERE Id = @p0;
@p0 = 310 [Type: Int32 (0)]

********************************************************************************

Batch commands:
command 0:DELETE FROM [Employee] WHERE Id = @p0;
@p0 = 258 [Type: Int32 (0)]
command 1:DELETE FROM [Employee] WHERE Id = @p0;
@p0 = 259 [Type: Int32 (0)]
command 2:DELETE FROM [Employee] WHERE Id = @p0;
@p0 = 260 [Type: Int32 (0)]

********************************************************************************

Batch commands:
command 0:DELETE FROM [Store] WHERE Id = @p0;
@p0 = 105 [Type: Int32 (0)]

********************************************************************************

When I put a breakpoint in DeleteByStoreId(), I can see that

var store = session.Get<Store>(StoreId);

only returns the element of Store_Id=105. So, why would it go and delete Store_id=106 as well??

I have to prove to my boss that Fluent NHibernate is capable of doing the CRUD operations so that I get the go ahead to use it in my new project. Hope you can answer me as soon as possible.

You may want to post your mapping for Store. Is a Store related to other Stores? Logically you wouldn't think so but you never know. Also have you debugged through it and looked at the Console output assuming you have nhibernate dumping the sql to the console? If so do you see it execute all the deletes on teh `Commit()` above? Just making sure this isn't happening somewhere outside of this code. — Cole W, Mar 07 '14 at 21:28
@ColeW Thanks for responding. I have posted the full source code for both the Entities and Mappings. I also have the SQL dump. I will add that to my original post as well. — Dev Enthusiast, Mar 07 '14 at 22:51

score 0 · Answer 1 · edited May 23 '17 at 12:13

The problem looks to be in the mapping. Take a look at this other answer which talks to a similar issue and how the inverse/cascading relationships can work.

If you apply that to your situation then the only down side is that you end up with products that are not assigned to any store. This could be okay if you have a general product catalog that can handle maintenance from time-to-time where products are not stocked in any store can safely be removed from the catalog.

public class ProductMap : ClassMap<Product>
{
    public ProductMap()
    {
        HasManyToMany(x => x.StoresStockedIn)
            .Table("StoreProduct")
            .ParentKeyColumn("Product_Id")
            .ChildKeyColumn("Store_Id")
            .Inverse()
            .Cascade.SaveUpdate();
    }
}

public class StoreMap : ClassMap<Store>
{
    public StoreMap()
    {
        HasManyToMany(x => x.Products)
            .Table("StoreProduct")
            .ParentKeyColumn("Store_Id")
            .ChildKeyColumn("Product_Id")
            .Cascade.SaveUpdate();
    }
}

Fluent NHibernate - DeleteById somehow deletes all records in DB table

1 Answers1