如何编写Nhibernate查询

Question

我有两个类被映射到数据库中。这些表彼此之间有着主键和外键关系，并使用字段“DeptId”进行关联。

Employee.cs

 public class Employee: Entity
    {
        public virtual Int32 Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Gender { get; set; }
        public virtual Int32 Age { get; set; }
        public virtual string Designation { get; set; }
        public virtual bool Enabled { get; set; }
        public virtual int CreatedById { get; set; }
        public virtual DateTime CreatedDate { get; set; }
        public virtual int? LastModifiedById { get; set; }
        public virtual DateTime? LastModifiedDate { get; set; }
        public virtual bool IsDeleted { get; set; }
        public virtual Department Department { get; set; }
    }

Department.cs

public class Department
    {
        public virtual int DeptId { get; set; }
        public virtual string DeptName { get; set; }
        public virtual bool Enabled { get; set; }
    }

我是NHibernate的新手，无法使用QueryOver编写更复杂的Linq查询。我已经编写了以下查询，但如何编写更高级的查询呢？请为我提供示例查询和参考资料。

var query = Session.QueryOver<Employee>().List(); 

- Bhupendra Shukla

---

大多数情况下，您可以使用LINQ..您尝试了什么？您想要做什么？ - Simon Whitehead

我想要实现NHibernate的连接和使用内置函数，如平均值、求和等。 - Bhupendra Shukla

很棒，如果这有所帮助的话 ;) NHibernate是一个很棒的工具，请不要放弃! ;) - Radim Köhler

Answer 1

NHibernate 查询文档非常完整且易于理解。您可以在此处找到基本知识：

• 第 15 章. Criteria Queries

QueryOver API 是Criteria的全类型版本，文档在此：

• 第 16章. QueryOver Queries

开始观察API文档，很快您将发现它非常符合逻辑（使用 .Where() 构建 WHERE 子句，使用 .Select() 调整 SELECT 语句等）。稍后，如果遇到任何问题，可以在 Stack Overflow 上找到 HOW TO。

下面是一个来自16.1章节并针对 Employee 进行调整的示例：

var list = session
        .QueryOver<Employee>()
        .WhereRestrictionOn(c => c.Age).IsBetween(18).And(60)
        .Select(c => c.Name)
        .OrderBy(c => c.Name).Asc
        .List<string>();

一个连接到 Department 的 JOIN（来自16.4的调整示例）

var query = session
        .QueryOver<Employee>()
        .JoinQueryOver(e => e.Department)
            .Where(k => k.DeptName == "Director");

- Radim Köhler

---

这些章节的链接现在已经失效了。 - krillgar

章节的链接已经恢复正常。 - Hermann.Gruber

Answer 2

简单的LINQ查询被视为NHibernate查询，但您需要将它们连接到存储库。

使用Repository和IRepository连接实体以减少其复杂性。

这样会更加结构化。

使用此链接了解实体和存储库之间的连接。 http://www.asp.net/mvc/tutorials/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application 请告诉我是否已经帮助您走上了正确的轨道。

- vipul_surana

Answer 3

这里是完整的样例

using DB.Extensions;
using DB.Modellers;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.Helpers;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Cfg.MappingSchema;
using NHibernate.Mapping.ByCode;
using NHibernate.Tool.hbm2ddl;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;

namespace DB
{
    public class DatabaseAdapter
    {
        public List<IConvention> DatabaseConventions { get; set; }
        ISessionFactory sessionFactory;
        public string SqlFilePath { get; }
        public DatabaseAdapter(string sqlFilePath)
        {
            this.SqlFilePath = sqlFilePath;
            if (sessionFactory == null)
                sessionFactory = InitializeSessionFactory();
        }
       
        public ISession GetSession()
        {
            // For now, this will create a new session - However, eventually we could re-use sessions within threads, HTTP request context, etc
            return sessionFactory.OpenSession();
        }

        public IStatelessSession GetStatelessSession()
        {
            return sessionFactory.OpenStatelessSession();
        }
        ISessionFactory InitializeSessionFactory()
        {
             //var conventions = new IConvention[]
             //{
             //    Table.Is(x => x.EntityType.Name.ToLowerInvariant()), // All table names are lower case
             //    ForeignKey.EndsWith("Id"), // Foreign key references end with Id
             //    DefaultLazy.Always() // Enable Lazy-Loading by default
             //}.Concat(DatabaseConventions.NeverNull()).ToArray();

             var config = Fluently.Configure()
               .Database(GetMonoSQLConfiguration())
               .Mappings(m =>
               {
                   m.FluentMappings.Conventions.Setup(c => c.Add(AutoImport.Never()));
                   m.FluentMappings.Conventions.AddAssembly(Assembly.GetExecutingAssembly());
                   m.HbmMappings.AddFromAssembly(Assembly.GetExecutingAssembly());

                   var assembly = Assembly.Load("DB");
                   m.FluentMappings.Conventions.AddAssembly(assembly);
                   m.FluentMappings.AddFromAssembly(assembly);
                   m.HbmMappings.AddFromAssembly(assembly);
               });
            var nhConfig = config.BuildConfiguration();
            var session = config.BuildSessionFactory();
            return session;
        }
        
        private IPersistenceConfigurer GetMonoSQLConfiguration()
        {
            var sql = SQLiteConfiguration.Standard.UsingFile(this.SqlFilePath)
             .ShowSql();
            return sql;
        }
        public void Dispose()
        {
            if (sessionFactory != null)
                sessionFactory.Dispose();
        }

        public void SaveUpdate()
        {
            using (var session = GetSession())
            {
                using (var transaction = session.BeginTransaction())
                {
                    var existingItem = session.QueryOver<books>()
                       .Where(p => p.number == 1)
                       .Where(p => p.human == "U")
                       .SingleOrDefault();

                    if (existingItem != null) // Update existing
                    {
                        existingItem.number = 1;
                        session.Update(existingItem);
                    }
                    else // Create 
                    {
                        session.Save(new books());
                    }
                    transaction.Commit();
                }
            }
        }
        public IEnumerable<books> GetBooks()
        {
            using (var session = GetStatelessSession())
            {
                var books = session.QueryOver<books>().List();
                return books;
            }
        }
        public IEnumerable<chapters> GetChapters(books books)
        {
            using (var session = GetStatelessSession())
            {
                var items = session.QueryOver<chapters>().Where(p=>p.reference_human == books.human).List();
                return items;
            }
        }
        public IEnumerable<verses> GetVerses()
        {
            using (var session = GetStatelessSession())
            {
                var items = session.QueryOver<verses>().List();
                return items;
            }
        }
    }
}

- user16012721