c# - 将公式映射到 IEnumerable bycode

Question

我正在尝试使用公式来映射 ICollection 类型的属性，但是，无论我使用哪种方法来确定映射中的类型，nHibernate 都会引发错误。

No parameterless constructor defined for this object.

这是映射文件

this.Property(
  x => x.AllChildIds,
  m =>
    {
      m.Type<NHibernate.Type.ListType>();
      m.Access(Accessor.Field);
      m.Formula(@"(WITH [Child] ([Id], [ParentId])
           AS (SELECT [hs0].[Id],
                      [hs0].[ParentId]
               FROM   [Client].[dbo].[HierarchySet] [hs0] (NOLOCK)
               WHERE  [hs0].[ParentId] IN (SELECT [hs1].[Id]
                                     FROM   [Client].[dbo].[HierarchySet] [hs1] (NOLOCK)
                                     WHERE  [hs1].[Id] = Id /* @p0 */)
               UNION ALL


               SELECT [Children].[Id],
                      [Children].[ParentId]
               FROM   [Client].[dbo].[HierarchySet](NOLOCK) AS [Children]
                      JOIN [Child]
                        ON [Children].[ParentId] = [Child].[Id])


      SELECT [Child].[Id]
      FROM   [Child]
      )");
});

这是我的课

private readonly ICollection<long> allChildIds;
public virtual IEnumerable<long> AllChildIds { get { return this.allChildIds; } }

如果我将映射文件中的类型更改为

m.Type<NHibernate.Type.GenericListType<NHibernate.Type.Int64Type>>();

然后我得到一个 Could not determine type for: System.Collections.Generic.IEnumerable错误

我知道 SQL 很复杂，但肯定不会影响它？

编辑我的 NHibernate session 配置

private static Configuration ConfigureNHibernate()
{
  var configration = new Configuration();

  configration.SessionFactoryName("SessionFactoryName");
  configration.DataBaseIntegration(db =>
                                     {
                                       db.Dialect<MsSql2005Dialect>();
                                       db.Driver<SqlClientDriver>();

                                       db.IsolationLevel = IsolationLevel.ReadUncommitted;
                                       db.ConnectionStringName = "database";
                                       db.BatchSize = 20;
                                       db.KeywordsAutoImport = Hbm2DDLKeyWords.AutoQuote;
                                     });

  if (ConfigurationManager.AppSettings["nhibernate-cache"] != null)
  {
    configration.Cache(
                       x =>
                         {
                           x.DefaultExpiration = 300;
                           x.UseMinimalPuts = true;
                           x.RegionsPrefix = "client-";
                           x.Provider<SysCacheProvider>();
                           x.UseQueryCache = true;
                         });
  }

  var mapper = new ModelMapper();
  mapper.AddMappings(typeof(MessageInMap).Assembly.GetTypes());
  var domainMapping = mapper.CompileMappingForAllExplicitlyAddedEntities();

  configration.AddMapping(domainMapping);

    configration.AddAuxiliaryDatabaseObject(new SimpleAuxiliaryDatabaseObject(@"
                            CREATE VIEW [Children]
                            AS 
                            WITH [Child] ([Id], [ParentId])
                            AS (
                                    SELECT 
                                            [hs0].[Id],
                                            [hs0].[ParentId]
                                    FROM   
                                            [isnapshot.Client].[dbo].[HierarchySet] (NOLOCK) AS [hs0]

                                    UNION ALL

                                    SELECT 
                                            [Children_].[Id],
                                            [Children_].[ParentId]
                                    FROM   
                                            [isnapshot.Client].[dbo].[HierarchySet] (NOLOCK) AS [Children_]
                                    JOIN [Child] ON [Children_].[ParentId] = [Child].[Id]
                            )
                            GO", "DROP VIEW [Children]"));

  return configration;
}

Answer 1

您需要将其映射为组件集(包、列表等)，而不是属性。然后使用子选择来获取它。

我的第一个假设是将其映射如下

c.Set(m => m.ChildrenIds,
    x =>
        {
            x.Access(Accessor.Field);
            x.Key(k => k.Column("ParentId"));
            x.Subselect(@"WITH [Child] ([Id], [ParentId])
AS 
(
    SELECT 
        [hs0].[Id],
        [hs0].[ParentId]
    FROM   
        [HierarchySet] (NOLOCK) AS [hs0]

    UNION ALL

    SELECT 
        [Children].[Id],
        [Children].[ParentId]
    FROM   
        [HierarchySet] (NOLOCK) AS [Children]
    JOIN 
        [Child] ON [Children].[ParentId] = [Child].[Id]
)

SELECT *
FROM [Child]");
        },
    x => x.Element(e => e.Column("Id")));

但是它不起作用，因为 WITH 子句不想在子选择中。生成的查询:

SELECT 
    childrenid0_.ParentId as ParentId0_, 
    childrenid0_.Id as Id0_ 
FROM ( WITH [Child] ([Id], [ParentId])
AS 
(
    SELECT 
        [hs0].[Id],
        [hs0].[ParentId]
    FROM   
        [HierarchySet] (NOLOCK) AS [hs0]

    UNION ALL

    SELECT 
        [Children].[Id],
        [Children].[ParentId]
    FROM   
        [HierarchySet] (NOLOCK) AS [Children]
    JOIN 
        [Child] ON [Children].[ParentId] = [Child].[Id]
)

SELECT *
FROM [Child] ) childrenid0_ 
WHERE childrenid0_.ParentId=@p0;@p0 = 2 [Type: Int32 (0)]

所以在这种情况下，唯一的解决方案是创建一个 View ，并映射到它。

将数据库对象添加到配置

configuration.AddAuxiliaryDatabaseObject(new SimpleAuxiliaryDatabaseObject(@"
    CREATE VIEW [Children]
    AS 
    WITH [Child] ([Id], [ParentId])
    AS (
        SELECT 
            [hs0].[Id],
            [hs0].[ParentId]
        FROM   
            [HierarchySet] (NOLOCK) AS [hs0]

        UNION ALL

        SELECT 
            [Children].[Id],
            [Children].[ParentId]
        FROM   
            [HierarchySet] (NOLOCK) AS [Children]
        JOIN [Child] ON [Children].[ParentId] = [Child].[Id]
    )

    SELECT *
    FROM [Child]", "DROP VIEW [Children]"));

如果您正在使用 SchemaExport/SchemaUpdate 功能来创建/更新您的数据库架构 - NHibernate 将创建 View 。 如果您不使用 SchemaExport/SchemaUpdate 功能，则需要手动创建 View 。

CREATE VIEW [Children]
AS 
    WITH [Child] ([Id], [ParentId])
    AS (
        SELECT 
            [hs0].[Id],
            [hs0].[ParentId]
        FROM   
            [HierarchySet] (NOLOCK) AS [hs0]

        UNION ALL

        SELECT 
            [Children].[Id],
            [Children].[ParentId]
        FROM   
            [HierarchySet] (NOLOCK) AS [Children]
        JOIN [Child] ON [Children].[ParentId] = [Child].[Id]
    )

    SELECT *
    FROM   [Child]

映射:

c.Set(m => m.ChildrenIds,
    x =>
        {
            x.Access(Accessor.Field);
            x.Key(k => k.Column("ParentId"));
            x.Table("Children"); // or x.Subselect("select * from Children") 
        },
    x => x.Element(e => e.Column("Id")));