是否可以在不获取所有链接的情况下获取链接表? [英] Is it possible to fetch a link table without fetching all links?

问题描述

好吧，所以我首先要说的是我正在为项目使用NHibernate，在这个项目中，我们还有一个同步功能(用于从中央MSSQL数据库同步到本地SQLite). .现在我知道NHibernate并不是用来同步数据库的，但是我还是想这样做.

Ok, so first of I would like to say that I'm using NHibernate for my project, and in this project we have (among other things) a sync function (to sync from a central MSSQL database to a local SQLite). Now I know that NHibernate was not made to sync databases, but I would like to do this anyways.

我有一个中型的大型数据库模型，所以不能在这里添加它，但是问题是我有两个数据表和一个链接表来链接这两个表.

I have a medium large database model so I can't add it here, but the problem is that I have two datatables, and one link table to link them both.

数据库模型:

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

数据库:

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

因此，在同步期间，我首先从Product表中复制所有数据，然后再从Producer表中复制所有数据(基本上是var products = session.Query<Products>().ToList()).这是由NHibernate在每个语句中完成的:

So during the sync, I first copy all data from the Product table, and then from the Producer table (basically var products = session.Query<Products>().ToList()). This is done by NHibernate in a single statement each:

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

现在我必须从第一届会议(products.ForEach(x => session.Evict(x));)逐出所有项目

Now I have to evict all items from the first session (products.ForEach(x => session.Evict(x));)

然后保存(products.ForEach(x => syncSession.save(x));)是每行插入一次(如预期的那样).

And then the save (products.ForEach(x => syncSession.save(x));) is one insert per row (as expected).

因此，当将数据保存在链接表中时，我希望也只有一个 select .但是事实并非如此.因为首先它会像上面一样制作select ....但是现在，在每行插入之前，它会为产品和生产者进行更多的选择.

So when saving the data in the link table I would have wished that there also would be just a single select. However that is not the case. Because first it makes a select ... as above. But now before every row to insert it does even more select for the Product and for the Producer.

所以它看起来像:

产品:

• 选择
• 插入(id 1)
• 插入(id 2)

制作人:

• 选择
• 插入(id 101)
• 插入(id 102)

ProdLinkProducer:

ProdLinkProducer:

• 选择
• 从产品中选择ID 1
• 从产品中选择ID 1
• 从生产者中选择id 101
• 从产品中选择ID 2
• 从产品中选择ID 2
• 从生产者中选择ID 102
• 从生产者中选择ID 102
• 插入
• 插入

• select
• select id 1 from Products
• select id 1 from Products
• select id 101 from Producer
• select id 2 from Products
• select id 2 from Products
• select id 102 from Producer
• select id 102 from Producer
• insert
• insert

那么有什么方法可以避免这种行为吗?

So is there anyway avoiding this behavior?

编辑

为了更好地解释我所做的事情，我创建了一个小测试项目.可以在这里找到: https://github.com/tb2johm/NHibernateSync (我宁愿只添加一个ghist，但我认为它可能遗漏了很多数据，对不起...)

To better explain what I have done, I have created a small test project. It can be found here: https://github.com/tb2johm/NHibernateSync (I would have preferred to add only a ghist, but I think that it might have left out to much data, sorry...)

EDIT2

我已经找到一种使它起作用的方法，但是我不喜欢它. 此解决方案的工作方式是在数据库模型中创建一个ProductLinkProducerSync表，该表不包含任何链接，而仅包含值，并且避免同步普通链接表，而仅同步"sync"表.但是正如我说的那样，我不喜欢这个主意，因为如果我更改数据库中的任何内容，那么我需要在两个地方更新相同的数据.

I have found out one way to make it work, but I don't like it. The way this solution works is to in the database model create a ProductLinkProducerSync table, that doesn't contain any links, but just the values, and avoid synchronizing the ordinary link tables, but just the "sync" tables. But as I said I don't like this idea, since if I change anything in the database, I have kind of the same data in two places that I need to update.

推荐答案

我无法以开箱即用的方式找到NHibernate.

I was unable to find NHibernate out of the box way of doing what you are asking.

但是，通过手动将FK引用(代理类)重新绑定到新会话上，我能够获得所需的行为(我想有些事情总比没有好:)

However I was able to get the desired behavior (I guess something is better than nothing:) by manually rebinding the FK references (proxy classes) to the new session:

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元数据服务的通用版本:

Here is the generalized version using NHibernate metadata services:

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方法:

and applying it to your Sync method:

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");
}

这篇关于是否可以在不获取所有链接的情况下获取链接表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！