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.