Accessing the actual data contexts...

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();

Comments

  • 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...
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!