I am asking for your help regarding a linq query to SQL.
Here is a part of the diagram of my base.
My problem lies 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, i want the last report on each bottle but it return all reports on each bottle.
- 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();
Would you have an idea of the solution?
Thank you for your time