Is it possible to get a link table without extracting all the links?

Question

So, first I would like to say that I use NHibernate for my project, and in this project we have (among other things) a synchronization function (for synchronization with the central MSSQL database with local SQLite), Now I know that NHibernate was not Created for database synchronization, but I would like to do it anyway.

I have an average database model, so I can’t add it here, but the problem is that I have two types of data and one reference table to link them.

Database Model:

| Product            | | ProductLinkProducer | | Producer            |
|--------------------| |---------------------| |---------------------|
| Id                 | | LinkId              | | Id                  |
| Name               | | Product             | | Name                |
| ProductLinkProducer| | Producer            | | ProductLinkProducer |

Database:

| Product | | ProductLinkProducer | | Producer |
|---------| |---------------------| |----------|
| Id      | | LinkId              | | Id       |
| Name    | | ProductId           | | Name     |
|         | | ProducerId          | |          |

Therefore, during synchronization, I first copy all the data from the Product table, and then from the Producer table (mostly var products = session.Query<Products>().ToList()). This is done by NHibernate in one statement each:

select
    product0_.id as id2_,
    product0_.name as name2_
from
    Product product0_

(products.ForEach(x => session.Evict(x));)

(products.ForEach(x => syncSession.save(x));) - ( ).

, , , . . select ..., . , , .

, :

:

• insert (id 1)
• insert (id 2)

:

• insert (id 101)
• insert (id 102)

ProdLinkProducer:

• id 1
• id 1
• 101
• id 2
• id 2
• id 102
• id 102

?

, , . : https://github.com/tb2johm/NHibernateSync ( ghist, , , ...)

EDIT2

, . , , , ProductLinkProducerSync, , , "sync". , , , , - , , .

Answer 1

NHibernate , , .

( , - , :), FK (-) :

var links = session.Query<ProductLinkProducer>().ToList(); 
links.ForEach(x => session.Evict(x));
foreach (var link in links)
{
    link.Product = syncSession.Get<Product>(link.Product.Id);
    link.Producer = syncSession.Get<Producer>(link.Producer.Id);
    syncSession.Save(link);
}
syncSession.Flush();

, NHibernate:

static IEnumerable<Action<ISession, T>> GetRefBindActions<T>(ISessionFactory sessionFactory)
{
    var classMeta = sessionFactory.GetClassMetadata(typeof(T));
    var propertyNames = classMeta.PropertyNames;
    var propertyTypes = classMeta.PropertyTypes;
    for (int i = 0; i < propertyTypes.Length; i++)
    {
        var propertyType = propertyTypes[i];
        if (propertyType.IsAssociationType && !propertyType.IsCollectionType)
        {
            var propertyName = propertyNames[i];
            var propertyClass = propertyType.ReturnedClass;
            var propertyClassMeta = sessionFactory.GetClassMetadata(propertyClass);
            yield return (session, target) =>
            {
                var oldValue = classMeta.GetPropertyValue(target, propertyName, EntityMode.Poco);
                var id = propertyClassMeta.GetIdentifier(oldValue, EntityMode.Poco);
                var newValue = session.Get(propertyClass, id);
                classMeta.SetPropertyValue(target, propertyName, newValue, EntityMode.Poco);
            };
        }
    }
}

Sync:

private static void Sync<T>(string tableName, ISession session, ISession syncSession)
{
    Console.WriteLine("Fetching data for ####{0}####...", tableName);
    var sqlLinks = session.Query<T>();
    var links = sqlLinks.ToList();
    Console.WriteLine("...Done");

    Console.WriteLine("Evicting data...");
    links.ForEach(x => session.Evict(x));
    Console.WriteLine("...Done");

    Console.WriteLine("Saving data...");
    var bindRefs = GetRefBindActions<T>(syncSession.SessionFactory).ToList();
    foreach (var link in links)
    {
        foreach (var action in bindRefs) action(syncSession, link);
        syncSession.Save(link);
    }
    Console.WriteLine("...Flushing data...");
    syncSession.Flush();
    Console.WriteLine("...Done");
    Console.WriteLine("\n\n\n");
}