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>

    <Abstract xsi:type="x:string">REST, XML and LINQ</Abstract>

    <IsPublished xsi:type="x:boolean">true</IsPublished>

    <GuestID xsi:type="x:string">volkan-uzun</GuestID>

    <CreateDate xsi:type="x:string">1/23/2009 12:00:00 AM</CreateDate>

    <PublishDate xsi:type="x:string">2/20/2009 12:00:00 AM</PublishDate>

  </at-podcastdata>

  <at-podcastdata>

    <s:Id>sql-data-services</s:Id>

    <s:Version>539554</s:Version>

    <PodCastDataId xsi:type="x:string">sql-data-services</PodCastDataId>

    <PodCastTitle xsi:type="x:string">SQL Data Services</PodCastTitle>

    <RunTime xsi:type="x:string">12:35</RunTime>

    <Description xsi:type="x:string">Jennifer goes into detail about how to use SQL Data Services to run a web site.</Description>

    <AdminNotes xsi:type="x:string">This is the first podcast</AdminNotes>

    <Abstract xsi:type="x:string">Using SQL Data Services to run a website</Abstract>

    <IsPublished xsi:type="x:boolean">true</IsPublished>

    <GuestID xsi:type="x:string">jennifer-louie</GuestID>

    <CreateDate xsi:type="x:string">1/24/2009 12:00:00 AM</CreateDate>

    <PublishDate xsi:type="x:string">1/24/2009 12:00:00 AM</PublishDate>

  </at-podcastdata>

  <at-podcastdata>

    <s:Id>working-with-office-2007</s:Id>

    <s:Version>539227</s:Version>

    <PodCastDataId xsi:type="x:string">working-with-office-2007</PodCastDataId>

    <PodCastTitle xsi:type="x:string">Working with Office 2007</PodCastTitle>

    <RunTime xsi:type="x:string">30:45</RunTime>

    <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>

    <AdminNotes xsi:type="x:string">Kinda flakey, but ok to publish</AdminNotes>

    <Abstract xsi:type="x:string">Office 2007 Macros</Abstract>

    <IsPublished xsi:type="x:boolean">true</IsPublished>

    <GuestID xsi:type="x:string">joe-blow</GuestID>

    <CreateDate xsi:type="x:string">1/23/2009 12:00:00 AM</CreateDate>

    <PublishDate xsi:type="x:string">2/11/2009 12:00:00 AM</PublishDate>

  </at-podcastdata>

</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>

    <PublishEmail xsi:type="x:boolean">true</PublishEmail>

  </at-guest>

  <at-guest>

    <s:Id>james-johnson</s:Id>

    <s:Version>50404939</s:Version>

    <GuestName xsi:type="x:string">james-johnson</GuestName>

    <FullGuestName xsi:type="x:string">James Johnson</FullGuestName>

    <GuestEmail xsi:type="x:string">james@james.com</GuestEmail>

    <GuestCompany xsi:type="x:string">Duringlunch</GuestCompany>

    <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>

    <PublishEmail xsi:type="x:boolean">true</PublishEmail>

  </at-guest>

  <at-guest>

    <s:Id>volkan-uzun</s:Id>

    <s:Version>49928700</s:Version>

    <GuestName xsi:type="x:string">volkan-uzun</GuestName>

    <FullGuestName xsi:type="x:string">Volkan Uzun</FullGuestName>

    <GuestEmail xsi:type="x:string">volkan@volkan.com</GuestEmail>

    <GuestCompany xsi:type="x:string">Uzun Enterprises</GuestCompany>

    <GuestBio xsi:type="x:string">Inland Empire .NET User's Group Most Valuable Member - 2007/2008. LINQ Expert. Nice Guy.</GuestBio>

    <PublishEmail xsi:type="x:boolean">true</PublishEmail>

  </at-guest>

</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();