Convert SQL statements to LINQ-To-SQL

Question

Can I get a combination that translates the following into a LINQ statement.

SELECT reports.* FROM [dbo].[ReportLists] rl INNER JOIN [dbo].[ReportItems] ri ON rl.Id = ri.ReportListId INNER JOIN [dbo].[Reports] reports ON ri.Id = reports.ReportItemId WHERE reports.createdDate IN ( SELECT MAX(report_max_dates.createdDate) FROM [dbo].[Reports] report_max_dates GROUP BY report_max_dates.seedLot ) 

I am currently dealing with this:

 db.ReportLists.Select(rl => db.ReportItems .Where(ri => ri.ReportListId == rl.Id) .Select(ri => db.Reports .Where(r => r.ReportItemId == ri.Id) .GroupBy(r => new { r.seedLot }) .Select(g => g.OrderByDescending(x => x.createdDate).FirstOrDefault()))); 

The problem with LINQ above is that it returns records with names. Inside the database, I save the history of all records (hence the need for the first order to be created in the created order. When I change the report from heading x to heading y, it appears under both headings when I need only the most recent record, which will therefore be under the heading y.

EDIT Sorry for the lack of details. I have a report table that contains information about the report (the seed is an identifier). Whenever a report is edited, a new record is inserted (or the old one is updated), so the history is saved. In this case, the maximum record for createdDate indicates that the report is the most recent record to be displayed. Then the reports are grouped into headers or ReportItems. These report elements contain a title and related reports. These report elements are stored in a ReportList so that I can print the JSON in the required format and simply contain the status column and identifier associated with ReportItems.

If the report is transferred from heading a to heading b, a new record will be entered with the foreign key of the heading associated with the name it was changed to. When this happens, the above LINQ returns a record under each individual ReportItem when it should only return the newest record for heading b (from the above example). In addition, the LINQ statement returns only the most recent record for the created file.

Here are my class structures (which also mimic a DB structure)

 public class ReportList { public int Id {get;set;} public string status {get;set;} public List<ReportItem> {get;set;} } public class ReportItem { public int Id {get;set} public string title {get;set;} public List<Report> {get;set;} } public class Report { public int Id {get;set;} public string Lot {get;set;} ... Other data ... public DateTime createdDate {get;set;} } 

Thanks, Dman

Answer 1

The database schema has been changed to sort it a bit, pulling out many branches.

Now the report is the root, and I have a table of headers that are attached to the reports.

Thanks everyone for the suggestions tho!

Answer 2

If I understood this correctly, then you need the latest seedLot report, then I will use the following:

 from r in Reports group r by r.CreateDate into g select g.OrderByDescending(r1=>r1.CreateDate).FirstOrDefault() 

It is not clear why you need ReportItem and ReportList, if you do not include them in the result, you can navigate to them using the navigation properties of Report.

Answer 3

Oh. Understand. Inner Join subtracts those records that do not have an identifier in the ReportItems table and ReportItems that do not have an identifier in the ReportLists table. Since you are not using Linq's inner join syntax, they are not equivalent. Have you tried this:

 var reports = from rl in db.ReportLists from ri in rl.ReportItems from r in ri.Reports where ( from report in db.Reports group report by report.seedLot into g select g.Max(rep => rep.createdDate)).Contains(r.CreatedDate) select rl; 

I'm not 100% sure that I have the syntax on the right in this group with Contains (I don't do a lot of grouping day after day). And if the LINQ provider does not support Max (or Contains, although it should), then it will pull the eveything client-side and a lot of data to click on the wire.

Answer 4

The details that you provide make it more understandable, although not completely, so I will give two alternatives. Navigation properties are useful since a very concise comprehensive syntax is now possible:

 from rl in context.ReportLists from ri in rl.ReportItems from r in ri.Reports group r by r.Lot into g select g.OrderByDescending(x => x.createdDate).FirstOrDefault() 

or (since you seem to group the Title into ReportItems ):

 from rl in context.ReportLists from ri in rl.ReportItems group ri by ri.Title into g select g.Select(x => x.Reports .OrderByDescending(x => x.createdDate).FirstOrDefault()) 

under the hood, this also creates SQL connections, but you don't need awkward syntax. In the current linq syntax, it boils down to SelectMany() s.

As you can see, I still can’t understand the role of Lot and Title , but I hope that alternatives will give some substance to form your own specifications.