3rd party table(plugin tables) can't join with nop tables in LINQ

consider following code:

if (storeId > 0)
            {

                query = from t in query
                        join sm in _storeMappingRepository.Table on t.Id equals sm.EntityId into t_sm
                        from sm in t_sm.DefaultIfEmpty()
                        where !t.LimitedToStores || (sm.EntityName == "Plugin_Tables" && storeId == sm.StoreId)
                        select t;

                //only distinct items (group by ID)
                query = from t in query
                        group t by t.Id
                            into tGroup
                            orderby tGroup.Key
                            select tGroup.FirstOrDefault();
                query = query.OrderBy(t => t.Id);

            }

the exception will be thrown in the "query.OrderBy" method, you will see this error:
"The specified LINQ expression contains references to queries that are associated with different contexts"

any idea?

Your code is not complete. What is "query"? How is it initialized before? Where is it used? To what entity is it mapped (joined)?

I presume you're doing it in a plugin with some new custom entity attached to a new DbContext. In this case it's fine because EF does not support joining of entities (tables) from distinct context (DbContext). This is limitation of EF.

P.S. Please do not post development questions in "bug reports" (moved to Development forum)

Has anyone worked out how to JOIN a Nop core entity with a custom entity in a plugin (without changing the Nop core)?

I don't believe that you can because your objects and the nop objects live in 2 different contexts.  You have to store references to Nop objects in your own data but retrieve them using services instead of one big LINQ query.

OK. Cheers. I'll have to resort to that. Was hoping to avoid because of the overhead.

Is there no way of kinda "injecting" the nop datacontext for use in the plugin?

Since you cannot join tables from different contexts in Entity Framework, I was able to workaround a solution by filtering using a local List. Hope it helps. Check code in bold.

        /// <summary>
        /// Search galleries
        /// </summary>
        /// <param name="keywords">Keywords</param>
        /// <param name="searchDescriptions">A value indicating whether to search by a specified "keyword" in gallery descriptions</param>
        /// <param name="orderBy">Order by</param>
        /// <param name="pageIndex">Page index</param>
        /// <param name="pageSize">Page size</param>
        /// <param name="storeId">Store identifier; 0 to load all records</param>
        /// <returns>Gallery collection</returns>
        public virtual IPagedList<Gallery> SearchGalleries(string keywords, bool searchDescriptions,
            GallerySortingEnum orderBy, int pageIndex, int pageSize, int storeId = 0)
        {
            //galleries
            var query = _galleryRepository.Table;
            query = query.Where(g => !g.Deleted);

            //searching by keyword
            if (!String.IsNullOrWhiteSpace(keywords))
            {
                query = from g in query
                        where (g.Name.Contains(keywords)) ||
                              (searchDescriptions && g.ShortDescription.Contains(keywords)) ||
                              (searchDescriptions && g.FullDescription.Contains(keywords))
                        select g;
            }

            if (storeId > 0)
            {
                //Store mapping
                var storeQry = from sm in _storeMappingRepository.Table
                               where (sm.EntityName == "Gallery" && storeId == sm.StoreId)
                               select sm.EntityId;
                var storeMap = storeQry.ToList();

                //Store mapping
                query = from g in query
                        where !g.LimitedToStores || storeMap.Contains(g.Id)
                        select g;
            }

            //only distinct galleries (group by ID)
            //if we use standard Distinct() method, then all fields will be compared (low performance)
            //it'll not work in SQL Server Compact when searching galleries by a keyword)
            query = from g in query
                    group g by g.Id
                        into gGroup
                        orderby gGroup.Key
                        select gGroup.FirstOrDefault();

            //sort galleries
            if (orderBy == GallerySortingEnum.NameAsc)
            {
                //Name: A to Z
                query = query.OrderBy(g => g.Name);
            }
            else if (orderBy == GallerySortingEnum.NameDesc)
            {
                //Name: Z to A
                query = query.OrderByDescending(g => g.Name);
            }
            else
            {
                //actually this code is not reachable
                query = query.OrderBy(g => g.Name);
            }

            var galleries = new PagedList<Gallery>(query, pageIndex, pageSize);

            //return galleries
            return galleries;
        }

A simple workaround in to just use a stored procedure and map the output to a domain object in the plugin.

sample code of calling a stored proc:

var results = _dbContext.ExecuteStoredProcedureList<YourCustomObject>("dbo.your_stored_proc_name");

To help facilitate creating stored procedures when the plugin installs, what we do is add a .sql file to the plugin using the stored procedure and set the file as an embedded resource. We then read the embedded file into a string and execute it as part of the install script of our plugin's ObjectContext class.

//Get embedded resource as string
private string getEmbeddedResource(string FileName)
{
    var assembly = Assembly.GetExecutingAssembly();
    var resourceName = assembly.GetManifestResourceNames().Where(x => x.Contains(FileName)).FirstOrDefault();
    using (Stream stream = assembly.GetManifestResourceStream(resourceName))
    using (StreamReader reader = new StreamReader(stream))
    {
        return reader.ReadToEnd();
    }
}

//Calling the getEmbeddedResource function
Database.ExecuteSqlCommand(getEmbeddedResource("dbo.your_stored_proc_name"));