hepsubah (3) [Avatar] Offline
#1
I'm trying to do the following
--------------------------------------------------------------------------------------------------------------
public static void JoinTest1()
{
EngDataDataContext ed = new EngDataDataContext();
FleetHDDataContext fh = new FleetHDDataContext();

var tickets = from u in ed.Units
join fhdt in fh.FIMS_Command_Issue_Trackings
on new { HNum = Convert.ToInt16(u.Hull), HType = u.TYPE } equals
new { HNum = Convert.ToInt16(fhdt.Hull_Number), HType = fhdt.Hull_Type }
select new
{
Hull = u.TYPE + "-" + u.Hull,
HDTID = fhdt.Command_Issue_ID,
HDHull = fhdt.Hull_Number
};

foreach (var t in tickets)
Console.WriteLine("{0} -> {1}{2}", t.Hull, t.HDTID, t.HDHull);

}
----------------------------------------------------------------------------------------------------------

which attempts to perform a join against 2 different SQL Databases (different machines)

I get the following error at runtime (on the foreach)
Invalid Operator Exception: "The query contains references to items defined on a different data context."

Is there any reason why I wouldn't be able to do a query across datacontexts?

Am I doing something stupid?

Thanks in advance,

Doug Stanley
jwooley (123) [Avatar] Offline
#2
Re: Trying to join across 2 datacontexts
You should be able to fetch both resultsets using .ToList and then join the in memory object structures after that. If you know that one of the sides of the query will return less values, consider casting its results .ToList and then using the Contains method in the Where clause against the database with the larger list. This only works where the first set is relatively small.

LINQ to SQL in this case is preventing the query from translating the statement to a single server where it can't guarantee that the target server knows anything about the other server, particularly since you are not passing the connection information of the second server to the first server.

Jim
hepsubah (3) [Avatar] Offline
#3
Re: Trying to join across 2 datacontexts
Thanks Jim, I think your first suggestion (using .ToList and then casting) will work for me, but I'm not sure I understand the second paragraph, and I'd like to know why in general this doesn't work.

How would I pass the connection information of the second server to the first?

Shouldn't the DataContext and the metadata from the context provide enough information to manifest this query?

I've got to think this has been done before, if it is possible at all.

Thanks again for the suggestion and if you could enlighten and obviously ignorant colleague, I'd greatly appreciate it.

Doug
jwooley (123) [Avatar] Offline
#4
Re: Trying to join across 2 datacontexts
I wasn't recommending that you would send the metadata to the second server. Using the Contains method in the Where clause, you would pass an array (series of parameters actually) into the second database based on the results from the smaller result set.

I have a post discussing Contains at http://www.thinqlinq.com/Default/Use_the_new_LINQ_Contains_extension_method_for_the_SQL_IN_clause.aspx
hepsubah (3) [Avatar] Offline
#5
Re: Trying to join across 2 datacontexts
Great - That works.

But what if data from both return sets is relatively large?

and I (my apologies) still don't really understand why this didn't work the way I laid it out in the first place.

Are you saying that when the LINQ code is translated to a native query (SQL in this case), that it is only allowed 1 connection per statement?

Thanks again for all the help. I can pretty much solve my specific problem right now, I just have the larger issue of truly understanding what is going on.

Doug
sheikhtahir.mca (4) [Avatar] Offline
#6
Re: Trying to join across 2 datacontexts
Hi Jwooley, I am having the same issue with the below code. Can you help please...!


var Query = from objMCMastCase in objClmStatusDC.MCMastCases
from objCMast in objClmStatusDC.CMasts
from objUWorkItemAttrib in objNotDC.uWorkItemAttribs

where objCMast.AgentCd == "D10014" && objUWorkItemAttrib.ActID == "1005"

join objMCMast in objClmStatusDC.MCMasts on objMCMastCase.ClmNoKey equals objMCMast.ClmNoKey
join objUWorkItem in objNotDC.uWorkItemAttribs on objMCMastCase.WorkRefNo equals objUWorkItem.WorkRefNoKey
join objAct in objAgtWrkLstDC.ACTIVITies on objUWorkItemAttrib.ActID equals objAct.ActivityID.ToString()
join objCMst in objClmStatusDC.CMasts on objMCMastCase.ClmNoKey equals objCMst.ClmNoKey

select new
{
objMCMastCase.ClmNoKey,
objMCMastCase.CaseNoKey,
objAct.ProcessID,
objUWorkItem.ActID,
objUWorkItem.ActName,
objCMst.Ins,
objMCMast.VehNo,
objMCMastCase.ClmTypeCd,
objMCMastCase.UserCdKey,
objMCMastCase.ClmCaseStatusCd
};
gvClmCases.DataSource = Query;
gvClmCases.DataBind();

Regards
Tahir
jwooley (123) [Avatar] Offline
#7
Re: Trying to join across 2 datacontexts
Tahir,

It sounds like you may be trying to do too much with your heterogeneous join. If you can limit one side of the join to a handful of records, you can change the join to a contains clause, however if you are unsure of the number of records on each side individually, you will need to rethink your query structures. Try simplifying the query and working your way back out piece by piece to get the results you want.

Jim
sheikhtahir.mca (4) [Avatar] Offline
#8
Re: Trying to join across 2 datacontexts
Thanks for the reply Jim,

I dont know in advance the number of records and also I cant change the query, as its already a stored proc that is working on production. I am trying to convert it to linq.

Regards
Tahir
jwooley (123) [Avatar] Offline
#9
Re: Trying to join across 2 datacontexts
Do you really need 2 contexts in this case? Are you querying against two separate databases or one?

If you are using two contexts but a single database, remember that just because a type is exposed by one context doesn't mean you can't consume it through a different context using context.GetTable<T>. The mapping is defined on the class, not the context, thus you can consume it through any context you want.

If it is coming from different databases, I suspect you are using a linked server and encapsulating the functionality in your stored proc. In that case, I would recommend continuing to use the stored proc and consuming that through LINQ (see chapter 8 for a discussion of using stored procs in LINQ).

Jim
sheikhtahir.mca (4) [Avatar] Offline
#10
Re: Trying to join across 2 datacontexts
Hi Jim,

Yes I am ofcourse using two different databases. The MCMastCase and CMast tables are in one database and uWorkItemAttrib in a different one.

So you mean it is not possible in Linq to Join 2 datacontexts(seperate databases)...???

Till now I am using that way you suggested. I have written a stored proc for that and I am consuming it in Linq, but I was searching for the whole solution in Linq only as I am not supposed to write any stored proc in my application.

Anyway thanks a lot for help.

Regards
Tahir