I have a database which I gave details of it down below.

There are projects, also modules and versions which are related with projects. Although, every version doesn't contain every module.

How can I get those records with using Linq on Entity Framework 6.4 ?

Here is the JSON result that I want to get.

{
  "id": 1,
  "name": "Super Project",
  "versions": [
    {
      "id": 1,
      "name": "Basic",
      "availableModules": []
    },
    {
      "id": 2,
      "name": "Advenced",
      "availableModules": [
        {
          "id": 1,
          "name": "3 Users"
        },
        {
          "id": 2,
          "name": "50Gb Space"
        }
      ]
    }
    ....
    ....
    
  ]
}

Here is the database query:

SELECT 
    p.Id ProjectID, p.Name ProjectName, pv.Name VersionName, 
    pm.Name ModuleName
FROM
    Project p
INNER JOIN
    ProjectVersion pv ON pv.ProjectId = p.Id
LEFT JOIN 
    ProjectModuleAvailableVersions mav ON mav.ProjectVersionId = pv.Id
LEFT JOIN
    ProjectModule pm ON pm.ID = mav.ProjectModuleId

There is a screenshot of the results of that query:

There is the database structure if you want to try:

IF OBJECT_ID('ProjectModuleAvailableVersions') IS NOT NULL 
     DROP TABLE ProjectModuleAvailableVersions

IF OBJECT_ID('ProjectModule') IS NOT NULL 
     DROP TABLE ProjectModule

IF OBJECT_ID('ProjectVersion') IS NOT NULL 
     DROP TABLE ProjectVersion

IF OBJECT_ID('Project') IS NOT NULL 
     DROP TABLE Project


CREATE TABLE dbo.Project
(
    Id INT IDENTITY(1,1) NOT NULL,
    Name VarChar(100) NULL,
    CONSTRAINT PK_Project PRIMARY KEY CLUSTERED (Id)
)
GO

CREATE TABLE dbo.ProjectVersion
(
    Id INT IDENTITY(1,1) NOT NULL,
    ProjectId INT,
    Name Varchar(100) NULL,
    CONSTRAINT PK_ProjectVersion PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_ProjectVersion_ProjectId 
        FOREIGN KEY (ProjectId) REFERENCES Project (Id) 
            ON DELETE CASCADE
)
GO

CREATE TABLE dbo.ProjectModule
(
    Id INT IDENTITY(1,1) NOT NULL,
    ProjectId INT,
    Name Varchar(100) NULL,
    CONSTRAINT PK_ProjectModule PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_ProjectModule_ProjectId 
        FOREIGN KEY (ProjectId) REFERENCES Project (Id) 
            ON DELETE CASCADE
)
GO

CREATE TABLE dbo.ProjectModuleAvailableVersions
(
    Id INT IDENTITY(1,1) NOT NULL,
    ProjectVersionId INT,
    ProjectModuleId INT,
    CONSTRAINT PK_ProjectModuleAvailableVersions PRIMARY KEY CLUSTERED (Id)
)
GO

INSERT INTO Project Values ('Super Project')           -- ID: 1
INSERT INTO Project Values ('Fantastic Project')       -- ID: 2

INSERT INTO ProjectVersion Values (1, 'Basic')         -- ID: 1
INSERT INTO ProjectVersion Values (1, 'Advanced')      -- ID: 2
INSERT INTO ProjectVersion Values (2, 'Modern')        -- ID: 3
INSERT INTO ProjectVersion Values (2, 'Professional')  -- ID: 4
INSERT INTO ProjectVersion Values (2, 'Cloud')         -- ID: 5 

INSERT INTO ProjectModule Values (1, '3 Users')        -- ID: 1
INSERT INTO ProjectModule Values (1, '50Gb Space')     -- ID: 2
INSERT INTO ProjectModule Values (2, 'SyncData')       -- ID: 3 
INSERT INTO ProjectModule Values (2, 'Export Data')    -- ID: 4
INSERT INTO ProjectModule Values (2, 'Import Data')    -- ID: 5

INSERT INTO ProjectModuleAvailableVersions Values (2,1)  -- Advanced - 3 Users
INSERT INTO ProjectModuleAvailableVersions Values (2,2)  -- Advanced - 50Gb
INSERT INTO ProjectModuleAvailableVersions Values (3,3)  -- Modern - SynData
INSERT INTO ProjectModuleAvailableVersions Values (4,3)  -- Professional - SyncData
INSERT INTO ProjectModuleAvailableVersions Values (4,4)  -- Professional - ExprotData
INSERT INTO ProjectModuleAvailableVersions Values (5,3)  -- Professional - SyncData
INSERT INTO ProjectModuleAvailableVersions Values (5,4)  -- Professional - ExprotData
INSERT INTO ProjectModuleAvailableVersions Values (5,5)  -- Professional - ImportData

Edit: My classes are here

public class ProjectDto : IDto
{
  public int Id { get; set; }
  public string? Name { get; set; }
  public List<ProjectVersionDto>? Versions { get; set; }
}

public class ProjectVersionDto : IDto
{
  public int Id { get; set; }
  public string? Name { get; set; }
  public List<ProjectModuleDto> Modules { get; set; }
}

public class ProjectModuleDto : IDto
{
  public int ID { get; set; }
  public string? Name { get; set; }
}

And that's how I tried to do it.

using CrmContext context = new CrmContext();
var result = from p in context.Project
    select new ProjectDto()
    {
        Id = p.ID,
        Name = p.Name,
        Versions = (from pv in context.ProjectVesion
            where pv.Project == p.ID
            join av in context.ProjectModuleAvailableVersions on pv.ID equals av.Version
            select new ProjectVersionDto
            {
            Modules = (from pm in context.ProjectModule
                        where pm.Project == p.ID
                        join an in context.ProjectModuleAvailableVersions on pv.ID equals an.Version
                        select new ProjectModuleDto
                        {
                        ID = 0,
                        Name = null,
                        }
                ).ToList()
            }).ToList()
    };
return result.ToList();

CodePudding user response：

Your problem seems to me to be with join operations.

var result = from p in context.Project
select new ProjectDto()
{
    Id = p.Id,
    Name = p.Name,
    Versions = (from pv in context.ProjectVesion.OrderBy(o => o.ProjectId)
        where p.Id == pv.ProjectId
        select new ProjectVersionDto
        {
        Id = pv.Id,
        Name = pv.Name,
        Modules = (from pm in context.ProjectModule
            where pm.ProjectId == pv.ProjectId
            join pav in context.ProjectModuleAvailableVersions on pm.Id equals pav.ModuleId
            where pav.VersionId == pv.Id
            select new ProjectModuleDto
            {
            Id = pm.Id,
            Name = pm.Name,
            }).ToList()
        }).ToList()
};
  return result.ToList();

The order of nested objects is as follows;

ProjectDto > Versions > Modules

If you dont make a left join to the ProjectModuleAvailableVersions table, you will have no problems.

Have a nice day.