Include in EF C#

Question

I have three tables: Member, business and business address

I'm able to fetch the business with member using Include, but I'm trying to get businessAddress, it is asking for direct relationship i guess. Here is my models:

[Table("member")]
public partial class Member
{
    public Member()
    {
        Business = new HashSet<Business>();
       // BusinessAddress = new HashSet<BusinessAddress>();

    }

    [Key]
    public int memberId { get; set; }
    public int chapterid { get; set; }
    public string title { get; set; }
    public string firstname { get; set; }
    public string lastname { get; set; }
    public bool IsActive { get; set; }
    public string classification { get; set; }
    public int SortOrder { get; set; }
    public virtual ICollection<Business> Business { get; set; }

   // public virtual ICollection<BusinessAddress> BusinessAddress { get; set; }
}

Business

[Table("Business")]
public partial class Business
{
    public Business()
    {
        BusinessAddress = new HashSet<BusinessAddress>();
    }

    [Key]
    public int BusinessID { get; set; }
    public int? categoryid { get; set; }
    public int? subcategoryid { get; set; }
    public int memberid { get; set; }
    public string businessname { get; set; }
    public string dealingin { get; set; }

    public int? sortOrder { get; set; }

    [ForeignKey("memberid")]
    public Member Member { get; set; }

    public ICollection<BusinessAddress> BusinessAddress { get; set; }
}

BusinessAddress

[Table("BusinessAddress")]
public partial class BusinessAddress
{
    [Key]
    public int businessaddressid { get; set; }

    public int businessid { get; set; }
    [ForeignKey("businessid")]
    public virtual Business Business { get; set; }
    public string address { get; set; }
}

Below is my EF query:

var list = _dbContext.Member.Include("Business").OrderByDescending(x => x.SortOrder).ThenBy(x => x.firstname).ToList();

I want to get Member with business and BusinessAddresses. I have tried Include("BusinesADdress") that didn't work, Help me please.

Answer 1

Try using Include accepting lambda selector followed by ThenInclude:

var list = _dbContext.Member
    .Include(m => m.Business)
    .ThenInclude(b => b.BusinessAddress)
    .OrderByDescending(x => x.SortOrder)
    .ThenBy(x => x.firstname)
    .ToList();

Also you can just append all needed relations via . in the Include call but in general I would recommend against it cause the first approach is type safe:

var list = _dbContext.Member
    .Include("Business.BusinessAddress")
    .OrderByDescending(x => x.SortOrder)
    .ThenBy(x => x.firstname)
    .ToList();