Pull data from multiple tables in one SQL query using LINQ and Entity Framework

Question

Hi guys i am trying to pull data from multiple tables in one SQL query using LINQ and Entity Framework. I need to get the details from the database.

i had refereed to this How to filter JSON array in C# I am not able to achieve this. help from any one will be appreciated.

public partial class Regions
    {
        public Regions()
        {
            Projects = new HashSet<Projects>();
            RegionUserRelation = new HashSet<RegionUserRelation>();
        }

        public int RegionId { get; set; }
        [Required]
        public string RegionName { get; set; }
        [Required]
        public string RegionFullname { get; set; }
        [Required]
        public int RegionTypeId { get; set; }
        public string RegionMisc { get; set; }

        public RegionType RegionType { get; set; }
        public ICollection<Projects> Projects { get; set; }
        public ICollection<RegionUserRelation> RegionUserRelation { get; set; }
    }

public partial class Projects
    {
        public Projects()
        {
            Sections = new HashSet<Sections>();
        }

        public Guid ProjectId { get; set; }
        [Required]
        public string ProjectName { get; set; }
        [Required]
        public string ProjectCode { get; set; }
        public string ProjectDesc { get; set; }
        [Required]
        public int RegionId { get; set; }
        [Required]
        public int EpsgId { get; set; }

        public Epsg Epsg { get; set; }
        public Regions Region { get; set; }
        public ICollection<Sections> Sections { get; set; }
    }


public partial class Sections
    {
        public Sections()
        {
            Flights = new HashSet<Flights>();
        }

        public Guid SectionId { get; set; }
        [Required]
        public string SectionName { get; set; }
        public string SectionDesc { get; set; }
        [Required]
        public Guid ProjectId { get; set; }

        public Projects Project { get; set; }
        public ICollection<Flights> Flights { get; set; }
    }

Expected output is Like

{ "Regions" : 
  [ { 
        "region_id" : "2012",
        "region_name" : "region1",
        "region_fullname" : "21700",
        "region_type" : "MERCH",
            "Projects" : [ 
               {     "project_id" : 2341,
                     "project_name " : "DXY",
                     "project_code " : "21700",
                     "sections" : [
                                { "section_id" : 4356,
                                "section_name" : sec_name,
                                "section_description" : sec_dis},
                                { "section_id" : 476,
                                "section_name" : sec_name2,
                                "section_description" : sec_dis2},
                                { "section_id" : 908,
                                "section_name" : sec_name3,
                                "section_description" : sec_dis3}
                            ]
                },
            "Projects" : [ 
               {     "project_id" : 1321,
                     "project_name " : "Proj_2",
                     "project_code " : "01700",
                     "sections" : [
                                { "section_id" : 4356,
                                "section_name" : sec_name,
                                "section_description" : sec_dis},
                                { "section_id" : 476,
                                "section_name" : sec_name2,
                                "section_description" : sec_dis2},
                                { "section_id" : 908,
                                "section_name" : sec_name3,
                                "section_description" : sec_dis3}
                            ]
                },
            ]
  },
  { 
        "region_id" : "2013",
        "region_name" : "region3",
        "region_fullname" : "21330",
        "region_type" : "MERCH",
            "Projects" : [ 
               {     "project_id" : 2341,
                     "project_name " : "DXY",
                     "project_code " : "21700",
                     "sections" : [
                                { "section_id" : 4356,
                                "section_name" : sec_name,
                                "section_description" : sec_dis},
                                { "section_id" : 476,
                                "section_name" : sec_name2,
                                "section_description" : sec_dis2},
                                { "section_id" : 908,
                                "section_name" : sec_name3,
                                "section_description" : sec_dis3}
                            ]
                },
            "Projects" : [ 
               {     "project_id" : 1321,
                     "project_name " : "Proj_2",
                     "project_code " : "01700",
                     "sections" : [
                                { "section_id" : 4356,
                                "section_name" : sec_name,
                                "section_description" : sec_dis},
                                { "section_id" : 476,
                                "section_name" : sec_name2,
                                "section_description" : sec_dis2},
                                { "section_id" : 908,
                                "section_name" : sec_name3,
                                "section_description" : sec_dis3}
                            ]
                },
            ]
  },
  { 
        "region_id" : "2012",
        "region_name" : "region2",
        "region_fullname" : "24300",
        "region_type" : "MERCH",
            "Projects" : [ 
               {     "project_id" : 2341,
                     "project_name " : "DXY",
                     "project_code " : "21700",
                     "sections" : [
                                { "section_id" : 4356,
                                "section_name" : sec_name,
                                "section_description" : sec_dis},
                                { "section_id" : 476,
                                "section_name" : sec_name2,
                                "section_description" : sec_dis2},
                                { "section_id" : 908,
                                "section_name" : sec_name3,
                                "section_description" : sec_dis3}
                            ]
                },
            "Projects" : [ 
               {     "project_id" : 1321,
                     "project_name " : "Proj_2",
                     "project_code " : "01700",
                     "sections" : [
                                { "section_id" : 4356,
                                "section_name" : sec_name,
                                "section_description" : sec_dis},
                                { "section_id" : 476,
                                "section_name" : sec_name2,
                                "section_description" : sec_dis2},
                                { "section_id" : 908,
                                "section_name" : sec_name3,
                                "section_description" : sec_dis3}
                            ]
                },
            ]
  },
] }

you will get lot of downvotes because of no code of controllers or data later check this official docs tutorial learn.microsoft.com/en-us/aspnet/core/tutorials/… — Pribina
– Pribina, Commented Apr 27, 2020 at 8:38
@Pribina Is it possible to achieve this. I checked many tutorials it didn't worked for me.Please help me to come out of this. — Mohammed zuhair
– Mohammed zuhair, Commented Apr 27, 2020 at 8:46
It should be possible, just create DTO classes - in VS you can use paste special Json as class and then crete query to map all properties — Pribina
– Pribina, Commented Apr 27, 2020 at 8:56
@Pribina Can i get any kind of help .. i am completely new this concept — Mohammed zuhair
– Mohammed zuhair, Commented Apr 27, 2020 at 9:07
which steps doesnt work for you retrieving data from db, or mapping it? can you add code how you retrieve data from db? and what you tried in controller? — Pribina
– Pribina, Commented Apr 27, 2020 at 9:11

Pribina · Accepted Answer · 2020-04-28 06:31:59Z

0

By your comments specifications: use Include and then ThenInclude. You have navigation properties in your model so you can use it in your advantage - reference link - https://learn.microsoft.com/en-us/ef/core/querying/related-data

using (var context = new YourContex())
{
    var regions = context.Regions
        .Include(blog => blog.Projects)
            .ThenInclude(post => post.Sections)
        .ToList()
        .Select(region => new YourDTO(){}); //you need to specify this by your expectation or just omit it and keep original model 
}

answered Apr 28, 2020 at 6:31

Pribina

7801 gold badge7 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Pull data from multiple tables in one SQL query using LINQ and Entity Framework

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related