getting duplicate rows for each row using linq query

Question

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
  ...
  ...

I have inserted three rows into local code table at the same time three rows inserted into request table with requestStage as RequestSubmitted

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.

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

Another attempt:

(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.

Many thanks

Domain models :

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

without seeing the details in each table, its hard to say why that would occur. From experience, normally it has somethign to do with 1:many relationships. One thing to do would be to run the query in SQL or DB backend one table at a time and continue joining till you see whats going on. MasterSections might be the problem child. — Jawad
– Jawad, Commented Dec 10, 2019 at 3:26
The problem with the MasterSections table and LocalCodes table, if LocalCodes table has repeated MasterSectionId (same id) this will cause the duplicate record, because how the Requests table will map to the localcodes with multi mastersectionid? which local will represent the mastersection id? — Cyber Progs
– Cyber Progs, Commented Dec 10, 2019 at 3:28
i might be seeing double but even if you changed the query from lc.Name to rs.Name, the duplicates would still come from various Stages that might exists under MasterSections. Each of those master sections will display each.all localCodes — Jawad
– Jawad, Commented Dec 10, 2019 at 3:40
@Jawad could you please let me know where i am doing wrong with these datamodels — Glory Raj
– Glory Raj, Commented Dec 11, 2019 at 0:41

sam · Accepted Answer · 2019-12-10 15:43:53Z

1

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

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

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

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

edited Dec 10, 2019 at 15:43

answered Dec 10, 2019 at 3:47

sam

1,9951 gold badge11 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Glory Raj Over a year ago

Many thanks for the response, I updated my table structure i forgot to mention there is foreign key in request table that is storing localcode id as well. Could you please verify and please suggest any ideas

sam Over a year ago

Updated my post. Please use the last query.

Glory Raj Over a year ago

getting error at Include(r => r.LocalCodes.MasterSections) here

sam Over a year ago

Does LocalCodes have Foreign Key reference to MasterSections? If yes, is the property name matches with your DB Foreign Key name? Please update domain models accordingly. With the info that is provided here, I created models.

Glory Raj Over a year ago

i figured that out thank you , but only Requests table is having createdAt column and getting error here anonType.rt.CreatedAt.Date

|

Collectives™ on Stack Overflow

getting duplicate rows for each row using linq query

1 Answer 1

17 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Related