Merging Multiple IQueryables together - Silverlight
Peeps,
Im a born again virgin to Silverlight so please bear with me. I have two seperate DomainServices, pointing to two different SQL Database Servers. Within these Domain Services I have setup some IQueryables in each Domain Service.
I need to merge two IQueryables together from seperate DomainServices. Im sure this has got to be do-able.
Below are the two domain services.
I want to merge GetCustomCallLogs (from HEATLiveDomainService) together with GetTblCallsLogged (from HeatDomainService). The key in GetCustomCallLogs would be the CallID and the key in GetTblCallsLogged would be the RecID.
If its possible to do i understand i would need to create a new type to take into account any fields from the two joined tables.
Hopefully ive explained my scenario, and im not being dumb.
Thanks in advance
public class HEATLIVEDomainService : LinqToEntitiesDomainService<HeatIT9_LiveEntities> { // TODO: // Consider constraining the results of your query method. If you need additional input you can // add parameters to this method or create additional query methods with different names. // To support paging you will need to add ordering to the 'Assignees' query. public IQueryable<Assignee> GetAssignees() { return this.ObjectContext.Assignees; } // TODO: // Consider constraining the results of your query method. If you need additional input you can // add parameters to this method or create additional query methods with different names. // To support paging you will need to add ordering to the 'CallLogs' query. public IQueryable<CallLog> GetCallLogs() { // return this.ObjectContext.CallLogs.Where(c => DateTime.Parse(c.ClosedDate).Year == 2012 && c.CallStatus == "Closed" && c.ClosedBy.Length > 0); return this.ObjectContext.CallLogs.Where(c => c.CallStatus == "Closed" && c.ClosedDate.Substring(0, 4).Equals("2013")); } public IQueryable<CallLog> GetCallLogsLastThisYear() { return this.ObjectContext.CallLogs.Where(c => c.CallStatus == "Closed" && (c.ClosedDate.Substring(0, 4).Equals("2012") || c.ClosedDate.Substring(0, 4).Equals("2013"))); } public IQueryable<CustomCallLog> GetCustomCallLogs(string year) { var result = from i in this.ObjectContext.CallLogs join p in this.ObjectContext.Assignees on i.ClosedBy equals p.LoginID where i.CallStatus == "Closed" && i.ClosedDate.Substring(0, 4) == year select new CustomCallLog { Score = CallLog = i.CallID, Name = p.Assignee1, Yr = year, Mth = i.ClosedDate.Substring(5, 2), GroupName = p.GroupName }; return result; } public IQueryable<CustomClosedJobs> GetCustomClosedJobs() { var result = from i in this.ObjectContext.CallLogs where i.CallStatus == "Closed" && i.ClosedDate.Substring(0, 4) =="2012" group i by i.ClosedDate.Substring(5,2) into y select new CustomClosedJobs { Type = "heat", ClosedCalls = y.Count(), Mth =y.Key }; return result; } // TODO: // Consider constraining the results of your query method. If you need additional input you can // add parameters to this method or create additional query methods with different names. // To support paging you will need to add ordering to the 'Subsets' query. public IQueryable<Subset> GetSubsets() { return this.ObjectContext.Subsets; } } public class HEATDomainService : LinqToEntitiesDomainService<FEEDBACKPRDEntities1> { // TODO: // Consider constraining the results of your query method. If you need additional input you can // add parameters to this method or create additional query methods with different names. // To support paging you will need to add ordering to the 'qryStoringLogs' query. public IQueryable<qryStoringLog> GetQryStoringLogs() { return this.ObjectContext.qryStoringLogs.OrderBy(e => e.monthno); } public IQueryable<tblStoringLog> GetTop100Logs() { return this.ObjectContext.tblStoringLogs.OrderByDescending(e => e.responsetime).Take(100); } public IQueryable<tblStoringLog> GetLogCount(DateTime s, DateTime e) { return this.ObjectContext.tblStoringLogs.Where(x => x.responsetime >= s && x.responsetime <= e); } public IQueryable<qryStoringLog> GetLogs(int year, int mth) { return this.ObjectContext.qryStoringLogs.Where(e => e.Month.Equals(mth) && e.yr.Equals(year)); } public IQueryable<qryStoringLog> GetLogsForYear(int year) { return this.ObjectContext.qryStoringLogs.Where(e => e.yr.Equals(year)).OrderBy(e => e.monthno); } public DateTime GetFirstDate() { return (DateTime)this.ObjectContext.tblStoringLogs.OrderBy(e => e.responsetime).First().responsetime; } public DateTime GetLastDate() { return (DateTime)this.ObjectContext.tblStoringLogs.OrderByDescending(e => e.responsetime).First().responsetime; } public IQueryable<tblCallLogged> GetTblCallLoggeds() { return this.ObjectContext.tblCallLoggeds; } public IQueryable<tblStoringLog> GetTblStoringLogs() { return this.ObjectContext.tblStoringLogs; } [Query(IsComposable = false)] public IQueryable<qryStoringLog> GetTblStoringLogsStatus(int statusid) { return this.ObjectContext.qryStoringLogs.Where(e => e.statusid == statusid); } public IQueryable<stResponsesLife_Result> LifeTimeResponses() { return this.ObjectContext.stResponsesLife().AsQueryable(); } public IEnumerable<CustomLifetime> GetCustomLifeTime() { var result = from i in this.ObjectContext.stResponsesLife().ToList() select new CustomLifetime { Dates = i.Dates, Vals = (int)i.Vals }; return result; } public int GetAllResponses() { return this.ObjectContext.qryStoringLogs.Count(); } }
Caveats: Cannot have linked servers, so doing it at the source (SQL Server) is out of the question. Cannot create a SP and use OpenQuery (well i could but i want to learn to do it correctly), as im sure this isnt the correct way of doing it.
Answers & Comments...
