使用linq查询获取每行的重复行 [英] getting duplicate rows for each row using linq query
问题描述
我有下面这样的表的数据库模式，我试图使用左查询或使用以下查询对这些表进行普通联接来获得结果.以某种方式获取重复的行，如下所述.
I have db schema having tables like this below, i am trying to get the results using left join or normal join on these table using the below query. Somehow getting duplicate rows like as mentioned below.
Requests (table) RequestStage (table)
------------- ----------------
id RequestStageid
createdAt(datetime) name (values: RequestSubmitted,Inreview)
RequestTypeId
MasterSectionID
RequestStatusID
RequestStageID
id (FK)(localCodes)
MasterSections (table)
-------------------------
MasterSectionId
name (values: code1,Code2)
LocalCodes (table)
---------
Id
MasterSectionid
Name
description
...
...
我已将三行同时插入到本地代码表中，其中三行已插入到请求表中，其中requestStage为RequestSubmitted
I have inserted three rows into local code table at the same time three rows inserted into request table with requestStage as RequestSubmitted
现在，我正在尝试使用以下查询拉出状态为RequestSubmitted的行.我应该能够获取3行，而不是获取9行(即I am getting 1 row three times).
Now i am trying to pull the rows which are having the status RequestSubmitted using below query. I should be able to get the 3 rows, instead of it i am getting 9 rows(i.e) I am getting 1 row three times.
如果我应用distinct，我将获得3行，但是还有其他方法可以实现而无需应用distinct
if i apply distinct i am getting 3 rows but is there any other way to achieve this with out applying distinct
var results = (from re in _dbContext.Requests
join rt in _dbContext.RequestTypes on re.RequestTypeId equals rt.RequestTypeId
join rs in _dbContext.RequestStages on re.RequestStageId equals rs.RequestStageId
join ms in _dbContext.MasterSections on re.MasterSectionId equals ms.MasterSectionId
join lc in _dbContext.LocalCodes on ms.MasterSectionId equals lc.MasterSectionId
where rs.Name == "RequestSubmitted"
select new SectionResponse
{
Section = lc.Name,
Description = lc.Description,
CreatedBy = "",
Type = rt.Name.ToString(),
Status = rs.Name,
Age = (DateTime.Now.Date - re.CreatedAt.Date).TotalDays.ToString() + "Days"
}).ToList();
另一种尝试:
(from re in _dbContext.Requests
join rt in _dbContext.RequestTypes on re.RequestTypeId equals rt.RequestTypeId into reqTypes
from x in reqTypes.DefaultIfEmpty()
join rs in _dbContext.RequestStages on re.RequestStageId equals rs.RequestStageId into reqStages
from y in reqStages.DefaultIfEmpty()
join ms in _dbContext.MasterSections on re.MasterSectionId equals ms.MasterSectionId into mstSections
from z in mstSections.DefaultIfEmpty()
join lc in _dbContext.LocalCodes on z.MasterSectionId equals lc.MasterSectionId into locCodes
from a in locCodes.DefaultIfEmpty()
where y.Name == "RequestSubmitted"
select new SectionResponse
{
Section = a.Name,
Description = a.Description,
CreatedBy = "",
Type = x.Name.ToString(),
Status = y.Name,
Age = (DateTime.Now.Date - re.CreatedAt.Date).TotalDays.ToString() + "Days"
}).ToList();
我不确定在这些查询中我在哪里做错了，请问有人提出关于仅获取三行的任何想法.另外，如果您需要更多信息，请告诉我.
I am not sure where i am doing wrong in these queries, Could any one please suggest any idea on how to get only three rows. Also please let me know if you need any more information.
非常感谢
域模型:
public class Request
{
public Guid RequestId { get; set; }
public string Description { get; set; }
public Guid DataId { get; set; }
public DateTime CreatedAt { get; set; }
public Guid MasterSectionId { get; set; }
public Guid RequestStatusId { get; set; }
public Guid RequestStageId { get; set; }
public Guid RequestTypeId { get; set; }
public MasterSection MasterSection { get; set; }
public RequestStatus Status { get; set; }
public RequestStage Stage { get; set; }
public RequestType RequestType { get; set; }
}
public class RequestStage
{
public Guid RequestStageId { get; set; }
public string Name { get; set; }
public ICollection<Request> Requests { get; set; }
}
public class MasterSection
{
public Guid MasterSectionId { get; set; }
public string Name { get; set; }
public ICollection<LocalCode> LocalCodes { get; set; }
public ICollection<Request> Requests { get; set; }
}
public class RequestStatus
{
public Guid RequestStatusId { get; set; }
public string Name { get; set; }
public ICollection<Request> Requests { get; set; }
}
public class LocalCode : IMasterObject
{
public Guid? Id { get; set; }
public string Name { get; set; }
public Guid MasterSectionId { get; set; }
public MasterSection MasterSection { get; set; }
}
推荐答案
我尝试使用导航属性，并根据描述提出了以下领域模型. 请尝试以下操作:(尽管未对代码进行测试)
I tried using navigational properties and based on the description came up with below domain models. Please try this: (code is not tested though)
// Junction Table
public class Requests
{
[Key]
public int Id {get; set;}
public DateTime createdAt {get; set;}
[ForeginKey("RequestType")]
public int RequestTypeId {get; set;}
public virtual RequestType RequestType {get; set;}
[ForeginKey("MasterSections")]
public int MasterSectionID {get; set;}
public virtual MasterSections MasterSections {get; set;}
public int RequestStatusID {get; set;}
[ForeginKey("RequestStage")]
public int RequestStageID {get; set;}
public virtual RequestStage RequestStage {get; set;}
}
public class RequestStage
{
[Key]
public int RequestStageID {get; set;}
public string name {get; set;}
}
public class MasterSections
{
public int MasterSectionId {get; set;}
public string name {get; set;}
}
public class LocalCodes
{
[Key]
public int Id {get; set;}
[ForeginKey("MasterSections")]
public int MasterSectionId {get; set;}
public virtual MasterSections MasterSections {get; set;}
public string Description {get; set;}
public string name {get; set;}
}
如果要使用IQueryable，则无法在投影时使用.NET Framework的DATE函数来计算Age.您将需要使用与EF DATE相关的DBFunction.
If you want to use IQueryable then you can not calculate Age at the time of projection using .NET framework's DATE functions. You will need to use EF DATE related DBFunctions.
db.Requests.Include(r => r.RequestStage).Include(r => r.MasterSections)
.Where(r => r.RequestStage.name == "RequestSubmitted")
.Join(_dbContext.LocalCodes.Include(l => l.MasterSections), rqst => rqst.MasterSectionID, lc => lc.MasterSectionId,
(rt, lc) => new SectionResponse
{
Section = lc.Name,
Description = lc.Description,
CreatedBy = "",
Type = rt.Name.ToString(),
Status = rt.rs.Name,
/* Age property can not be done from within Queryable as .Date is not available in EF. If you want this, convert the query to enumerable and then project.*/
// Age = (DateTime.Now.Date - re.CreatedAt.Date).TotalDays.ToString() + "Days"
}).ToList();
通过转换为Enumerable，您可以按如下所示的时间投影计算年龄:
By converting to Enumerable, you can calculate Age at the time projection like below:
db.Requests.Include(r => r.RequestStage).Include(r => r.MasterSections)
.Where(r => r.RequestStage.name == "RequestSubmitted")
.Join(_dbContext.LocalCodes.Include(l => l.MasterSections), rqst => rqst.MasterSectionID, lc => lc.MasterSectionId,
(rt, lc) => new {rt = rt, rs = rt.RequestStage, lc = lc, ms = lc.MasterSections}).AsEnumerable().
Select(anonType => new SectionResponse
{
Section = anonType.lc.Name,
Description = anonType.lc.Description,
CreatedBy = "",
Type = anonType.rt.Name.ToString(),
Status = anonType.rs.Name,
Age = (DateTime.Now.Date - anonType.rt.CreatedAt.Date).TotalDays.ToString() + "Days"
}).ToList();
基于对Request模型的更新，可以使用以下查询来实现:
Based on update to Request model, it can be achieved using below query:
db.Requests.Include(r => r.RequestStage).Include(r => r.RequestType).Include(r => r.LocalCodes.MasterSections)
.Where(r => r.RequestStage.name == "RequestSubmitted")
.Select(r => new {r = r, rt = r.RequestType, rs = rt.RequestStage, lc = r.lc, ms = r.lc.MasterSections}).AsEnumerable().
Select(anonType => new SectionResponse
{
Section = anonType.lc.Name,
Description = anonType.lc.Description,
CreatedBy = "",
Type = anonType.rt.Name.ToString(),
Status = anonType.rs.Name,
Age = $"{DateTime.Now.Date.Subtract(anonType.r.CreatedAt.Date).TotalDays} Days"
}).ToList();
这篇关于使用linq查询获取每行的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
