Accessing the actual data contexts...

wizaerd · December 2016

I know there's a tricky way to do a LINQ query across two databases on the same table, so this is not that same question. Let's get that right out of the way first.

What I want to do is programmatically create two data contexts, to have their queries run individually to LIST, and then join them (the LIST) together. I have had no problem doing this in C#, and I always end up writing these little one-off console apps to perform this, when I'd just much rather script it out in LINQPad.

So I know the a query in LINQPad has it's own data context. Fine, but how do I go about actually create a new context programmatically and using it within the query?

Something like this (I know this is not the most efficient code, it was thrown together as an example):


            List<GarnishmentData> gd;
            List<Document> doc;

            // use the WellandData context first
            using (var welland = new WellandExport())
            {
                gd = (from g in welland.EMPLOYEE_GARNISHMENTS
                      join epd in welland.EMPLOYEE_PERSONAL_DETAILS on g.EE_ID equals epd.EE_ID
                      join ecd in welland.EMPLOYEE_CLIENT_DETAILS on epd.EE_ID equals ecd.EE_ID
                      join pay in welland.EMPLOYEE_PAY_DETAILS on ecd.u2_id equals pay.u2_id
                      where ecd.Client_ID == 10659 && ecd.EE_Status_Code == "A"
                      // don't really need to use the GarnishmentData class, but it's convienent
                      select new GarnishmentData()
                      {
                          ID = g.EE_ID,
                          SSN = pay.EE_SSN.Replace("-", "")
                      }).ToList();

                // calling the two list will actually execute the query and populate the list.  Without it, the data isn't retrieved until
                // the collection is accessed.  We cannot join a memory object to a DB object, so we call ToList() to resolve the query and
                // have the data in memory for joining later on...
            }

            // then get the document data from a different data context 
            // different database server altogether
            using (var garnish = new Garnishments())
            {
                doc = garnish.Documents.ToList();
                // again calling the ToList so the query finalizes and the data is in memory
            }

            // now we can join the two in memory objects to find specific filenames now
            // BTW, this is a LEFT JOIN
            var gQuery = (from g in gd
                          join d in doc on g.SSN equals d.SSN into gs
                          from d in gs.DefaultIfEmpty()
                          select new GarnishmentData()
                          {
                              ID = g.ID,
                              SSN = g.SSN,
                              Filename = d == null ? "(no file found)" : d.GarnishmentFileName
                          }).ToList();

            foreach (var thisRec in gQuery)
            {
                Console.WriteLine($"{thisRec.ID} - {thisRec.SSN} - {thisRec.Filename}");
            }

            Console.ReadKey();

wizaerd · December 2016

Actually, I just went ahead and created a VS2015 DLL with the contexts, then just used them in LINQPad... I suppose you can just delete or skip this message altogether...

Accessing the actual data contexts...

Comments