sql server - How do I write a C# list query accross 3 tables in 2 different Azure SQL Databases -

i have need add code feed existing code list of object names go pull azure blob store. gotcha table pulling list table of blob names in 1 sql azure db (admindb) , actual list need construct aggregate of 3 tables in sql azure db (runtimedb) cannot join query across 2 since azure's virtualized dbs don't allow that.

furthermore performance reasons runtimedb doesn't use blobnames instead uses internalid has looked in intermediary table in runtimedb

so have following c# definitions

 public partial class admindb : dbcontext  {      public virtual dbset<missingobjnames> missingobjects {get; set;}      // contains fields uchar(16) blobid;  }  public partial class runtimedb: dbcontext  {      public virtual dbset<myobjects> myobjects {get; set;}      // contains fields int internalid, int blobtype uchar() objectname, uchar() objectdesc.      public virtual dbset<idmap> idmap {get; set}     // contains fields int internalid, uchar(16) blobid   } 

so i've given on elegant solution , trying brute force since know @ list contain 4k-10k entries (this db cleannup code)

i've got access each db defined as

var runcontext = new runtimedbcontextcontainer(); var admincontext = new admindbcontextcontainer(); 

then go pull list of blob names

var  missingobjlist = (admincontext.missingobjects.select( ss =>  ss.blobid )).tolist();  var listinternalids = new list<int.(); foreach ( myblobid in missingobjlist ) {     int blobinternalid = (from r in context.idmap                                r.blobid == myblobid                                select r.internalid).first();      missingobjlist.add(blobinternalid); } 

so far end list of internal ids pull myobjects table

now i've got

public class objectdataitem {     public int internalid {get; set;}     public int? blobtype {get; set;}     public string objectname {get; set;}     public string objectdesc {get; set;} } 

so code goes on try , build list of missing objects (which rest of update code expects) iterating through list of internalids . yes know - dumb brute force code , because i'm n00b @ using linq , 3 days of msdn tutorials hasn't made me smarter (i'm fixing inherited code)

var listblobs = new list<objectdataitem>();   foreach ( var missingblobid in missingobjlist )  {     iqueryable <myobjects> objquery = (from o in runcontext.myobjects                                            o.interalid = missingblobid                                            select o);       var thisobj = new {objquery.internalid,                          objquery.blobtype,                         objquery.objname,                         objquery.objdesc};       listblobs.add(thisobj);   } 

these last 2 causing me syntax error. intellisense tells me objquery not contain definitions of objectdataitem i'm enumerating

and i'm of n00b figure out i've done wrong

your call create thisobj needs typed. e.g.:

 var thisobj = new objectdataitem() {internalid = objquery.internalid,                      blobtype = objquery.blobtype,                     objectname = objquery.objname,                     objectdesc = objquery.objdesc}; 

as side note, i'd recommend consistency in naming things - though throw-away script. public properties / fields capitalized, whereas internal fields / local variables lower-cased starts; having field name objname in 1 place , objectname in confusing.