LINQ Examples With IENumerable On SQL Databases

This example doing LINQ To SQL from a simple database where I’m using a couple examples of using LINQ to query a SQL database for information.

RemotinLinqSolution RemotinLinqProgram

 

Program.cs Code Contents

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Reflection;
using System.Xml.Linq;
using System.Diagnostics;

namespace MyLinq
{
    class Program
    {
        /// <summary>
        /// Demonstrating an example of using LINQ to query a simple SQL database with a database model.
        /// </summary>
        /// <param name="args"></param>

        static void Main(string[] args)
        {
            EnumerableSql();
            QueryableSql();
            
            Console.ReadLine();
        }

        //Private Methods
        //
        private static void EnumerableSql()
        {
            Console.WriteLine("****Running Enumerable SQL Query****");

            // The data context used to connect and pull information from the database.
            MovieReviewsDataContext movieReviewsContext = new MovieReviewsDataContext();

            // LINQ query to get the top rated movies after a certain released year.
            IEnumerable<Movie> topMovies =
                from r in movieReviewsContext.Movies
                where r.ReleaseDate > 1980
                orderby r.Reviews.Average(rating => rating.Rating) descending       // Able to pull the review rating because of the DB association in the dbml.
                select r;

            // Iterate thru the IEnumerable to display the data from the LINQ query.
            foreach (Movie movie in topMovies)
            {
                Console.WriteLine(movie.Title);
            }
            Console.WriteLine();
        }

        private static void QueryableSql()
        {
            Console.WriteLine("****Running Queryable SQL****");

            // Create a data context to work with the database.
            MovieReviewsDataContext context = new MovieReviewsDataContext();

            // Variable with a LINQ statement querying against the database.
            IEnumerable<Movie> movies = context.Movies.Where(m => m.ReleaseDate >= 2000);

            // Display the data from the query.
            foreach (var item in movies)
            {
                Console.WriteLine(item.Title);
            }
            Console.WriteLine();
        }
    }
}

This is another example of using two types on LINQ queries to access data in a database. I’m using query and method syntax. You will see these two ways in the LINQ examples going forward. As far as preference both ways can accomplish the same thing. It just comes down to what you personally think is more readable.

Also please ignore my incorrect use of camel case. This example was kind of tossed together along with a lesson I was taking. I was following the direction of an instructor, we got into a minor beef about it which ended in me just saying ok, whatever, so I could turn something in. 🙂

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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