Using Linq to Entities: Read Operation (Quick Reference)

How to Use Linq to Entities to Retrieve Records from a Relational Database

This quick reference is for those who are trying out Linq to Entities and want plenty of examples of how to perform everyday data retrieval tasks such as joining records (entities) and filtering and sorting query results.

Quick Reference Setup

Entity Framework & Linq to Entities: “Movies” Example Setup contains diagrams representing the database structure, entity data model, and entity object model used in the following code examples.

Retrieve a list of Movie objects using a Linq to Entities query

private static MovieEntities _entities = new MovieEntities();

static void Main(string[] args)
{
     var moviesList = (from m in _entities.MovieSet select m);

     foreach (var movie in moviesList)
     {
          Console.WriteLine(movie.Title);
     }
     Console.ReadLine();
}

A Linq query is used to retrieve all of the Movies from the database. The var keyword is used for the initialization of the moviesList variable to allow the compiler to dynamically determine its type. Notice that the var keyword is also used within the foreach construct. The var keyword was added to the C# language to support anonymous types in Linq and is meant to be used in these cases.

Access the ObjectQuery collection directly

private static MovieEntities _entities = new MovieEntities();

static void Main(string[] args)
{
     foreach (Movie movie in _entities.MovieSet)
     {
          Console.WriteLine(movie.Title);
     }
     Console.ReadLine();
}

Loop over the MovieSet (ObjectQuery<TEntity>) collection directly without assigning it to another variable.

Retrieve a list of Movie objects using the ToList() extension method

private static MovieEntities _entities = new MovieEntities();

static void Main(string[] args)
{
     List<Movie> moviesList = _entities.MovieSet.ToList();

     foreach (Movie movie in moviesList)
     {
          Console.WriteLine(movie.Title);
     }
     Console.ReadLine();
}

The ToList() extension method is used to retrieve all of the Movies from the database. When the type can be inferred by the compiler, it isn’t necessary to specify the type, (e.g. ToList<Movie>()) in the method’s signature. Since MovieSet implements IEnumerable<T> the compiler knows the type, therefore, calling ToList() will do the trick.

Retrieve a sorted list of Movie objects using a Linq to Entities query

private static MovieEntities _entities = new MovieEntities();

static void Main(string[] args)
{
     var moviesList = (from m in _entities.MovieSet
                       orderby m.Title ascending,
                               m.DateReleased descending
                       select m);

     foreach (Movie movie in moviesList)
     {
          Console.WriteLine("{0} {1:d}", movie.Title,
                                         movie.DateReleased);
     }
     Console.ReadLine();
}

The orderby Linq operator is used to specify which properties should be used to sort the collection.

Retrieve a filtered list of Movie objects using a Linq to Entities query

private static MovieEntities _entities = new MovieEntities();

static void Main(string[] args)
{
     var moviesList = (from m in _entities.MovieSet
                       where m.Title == "Rumble Fish"
                       select m);

     foreach (Movie movie in moviesList)
     {
          Console.WriteLine(movie.Title);
     }
     Console.ReadLine();
}

An expression is used to specify the value of the Title property for the Movies we wish to retrieve. Note that this query returns a collection of Movies and not a single Movie.

Retrieve a single Movie object using a Linq to Entities query and the First() extension method

private static MovieEntities _entities = new MovieEntities();

static void Main(string[] args)
{
     Movie movGodfather = (from m in _entities.MovieSet
                           where m.Title == "The Godfather"
                           select m).First();

     Console.WriteLine(movGodfather.Title);
     Console.ReadLine();
}

An expression is used to specify the value of the Title property for the Movies we wish to retrieve. We make use of the First() extension method in order to retrieve a single Movie object. If there are multiple matches, this method retrieves the first in the list.

Retrieve a single anonymous object using a Linq to Entities navigation property to join related records

private static MovieEntities _entities = new MovieEntities();

static void Main(string[] args)
{
     var movGodfather = (from d in _entities.DirectorSet
                         from m in d.Movies
                         where m.Title == "The Godfather"
                         select new { m.Title, d.Name }).First();

     Console.WriteLine("{0} by {1}", movGodfather.Title,
                                     movGodfather.Name);
     Console.ReadLine();
}

Two from operations are included in the query to retrieve information from associated objects. The resulting type is an anonymous object with Title and Name properties. Notice the use of the var keyword to specify that the query returns an anonymous type.  The select Linq operator statement uses object initialization syntax to specify the properties to retrieve from each object. The property names for the anonymous type could also be defined explicitly. By way of example: new { Title = m.Title, DirectorName = d.Name }.

Retrieve a collection of anonymous objects using a Linq to Entities navigation property to join related records

private static MovieEntities _entities = new MovieEntities();

static void Main(string[] args)
{
     var moviesList = (from d in _entities.DirectorSet
                       from m in d.Movies
                       where m.Title == "The Godfather"
                       // Or grab only Movies with Titles
                       // that start with "The".
                       // where m.Title.StartsWith("The")
                       select new { m.Title, d.Name });

     foreach (var movie in moviesList)
     {
          Console.WriteLine("{0} by {1}", movie.Title,
                                          movie.Name);
     }
     Console.ReadLine();
}

The Director.Movies navigation property is an EntityCollection<TEntity> that provides interoperability with Linq queries. When doing a join in this fashion the order of the from statements matters. It is necessary to specify the “one” side of a “one to many” relationship prior to specifying the “many” side of the relationship. So first we specify Directors in the DirectorSet collection and then we specify the Movies associated with each Director by way of Director.Movies.

Retrieve a collection of objects using a subquery in Linq to Entities

private static MovieEntities _entities = new MovieEntities();

static void Main(string[] args)
{
     var moviesList = (from d in _entities.DirectorSet
                       from m in d.Movies
                       where m.Title == 
                             (from mov in d.Movies
                              where mov.Title == "The Godfather"
                              select mov.Title).FirstOrDefault()
                       select new { m.Title, d.Name });

     foreach (var movie in moviesList)
     {
          Console.WriteLine("{0} by {1}", movie.Title,
                                          movie.Name);
     }
     Console.ReadLine();
}

The FirstOrDefault() extension method is used within the subquery since First() can only be used at the very end of a Linq query statement as the final query operation. Of course, the above query is only meant to serve as an example. There would be no reason to perform a subquery since where m.Title == "The Godfather" would do the trick.

Advertisements

One Response

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: