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):
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