LINQ To SQL Example

In this example I’m showing how you can use LINQ to access data inside a SQL Database. I’ve created a simple Movies SQL database using the dbml modeler with Visual Studio. The database has three simple tables that hold the Movies, Reviews, and Logs. The main table I’ll be working with is the Movies table.

LinqToSqlDatabase LinqToSqlDbml LinqToSqlSolution

 
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)
        {
            QuerySql();
            
            Console.ReadLine();
        }

        //Private Methods
        //
        private static void QuerySql()
        {
            Console.WriteLine("****Displaying The Top Rated Movies****");

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

The only method that is being executed is QuerySql. What I’m doing is creating a data context object that gives me the connection I need to the database so I can run LINQ queries against. The data context is created when you create the database. Then I just added the tables and populated some data beforehand.

The LINQ query is a comprehesion query that gets all the movies made after 1980 according to their release data in the database, then displays them in decending order by their ratings. Again the ratings are stored in the database when the LINQ query is executed. Oh also the LINQ query is of a IEnumerable interface. This is so I can interate thru the query with a foreach loop and display the data how I need to.

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