NHibernate的 - queryover - 如何使动态排序的查询加盟 [英] nhibernate - queryover - how to make dynamic sorting for joined queries

问题描述

我有一个用户帐户表管理面板，这台拥有不同的数据库表（在SQL数据库表）的数据。和用户帐户表中有支持分页和排序。当我尝试将数据与姓，从客户数据库中取出进行排序，.NET抛出一个异常，说位置数据库表中没有姓列。我的方法是这样的：

 帐户ACC = NULL; //布努BUşekildetanımlamadanolmuyor：•
账户登入ACCL = NULL; //布努BUşekildetanımlamadanolmuyor：•
计划PROG = NULL; 
地点学校= NULL; 
位置镇= NULL; 
位置的城市= NULL; 
 
 // Takip edilenbloggerınTUM博客sayfalarınıCEK 
 VAR的查询= Session.QueryOver<帐户>（（）=> ACC）
 .Left.JoinQueryOver<账户登入>（ X => x.Logins，（）=> ACCL）
 .Left.JoinQueryOver（X => acc.AtentedToProgram，（）=> PROG）
 .Left.JoinQueryOver（X = > acc.LiveIn，（）=>学校）
 .Left.JoinQueryOver（X => school.Parent，（）=>镇）
 .Left.JoinQueryOver（X => town.Parent，（）=>市）
。凡（X => acc.CreateDate.IsBetween（DateTime.Now.AddMonths（-12））和（DateTime.Now）
） ; 
 
如果（program_id！= 0）
 query.And（X => prog.program_id == program_id）; 
 
 =搜索？; 
 
的String [] = searchedTags search.Split（新[] {''}，StringSplitOptions.RemoveEmptyEntries）; 
 
如果（searchedTags = NULL&放大器;！&安培; searchedTags.Count（）0）
 {
析取DIS =新析取（）; 
 
的for（int i = 0; I< searchedTags.Count（）;我++）
 {
 dis.Add（Expression.Like（Projections.Property（（）= > accl.UserName），searchedTags [I]，MatchMode.Anywhere））; 
 dis.Add（Expression.Like（Projections.Property（（）=> acc.FirstName），searchedTags [I]，MatchMode.Anywhere））; 
 dis.Add（Expression.Like（Projections.Property（（）=> acc.LastName），searchedTags [I]，MatchMode.Anywhere））; 
 dis.Add（Expression.Like（Projections.Property（（）=> acc.Email），searchedTags [I]，MatchMode.Anywhere））; 
} 
 
 query.And（DIS）; 
} 
 
如果（city_id！= 0）
 query.And（X => city.LocationId == city_id）; 
 
如果（town_id！= 0）
 query.And（X => town.LocationId == town_id）; 
 
如果（学校ID！= 0）
 query.And（X => school.LocationId ==学校ID）; 。
 
 VAR countquery = query.Clone（）ToRowCountQuery（）FutureValue< INT>（）; 
 
 pager.TotalPageNumber = countquery.Value; 
 
 //acc.GetType().GetProperty(pager.SortColumn）
 VAR orderred = query.OrderBy（Projections.Property（pager.SortColumn））; 
 
 
 
返回ordered.Skip（pager.Skip）。取（pager.Take）的.List（）; 
  

VAR orderred = query.OrderBy（Projections.Property（pager.SortColumn ））;线路有问题。

这个问题，当我通过pager.SortColumn参数作为字符串的OrderBy方法，它不能知道哪个表有列，并尝试从最后被加入数据库表的位置db表得到名字。我怎样才能解决这个问题？在此先感谢

解决方案

在使用 JoinQueryOver ，但从来没有使用x在在lambda表达式之后。你可能是打算是 JoinAlias 以创建别名。

 。左.JoinAlias<账户登入>（X => x.Logins，（）=> ACCL）
 .Left.JoinAlias（（）=> acc.AtentedToProgram，（）=> PROG）
 .Left.JoinAlias（（）=> acc.LiveIn，（）=>学校）
 .Left.JoinAlias（（）=> school.Parent，（）=>镇）
 .Left.JoinAlias（（）=> town.Parent，（）=>市）
  

I have a user accounts table in admin panel and this table holds data from different db tables (tables in SQL database ). And user accounts table has to support paging and sorting. When I try to sort data with "FirstName" fetched from Account db table , .net throw an exception saying Location db table does not have "FirstName" column. My method is like:

        Account acc = null; //bunu bu şekilde tanımlamadan olmuyor :S
        AccountLogin accl = null; //bunu bu şekilde tanımlamadan olmuyor :S
        Program prog = null;
        Location school = null;
        Location town = null;
        Location city = null;

        //Takip edilen bloggerın tüm blog sayfalarını çek
        var query = Session.QueryOver<Account>(() => acc)
            .Left.JoinQueryOver<AccountLogin>(x => x.Logins, () => accl)
            .Left.JoinQueryOver(x => acc.AtentedToProgram, () => prog)
            .Left.JoinQueryOver(x => acc.LiveIn, () => school)
            .Left.JoinQueryOver(x => school.Parent, () => town)
            .Left.JoinQueryOver(x => town.Parent, () => city)
            .Where(x =>                                   acc.CreateDate.IsBetween(DateTime.Now.AddMonths(-12)).And(DateTime.Now)
                   );

        if (program_id != 0)
            query.And(x => prog.program_id == program_id);

        search = search??"";

        string[] searchedTags = search.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

        if (searchedTags != null && searchedTags.Count() > 0)
        {
            Disjunction dis = new Disjunction();

            for (int i = 0; i < searchedTags.Count(); i++)
            {
                dis.Add(Expression.Like(Projections.Property(() => accl.UserName), searchedTags[i], MatchMode.Anywhere));
                dis.Add(Expression.Like(Projections.Property(() => acc.FirstName), searchedTags[i], MatchMode.Anywhere));
                dis.Add(Expression.Like(Projections.Property(() => acc.LastName), searchedTags[i], MatchMode.Anywhere));
                dis.Add(Expression.Like(Projections.Property(() => acc.Email), searchedTags[i], MatchMode.Anywhere));
            }

            query.And(dis);
        }

        if (city_id != 0)
            query.And(x => city.LocationId == city_id);

        if (town_id != 0)
            query.And(x => town.LocationId == town_id);

        if (school_id != 0)
            query.And(x => school.LocationId == school_id);

        var countquery = query.Clone().ToRowCountQuery().FutureValue<int>();

        pager.TotalPageNumber = countquery.Value;

         //acc.GetType().GetProperty(pager.SortColumn)
        var orderred = query.OrderBy(Projections.Property(pager.SortColumn));



            return ordered.Skip(pager.Skip).Take(pager.Take).List();

var orderred = query.OrderBy(Projections.Property(pager.SortColumn)); line has problem.

The problem, when I pass pager.SortColumn parameter as string to OrderBy method, it cant know which table has the column and try to get "Firstname" from Location db table that is last joined db table. How can I solve this ? Thanks in advance

解决方案

you use JoinQueryOver but never use the x in the lambdas afterwards. What you probably intended was JoinAlias to create the alias.

.Left.JoinAlias<AccountLogin>(x => x.Logins, () => accl)
.Left.JoinAlias(() => acc.AtentedToProgram, () => prog)
.Left.JoinAlias(() => acc.LiveIn, () => school)
.Left.JoinAlias(() => school.Parent, () => town)
.Left.JoinAlias(() => town.Parent, () => city)

这篇关于NHibernate的 - queryover - 如何使动态排序的查询加盟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！