Joining Returned SQL Data Services Responses

Thursday, 5 February 2009 05:08 by james

In preparing for my talk on SDS at last month’s Code Camp, I needed to figure out a way to join to SQL Data Services responses. I came up with this method using LINQ. I don’t know if its the best way to do it, but it works for me. I’d appreciate any feedback.

 

1. Master Page – Page_Load calls GetPodcasts()

2. GetPodcasts() gets two SDS responses, podCastDoc and guestDoc, does some LINQ, then returns a System.Linq.Enumerable

3. A ListView on the Master Page is bound to the podcast object.

 

Method GetPodcasts()

   1: public void GetPodcasts()
   2: {
   3:     AT_Podcasts at_Podcasts = new AT_Podcasts();
   4:     AT_Guests at_Guests = new AT_Guests();
   5:     XElement podCastDoc = at_Podcasts.SelectPodcasts();
   6:     XElement guestDoc = at_Guests.SelectGuests();
   7:  
   8:     if (podCastDoc != null && guestDoc != null)
   9:     {
  10:         var podcasts = from podcast in podCastDoc.Descendants("at-podcastdata")
  11:                        join guest in guestDoc.Descendants("at-guest")
  12:                        on (string)podcast.Element("GuestID").Value
  13:                        equals (string)guest.Element("GuestName").Value
  14:                        orderby (DateTime)podcast.Element("PublishDate")
  15:                        select new
  16:                       {
  17:                           PodCastId = podcast.Element("PodCastDataId").Value,
  18:                           PodCastTitle = podcast.Element("PodCastTitle").Value,
  19:                           PodCastPublishDate = DateTime.Parse(podcast.Element("PublishDate").Value).ToLongDateString(),
  20:                           PodCastAbstract = podcast.Element("Abstract").Value,
  21:                           RunTime = podcast.Element("RunTime").Value,
  22:                           GuestID = podcast.Element("GuestID").Value,
  23:                           GuestName = guest.Element("FullGuestName").Value,
  24:                           GuestCompany = guest.Element("GuestCompany").Value
  25:                       };
  26:         lvPodCasts.DataSource = podcasts;
  27:         lvPodCasts.DataBind();
  28:     }
  29: }

 

Method at_Podcasts.SelectPodcasts()

   1: public XElement SelectPodcasts()
   2:     {
   3:         XElement atPodCasts;
   4:         AT_SDSConfig config = AT_SDSConfig.Create();
   5:         Uri container = config.GetPodCastDataContainer();
   6:         string uri = container.ToString() + "?q=";
   7:         var request = AT_SDSUtils.CreateRequest(config, new Uri(uri), "GET", string.Empty);
   8:         try
   9:         {
  10:             var response = (HttpWebResponse)request.GetResponse();
  11:             using (var stream = response.GetResponseStream())
  12:             using (var reader = new System.IO.StreamReader(stream))
  13:             {
  14:                 XmlTextReader r = new XmlTextReader(reader);
  15:                 atPodCasts = XElement.Load(r);
  16:                 return atPodCasts;
  17:             }
  18:         }
  19:         catch (WebException ex)
  20:         {
  21:             AT_SDSUtils.OutputException(ex);
  22:             return null;
  23:         }
  24:     }

NOTE: at_Guests.SelectGuests() is similar, just going into the Guest Container

 

Results of SelectPodcasts()

   1: <s:EntitySet xmlns:s="http://schemas.microsoft.com/sitka/2008/03/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema">  
   2:   <at-podcastdata>
   3:     <s:Id>getting-restful</s:Id>
   4:     <s:Version>539244</s:Version>
   5:     <PodCastDataId xsi:type="x:string">getting-restful</PodCastDataId>
   6:     <PodCastTitle xsi:type="x:string">Getting RESTful</PodCastTitle>
   7:     <RunTime xsi:type="x:string">48:25</RunTime>
   8:     <Description xsi:type="x:string">Volkan talks about how to work with REST and process the XML with LINQ</Description>
   9:     <AdminNotes xsi:type="x:string">Great episode</AdminNotes>
  10:     <Abstract xsi:type="x:string">REST, XML and LINQ</Abstract>
  11:     <IsPublished xsi:type="x:boolean">true</IsPublished>
  12:     <GuestID xsi:type="x:string">volkan-uzun</GuestID>
  13:     <CreateDate xsi:type="x:string">1/23/2009 12:00:00 AM</CreateDate>
  14:     <PublishDate xsi:type="x:string">2/20/2009 12:00:00 AM</PublishDate>
  15:   </at-podcastdata>
  16:   <at-podcastdata>
  17:     <s:Id>sql-data-services</s:Id>
  18:     <s:Version>539554</s:Version>
  19:     <PodCastDataId xsi:type="x:string">sql-data-services</PodCastDataId>
  20:     <PodCastTitle xsi:type="x:string">SQL Data Services</PodCastTitle>
  21:     <RunTime xsi:type="x:string">12:35</RunTime>
  22:     <Description xsi:type="x:string">Jennifer goes into detail about how to use SQL Data Services to run a web site.</Description>
  23:     <AdminNotes xsi:type="x:string">This is the first podcast</AdminNotes>
  24:     <Abstract xsi:type="x:string">Using SQL Data Services to run a website</Abstract>
  25:     <IsPublished xsi:type="x:boolean">true</IsPublished>
  26:     <GuestID xsi:type="x:string">jennifer-louie</GuestID>
  27:     <CreateDate xsi:type="x:string">1/24/2009 12:00:00 AM</CreateDate>
  28:     <PublishDate xsi:type="x:string">1/24/2009 12:00:00 AM</PublishDate>
  29:   </at-podcastdata>
  30:   <at-podcastdata>
  31:     <s:Id>working-with-office-2007</s:Id>
  32:     <s:Version>539227</s:Version>
  33:     <PodCastDataId xsi:type="x:string">working-with-office-2007</PodCastDataId>
  34:     <PodCastTitle xsi:type="x:string">Working with Office 2007</PodCastTitle>
  35:     <RunTime xsi:type="x:string">30:45</RunTime>
  36:     <Description xsi:type="x:string">In this episode, Joe talks about a cool macro he wrote that makes all the busy work his boss has him do, a lot more fun.</Description>
  37:     <AdminNotes xsi:type="x:string">Kinda flakey, but ok to publish</AdminNotes>
  38:     <Abstract xsi:type="x:string">Office 2007 Macros</Abstract>
  39:     <IsPublished xsi:type="x:boolean">true</IsPublished>
  40:     <GuestID xsi:type="x:string">joe-blow</GuestID>
  41:     <CreateDate xsi:type="x:string">1/23/2009 12:00:00 AM</CreateDate>
  42:     <PublishDate xsi:type="x:string">2/11/2009 12:00:00 AM</PublishDate>
  43:   </at-podcastdata>
  44: </s:EntitySet>

Results of SelectGuests()

   1: <s:EntitySet xmlns:s="http://schemas.microsoft.com/sitka/2008/03/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema">
   2:   <at-guest>
   3:     <s:Id>jennifer-louie</s:Id>
   4:     <s:Version>50343029</s:Version>
   5:     <GuestName xsi:type="x:string">jennifer-louie</GuestName>
   6:     <FullGuestName xsi:type="x:string">Jennifer Louie</FullGuestName>
   7:     <GuestEmail xsi:type="x:string">jennifer@jennifer.com</GuestEmail>
   8:     <GuestCompany xsi:type="x:string">Louie Software</GuestCompany>
   9:     <GuestBio xsi:type="x:string">Jennifer is a .NET Rock Star. She's really cute and hot!</GuestBio>
  10:     <PublishEmail xsi:type="x:boolean">true</PublishEmail>
  11:   </at-guest>
  12:   <at-guest>
  13:     <s:Id>james-johnson</s:Id>
  14:     <s:Version>50404939</s:Version>
  15:     <GuestName xsi:type="x:string">james-johnson</GuestName>
  16:     <FullGuestName xsi:type="x:string">James Johnson</FullGuestName>
  17:     <GuestEmail xsi:type="x:string">james@james.com</GuestEmail>
  18:     <GuestCompany xsi:type="x:string">Duringlunch</GuestCompany>
  19:     <GuestBio xsi:type="x:string">James is a .NET Developer who has been writing software since punch cards. He often remembers the time when his Computer Science PhD project got scattered to the wind, when he stumbled across a brand new orange screen monitor and his breath was taken away.</GuestBio>
  20:     <PublishEmail xsi:type="x:boolean">true</PublishEmail>
  21:   </at-guest>
  22:   <at-guest>
  23:     <s:Id>volkan-uzun</s:Id>
  24:     <s:Version>49928700</s:Version>
  25:     <GuestName xsi:type="x:string">volkan-uzun</GuestName>
  26:     <FullGuestName xsi:type="x:string">Volkan Uzun</FullGuestName>
  27:     <GuestEmail xsi:type="x:string">volkan@volkan.com</GuestEmail>
  28:     <GuestCompany xsi:type="x:string">Uzun Enterprises</GuestCompany>
  29:     <GuestBio xsi:type="x:string">Inland Empire .NET User's Group Most Valuable Member - 2007/2008. LINQ Expert. Nice Guy.</GuestBio>
  30:     <PublishEmail xsi:type="x:boolean">true</PublishEmail>
  31:   </at-guest>
  32: </s:EntitySet>

 

Back to the LINQ which does the join

   1: var podcasts = from podcast in podCastDoc.Descendants("at-podcastdata")
   2:    join guest in guestDoc.Descendants("at-guest")
   3:    on (string)podcast.Element("GuestID").Value
   4:    equals (string)guest.Element("GuestName").Value
   5:    orderby (DateTime)podcast.Element("PublishDate")
   6:    select new
   7:   {
   8:       PodCastId = podcast.Element("PodCastDataId").Value,
   9:       PodCastTitle = podcast.Element("PodCastTitle").Value,
  10:       PodCastPublishDate = DateTime.Parse(podcast.Element("PublishDate").Value).ToLongDateString(),
  11:       PodCastAbstract = podcast.Element("Abstract").Value,
  12:       RunTime = podcast.Element("RunTime").Value,
  13:       GuestID = podcast.Element("GuestID").Value,
  14:       GuestName = guest.Element("FullGuestName").Value,
  15:       GuestCompany = guest.Element("GuestCompany").Value
  16:   };
  17: lvPodCasts.DataSource = podcasts;
  18: lvPodCasts.DataBind();

watch-screencap

menu-screencap

 

Again, like I said. I’d appreciate your comments.

J

Categories:   SQL Data Services | SQL Services | Web Development
Actions:   E-mail | del.icio.us | Permalink | Comments (1) | Comment RSSRSS comment feed

Comments

February 5. 2009 09:42

Hey James,
Check out my post on your session from Code Camp:
mattpenner.info/.../

At the very bottom I have a couple of paragraphs regarding your join.  The problem issue I see is that you have to grab your entire set of Podcasts and Guests before the join.  It's not a big deal now but in the future this could lead to hundreds of results and you can't do any filtering prior to the load.

At Code Camp it seems that Bret Stateham gave a demo on using lamda expressions in the REST url.  I couldn't find that exactly but here is a similar post using SDS:
www.writebetterbits.com/.../...t-service-with.html

Also, PhluffyPhotos (http://www.codeplex.com/phluffyfotos) is a project on Code Plex where they have created their own Linq-to-SDS solution.

Check it out!

Matt Penner

James Johnson

My views and opinions on running a .NET User Group, web development, evangelizing, mentoring, utilizing the latest technologies, living as a gringo in a Latin family, and, of course, life in general.

Join BizSpark

BizSpark provides qualified Startups with FREE* software and support getting their idea up and running.
Join BizSpark
There is no charge for signing up. However a minimal fee of $100 is required when you leave the program.

Recent Comments