Search code examples
c#.netlinqormtelerik

Using Telerik ORM it is throwing error when joining 2 tables from different contexts

I am trying to use a join in LINQ to join to tables that are in different contexts. When I join two tables in the same context it works, so I believe my join is OK, but when I join two tables that are in different contexts I get an error. Is it possible to join on two tables that are from different contexts?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using RmmDal.Contexts.RmmCrm;
using RmmDal.Contexts.LMS;
using Telerik.OpenAccess;

namespace ConsoleApplication_Test_ORM
{
    class Program
    {
        static void Main(string[] args)
        {
            RmmDal.Contexts.RmmCrm.RmmCrmContext dbContextRmmCrm = new RmmDal.Contexts.RmmCrm.RmmCrmContext();
            RmmDal.Contexts.LMS.LMS_000Context dbContextLMS = new RmmDal.Contexts.LMS.LMS_000Context();

            try
            {
                Guid LeadId = new Guid("9EF2874C-D37F-4503-A3D8-1A73774BFBBC");

                //This doesn't work, I think because it is using 2 seperate Contexts
                //I need this to work
                var Leads1 = from lo in dbContextLMS.Tbl_Loan_Appls
                             join la in dbContextRmmCrm.LeadApplications
                             on lo.Appl_No equals la.Appl_No
                             select new
                             {
                                 SSN = lo.Cust_SSN,
                                 TDCCustID = lo.Cust_ID
                             };

                //This works, I think because they are the same context
                var Leads2 = from lo in dbContextLMS.Tbl_Loan_Appls
                             join la in dbContextLMS.Tbl_Customers
                             on lo.Cust_ID equals la.Cust_ID
                             select new
                             {
                                 SSN = lo.Cust_SSN,
                                 TDCCustID = lo.Cust_ID
                             };

                var something = Leads1.FirstOrDefault();

                var something2 = Leads1.FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

Here is the error that is thrown:

An exception occured during the execution of 'Extent<RmmDal.Tbl_Loan_Appl>().Join(Extent<RmmDal.Contexts.RmmCrm.LeadApplication>(), lo => lo.Appl_No, la => la.Appl_No, (lo, la) => new <>f__AnonymousType0`2(SSN = lo.Cust_SSN, TDCCustID = lo.Cust_ID))'. Failure: Object reference not set to an instance of an object.

See InnerException for more details.

Complete Expression:

.Call System.Linq.Queryable.Join(
    .Constant<Telerik.OpenAccess.Query.ExtentQueryImpl`1[RmmDal.Tbl_Loan_Appl]>(Extent<RmmDal.Tbl_Loan_Appl>()),
    .Constant<Telerik.OpenAccess.Query.ExtentQueryImpl`1[RmmDal.Contexts.RmmCrm.LeadApplication]>(Extent<RmmDal.Contexts.RmmCrm.LeadApplication>()),

    '(.Lambda #Lambda1<System.Func`2[RmmDal.Tbl_Loan_Appl,System.Int64]>),

    '(.Lambda #Lambda2<System.Func`2[RmmDal.Contexts.RmmCrm.LeadApplication,System.Int64]>),

    '(.Lambda #Lambda3<System.Func`3[RmmDal.Tbl_Loan_Appl,RmmDal.Contexts.RmmCrm.LeadApplication,<>f__AnonymousType0`2[System.String,System.Int64]]>))

.Lambda #Lambda1<System.Func`2[RmmDal.Tbl_Loan_Appl,System.Int64]>(RmmDal.Tbl_Loan_Appl $lo) {
    $lo.Appl_No
}

.Lambda #Lambda2<System.Func`2[RmmDal.Contexts.RmmCrm.LeadApplication,System.Int64]>(RmmDal.Contexts.RmmCrm.LeadApplication $la)
{
    $la.Appl_No
}

.Lambda #Lambda3<System.Func`3[RmmDal.Tbl_Loan_Appl,RmmDal.Contexts.RmmCrm.LeadApplication,<>f__AnonymousType0`2[System.String,System.Int64]]>(
    RmmDal.Tbl_Loan_Appl $lo,
    RmmDal.Contexts.RmmCrm.LeadApplication $la) {
    .New <>f__AnonymousType0`2[System.String,System.Int64](
        $lo.Cust_SSN,
        $lo.Cust_ID)
}
Solution

  • Joining entities that come from two different contexts is not supported by design.
    The only way to join these data sets is to use in-memory join like Trust me - I'm a Doctor suggested.

    The brute force method can be just to call .ToList() on both context endpoints and then use the in-memory data in the join query. This will be inefficient and problematic since a lot of data will be put in memory and possibly discarded after the join is performed so expect really bad performance.

    A more efficient way will be page through the results from the left side and use a .Contains() method to filter out the "joined" records from the right side. 

    // Load a small fragment of leads in memory
var Leads1 = dbContextLMS.Tbl_Loan_Appls.Skip(0).Take(10).ToList();
// find the IDs
var leadIds = Leads1.Select(l= > l.Appl_No);
// filter out only the matching applications
var applications = dbContextRmmCrm.LeadApplications
                                  .Where(a=> leadIds.Contains(a.Appli_No))
                                  .Select(a=> new { SSN = a.Cust_SSN, TDCCustID = а.Cust_ID });

    Paging is required in order to work with a small subset of data so the .Contains() clause can be safely translated into an SQL IN clause. You will have to wrap the code snippet in a loop and increment the Skip() and Take() parameters accordingly.