0

I am asking for your help regarding a linq query to SQL.

Here is a part of the diagram of my database: https://i.stack.imgur.com/EaY1J.jpg

My problem is in the following relationship: tbl_607_bottle and tbl_607_gaz_reporting, I can have several reportings for a bottle but reporting can only have one bottle.

I do this request but it's not satisfying. 

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();

I want the last report on each bottle but it return all reports on each bottle. Would you have an idea of ​​the solution?

Thank you for your time

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lyzzie
  • 37
  • 4
  • 1
    How about group by bottles and take the first in each group like in [this answer](https://stackoverflow.com/questions/19012986/how-to-get-first-record-in-each-group-using-linq)? – kovac Aug 02 '19 at 07:55
  • 1
    This happening because each report will be distinct presumably from any other report. You will need to group them first and take the correct item out of the group. – sr28 Aug 02 '19 at 08:03
  • One thing about grouping, ordering and selecting one per group you need to be careful about is, the query might load all records to memory in your client app and do the aggregations in-memory. Do check if the query you expect is actually sent to database. – kovac Aug 02 '19 at 08:20

1 Answers1

0

Thank you so much! I persisted in trying to solve the problem in a single query when the solution was so simple.

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();
Lyzzie
  • 37
  • 4