Issue - returning three related entity objects from a LINQ to entities statement RRS feed

  • Question

  • Have been thrown into EF world by trying to customize the nopCommerce open source ecommerce product, which uses EF extensively.
    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 (Np = navigation property, PagedList is just a paginated extension of System.Collections.Generic.List).
    var query = from c in _context.Countries
                from cr in c.NpRegions
                from crc in cr.NpCurrency
                (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.
    Thursday, September 8, 2011 6:18 AM


All replies