转换一个SQL语句转换成LINQ到SQL [英] Convert an SQL statement into LINQ-To-SQL

问题描述

我可以拿到手的转换后面成一个LINQ声明。

can I get a hand converting 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
    )

目前我有它下来这样的：

Currently I have it down to 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())));

以上的LINQ的问题是，它返回有标题更改的条目。在数据库中我把所有的记录（因此需要先上createdDate降序排列的历史。当我更改从标题的报告x可称号Y，它显示了两个标题下，当我只想要最新记录这将因此可以根据标题y中的之一。

The problem with the LINQ above is that it returns entries that have titles changed. Within the database I keep history of all records (hence the need for the first on the createdDate order descending. When I change a report from title x to title y, it shows up under both titles when I only want the most recent record which would hence be the one under title y.

修改
对不起缺乏细节。我有一个持有信息一报告表关于报告（seedlot作为一个标识符）。每当报告被编辑时，一个新的记录被插入（与更新旧），使得历史被保留。在这种情况下，然后对createdDate最大条目指示该报告是要显示的最新记录。然后报告分为标题或reportItems，这些报表项目持有标题和相关报告，这些reportItems在一个ReportList举行，这样我可以在想要的格式打印出来的JSON，只是包含一个状态列和id的ReportItems链接。

EDIT Sorry for the lack of detail. I have a reports table which holds info about the report (seedlot being an identifier). Whenever a report is edited, a new record is inserted (vs updating the old one) such that history is kept. In this case then the max entry for the createdDate indicates that the report is the most recent record to be displayed. Reports are then grouped into titles or ReportItems. These report items hold the title and associated reports. These reportItems are held in a ReportList such that I can print out the JSON in a desired format and just contains a status column and id linked to by the ReportItems.

在事件报告从到标题b冠军的感动，一个新的记录输入的标题外键链接到标题又改为。发生这种情况时上面给出的LINQ返回每一个人ReportItem下的记录时，它应该只返回标题B最新的项目（从上面的例子）。除此之外的LINQ语句只返回最近的createdDate记录。

In the event that a report is moved from title a to title b, a new record is entered with the title foreign key linking up to the title it was changed to. When this happens the above given LINQ returns the record under each individual ReportItem when it should only return the newest entry for title b (from the example above). Other than this the LINQ statement only returns the most recent record for the createdDate.

下面是我的阶级结构（其中模仿DB结构和）

Here are my class structures (which mimic the DB structure as well)

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;}
}

谢谢，
DMAN

Thanks, Dman

推荐答案

更改的数据库架构八九不离十压平了咬了拿出了很多分支的。

Changed the database schema to sorta flatten it out a bit a take out a lot of the branching.

报告是现在的根，我只是有一个获得附加到报告标题的表。

Report is now the root and I just have a table of titles that get attached to the reports.

感谢所有为寿！

这篇关于转换一个SQL语句转换成LINQ到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！