ManyToMany cascade not working in migration scenario
Hei everybody,
I have two databases (one is a SqlServer instance, the other is in a SqlCe file) and I'm implementing a very simple syncronization system between the two. What I want to accomplish is to have all the data in the SqlCe file match the ones in the SqlServer instance. The SqlCe file's data are readonly (no update/insert) and the SqlServer/SqlCe schemas are identical except for the fact that the SqlServer surrogate keys are defined as INT IDENTITY NOT NULL while the SqlCe's are INT NOT NULL (this is mapped in fluent as Id(x => x.Id) for the SqlServer instance and Id(x => x.Id).GeneratedBy.Assigned() for the SqlCe). These are two of the models:
public class Customer
{
public virtual Int32 Id { get; set; }
public virtual bool IsDeleted { get; set; }
public virtual String Name { get; set; }
public virtual IList<Truck> Trucks { get; set; }
public Customer()
{
Trucks = new List<Truck>();
}
}
public class Truck
{
public virtual Int32 Id { get; set; }
public virtual bool IsDeleted { get; set; }
public virtual Int32 Capacity { get; set; }
}
and these are the mappings for the two models:
public class CustomerMap : ClassMap<Customer>
{
public CustomerMap()
{
Id(x => x.Id);
Map(x => x.IsDeleted);
Map(x => x.Name);
HasManyToMany(x => x.Trucks).Cascade.AllDeleteOrphan();
}
}
public class TruckMap : ClassMap<Truck>
{
public TruckMap()
{
Id(x => x.Id);
Map(x => x.IsDeleted);
Map(x => x.Capacity);
}
}
When I generate the schemas for the two istances (SqlServer and SqlCe) everything's fine and i get 3 tables (Customer, Truck, TruckToCustomer) as expected, with TruckToCustomer being the join table and containing the two Int columns Customer_id and Truck_id. The only difference between the 2 database schemas is the fact that for the SqlServer instance the surrogate keys are identities while for the SqlCe instance there is no increment defined - as i want it to be.
What I'm trying to do is to load all the data in the customer table on the SqlServer instance and write it on the SqlCe customer table, along with all the trucks associated and the data in the join table. I'd like to do it in one step so that's the code i wrote:
using(var sqlServerSession = sqlServerFactory.OpenSession())
{
using(var sqlServerTransaction = sqlServerSession.BeginTransaction())
{
using(var sqlCeSession = sqlCeFactory.OpenSession())
{
using(var sqlCeTransaction = sqlCeSession.BeginTransaction())
{
IList<Customer> customers = sqlServerSession.CreateCriteria<Customer>().List<Customer>();
foreach (var customer in customers)
{
sqlServerSession.Evict(customer);
sqlCeSession.SaveOrUpdate(customer);
}
sqlCeTransaction.Commit();
}
}
}
}
I was expecting this to populate my table with data for customers, trucks and the join table but instead only the customers table gets populated. If i go and peek at the trucks collection with the debugger BEFORE the session.Evict() call then the trucks collection gets correctly unproxied and persisted in the SqlCe schema but still the join table remains empty.
Also: if i just create a new Customer, add some new Trucks to it and save it (without loading it from the SqlServer instance), as in :
using(var sqlCeSession = sqlCeFactory.OpenSession())
{
using(var sqlCeTransaction = sqlCeSession.BeginTransaction())
{
Customer newCustomer = new Customer();
newCustomer.Trucks.Add(new Truck { Id = 1 });
newCustomer.Trucks.Add(new Truck { Id = 2 });
sqlCeSession.SaveOrUpdate(newCustomer);
sqlCeTransaction.Commit();
}
}
everything works fine, so i get a customer with id = 0 in the customer table, 2 trucks with id 1 and 2 in the trucks table and 2 records in the join table with ids (0,1) and (0,2).
I'd like to know what I'm doing wrong, I'm out of ideas :-(
Thanks in advance
Andrea
Support Staff 2 Posted by James Gregory on 16 May, 2010 04:49 PM
This is more of an NHibernate question than Fluent NHibernate. My guess would be that NHibernate isn't liking the fact you're loading an entity from one session (and session factory), and trying to save it into another. I would investigate creating a new instance and copy the values across (using something like AutoMapper) before you save into CE.
3 Posted by anonymous on 02 Mar, 2011 10:57 AM
Hmm. I'm pitching in a bit late here, but I'm trying to find an answer to a similar problem, so it may help.
I'm not sure the session thing (vs using Automapper as suggested) is the issue. In my case I am streaming the session objects out to a file, then deserializing and want to load them back to the same DB schema (as in save out of one instance and import into another). I've noticed that when I have the Id generator as 'assigned' the linking table isn't populated. If I have the Id as, say, guid then the linking table is populated.