NHibernate - sql 表别名不一致

发布于 2024-11-27 04:14:10 字数 4780 浏览 0 评论 0原文

有没有办法预测nhibernate生成sql时将为表生成什么别名?

理论上它应该是“this_”、“this_1_”等,这会很棒,但我刚刚发现当我在映射中使用 Join 时它会更加随机。

例如,在集成测试中,我得到这样的结果:

table1 this_ 
left outer join
   table2 o this_1_
        on this_.id=this_1_.t1id
left outer join
    table3  this_2_ 
        on this_.id=this_2_.t1id
left outer join
    table4 this_3_ 
        on this_.id = this_3_.t1id
left outer join
    table5 somealiasbasedonrootentity_2_
        on this_.id=somealiasbasedonrootentity_2_.t1id

但是当我在网络服务器上运行相同的映射时...所有别名都是 somealias_, somealias_1_ 等等 难道

它不应该至少......更加一致吗?

它使得在映射中使用任何类型的“哪里”或“公式”变得非常困难(甚至不可能)

——编辑:我想通过映射实现的示例查询

select 
  t1.a, t2.a, (select max(t3.value) where t3.id=t2.t3id) 
from table1 t1 
  left join table2.t2 on t2.t1id=t1.id;

——编辑这里是(几乎最新的)映射: 我知道它很大,但我打算将其用于报告目的。 (顺便说一句,这不是最新版本)

public class ClaimHistoryMap : ClassMap<ClaimHistory>
    {
        public ClaimHistoryMap()
        {
            Table("wts_claim");
            ReadOnly();
            Not.LazyLoad();
            Id(x => x.Id, "claimid");
            Map(x => x.ClaimNo, "claimNo");
            Map(x => x.DateCompleted, "ModificationDate");
            Map(x => x.DateOfDispatch, "DateOfDispatch");
            Map(x => x.DateProcessed, "ModificationDate");
            Map(x => x.Status, "status");
            Map(x => x.WorkOrderNo, "ServiceWorkOrder");
            Map(x => x.SerialNo, "serialNo");
            Map(x => x.IsEnabled, "bStatus");
            Map(x => x.InvoiceNo, "InvoiceNo");
            Map(x => x.ServiceCoverage).Formula(
                @"(Select c.coveragename from wts_servicecoverage as c where c.servicecoverageid=servicecoverageid)");
            Join("wts_site_info",x=>
                             {

                                     x.Optional().KeyColumn("claimid");
                                     x.Map(s => s.CustomerName, "CustomerName");
                                     x.Map(s => s.CustomerAddress, "Address");
                                     x.Map(s => s.CustomerCity, "City");
                                     x.Map(s => s.CustomerPhone, "Phone");
                                     x.Map(s => s.CustomerZip, "Zip");
                                     x.Map(s => s.ReportComplaint, "Complaint");
                                     x.Map(s => s.TechnicianName, "TechName");

                                     x.Map(s => s.Model)
                                         .Formula(@"(Select mo.Model from WTS_Product mo where mo.ProductId=this_1_.ProductId)");
                                     x.Map(s => s.CustomerState)
                                         .Formula(@"(Select st.statename from wts_state st where st.stateid=this_1_.state)");
                                 });
        Join("wts_grand_total", x =>
                                    {
                                        x.Optional().KeyColumn("claimid");
                                        x.Map(s => s.TotalCharge, "total");
                                        x.Map(s => s.FreightCharge, "Frieght");
                                        x.Map(s => s.PartsCharge, "Parts");
                                        x.Map(s => s.HandlingFee, "Handling");
                                        x.Map(s => s.SalesTax, "Mix");
                                    });
        Join("wts_labour_travel", x =>
        {
            x.Optional().KeyColumn("claimid");
            x.Map(s => s.TravelCharge).Formula("traveltotal+travelovertotal+MilageRegular+MilageOvertime+supmileagehour+supmileageoverhour");
            x.Map(s => s.TravelTime).Formula("TravelHourRegular+TravelHourOvertime+suptravelhour+suptraveloverhour");
            x.Map(s => s.LaborCharge).Formula("labortotal+laborovertotal");
            x.Map(s => s.LaborTime).Formula("LaborHoursRegular+LaborHoursOvertime+suplaborhour+suplaboroverhour");
            x.Map(s => s.TripsNo, "trips");
            x.Map(s => s.TruckCharge).Formula(
                "(select max(ltr.TruckRate) from wts_labour_travel_rate ltr where ltr.LabourTravelId = this_3_.LabourTravelId)");
        });

        Map(x => x.WasModified).Formula(
            "(select count(comm.claim_id) from wts_claim_status comm where comm.claim_id=this_.claimid and comm.Status=3)");
        References(x => x.User, "entryBy").Fetch.Join().Not.LazyLoad();

        HasMany(x => x.PartNo).KeyColumn("claimid").Table("wts_general_part").Element("partNo");
        HasMany(x => x.Repairs).KeyColumn("claimid").Table("wts_Claim_Resolution").Element("resolutionDesc");
    }
}

Is there any way to predict what alias will be generated for the table when nhibernate is generating sql?

Theoretically it should be 'this_', 'this_1_' etc and it would be great but I just discovered that it's a little bit more random when i'm using Join in the mapping.

for example in integration test i'm getting something like this:

table1 this_ 
left outer join
   table2 o this_1_
        on this_.id=this_1_.t1id
left outer join
    table3  this_2_ 
        on this_.id=this_2_.t1id
left outer join
    table4 this_3_ 
        on this_.id = this_3_.t1id
left outer join
    table5 somealiasbasedonrootentity_2_
        on this_.id=somealiasbasedonrootentity_2_.t1id

but when i run the same mapping on webserver... all the aliases are somealias_, somealias_1_
etc

Shouldn't it be at least... more consistent?

it makes really hard (even impossible) to use any kind of 'where' or 'formula' within the mapping

---edit: sample query i want to achieve with mapping

select 
  t1.a, t2.a, (select max(t3.value) where t3.id=t2.t3id) 
from table1 t1 
  left join table2.t2 on t2.t1id=t1.id;

--- edit here's the (almost latest) mapping:
I know it's quite big but i was going to make it for reporting purposes. (btw it's not very recent version)

public class ClaimHistoryMap : ClassMap<ClaimHistory>
    {
        public ClaimHistoryMap()
        {
            Table("wts_claim");
            ReadOnly();
            Not.LazyLoad();
            Id(x => x.Id, "claimid");
            Map(x => x.ClaimNo, "claimNo");
            Map(x => x.DateCompleted, "ModificationDate");
            Map(x => x.DateOfDispatch, "DateOfDispatch");
            Map(x => x.DateProcessed, "ModificationDate");
            Map(x => x.Status, "status");
            Map(x => x.WorkOrderNo, "ServiceWorkOrder");
            Map(x => x.SerialNo, "serialNo");
            Map(x => x.IsEnabled, "bStatus");
            Map(x => x.InvoiceNo, "InvoiceNo");
            Map(x => x.ServiceCoverage).Formula(
                @"(Select c.coveragename from wts_servicecoverage as c where c.servicecoverageid=servicecoverageid)");
            Join("wts_site_info",x=>
                             {

                                     x.Optional().KeyColumn("claimid");
                                     x.Map(s => s.CustomerName, "CustomerName");
                                     x.Map(s => s.CustomerAddress, "Address");
                                     x.Map(s => s.CustomerCity, "City");
                                     x.Map(s => s.CustomerPhone, "Phone");
                                     x.Map(s => s.CustomerZip, "Zip");
                                     x.Map(s => s.ReportComplaint, "Complaint");
                                     x.Map(s => s.TechnicianName, "TechName");

                                     x.Map(s => s.Model)
                                         .Formula(@"(Select mo.Model from WTS_Product mo where mo.ProductId=this_1_.ProductId)");
                                     x.Map(s => s.CustomerState)
                                         .Formula(@"(Select st.statename from wts_state st where st.stateid=this_1_.state)");
                                 });
        Join("wts_grand_total", x =>
                                    {
                                        x.Optional().KeyColumn("claimid");
                                        x.Map(s => s.TotalCharge, "total");
                                        x.Map(s => s.FreightCharge, "Frieght");
                                        x.Map(s => s.PartsCharge, "Parts");
                                        x.Map(s => s.HandlingFee, "Handling");
                                        x.Map(s => s.SalesTax, "Mix");
                                    });
        Join("wts_labour_travel", x =>
        {
            x.Optional().KeyColumn("claimid");
            x.Map(s => s.TravelCharge).Formula("traveltotal+travelovertotal+MilageRegular+MilageOvertime+supmileagehour+supmileageoverhour");
            x.Map(s => s.TravelTime).Formula("TravelHourRegular+TravelHourOvertime+suptravelhour+suptraveloverhour");
            x.Map(s => s.LaborCharge).Formula("labortotal+laborovertotal");
            x.Map(s => s.LaborTime).Formula("LaborHoursRegular+LaborHoursOvertime+suplaborhour+suplaboroverhour");
            x.Map(s => s.TripsNo, "trips");
            x.Map(s => s.TruckCharge).Formula(
                "(select max(ltr.TruckRate) from wts_labour_travel_rate ltr where ltr.LabourTravelId = this_3_.LabourTravelId)");
        });

        Map(x => x.WasModified).Formula(
            "(select count(comm.claim_id) from wts_claim_status comm where comm.claim_id=this_.claimid and comm.Status=3)");
        References(x => x.User, "entryBy").Fetch.Join().Not.LazyLoad();

        HasMany(x => x.PartNo).KeyColumn("claimid").Table("wts_general_part").Element("partNo");
        HasMany(x => x.Repairs).KeyColumn("claimid").Table("wts_Claim_Resolution").Element("resolutionDesc");
    }
}

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

被你宠の有点坏 2024-12-04 04:14:10

您不需要在映射中引用别名,只需引用您感兴趣的属性即可。例如,如果您有一个具有名为“SomeValue”属性的类的 FluentNHibernate 映射,您可以在像这样的映射:

Where("SomeValue = 1");

类似的事情也适用于 XML 文件映射。

You should not need to reference the alias in the mapping, just reference the property which you are interested in. For example, if you have a FluentNHibernate mapping for a class that has a property called 'SomeValue' you can create a where restriction in the mapping like this:

Where("SomeValue = 1");

A similar thing would apply for XML file mappings.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文