时间戳

首页 > 解决方案 > 需要有关 Linq-to-SQL 查询的帮助（可能是嵌套查询？）

c# - 需要有关 Linq-to-SQL 查询的帮助（可能是嵌套查询？）

问题描述

我正在寻求有关对 SQL 的 linq 查询的帮助。

这是我的数据库图表的一部分：https ://imgur.com/xFBUm3q

我的问题在于以下关系：tbl_607_bottle并且tbl_607_gaz_reporting，我可以为一瓶有多个报告，但报告只能有一个瓶。

我提出了这个要求，但并不令人满意。

var GazReportingWizardViewModel = 
    (from gr in db.tbl_607_gaz_reporting  
     join a in db.tbl_607_actors on gr.FK_ID_actors equals a.id  
     join bo in db.tbl_607_bottle on gr.FK_ID_bottle equals bo.ID  
     join loc in db.tbl_607_location on bo.FK_ID_location equals loc.ID  
     join cc in db.tbl_607_conformity_certificate on bo.FK_ID_conformity_certificate equals cc.ID  
     join j in db.tbl_607_join_bottle_gaz on bo.ID equals j.FK_ID_bottle  
     join g in db.tbl_607_gaz on j.FK_ID_gaz equals g.ID  
     join od in db.tbl_607_order_details on g.FK_ID_order_details equals od.ID  
     where loc.ID == Process   
     select new GazReportingWizardViewModel  
                {  
                    bottle_conti_number = bo.bottle_conti_number,  
                    pressure_value = gr.pressure_value,  
                    reporting_date = gr.reporting_date,  
                    first_name = gr.tbl_607_actors.first_name,  
                    last_name = gr.tbl_607_actors.last_name,  
                    expiration_date = cc.expiration_date,  
                    content = od.content_comments  
                }).Distinct()
                  .OrderBy(t => t.reporting_date)
                  .ToList();

我想要每瓶的最后一份报告，但它会返回每瓶的所有报告。您对解决方案有想法吗？

感谢您的时间

标签： c#sqllinq

解决方案

太感谢了！当解决方案如此简单时，我坚持尝试在单个查询中解决问题。

var GazReportingWizardViewModel = (from gr in db.tbl_607_gaz_reporting
                                   join a in db.tbl_607_actors on gr.FK_ID_actors equals a.id
                                   join bo in db.tbl_607_bottle on gr.FK_ID_bottle equals bo.ID
                                   join loc in db.tbl_607_location on bo.FK_ID_location equals loc.ID
                                   join cc in db.tbl_607_conformity_certificate on bo.FK_ID_conformity_certificate equals cc.ID
                                   join j in db.tbl_607_join_bottle_gaz on bo.ID equals j.FK_ID_bottle
                                   join g in db.tbl_607_gaz on j.FK_ID_gaz equals g.ID
                                   join od in db.tbl_607_order_details on g.FK_ID_order_details equals od.ID
                                   where loc.ID == Process 
                                   select new GazReportingWizardViewModel
                                   {
                                    bottle_conti_number = bo.bottle_conti_number,
                                    pressure_value = gr.pressure_value,
                                    reporting_date = gr.reporting_date,
                                    first_name = gr.tbl_607_actors.first_name,
                                    last_name = gr.tbl_607_actors.last_name,
                                    expiration_date = cc.expiration_date,
                                    content = od.content_comments
                                    }).ToList();

 var res = from element in GazReportingWizardViewModel
           group element by element.bottle_conti_number
           into groups
           select groups.OrderByDescending(p => p.reporting_date).First();

推荐阅读