NHibernate查詢示例合集

DukeCheng發表於2015-11-15

基本查詢

複雜查詢示例

/// <summary>
        /// 獲取自定義表單資料中屬於部門的部分
        /// </summary>
        /// <param name="month"></param>
        /// <param name="departmentId"></param>
        /// <param name="positionId"></param>
        /// <param name="fillUserName"></param>
        /// <param name="departmentName"></param>
        /// <param name="formName"></param>
        /// <param name="fieldName"></param>
        /// <param name="assesserName"></param>
        /// <returns></returns>
        public IList<CustomFormResultItem> GetDepartmentCustomFormItems(DateTime month, Guid? customFormId, Guid? departmentId, string fillUserName = "", string departmentName = "", string formName = "", string fieldName = "", string assesserName = "")
        {
            return GetCustomFormItemInternal(2, month, customFormId, departmentId, null, fillUserName, departmentName, formName, fieldName, assesserName);
        }

        public IList<CustomFormResultItem> GetCustomFormItemInternal(int? userorDeptControlType, DateTime month, Guid? customFormId, Guid? departmentId, Guid? positionId,
            string fillUserName = "", string belongToUserOrDepartmentName = "", string formName = "", string fieldName = "", string assesserName = "")
        {
            var query = _customFormResultItemRep.CreateCriteriaQuery()
               .CreateAlias("t.Result", "result")

               .CreateAlias("result.CreateUser", "createUser")
               .CreateAlias("t.FormItem", "formItem")
               .CreateAlias("result.CustomForm", "customForm");



            if (month == DateTime.MinValue)
                month = DateTime.Now;

            //Filter by month
            query.Add(Restrictions.Eq(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int16, "datediff(month,?1,?2)"),
                NHibernateUtil.Int16, Projections.Property("CreateDate"), Projections.Constant(month)), 0));

            if (userorDeptControlType.HasValue)
            {
                if (userorDeptControlType == (int)FormControl.姓名)
                {
                    query.CreateAlias("result.BelongUser", "belongUser");
                    if (departmentId.HasValue && departmentId != Guid.Empty)
                    {
                        query.Add(Restrictions.Eq("belongUser.Department.Id", departmentId));
                    }
                    if (positionId.HasValue && positionId != Guid.Empty)
                    {
                        query.Add(Restrictions.Eq("belongUser.Position.Id", positionId));
                    }
                    if (!string.IsNullOrWhiteSpace(belongToUserOrDepartmentName))
                    {
                        query.Add(Restrictions.Like("belongUser.Name", belongToUserOrDepartmentName, MatchMode.Anywhere));
                    }
                }
                else if (userorDeptControlType == (int)FormControl.部門)
                {
                    query.CreateAlias("result.BelongDepartment", "belongDepartment");

                    if (departmentId.HasValue && departmentId != Guid.Empty)
                    {
                        query.Add(Restrictions.Eq("belongDepartment.Id", departmentId));
                    }
                    //if (positionId.HasValue && positionId != Guid.Empty)
                    //{
                    //    query.Add(Restrictions.Eq("belongUser.Position.Id", positionId));
                    //}
                    if (!string.IsNullOrWhiteSpace(belongToUserOrDepartmentName))
                    {
                        query.Add(Restrictions.Like("belongDepartment.Name", belongToUserOrDepartmentName, MatchMode.Anywhere));
                    }
                }

                var customFormSubQuery = DetachedCriteria.For<CustomFormItem>("cfi")
                    .CreateAlias("CustomForm", "cf")
                    .Add(Restrictions.Eq("cfi.FieldType", (FormControl)userorDeptControlType))
                     .SetResultTransformer(Transformers.DistinctRootEntity)
                    .SetProjection(Projections.Property("cf.Id"));

                query.Add(Subqueries.PropertyIn("customForm.Id", customFormSubQuery));

                if (userorDeptControlType == (int)FormControl.姓名)
                {
                    query.AddOrder(new Order("belongUser.Name", true));
                }
                else
                {
                    query.AddOrder(new Order("belongDepartment.Name", true));
                }
            }


            if (!string.IsNullOrWhiteSpace(fillUserName))
            {
                query.Add(Restrictions.Like("createUser.Name", fillUserName, MatchMode.Anywhere));
            }
            if (!string.IsNullOrWhiteSpace(formName))
            {
                query.Add(Restrictions.Like("customForm.Name", formName, MatchMode.Anywhere));
            }
            if (customFormId != null && customFormId != Guid.Empty)
            {
                query.Add(Restrictions.Eq("customForm.Id", customFormId));
            }

            if (!string.IsNullOrWhiteSpace(fieldName))
            {
                query.Add(Restrictions.Like("t.FieldName", fieldName, MatchMode.Anywhere));
            }
            if (!string.IsNullOrEmpty(assesserName))
            {
                query.Add(Restrictions.Like("createUser.Name", assesserName, MatchMode.Anywhere));
            }

            var types = new List<FormControl>() { FormControl.單選, FormControl.多選, FormControl.引用部門, FormControl.指標標準, FormControl.數字框, FormControl.文字框, FormControl.日期, FormControl.段落 }.ToArray();
            if (types.Length > 0)
                query.Add(Restrictions.In("formItem.FieldType", types));

            query.AddOrder(new Order("result.Id", true));
            query.AddOrder(new Order("formItem.OrderByIndex", true));

            query.SetMaxResults(100);

            var resultList = query.List<CustomFormResultItem>();
            return resultList;
        }

資料庫函式查詢示例

public IList<DepartmentIndicatorResult> SearchDepartmentIndicatorResult(DateTime evaluateMonth, Guid? departmentId, string indicatorName, string departmentName)
        {
            var query = this._departmentIndicatorResultRep.CreateCriteriaQuery().CreateAlias("t.Department", "dept").CreateAlias("t.Indicator", "indicator");

            if (evaluateMonth == DateTime.MinValue)
                evaluateMonth = DateTime.Now;
            //Filter by month
            query.Add(Restrictions.Eq(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int16, "datediff(month,?1,?2)"), NHibernateUtil.Int16, Projections.Property("PointDate"), Projections.Constant(evaluateMonth)), 0));
            //filter by day
            //query.Add(Restrictions.Eq(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int16, "datediff(day,?1,?2)"), NHibernateUtil.Int16, Projections.Property("PointDate"), Projections.Constant(evaluateMonth)), 0));

            if (departmentId.HasValue && departmentId != Guid.Empty)
            {
                query.Add(Restrictions.Eq("user.Department.Id", departmentId));
            }

            if (!string.IsNullOrWhiteSpace(departmentName))
            {
                query.Add(Restrictions.Like("dept.Name", departmentName, MatchMode.Anywhere));
            }
            if (!string.IsNullOrWhiteSpace(indicatorName))
            {
                query.Add(Restrictions.Like("indicator.Name", indicatorName, MatchMode.Anywhere));
            }
            //query.Add(Restrictions.Eq("indicator.DataCollectorType", DataCollectorType.月底收集));

            query.SetMaxResults(100);
            //query.AddOrder(new Order("pos.Name", true));

            var resultList = query.List<DepartmentIndicatorResult>();
            return resultList;
        }

查詢示例展示

/// <summary>
        /// 查詢員工指標考核結果(日評，月底指標考核成績)
        /// </summary>
        /// <param name="dateRangeType"></param>
        /// <param name="date"></param>
        /// <param name="departmentId"></param>
        /// <param name="positionId"></param>
        /// <param name="indicatorName"></param>
        /// <param name="userName"></param>
        /// <param name="dataCollectorTypes"></param>
        /// <returns></returns>
        private IList<UserIndicatorResult> SearchUserIndicatorResult(DateRangeType dateRangeType, DateTime date, Guid? departmentId = null, Guid? positionId = null, string indicatorName = null,
            string userName = null, IList<DataCollectorType> dataCollectorTypes = null, IList<DataInputType> dataInputTypes = null, Guid? userId = null, Guid? indicatorId = null, int limit = 200, bool isAssess = true)
        {
            var query = this._userIndicatorResultRep.CreateCriteriaQuery().CreateAlias("t.User", "user").CreateAlias("t.Indicator", "indicator")
                .Add(Restrictions.Eq("t.DateRangeType", dateRangeType));

            if (date == DateTime.MinValue)
                date = DateTime.Now;
            if (dateRangeType == DateRangeType.Monthly || (dataCollectorTypes != null && dataCollectorTypes.Contains(DataCollectorType.月底收集)))
            {
                //Filter by month
                query.Add(Restrictions.Eq(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int16, "datediff(month,?1,?2)"), NHibernateUtil.Int16, Projections.Property("PointDate"), Projections.Constant(date)), 0));
            }
            else if (dateRangeType == DateRangeType.Daily)
            {
                //filter by day
                query.Add(Restrictions.Eq(Projections.SqlFunction(new SQLFunctionTemplate(NHibernateUtil.Int16, "datediff(day,?1,?2)"), NHibernateUtil.Int16, Projections.Property("PointDate"), Projections.Constant(date)), 0));
            }

            if (departmentId.HasValue && departmentId != Guid.Empty)
            {
                query.Add(Restrictions.Eq("user.Department.Id", departmentId));
            }
            if (positionId.HasValue && positionId != Guid.Empty)
            {
                query.Add(Restrictions.Eq("user.Position.Id", positionId));
            }
            if (isAssess)
            {
                //todo
                query.Add(Restrictions.IsNotNull("TextResult"));
            }
            else
            {
                query.Add(Restrictions.IsNull("TextResult"));
            }
            if (!string.IsNullOrWhiteSpace(userName))
            {
                query.Add(Restrictions.Like("user.Name", userName, MatchMode.Anywhere));
            }
            if (userId.HasValue)
            {
                query.Add(Restrictions.Eq("user.Id", userId.Value));
            }
            if (indicatorId.HasValue)
            {
                query.Add(Restrictions.Eq("indicator.Id", indicatorId.Value));
            }
            if (!string.IsNullOrWhiteSpace(indicatorName))
            {
                query.Add(Restrictions.Like("indicator.Name", indicatorName, MatchMode.Anywhere));
            }

            if (dataCollectorTypes != null)
                query.Add(Restrictions.In("indicator.DataCollectorType", dataCollectorTypes.ToArray()));
            if (dataInputTypes != null)
                query.Add(Restrictions.In("indicator.DataInputType", dataInputTypes.ToArray()));

            //query.Add(Restrictions.Eq("indicator.DataCollectorType", DataCollectorType.月底收集));

            //為了效能的考慮，每次值允許拉200條記錄
            query.SetMaxResults(limit);
            query.AddOrder(new Order("user.Name", true));

            var resultList = query.List<UserIndicatorResult>();
            return resultList;
        }

public IList<PositionIndicator> SearchPositionIndicatorList(
            List<Guid> departmentIds = null, Guid? departmentId = null, string departmentName = null,
            List<Guid> positionIds = null, Guid? positionId = null, string positionName = null,
            List<Guid> linkedFormIds = null, Guid? linkedFormId = null,
            string indicatorName = null,
            EvaluateType? evaluateType = null,
            string dataSource = null,
            string discriminant = null,
            Guid? crossAssesserId = null,
            string crossAssesserName = null,
            List<DataCollectorType> dataCollectorTypes = null, DataCollectorType? dataCollectorType = null,
            int limit = 200)
        {
            var query = CreateCriteriaQuery<PositionIndicator>()
                .CreateAlias("t.Position", "pos").CreateAlias("t.CrossAssesser", "ca", JoinType.LeftOuterJoin);

            #region Special Query
            if (departmentIds != null || departmentId.HasValue)
            {
                if (departmentIds == null)
                    departmentIds = new List<Guid>();
                if (departmentId.HasValue)
                    departmentIds.Add(departmentId.Value);

                var positionSubquery = DetachedCriteria.For<Department>()
                    .CreateAlias("Positions", "pos", NHibernate.SqlCommand.JoinType.InnerJoin)
                    .Add(Restrictions.In("Id", departmentIds.ToArray()))
                    .SetProjection(Projections.Property("pos.Id"));

                query.Add(Subqueries.PropertyIn("pos.Id", positionSubquery));
            }

            if (!string.IsNullOrWhiteSpace(departmentName))
            {
                var positionSubquery = DetachedCriteria.For<Department>()
                       .CreateAlias("Positions", "pos", NHibernate.SqlCommand.JoinType.InnerJoin)
                       .Add(Restrictions.Like("Name", departmentName, MatchMode.Anywhere))
                       .SetProjection(Projections.Property("pos.Id"));

                query.Add(Subqueries.PropertyIn("pos.Id", positionSubquery));
            }

            if (positionIds != null || positionId.HasValue)
            {
                if (positionIds == null)
                    positionIds = new List<Guid>();
                if (positionId.HasValue)
                    positionIds.Add(positionId.Value);
                if (positionIds.Count > 0)
                    query.Add(Restrictions.In("t.Position.Id", positionIds.ToArray()));
            }

            if (!string.IsNullOrWhiteSpace(positionName))
            {
                query.Add(Restrictions.Like("t.Position.Name", positionName, MatchMode.Anywhere));
            }
            #endregion

            BindIndicatorQuery(query, linkedFormIds, linkedFormId, indicatorName, evaluateType, dataSource, discriminant, crossAssesserId, crossAssesserName, dataCollectorTypes, dataCollectorType, limit);

            return query.List<PositionIndicator>();
        }

        public IList<DeptIndicator> SearchDepartmentIndicatorList(
            List<Guid> departmentIds = null, Guid? departmentId = null, string departmentName = null,
            List<Guid> linkedFormIds = null, Guid? linkedFormId = null,
            string indicatorName = null,
            EvaluateType? evaluateType = null,
            string dataSource = null,
            string discriminant = null,
            Guid? crossAssesserId = null,
            string crossAssesserName = null,
            List<DataCollectorType> dataCollectorTypes = null, DataCollectorType? dataCollectorType = null,
            int limit = 200)
        {
            var query = CreateCriteriaQuery<DeptIndicator>().CreateAlias("t.CrossAssesser", "ca");
            if (departmentIds != null || departmentId.HasValue)
            {
                if (departmentIds == null)
                    departmentIds = new List<Guid>();
                if (departmentId.HasValue)
                    departmentIds.Add(departmentId.Value);
                if (departmentIds.Count > 0)
                    query.Add(Restrictions.In("t.Department.Id", departmentIds.ToArray()));
            }

            BindIndicatorQuery(query, linkedFormIds, linkedFormId, indicatorName, evaluateType, dataSource, discriminant, crossAssesserId, crossAssesserName, dataCollectorTypes, dataCollectorType, limit);

            return query.List<DeptIndicator>();
        }

        private static void BindIndicatorQuery(ICriteria query,
            List<Guid> linkedFormIds = null, Guid? linkedFormId = null,
            string indicatorName = null,
            EvaluateType? evaluateType = null,
            string dataSource = null,
            string discriminant = null,
            Guid? crossAssesserId = null,
            string crossAssesserName = null,
            List<DataCollectorType> dataCollectorTypes = null, DataCollectorType? dataCollectorType = null,
            int limit = 200)
        {
            if (linkedFormIds != null || linkedFormId.HasValue)
            {
                if (linkedFormIds == null)
                    linkedFormIds = new List<Guid>();
                if (linkedFormId.HasValue)
                    linkedFormIds.Add(linkedFormId.Value);
                if (linkedFormIds.Count > 0)
                    query.Add(Restrictions.In("t.Form.Id", linkedFormIds.ToArray()));
            }
            if (!string.IsNullOrWhiteSpace(indicatorName))
            {
                query.Add(Restrictions.Like("t.Name", indicatorName, MatchMode.Anywhere));
            }
            if (evaluateType.HasValue)
            {
                query.Add(Restrictions.Eq("t.EvaluateType", evaluateType));
            }
            if (!string.IsNullOrWhiteSpace(dataSource))
            {
                query.Add(Restrictions.Like("t.DataSource", dataSource, MatchMode.Anywhere));
            }
            if (!string.IsNullOrWhiteSpace(discriminant))
            {
                query.Add(Restrictions.Like("t.Discriminant", discriminant, MatchMode.Anywhere));
            }
            if (!string.IsNullOrWhiteSpace(crossAssesserName))
            {
                query.Add(Restrictions.Like("ca.Name", crossAssesserName, MatchMode.Anywhere));
            }
            if (crossAssesserId.HasValue)
            {
                query.Add(Restrictions.Eq("t.CrossAssesser.Id", crossAssesserId));
            }
            if (dataCollectorTypes != null || dataCollectorType.HasValue)
            {
                if (dataCollectorTypes == null)
                    dataCollectorTypes = new List<DataCollectorType>();
                if (dataCollectorType.HasValue)
                    dataCollectorTypes.Add(dataCollectorType.Value);

                if (dataCollectorTypes.Count > 0)
                    query.Add(Restrictions.In("t.DataCollectorType", dataCollectorTypes.ToArray()));
            }
            query.SetMaxResults(limit);
        }

Nhibernate 對view 查詢的幾種方法
2014-07-04
View
讓OData和NHibernate結合進行動態查詢
2016-05-05
MySQL join連表查詢示例
2018-09-28
MySql
AntDesignBlazor示例——分頁查詢
2023-12-16
Blazor
sql server分組查詢示例
2006-04-05
SQLServer
AntDesignBlazor示例——列表查詢條件
2023-12-03
Blazor
mysql 跨表查詢、更新、刪除示例
2021-09-09
MySql
關聯查詢的resultMap寫法示例
2015-01-28
ElasticSearch - 分頁查詢方式二【scroll】滾動查詢（kibana、Java示例）
2020-10-20
ElasticsearchJava
在檔案上使用 SQL 查詢的示例
2020-12-10
SQL
並查集合集
2019-01-30
並查集
並查集題目合集
2018-05-21
並查集
常用設計模式之.Net示例程式碼合集
2023-05-15
設計模式
SQL查詢的：子查詢和多表查詢
2020-11-18
SQL
查詢之折半查詢
2014-12-09
mysql-分組查詢-子查詢-連線查詢-組合查詢
2020-12-22
MySql
關於物流公司呼叫快遞單號查詢API介面的示例
2021-05-25
API
SQL Server中Table字典資料的查詢SQL示例程式碼
2018-12-17
SQLServer
查詢GBase XDM Cluster的客戶端示例程式介紹
2021-11-15
客戶端
C# 資料操作系列 - 12 NHibernate的增刪改查
2020-05-22
C#
複雜查詢—子查詢
2020-11-16
查詢——二分查詢
2020-09-18
子查詢-表子查詢
2017-11-14
查詢(1)--靜態查詢
2014-02-21
查詢(2)--動態查詢
2014-02-21
MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]
2024-03-10
MySql筆記
資料庫 - 連線查詢、巢狀查詢、集合查詢
2015-05-05
資料庫巢狀
離線查詢與線上查詢
2018-12-11
【SQL查詢】集合查詢之INTERSECT
2016-11-13
SQL
查詢與排序02,折半查詢
2014-07-28
排序
USING NHIBERNATE WITH MySQL
2013-12-21
MySql
NHibernate 快速入門
2013-07-14
Using NHibernate with SQLite
2013-08-25
SQLite
Nhibernate教程2（4）
2009-08-04
查詢
2017-07-02
#查詢演算法#【1】簡單查詢：順序、折半查詢
2014-07-22
演算法
使用 sed 命令查詢和替換檔案中的字串的 16 個示例
2019-09-20
字串
【LC】locale設定查詢及相關環境變數含義及示例
2012-04-30
變數

NHibernate查詢示例合集

相關文章