I am customising nopCommerce to be able to assign countries to different regions, as we have different pricing for different parts of the world.

I am trying to perform in LINQ to entities what in SQL would be a fairly simple operation, namely three left outer joins.

We wish to return a Country entity object that has populated Region and Currency entity properties.

According to my reading, the following should work, but it doesn't.

var query = from c in _context.Countries
            from cr in c.NpRegions
            from crc in cr.NpCurrency
            where
            (String.IsNullOrEmpty(countryName) || c.Name.Contains(countryName)) &&
            (regionId.Equals(0) || cr.RegionId.Equals(regionId))
            orderby c.Name ascending
            select new Country { Region = cr, Region.Currency = crc };
var countries = new PagedList<Country>(query, pageIndex, pageSize);


Region is a custom property I've added to the nopCommerce Country class to contain the Region reference. This is necessary because I've related Countries and regions through a link table in order to ensure that the base Nop_Country table is not changed (i.e. rather than adding a foreign key field to Nop_Country). Essentially, this means that region is a navigation property of Country.

In the Country entity (NopBusinessLogic\Directory\Country.cs) I've added:

        
        public virtual ICollection<MyCompany.NopCommerce.BusinessLogic.Region> NpRegions { get; set; }

        public MyCompany.NopCommerce.BusinessLogic.Region Region { get; set; }


Region.Currency is a similar property I've created in the Region class to reference the base nopCommerce Currency entity class.

The database structure should be obvious from this SQL version of what I wish to obtain:


SELECT  c.*, cr.*, crc.*
FROM    Nop_Country c LEFT JOIN Region_Country_Mapping rcm ON
        c.CountryId = rcm.CountryId LEFT JOIN Region cr ON
        rcm.RegionId = cr.RegionId LEFT JOIN Nop_Currency crc ON
        cr.CurrencyId = crc.CurrencyId


Any help, links, thoughts would be appreciated.