Multiple Lightspeed context in single LINQ query not working

 I have two lightspeed models and unitofwork for each i.e. Basisbeheer and Wegbeheer. I have defined different connection strings to each lightspeed model unit of work. But when I am querying with both contexts, I am not getting the desired result. Following is the LINQ query for the same:

 var result = from Globalevakken in GeneralFunctions.LightSpeedWegDataContext.WbGlobalevakkens.OrderBy(g => g.GlobVakcode)

 join Geoobject in GeneralFunctions.LightSpeedDataContext.BbGeoobjects on Globalevakken.Id equals Geoobject.Id

 join Positie in GeneralFunctions.LightSpeedDataContext.BbPosities on Geoobject.PosId equals

Positie.Id

 join Straat in GeneralFunctions.LightSpeedDataContext.BbStraats on Positie.StrId equals

Straat.Id

 join Gebied in GeneralFunctions.LightSpeedDataContext.BbGebieds on Positie.GebId equals

Gebied.Id

 where Straat.StrNaamOfficieel == strStreetName

{

ID = Globalevakken.Id.ToString(),

Description = Globalevakken.GlobVakcode.ToString()

};

I have checked and confirmed the connection string of both of the contexts but its seems to me that above query is still being performed on a single database.

Can anyone help me out to resolve the issue?

Thanks,

Kaushal

 

 select new  ComboBoxItem

 

Hello Kaushal,

The problem is that you are trying to join across two databases (or at the very least two connections).  The LINQ join keyword gets translated to a SQL JOIN, and you can't perform a SQL JOIN from one database to another.

We should probably raise an error in this case rather than incorrectly assuming that all queries apply to the same database.

Probably the best you can do is load the result sets from each collection using ToList() or AsEnumerable(), then join the result sets on the client using LINQ to Objects.

Hello Ivan,

Thanks for your answer. From the SQL itself, we can query between multiple databases e.g. AA.Table1.Field1 = BB.Table2.Field1 so its bit surprising for me that we can not execute the same using lightspeed.

As per your suggestion, we should get a resultset from one collection (i.e. a lightspeed context) and join it with resultset of another collection (secondary lightspeed context). I appritiate if you could post the solution of above query using your suggestion.

Thanks once again.

If your database supports it, you can query between multiple databases in LightSpeed using IUnitOfWork.FindBySql or by writing a stored procedure.  You might also be able to do it by using a single unit of work but mapping the entities into their respective databases using the Schema setting e.g. mapping Table2 into schema BB.

Alternatively as mentioned you can do it by materialising both collections and using LINQ to Objects e.g.

var result = from Globalevakken in GeneralFunctions.LightSpeedWegDataContext.WbGlobalevakkens.OrderBy(g => g.GlobVakcode).ToList()

  join Geoobject in GeneralFunctions.LightSpeedDataContext.BbGeoobjects.ToList() on Globalevakken.Id equals Geoobject.Id

... etc...

Note the use of ToList() to materialise each collection.  Be warned that this can result in large collections and expensive selects if you have a large database -- in that case you will almost certainly want to fall back on FindBySql or a stored procedure.

(By the way, the reason we can't join across multiple UOWs is that each UOW is a business transaction -- a database session, not just a database identifier.  Even with handcoded SQL you can't query across two database sessions: you have to establish a session [a connection] and then within that session you can query across multiple schemas/databases.  This is why mapping the schema might work.)

 

Can you please give more explanation about IUnitOfWork.FindBySQL?

Have a look at:

http://www.mindscapehq.com/documentation/lightspeed/Working-with-Database-Providers/Low-Level-Database-Access

http://www.mindscapehq.com/Help/LightSpeed/html/M_Mindscape_LightSpeed_IUnitOfWork_FindBySql__1.htm

The latter includes a brief sample demonstrating the usage.  Let us know if these don't answer your question!

 Dear Ivan,

Thanks for your valuable suggestions. Following is my pointwise reply:

1. IUnitOfWork.FindBySQL

I can not use this approach because first I don't want to hard code the queries second sometimes schema name can be different for connected databases so even SQL command may not give the good result.

2. Materialising collections and use in LINQ

As you've also mentioned that it could be time consuming in large collections, so this approch is also not good for us because we are dealing with very large databases. I have checked by implementing the same and its taking consuming time more than10 times compared to previous.

Now, I have splitted both the collections in different results. First I looked into the first collection to get the result on spacific criteria. Now if result is positive then I sored the resultset in a list and use that list to query the next collection and its working really fast as I expected. Following is the updated code:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

var

 

result =

 

from Geoobject in GeneralFunctions

.LightSpeedDataContext.BbGeoobjects

 

join Positie in GeneralFunctions.LightSpeedDataContext.BbPosities on Geoobject.PosId equals

Positie.Id

 

join Straat in GeneralFunctions.LightSpeedDataContext.BbStraats on Positie.StrId equals

Straat.Id

 

join Gebied in GeneralFunctions.LightSpeedDataContext.BbGebieds on Positie.GebId equals

Gebied.Id

 

where

Straat.StrNaamOfficieel == strStreetName

 

select

Geoobject.Id;

 

if

(result.Count() > 0)

iObjID = result.ToList();

 

else

 

return new List<ComboBoxItem

>();

 

var

result1 =

 

from Globalevakken in GeneralFunctions

.LightSpeedWegDataContext.WbGlobalevakkens.OrderBy(g => g.GlobVakcode).ToList()

 

where

iObjID.Contains(Globalevakken.Id)

 

select new

ComboBoxItem

{

ID = Globalevakken.Id.ToString(),

Description = Globalevakken.GlobVakcode.ToString()

};

 

if

(result.Count() > 0)

 

return

result1.ToList();

 

else

 

return new List<ComboBoxItem

>();

Looking to the above aproach, I would like to have your valuable comments so that I can proceed further.

Thanks and kind regards,

Kaushal Sharma

Hi Kaushal,

That looks fine.  You could consider tweaking the "if Count() == 0 ... else ToList()" pattern.  This currently results in two queries to the database -- one COUNT, followed by a SELECT if the count is nonzero.  Whereas e.g.

iObjID = result.ToList();
if (iObjID.Count == 0)
  return new List<ComboBoxItem>();

avoids the COUNT query and performs only the SELECT.  However this probably isn't important unless database queries are slow or performance is ultra-critical.