LINQ - XElement and Entities

 

With LINQ we get a common data programming experience across multiple data sources. We can query in-memory collections, relational tables and XML. The other day I was looking at the various LINQ-XML examples and thought to myself that I would like to play around with loading my database from a remote XML source. Using XElement this becomes quite easy… I can load XML from a remote source, filter out the data that I care about and use this data to instantiate and persist data in my local database. Being a basketball fan I figured I would try this out with sports information, following is a discussion of my little experiment in this regard:

 

Loading Data From A Remote Data Source:

 

The first thing I needed was some sports information from a remote source, I went on live.com and did a search for sports information. I came across an XML format for sports information called SportsML and an URL with sample basketball data. For this exercise I figured that this would be a reasonable source for my endeavor.

 

Retrieving the information in a form that I can use LINQ over it is trivial, given a URL:

 

private String _sportsML = @"https://www.sportsml.org/IPTC/SportsML/1.7/examples/sportsml-basketball_standings.xml";

 

I can use the Load method on the XElement class as my starting point:

 

XElement sports = XElement.Load(_sportsML);

 

I can now perform LINQ queries over this to retrieve the data I want. The sportsML schema provides results, schedules and more, I just wanted to pull out schedules which I did as follows:

 

protected IEnumerable<XElement> GetSchedule()

        {

            XElement sports = XElement.Load(_sportsML);

            var schedule = from s in sports.Elements()

                           where s.Name.LocalName == "schedule"

                           select s;

            return schedule;

        }

 

Given a schedule I can further use LINQ to pull out the pieces of the schedule that I want. For this exercise I will use LINQ to pull the date & time plus the participating teams:

 

Retrieve the Date:

 

        protected String GetEventDateTime(XElement sportsEvent)

        {

            var dateTimeResult = from eventMetadata in sportsEvent.Elements()

                                where eventMetadata.Name.LocalName == "event-metadata"

                                from evDateTime in eventMetadata.Attributes() where evDateTime.Name.LocalName == "start-date-time"

                                select evDateTime;

           

            return (dateTimeResult.Count() > 0) ? dateTimeResult.First().Value : null;

        }

 

Retrieve the Teams:

 

        protected String[] GetTeams(XElement sportsEvent)

        {

           

            var teamNames = from teams in sportsEvent.Elements()

                            where teams.Name.LocalName == "team"

                            from teamMetadata in teams.Elements()

                            where teamMetadata.Name.LocalName == "team-metadata"

                            from nameElement in teamMetadata.Elements()

                            select nameElement.Attribute("first").Value +" "+ nameElement.Attribute("last").Value;

            return teamNames.ToArray<String>();

        }

 

With this information at hand I can now create persistent instances that I will save using the ADO.NET Entity Framework:

 

        protected SportsEvent CreateSportsEvent(XElement sportsEvent)

        {

            SportsEvent entitySportsEvent = null;

            String dateTimeAsString = GetEventDateTime(sportsEvent);

            if (null != dateTimeAsString)

            {

                String[] teamNames = GetTeams(sportsEvent);

                entitySportsEvent = new SportsEvent();

                entitySportsEvent.EventDateTime = DateTime.Parse(dateTimeAsString);

                entitySportsEvent.AwayTeam = teamNames[0];

                entitySportsEvent.HomeTeam = teamNames[1];

            }

            return entitySportsEvent;

        }

 

 

Saving this is merely a matter of adding the instance to the ObjectContext and saving:

 

        public void ScheduleReader()

        {

            BlogRssContext context = new BlogRssContext();

           

            IEnumerable<XElement> schedule = GetSchedule();

            foreach (XElement sportsEvent in schedule.Elements())

            {

                SportsEvent entitySportsEvent = CreateSportsEvent(sportsEvent);

                if (null != entitySportsEvent)

                {

                    context.AddObject(entitySportsEvent);

                }

            }

            context.SaveChanges();

        }

 

With that we have pulled data from a remote service and persisted it in a local database.

 

Tim Mallalieu

Program Manager, ADO.NET