Query many-to-many relationships with Entity Framework

Question

I am new to the C# Entity Framework. I created three classes - Country - Area - Subarea

There is a many-to-many relationship between Country and Area. There is another many-to-many relationship between Area and Subarea.

A Country can contain many Areas, but there also Areas that belong to more than one country). The same for Area and Subarea.

I created the respective classes and the database tables have been automatically created. The tables for CountryAreas and SubAreaAreas have been created too, so all looks good. Also the foreign keys look fine.

I am able to write data to the tables via (see below).

I am now struggling getting all the Countries with the respective Areas and Subareas selected from the database.

I read a couple of post and it looks like I am lacking basic knowledge about LINQ queries and the Entity Framework.

public class Country
{
    #region attributes
    [Key]
    public string Name { get; set; }
    public List<Area> Areas { get; set; } // virtual enabled lazy loading
    #endregion
}

public class Area
{
    #region attributes
    [Key]
    public string Name { get; set; }
    public virtual List<SubArea> Subareas { get; set; }
    public virtual List<Country> Countries { get; set; }
    #endregion
}

public class SubArea
{
    #region attributes
    [Key]
    public string Name { get; set; }
    public virtual List<Area> Areas { get; set; }
    #endregion
}

public class LocationScoutContext : DbContext
{
    public LocationScoutContext()
        : base("name=LocationScout")
    {
    }

    public DbSet<Country> Countries { get; set; }
    public DbSet<Area> Areas { get; set; }
    public DbSet<SubArea> SubAreas { get; set; }

}


// *** reading the data works fine ***
using (var db = new LocationScoutContext())
{
   db.Countries.Add(newCountry);
   db.SaveChanges();
}


// *** I tried this ***
var allCountries = new List<Countries>();
using (var db = new LocationScoutContext())
{
   var query = from c in db.Countries select c;
}

foreach (var c in query)
{
   allCountries.Add(c);
}

I tried something like shown above but this obviously does not do any join and just gives me the names of the Countries with empty Areas and SubAreas.

Any help is appreciated :-)

Side note: You don't need to add the countries one by one from the query. `allCounties = db.Countries.ToList();` would do just fine. — 41686d6564 stands w. Palestine, Jan 16 '19 at 15:41

TanvirArjel · Accepted Answer · 2019-01-16T15:45:42.160

4

Try as follows. This will give all the countries with its areas and subareas.

For EF 6.x:

using (var db = new LocationScoutContext())
{
   var countries = db.Countries.Include(c => c.Areas.Select(a => a.SubAreas)).ToList();
}

For EF Core:

using (var db = new LocationScoutContext())
{
   var countries = db.Countries.Include(c => c.Areas).ThenInclude(a => a.SubAreas).ToList();
}

edited Jan 16 '19 at 15:45

answered Jan 16 '19 at 15:40

TanvirArjel

30,049
14
78
114

Please note that `ThenInclude` is [only supported in EF Core](https://stackoverflow.com/a/10823103/4934172). – 41686d6564 stands w. Palestine Jan 16 '19 at 15:43
I just tried the code above and it give me a compiler error Error CS1660 Cannot convert lambda expression to type 'string' because it is not a delegate type – Bill Miller Jan 16 '19 at 15:48
What is the compiler error? Look at I have used `SubAreas` not `Subareas`. – TanvirArjel Jan 16 '19 at 15:48
Error CS1660 Cannot convert lambda expression to type 'string' because it is not a delegate type – – Bill Miller Jan 16 '19 at 15:49
Okay! Let me check – TanvirArjel Jan 16 '19 at 15:49
1

@BillMiller [You probably need `using System.Data.Entity;`](https://stackoverflow.com/questions/10822656/entity-framework-include-multiple-levels-of-properties/10823103#comment26153602_10823103) if you don't have it already. – 41686d6564 stands w. Palestine Jan 16 '19 at 15:50
Yeah the "using System.Data.Entity" was missing - I am now checking the EF 6.x solution – Bill Miller Jan 16 '19 at 15:53
@BillMiller What is your project version? Is it `ASP.NET Core` project or `ASP.NET MVC 5` project? – TanvirArjel Jan 16 '19 at 15:54
@TanvirArjel: How can I see if my project if is it ASP.NET Core project or ASP.NET MVC 5 project? – Bill Miller Jan 16 '19 at 15:56
Just tell me from the answer which query have you used? :) – TanvirArjel Jan 16 '19 at 15:58
The first one (without "ThenInclude") – Bill Miller Jan 16 '19 at 16:00
Then it is `ASP.NET MVC 5` :) – TanvirArjel Jan 16 '19 at 16:01

Query many-to-many relationships with Entity Framework

1 Answers1