Stored Procedures Vs. No Stored Procedures

There is this debate raging among developers: “Is it better to use stored procedures or not use stored procedures”.  From first glance, this seems like a simple question, but there are some complicated implications around this question.  Here’s the basic pros and cons of using stored procedures in your system:

Pros

  • You can isolate table changes from your front-end.
  • Updating business code is easy, just update the stored procedure while your system is running (assuming you already tested the changes off-line and everything is good).
  • Many queries can be reduced to one call to the database making your system faster.

Cons

  • Your business processing is performed by your database which is expensive (license fees compared to web servers).
  • Unit testing is all but impossible.
  • Version control is difficult.
  • You are married to your database server technology (MS SQL cannot be easily ported to Oracle).
  • Stored Procedure languages are not well structured.  This leads to spaghetti code.

Now, let’s expand a bit and discuss each of these issues:

Isolate Table Changes

It may seem like overkill to setup all of your select, insert, update and delete queries to call a stored procedure, then have the stored procedure call the tables.  This can be a boon for situations where you might change your table structure in the future.  An example would be a situation where a table becomes too wide and you want to break the table into two tables.  Your stored procedures can have the same interface to your program and the code inside could handle all the details of where the data is stored and read from.  Removing a field from a table can also be safely done, since you can just remove it inside the stored procedure but leave the parameter called from your software until you can root out all calls to the database and change those (or leave the dead parameter).

Updating Business Code

Business code is the logic that computes and performs work on your database.  Your stored procedure might update one table, add an entry to a log table and remove corresponding records from another table.  Another use for a stored procedure is to pass filter and ordering information into a query for a list of data.  A dynamic query can be formed in a stored procedure and executed with all the parameters entered.  This relieves your front-end from the task of sorting and filtering data.  It can also reduce the amount of raw data returned from the database.

The point here is that your business code might need a change or an enhancement.  If there is a bug, then it can be fixed and deployed on the fly.  A .Net application must be compiled and carefully deployed on servers that are not accessed during the time of deployment.  A stored procedure can be changed on the fly.  If your website is a large monolithic application, this capability becomes a larger “pro”.

One Call to the Database

If a record edit requires a log entry, then you’ll be forced to call your database twice.  If you use a stored procedure, the stored procedure code can make those calls, which would be performed from the database itself and not over the wire.  This can reduce the latency time to perform such an operation.

Now for the list of cons…

Business Processing is Being Performed by the Database

MS SQL server and Oracle licensing is expensive.  Both licenses are based on number of CPUs and the price can get steep.  If you have a choice between performing your processing on a web server or a database server, it’s a no-brainer.  Do it on the web server.  Web servers, even an MS Server license is cheaper than a SQL Server license.  Initially your business will not feel the cost difference because your system is small.  However, once your system grows you’ll see your costs increase on an exponential scale.

Unit Testing

Unit testing is not available for stored procedures.  You could probably get creative and setup a testing environment in Visual Studio, but it would be custom.  Without unit tests, you are not able to regression test.  If your stored procedures contain logic that allows different modes to occur, it can be difficult to properly test each mode.  An example is a stored procedure that performs filter and sorting operations.  If you are building a dynamic query using “if” statements, then you’ll have a combination of different possible inputs.  How do you ensure that your dynamic query doesn’t have a missing “and” or a missing comma, or a union query with non-matching fields?  It’s difficult.  If this logic is in your website code, you can wrap each combination of inputs in unit tests to provide regression testing when you add a filter or sorting option or change an option.

Version Control

No matter how you divide your code, you’ll need to make sure you version control your database so you can keep a history of your changes and match those changes with your code.  Visual Studio allows you to define all your database objects in a project (see SQL Server Database Project type).  There are tools available to allow you to create change scripts from two Team Foundation Server versions.  This can be used to update multiple databases.  Versioning a database is not a common practice and that is why I’ve put this under the “con” list instead of the “pro”.  For companies that keep their database definitions in a version control system, they can take this off the “con” list.

Married to Your Database Server

Till death do you part!  If you ever decide to switch from one database server technology to another, you’ll discover how steep the hill is that you’ll need to climb.  Each stored procedure will need to be converted by hand one-by-one.  If your system doesn’t have stored procedures, then you’ll have an easier time converting.  Minor differences between triggers and indexes might be an issue between Oracle and SQL, and there’s the recursive query in Oracle that is different.  You might even have issues with left and right outer joins if you used the “(+)” symbol in Oracle.  Stored procedures will be your Achilles heel.

Spaghetti Code

Writing stored procedures is a lot like writing in Classic ASP.  It’s messy.  I see a lot of sloppy coding practices.  There is no standard for formatting queries or TSQL code.  Everybody has their own short-cuts.  Once a system grows to a point where it contains tens of thousands of stored procedures, you’re faced with a mess that has no hope.  C# code has the luxury of being able to be refactored.  This is a powerful capability that can be used to reduce entangled code.  Being able to break code into small and management chunks is also helpful.  If your database code is contained in a Visual Studio project, you can perform some of the same refactoring, but you can’t test on the fly.  So programmers prefer to change stored procedures on their test database where refactoring is not available.

Conclusion

Are there more pros and cons?  Sure.  Every company has special needs for their database.  Some companies have a lot of bulk table to table processing that must be performed.  That screams stored procedures, and I would recommend sticking with that technique.  Other companies have a website front-end with large quantities of small transactions.  I would recommend those companies keep their business logic in their website code where they can unit test their logic.  In the end, you’ll need to take this list of pros and cons and decide which item to give more weight.  Your scale may tip one way or the other depending on which compromise you want to make.

 

EF Code-First Stored Procedures With Parameters

Summary

In an earlier post I showed how to create a method that can access a stored procedure using Entity Framework Code-First.  In this post I’m going to show how to pass parameters and show a few tricks.

The Stored Procedure

First, you’ll need to create a new stored procedure in your APIUniversity database (see this post for the table definition and other stored procedure).

CREATE PROCEDURE [dbo].[select_one_room]
    @roomnum AS int
AS
BEGIN
    SELECT
id,name FROM Room WHERE roomnumber = @roomnum
END

New Method

Now create a new method named GetRoom:

public DbRawSqlQuery<RoomListResult> GetRoom(int roomNumber)
{
    var roomNumberParam = new SqlParameter(“roomnum“, roomNumber);

    return this.Database.SqlQuery<RoomListResult>(“EXEC select_one_room @roomnum“, roomNumberParam);
}

 First, you’ll notice that I added a SqlParameter for the parameter that will be fed into the SqlQuery call.  Second, you might notice that I had to put in the “EXEC” command in the stored procedure string.  Apparently, this can be left off, if you’re stored procedure doesn’t contain any parameters, but will complain with an error “Incorrect syntax near ‘select_one_room'” if it is not used in this case.

Make sure you put commas between multiple parameters.  You can also leave off the “@” symbol in the parameter name when used in the SqlParameter() method, but you’ll need the “@” symbol in the SqlQuery() method string.

To use this stored procedure, your query might look something like this:

using (var db = new MyContext())
{
    var results = db.GetRoom(1);

    foreach (var item in results)
    {
        Console.WriteLine(item.Name);
    }

    Console.ReadKey();
}

Bug in the Return Class Column Attribute

For tables, you can rename fields by using an attribute to declare what the column name is inside the database.  Here’s an example where I changed the “Room” class so that the “Name” field is now “RoomName”, but the field in the database remains “Name”:

[Table(“Room“)]
public class Room
{
    [Key]
    public int id { get; set; }

    [Column(“Name“)]
    public string RoomName { get; set; }
    public int RoomNumber { get; set; }
}

This works as expected.

Now, if you try this trick on the “RoomListResult” class, you’ll discover that the attribute is ignored.  This is a known bug (maybe sometime in the future I can amend this post if the bug gets fixed).  Your result will return null for any fields that do not match the field name returned in the stored procedure result.

 Where to Get the Sample Code

I have posted the sample code on my GitHub account.  You can download it by clicking here.  Be sure to search for “My Connection String” and replace with your own database connection string.

 

 

Unit Testing Methods that Call Stored Procedures

Summary

One of the biggest problems I used to find in unit testing is that most of the products I work on contain mostly methods that access a database.  I found that most objects and methods work with data and it’s very tedious to try and mock or fake the database component.  If you’ve followed this blog for some time you’ll know that I have an easy and effective way around this problem.  I have a framework that I developed that enables me to load a SQLLocalDB instance, replicate the tables, views, stored procedures and even constraints before a unit test is executed.  In this blog post, I’m going to demonstrate how to unit test a method that contains a call to a stored procedure.  I’m going to show two different methods you can use to perform this operation.

What Are We Testing.  The Full Database Method.

First, we need to determine what we’re really testing.  If you go to my GitHub account here, and download the NHibernate mapping generator, you can use this to extract all the table, view and stored procedure definitions from a selected database (you can ignore or delete the NHibernate ORM stuff if you’re not going to use NHibernate).  This solution contains a project called “Helpers” that contains a framework for setting up a SQLLocalDB instance and can create a database using the source you generate when you run the NHibernate generator tool.  In order to use this framework, you can then create unit tests that stuff information into the SQLLocalDB database instance, execute your method, then read the results from the database (or test results returned from the method under test).

Let’s create an example..

First, open up sql server and create a table and a stored procedure.

CREATE TABLE [dbo].[person](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [first] [varchar](50) NULL,
    [last] [varchar](50) NULL,
 CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON
[PRIMARY]

GO

CREATE PROCEDURE [dbo].[ReadPersonNames]
AS
BEGIN

    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.

    SET NOCOUNT ON;

    — Insert statements for procedure here
    SELECT first, last FROM person
END
GO

Insert some data into your person table:

INSERT INTO person (first,last) VALUES (‘Mary‘,’Jane‘)
INSERT INTO person (first,last) VALUES (‘Joe‘,’Cool‘)

Now, download the NHibernate Mapping Generator program from GitHub (click here).  There are several projects inside the solution.  Make sure that the project named “NHibernateMappingGenerator” is set as the starting project.  Then hit F5 (or click the run/start button).   In the drop-down list box, select your database instance (MS SQL Server).  A list of databases inside that server will display in the check list box below the drop-down.  Then check the database that you created the table and stored procedure above, then click the generate button.  When the generate button turns disabled, then you’re done.  Exit from the program.  You might receive a “File Modification Detected” error, this is normal.  Just click “Reload” and you’ll see your database added to the NHIbernateDataLayer project.

Create a new console project.  Add an existing project to your solution and navigate to the helpers project (or you can copy the helpers from the NHibernate solution into your solution).  Make sure you add a reference to the helpers project from your main project and add a “using Helpers;” at the top.

Next, add a unit test project to your solution.  In your unit test project you will need to reference the Helpers and your console project.  Also, include the using statements at the top.

Add this code to your main program:

class Program
{
    static void Main(string[] args)
    {
        var personSelector = new PersonSelector();
        DataSet data= personSelector.ReadNames();

        foreach (DataRow row in data.Tables[0].Rows)
        {
            Console.WriteLine(row[“first“].ToString() + ” “ +
               row[“last“].ToString());
        }

        Console.ReadKey();
    }
}

public class PersonSelector
{
    public DataSet ReadNames()
    {
        using (var db = new ADODatabaseContext(“Server=YourServer;
            Initial Catalog=sampledata;Integrated Security=True“))
        {
            return db.StoredProcedureSelect(“sampledata.dbo.ReadPersonNames“);
        }
    }
}

As you can see, I’m just using ADO to read the records from the person table using the stored procedure read method defined in the Helpers project.  There are no parameters to this stored procedure, it just reads the first and last names of the entire table.  You should be able to execute this tiny program and see the first and last names of each record listed (Mary Jane and Joe Cool if you used my insert statements earlier).  If your database is not named “sampledata” then you’ll need to change that in the code above.

Now we’ll need to add a database context project to your console application.  This will contain the cs files created by the NHibernate mapping generator.  First, create a new class library project named “Data”.  Inside the NHibernateDataLayer project you’ll find a subdirectory with the name of your database, inside that will be a folder named “TableGeneratorCode”, and it will contain a cs file named “sampledataTableGeneratorCode”.  Copy that file into your “Data” project.  You’ll have to include the helpers project and add your using statement.

Next, you’ll need to copy over the stored procedure generator code.  This will be in the NHibernateDataLayer project, in the folder named after your database, in a subfolder named “StoredProcedures” and there will be a cs file for each stored procedure that was found in your database.  Grab the file for the one stored procedure named “ReadPersonNames.cs”.  Copy that file into your “Data” project.  Don’t forget to include the Helpers using clause.

Now for the unit tests.  You’ll need to create a class that will initialize the SQLLocalDB.  I usually call this source “AssemblyCommon.cs”.  You’ll need an assembly cleanup and initialize method.  Before you start, add a reference to the Helpers and Data projects, then add these using statements:

using Helpers;
using Data;


Now add in the code below:

[TestClass]
public class AssemblyCommon
{
    [AssemblyInitialize]
    public static void ClassStartInitialize(TestContext testContext)
    {
        UnitTestHelpers.Start(“storedprocinstance“, new string[] { Data.sampledataTables.DatabaseName });

        // create tables
UnitTestHelpers.CreateAllTables(Data.sampledataTables.TableList, Data.sampledataTables.DatabaseName);
        ReadPersonNames.Instance.CreateStoredProcedure(UnitTestHelpers.InstanceName);
    }

    [AssemblyCleanup]
    public static void ClassEndCleanup()
    {
        UnitTestHelpers.End();
    }
}

The assembly initialize code will start up an instance named “storedprocinstance”.  You can put a breakpoint at the end of the AssemblyInitialize method and then startup SQL Server Management Studio and go to a database named “(localdb)storedprocinstance” and see the database that is running.  This can be handy for troubleshooting your unit test data.

Next, go to your unit test cs file and add a TestCleanup method:

[TestCleanup]
public void Cleanup()
{
    UnitTestHelpers.TruncateData();
}


This will reset your database between unit tests without tearing down the whole database and rebuilding it.

You’ll have to add a reference to System.Data and a using statement at the top (which will also need a reference to System.XML, but you won’t need a using statement for that one).  Then add the following unit test method:

[TestMethod]
public void TestMethod1()
{
    using (var db = new ADODatabaseContext(“TEST“, “sampledata“))
    {
        db.ExecuteNonQuery(“INSERT INTO person (first,last) VALUES 

          (‘Mary’,’Jane’)“);
        db.ExecuteNonQuery(“INSERT INTO person (first,last) VALUES 

          (‘Joe’,’Cool’)“);
    }

    var personSelector = new PersonSelector();
    DataSet data = personSelector.ReadNames();

    Assert.AreEqual(2, data.Tables[0].Rows.Count);
}


This unit test starts with the instance already in place.  The person table is already created and the stored procedure exists.  All you need to focus on is your unit tests.  In the sample above, I opened an ADO context into the database and inserted two records (you can use Entity Framework or any other ORM for your unit tests, treat it like a normal SQL server).  Next, I instantiate the object and call my method that I wish to test.  Then I check the result with an assert.  In this case, I’m just checking to see if two rows were inserted.



What Are We Testing?  The Fake Database Method.

In this example, I’m going to show you how to setup the same unit test, except I will not be using the person table.  Why would I do this?  In or previous “toy” example, there is one stored procedure that calls one table and selects data.  Very simple.  In a real database or a legacy system, there will be stored procedures that call dozens of tables containing data that is related to each other and possibly tables that are not contained in the stored procedure.  You might not need to setup all that data just to test the method you want to test.  Realistically, your method only sees the results from your stored procedure depending on variables that it passes to that procedure.

What I’m going to do in this unit test setup is I’m going to create a fake unit test that is named the same as the unit test that the method under test expects.  Except I’m going to fake the output with a simple select statement that uses no tables.  Then I’m going to execute the method and see if my results are as expected.  Then in another unit test, I will create a new fake stored procedure with the same name (by dropping any currently created stored procedures in the database and replacing it).

Use the solution created in the sample above.  First, add a directory in your unit test project and name it “TestData”, then add two text files to this directory named “TestFakeSproc1.sql” and “TestFakeSproc2.sql”.  You’ll need to select these two files and then in the properties window, set the files to “Embedded Resource”:


This will ensure that the test data that you are going to create will be embedded in the unit test dll.  This will prevent any directory issues that you’ll encounter if you’re using an automated deployment product like TeamCity.

Now, add some SQL to the first sql file:

USE [sampledata]
GO

IF exists (SELECT * FROM sys.objects WHERE name = N’ReadPersonNames’ AND type = N’P’)
BEGIN
    DROP PROCEDURE ReadPersonNames
END
GO

CREATE PROCEDURE [dbo].[ReadPersonNames]
AS
BEGIN

    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.

    SET NOCOUNT ON;

    — Insert statements for procedure here
    SELECT
        ‘LisaAS first,
        ‘SmithAS last
    WHERE
        1 = 1
END
GO


Now add the first unit test method:

[TestMethod]
public void TestFakeSproc1()
{
UnitTestHelpers.ExecuteSQLCode(“UnitTestPersonSelector.TestData.TestFakeSproc1.sql“);

    var personSelector = new PersonSelector();
    DataSet data = personSelector.ReadNames();

    Assert.AreEqual(1, data.Tables[0].Rows.Count);
    Assert.AreEqual(“Lisa“, data.Tables[0].Rows[0]
           [“first“].ToString());
}


The full pathname to an embedded resource will be the namespace followed by the subdirectory name followed by the file name.  If you don’t get this right, then you’ll get an error at the streamreader portion of the ExecuteSQLCode() method.

In this unit test, the stored procedure that is created will just return one row of data that is hard-coded.  No tables were read and the method will receive exactly one row of data no matter what the inputs are.  The point of this unit test is to test the method itself given the pre-planned data that will be produced by the stored procedure.  Now let’s do another fake stored procedure just to show how this works for multiple unit tests.

Inside TestFakeSproc2.sql, copy this text:

USE [sampledata]
GO

IF exists (SELECT * FROM sys.objects WHERE name = N’ReadPersonNames’ AND type = N’P’)
BEGIN
    DROP PROCEDURE ReadPersonNames
END
GO

CREATE PROCEDURE [dbo].[ReadPersonNames]
AS
BEGIN

    — SET NOCOUNT ON added to prevent extra result sets from
    — interfering with SELECT statements.

    SET NOCOUNT ON;

    — Insert statements for procedure here
    SELECT
        ‘LisaAS first,
        ‘SmithAS last
    UNION
    SELECT
        ‘JoeAS first,
        ‘CoolAS last

    UNION
    SELECT
        ‘AdamAS first,
        ‘WestAS last
  
    WHERE
        1 = 1
END
GO

Now create this unit test:

[TestMethod]
public void TestFakeSproc2()
{
UnitTestHelpers.ExecuteSQLCode(“UnitTestPersonSelector.TestData.TestFakeSproc2.sql“);

    var personSelector = new PersonSelector();
    DataSet data = personSelector.ReadNames();

    Assert.AreEqual(3, data.Tables[0].Rows.Count);
}

This method will count the number of rows as 3.  You can setup your select statement to return anything you want to simulate.


Download the Sample Code

You can go to my GitHub account and download the entire functional unit testing sample by clicking here.





 

How to detect if your code was called from a unit test

I’ve done a few unit test blog posts, but this time I’m going to demonstrate how to detect if a method is running under a unit test.  This flag can be used to break dependencies in an object or run a test database instance instead of the production instance.  

The first article I stumbled across was this stack overflow article on just such a problem:

determine-if-code-is-running-as-part-of-a-unit-test

Answer #22 had my solution, using reflection to determine if one of the included DLL’s was the UnitTestFramework.


public static class UnitTestDetector
{
    public static bool IsInUnitTest()
    {
        string assemblyName = 
         “Microsoft.VisualStudio.QualityTools.UnitTestFramework“;
        return AppDomain.CurrentDomain.GetAssemblies().Any(a => 
                   a.FullName.StartsWith(assemblyName));
    }
}


So what’s the point?  The purpose of this class/method is to embed this into your context code and force your ORM to use a test database when you are running unit tests, but run your production database when the calling assembly is not a unit test assembly.  By using this code to switch your database at the context level, you no longer have to worry about breaking dependencies of your objects that will be under unit test.

Another advantage is that this can be used in multiple ORM’s (such as EF, NHibernate, etc.).  It can even be used in direct queries if you have a SqlConnection wrapper that feeds the connection string in one place.

In a future post, I’ll demonstrate how to use this class/method in Entity Framework and show how to connect EF to SQLLocalDB for unit testing purposes.

 

Fluent NHibernate “Not Supported By” Exception

Summary

So my company has successfully switched to using Fluent NHibernate for our production systems.  So far the results are astounding.  One thing our developers have learned is that there is no “perfect” ORM.  Not yet anyway.  Each seems to have a flaw or two.  NHibernate has a couple of issues that can be worked around.  One issue, I mentioned in an earlier post regarding the 1200 parameter limit.  While unexpected, it was easy to see why it was an issue.  The issue I want to talk about today came up when one of our developers was working with a group by query involving other query operations like order by and take, etc. So I’m going to talk about one issue and show a solution.


The Setup

I’m going to use my standard sampledata database in MS SQL server with 3 tables: Product, ProductType and Store.  Here’s the ERD:



I put some seed data into these tables, it doesn’t matter what you put in the tables.  There is a SQL script in the project (named “CreateTables.txt”), you can copy the text inside this file and execute it inside a query window in MS SQL Server Management Studio.


The Ugly

Here’s the offending LINQ that I setup in my console app:

var query =
    (from p in session.Query<Product>()
     join pt in session.Query<ProductType>() 
       on p.ProductType equals pt.Id
     join s in session.Query<Store>() on p.store equals s.id
     group new { p, pt } 
     by new { pt.Id, pt.Description, p.Name } into temp
     select new
     {
      name = temp.Key.Name,
      id = temp.Key.Id,
      description = temp.Key.Description,
      count = temp.Count()
     }).OrderBy(u => u.name).ToList();

If you attempt to execute this query, you’ll get an error like this:


This error only occurred when I added the “OrderBy” onto the query.  So it appears that a combination of query parameters can cause this error to occur, or possibly there is an issue with the anonymous types generated in the “select new” part of the query.  


The Fix

I dug around for an answer to this problem and found this Stack Overflow issue:

Linq query nhibernate; not supported exception

That’s where I discovered a solution to this query.  I added the “AsEnumerable()” before the “OrderBy” to fix this problem:

var query =
    (from in session.Query<Product>()
     join pt in session.Query<ProductType>() 
       on p.ProductType equals pt.Id
     join s in session.Query<Store>() on p.store equals s.id
     group new { p, pt } 
     by new { pt.Id, pt.Description, p.Name } into temp
     select new
     {
      name = temp.Key.Name,
      id = temp.Key.Id,
      description = temp.Key.Description,
      count = temp.Count()
     }).AsEnumerable().OrderBy(u => u.name).ToList();


Now the query executes and produces the expected results.

You can download the sample project here:
FluentNHibernateGroupBlogPost.zip

 

So You’re About to Graduate with a Degree in Computer Science…

Introduction

I’m going to relax and write a free-form blog post today about preparing for a career in the field of Computer Science. This post is directed at students that are getting close to graduation or are thinking ahead about how they are going to turn their expensive degree into a job that is both satisfying to work at and earn an excellent income.


How Prepared are Students Fresh out of College?

I’m afraid that this question is more complicated than it seems.  Technically the core Computer Science courses taught at major universities are more than adequate to get you started in the field of software development.  However, competition is tough, and you want to rise above the “average” student.  You also don’t want to spend a lot of time finding a job.  It’s better to be able to choose from a list of jobs.  Knowledge that is taught in the classroom is just the foundation that you need to build your career on.  You’ll need to learn some practical knowledge before you can really shine, and you’ll need to make sure you fully understand the principles that you learned in college.  What principles am I talking about?  Object oriented programming principles for one.  Database design principles, data structures, coding practices, etc.

What all this means is that you’ll have to do some studying outside the classroom.  You’ll also need to do a few example programs to gain a full understanding of the subjects you’ve been taught.


Does this Stuff get Easier when I get into the Working World?


As you get more practice at building software it definitely gets easier.  Some techniques are still hard, and the ever-changing technology world makes learning new things a constant task.  It also makes it an interesting and challenging career.  As a student, most people are just learning about the syntax of their first language.  Once you’ve written a few programs, the syntax will come automatically, like riding a bicycle.  At first, riding a bicycle is all about focusing on balancing.  After a while, you don’t think about balancing any more.  It’s automatic.  The same applies to programming.  Once you know the syntax, then the next challenge is learning how to apply algorithms to solving the total problem.  This is really just a challenge in breaking the problem down small enough to solve with an algorithm you have in your tool box.  Once you have that down, you just get so good at it that you can think up solutions to big problems in minutes.


Is there any Way to Prepare Myself Now?

Yes there is.  There are two primary directions in the software development field and you’ll need to choose one field or the other so you can focus your efforts.  One field is the .Net field with Microsoft products like Visual Studio, IIS, C#, Visual Basic, SQL Server, etc.  The other field is unix based systems with PHP, Ruby, Python, Apache, Oracle, MySQL, etc.  There are other fields as well, but I’d stick to one of the two main paths.  I have developed software from both fields at one time or another.  I rarely use my knowledge from both fields.  Normally a company will pour their resources into one or the other and it’s difficult to try and learn both at the same time.

Currently, I’m working for a company that uses Microsoft products and I’m working in the .Net field.  So I’m going to give some pointers on how you can prepare yourself to get a good job with some .Net knowledge coupled with your college degree.

As of this writing this is what you should learn outside of class:

1. MS SQL server
2. C#
3. An ORM using LINQ queries (Entity Framework is an easy ORM to learn)
4. MVC website programming

Start off slowly.  Ease into it.  If you try to bite off too much you’ll get frustrated and give up.  What you want to do is learn how to install some free software, experiment with it on a small scale and then think up a project that want to build using the techniques you learned.  This is important, and it’s can be listed on your resume as a hobby.  Hobbies that involve your career will impress a person looking at your resume.  It makes the statement that you are serious about your career and you are interested in the job that you are seeking.  


OK, I’m Sold, How do I get Started?

Start with MS SQL server express.  Type “download ms sql server express” into google and look for microsoft’s download center.  Download the software and install it on your PC.  As of this writing SQL Server Express 2012 is the current version available.  Avoid using any pre-release or Technology Preview versions.  These could have bugs and will only distract you from learning what you really should learn: How to create and access a database.

OK, now, let’s assume you installed SQL Server and you want to proceed.  Open the Server Manager Studio console and get familiar with how it works.  This is an interface into your database.  Right now you don’t have any databases created, so right-click on the “Databases” node and select “New Database”.  Give it a name (I’ll call mine “sampledata”) and create it.  Next, create a table inside the new database.  Open your database node (“sampledata”) by clicking on the “+” symbol next to it.  You’ll see what looks like a subdirectory named “Tables”.  Right click on that and select “New Table”.  Give it a name (I’ll call mine “students”).  You’ll be presented with a designer interface where you can create fields (called columns).  Name the first column “id”, then make it an “int”.  Name the second column “Name”, then make it a “char(50)”.  You should see something like this:

Now click on the save button and name the table “student”.  Close the current window.

Next, we’re going to put some data into that table that we created.  Open the subdirectory named “Tables” and right click on the node named “dbo.student”, select “Open Table”.  Now put some data into your table.  Here’s a sample of what I typed into my table:


What are you learning?  You’re beginning to learn the basic tasks of setting up a database.  Next, let’s do a simple select query.  Click on your table node and then click the “New Query” button. There is a little drop-down list in the tool bar that is above your “Object Explorer” window and it should say “sampledata” in it.  That is the current database that will be queried.  If this drop-down does not have your database selected, then just select your database.  Then your queries will be performed against tables in your database.  Now type this into the query window:

select * from student

Hit the F-5 key, or click on the “! Execute” button.  You should see this at the bottom of your query window:


Now you can experiment with your query to select all the students and order by “name”, select students with “id” less than 3, etc. etc.  Create a second table that is related to the first table you created.  Then fill it with data and learn how to join two tables together.  Do some research on inner join and outer join.  Practice your queries by adding more related tables and test joining them together.  You should fully understand what an outer join is and the difference between a left and right outer join.  You could spend a day learning this knowledge, but it will become the knowledge that sets you apart from a newly minted college student.


Is that all?

Nope.  That’s just the beginning.  Next, you should download Visual Studio for web development.  The free version.  Type “visual studio express” into google and find the Microsoft products screen.  Currently “Express 2013 for Web” is available as well as “Express 2013 for Windows”.  Download “Express 2013 for Web” and install this on your machine.  You’ll need an MSN account to download it, this is free.  It’s a bit time consuming, but I’m going to teach you how to connect to that database that you just installed and created.

Once you’ve installed Express, start it up and create a new MVC4 project.  You’ll need to look under the “Visual C#” section then under “Web” and select “Visual Studio 2012” and finally click on the “ASP.NET MVC4 Web Application” type.  Give it a name and click “OK”.  Next it will ask you what type of MVC application you want, choose an empty project with razor in the drop-down.

In your solution explorer (the panel in the top right corner of visual studio) right click on the “controllers” directory and “add”, then click on “controller”.  Leave the dropdown as an “Empty MVC Controller”  and name it “HomeController”.  Click the “Add” button.  You should see a window with a little bit of code an din the middle is a “return View();” line of code.  Right click on “View()” and select “Add View” then click the “Add” button.  This will create a matching view for this controller.  Now you’ll see a cshtml web page.  At this point, you have a working website and it will startup a virtual webserver and a webpage will appear withe word “Index” on it.  Yeah!

OK, seriously.  Let’s connect to a database and read something, then print it on the web page.  Right-click on the “Models” directory in your “Solution Explorer” window, then “add -> New Item”.  Click on the “Data” item and click “ADO.NET Entity Data Model”:


Just click the “Add” button and leave the default name “Model.edmx”.  Click “Next” to generate from a database.  Now you’re going to need a new connection.  This is the tricky part.  Click the “New Connection” button and select a “Microsoft SQL Server” datasource.  Click “Continue” and another window appears.  You should be able to select your server from the “Server Name” list.  It should match the name that appears in the SQL server connect to database window.  The server name is also shown in the top of your “Object Explorer” of the SQL Server Management Studio window.  You can correct the name by typing it into the “Server name” text/drop-down control.  If it is correct, then you can select the database (leave the “use windows authentication” radio button alone).  Select your database and click “OK”.  Then click the “Next” button.  Now you’ll see this window (assuming all went as planned):


Now drill down into the tables and check all the tables you wish to access from your program and click the “Finish” button.  

If your table does not contain a primary key, then you’ll have to do some research (because EF will not allow tables without primary keys).  This is easy to fix by going back to the SQL Server console, right-click on your table and select “modify”, then right click on the “id” field and make it “set primary key”.  If your data has unique values in that field, you can save your changes.  If that field contains data that is null or duplicated, you’ll need to fix the data first, then come back and add the primary key. Sounds frustrating?  You’re learning a very important lesson about database design.  Set the primary key first, then put data in the table.

If you need to update or add tables to your entity framework, you can double-click the edmx file in the solution explorer of visual studio and then right-click on the blank window and select “Update Model from Database”.  That will take you back to the window pictured above and you can check all tables that you want to add to the edmx file.  You should see a little graphical representation of your table in the context window.  Now make sure you click on the model1.edmx window to ensure that it is currently selected, then click on the save button.

Now you have knowledge of how to setup an Entity Framework context.

Next, let’s go back to the HomeController.cs file and add a using at the top.  I’m trying to minimize the amount of code here, so I’m going to cheat and access the database directly from the controller.  Normally you’ll create classes in your Model directory and they will access your data and return it to your controller.  

Type this after your last using statement:

using TestMVC4Application.Models;

Now type this inside your controller (assuming you added a table named student to your context):

public ActionResult Index()
{
    using (var db = new sampledataEntities())
    {
        var studentNames = (from s in db.students select s.Name).ToList();

        return View();
    }

}

The “sampledataEntities()” name came from the context.  If you double-click on your edmx file and click on the background window, you’ll see the properties in the lower right that will tell you your “Entity Container Name”.  That is the context name.  Very important knowledge.

In the code above, I opened a context and called it “db”.  Entity framework renamed the table with an “s” on the end.  It creates a class with the table name and it creates a list with the same name and an “s” on the end.  Lesson to take from this is to always name your SQL Server tables in singular form (like “student” or “teacher, not “students” or “teachers”).  That will save you some headaches later when attempting to find the right names in your LINQ code.

Now your program will run, but nothing new will happen.  You’ll still get a webpage with “Index” printed on it.  However, you can now put a break-point on the “return View()” line of code and run your program.  There is a gray bar on the left side of the window.  Click on that bar at the line of code you wish to break on and it will turn the line red.


Now run your program.  When it stops at the red line, you can hover your mouse cursor over the studentNames variable and see that it has items in it.  Now add a line to put your studentNames into the viewbag variable:

public ActionResult Index()
{
    using (var db = new sampledataEntities())
    {
        var studentNames = (from in db.students select s.Name).ToList();

        ViewBag.studentNames = studentNames;

        return View();
    }

}

The ViweBag object is part of razor and it allows you to transfer data to the view.  Now double-click on your view called “Index.cshtml” (it’s located in the Views folder in the Solution Explorer.

Add a line to your view:

@{
    ViewBag.Title = “Index“;
}

<h2>Index</h2>

@ViewBag.studentNames[0]


This will print the first student name (it’s at index zero of an array of students).  Run your program and see for yourself.

If you got this far, then you have just learned a little MVC programming.  You learned a little C# programming.  You learned how to setup and use an ORM called Entity Framework.  You learned a little bit of Razor.  You also learned how to connect to a database server and read data into your application.

Now it’s time to run with it.  Find a web application that you want to build.  Something that is not too big, but big enough to be a challenge.  Design and build your database, test your queries using the SQL Server Management Studio console.  Add tables to your edmx context in your C# application.  Try out your queries in LINQ inside your controller.  Add controllers and views.  Tweak the HTML inside the view to display your data in a more attractive format.   If you can’t figure out how to do something, Google it.  There are a lot of Entity Framework LINQ sites that show how to do queries.  There’s a lot of examples of MVC programming and Razor.

You need a project that will hold your interest for a while.  Once you’ve built something that performs a real task, you’ll have a working knowledge of these technologies.  These are valuable tools that you should put on your resume.  Make sure your confident with these technologies and go nail down that job you really wanted.



 

Fluent NHibernate Using Multiple Database

Summary

This time I’m going to show how to use tables from two different databases in one context.  It’s difficult to find information on this subject and many links point to an article that uses a dictionary of context’s with all kinds of fancy configuration and code to map things.  After an hour of hunting around in that rabbit hole (technically, the technique being presented works across different database servers, where the version I’m looking for is multiple databases in one server), I stumbled onto something that Fluent NHibernate has that makes it all soooo clear.  Inside the class mappings for each table you can specify the exact table mapping in the database.  This can be used in situations where you want to use a different name for your class object table name than the database table name.  It can also be used to define which database your table is accessed from.


Setting Up

The ERD I’m going to use looks like this:



I’m trying to keep this simple, so there are really no integrity constraints as shown in the diagram above.  Each class can have one teacher, and each student can only be in one class (but classes can have more than one student).  This is not really practical, but it’s just a minor example.  Technically, I could have shown this example with two tables, but I want to demonstrate two tables in one database and one table in a different database.  The databases are named facultydata and studendata.

Don’t forget to populate your database with some data.  I hand-typed this data into the three tables shown:




On to the Code

Here’s the class for the Student table with the mappings.  This is where the magic occurs:

public class Student
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual int classid { getset; }
}

public class StudentMap : ClassMap<Student>
{
    public StudentMap()
    {
        Id(u => u.Id);
        Map(u => u.Name).Nullable();
        Map(u => u.classid).Not.Nullable();
        Table(“studentdata..student“);
    }

}

Notice the addition of the “Table” mapping.  Also notice that I introduced the database name right in the table mapping string.  NHibernate will replace the tablename in any “where” clause of it’s queries with the whole text inside this mapping.


How do I do the LINQ Query?

No difference.  Here’s the query that I used:

using (ISession session = NHibernateHelper.OpenSession())

{
    var query = (from t in session.Query<Teacher>()
            join c in session.Query<Class>() on t.Id equals c.TeacherId
            join s in session.Query<Student>() on c.Id equals s.classid
            select new 
            { 
                TeacherName = t.Name,
                ClassName = c.Name,
                StudentName = s.Name

            }).ToList();
}

That is the only change from previous NHibernate blog examples.  You can download the entire Visual Studio 2012 project here: 

NHibernateMultipleDatabases.zip


What if I have Two Tables with the Same Names?

For this situation, I would use the mapping to indicate which table is being referenced, then change the class name to reflect which table.  Let’s say that you had a student table in two different databases (who knows why, but this stuff happens).  You can name each class with a combination of database name and table name like StudentdataStudent and FacultydataStudent.  When you form your LINQ query, you’ll use the long name version of your tables and the ORM will map to the table in the correct database.

 

Fluent NHibernate with MS SQL Server

Summary

I’m currently researching all my options regarding ORM’s.  In my previous posts I talked about NHibernate and how to setup a simple example.  This time I’m going to demonstrate how Fluent NHibernate works and how you can use it to avoid setting up xml documents for mapping your tables.

Project Setup

Create an empty console application in Visual Studio.  Go to your NuGet window (Tools -> NuGet Package Manager -> Manage NuGet Packages For Solution…).  Search for FluentNHibernate and add the package to your project.

Next, create a subdirectory inside the project called “domain”.  Now create a cs file named “product.cs” and copy this code into it:

using FluentNHibernate.Mapping;

namespace FluentNHibernateBlogPost
{
    public class Product
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Category { get; set; }
        public virtual bool Discontinued { get; set; }
        public virtual int Store { get; set; }
    }

    public class ProductMap : ClassMap<Product>
    {
        public ProductMap()
        {
            Id(u => u.Id);
            Map(u => u.Name).Nullable();
            Map(u => u.Category).Nullable();
            Map(u => u.Discontinued);
            Map(u => u.Store);
        }
    }

}

Next, add a cs file called “store.cs” and copy this code into it:

using FluentNHibernate.Mapping;
using System.Collections.Generic;

namespace FluentNHibernateBlogPost
{
    public class Store
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Address { get; set; }
        public virtual string City { get; set; }
        public virtual string Zip { get; set; }
    }

    public class StoretMap : ClassMap<Store>
    {
        public StoretMap()
        {
            Id(u => u.Id);
            Map(u => u.Name).Nullable();
            Map(u => u.Address).Nullable();
            Map(u => u.City).Nullable();
            Map(u => u.Zip).Nullable();
        }
    }

}

Now we need the session factory.  Create a cs file in the root directory and name it “SessionFactory.cs”, copy this code into it (modify your connection string to match your database, you can copy it right out of the xml file from the previous project, if you got that to work):

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHibernate.Tool.hbm2ddl;

namespace FluentNHibernateBlogPost
{
    public class NHibernateHelper
    {
        private static ISessionFactory _sessionFactory;
        private static ISessionFactory SessionFactory
        {
            get
            {
                if (_sessionFactory == null)
                {
                    _sessionFactory = Fluently.Configure()
                    .Database(MsSqlConfiguration.MsSql2005
                    .ConnectionString(“Server=localhost;Initial Catalog=sampledata;Integrated Security=True“))
                    .Mappings(m => m.FluentMappings.AddFromAssemblyOf<Program>())
                    .ExposeConfiguration(config =>
                    {
                        SchemaExport schemaExport = new SchemaExport(config);
                    })
                    .BuildSessionFactory();
                }
                return _sessionFactory;
            }
        }
        public static ISession OpenSession()
        {
            return SessionFactory.OpenSession();
        }
    }

}

Now you can write a query and access your data.  For this sample, I’m going to use the exact same query that I did in my previous blog post just to demonstrate that the output will be identical.  Modify your Program.cs file to look like this:

using System;
using System.Linq;
using NHibernate;
using NHibernate.Linq;

namespace FluentNHibernateBlogPost
{
    class Program
    {
        static void Main(string[] args)
        {
            using (ISession session = NHibernateHelper.OpenSession())
            {
                var query = from p in session.Query<Product>()
                            join s in session.Query<Store>() on p.Store equals s.Id
                            where s.Id == 2
                            select p;

                foreach (var item in query)
                {
                    Console.WriteLine(item.Name);
                }
            }
            Console.ReadKey();
        }
    }

}

That’s it.  That is the entire program.  No XML files.  No embedded properties, no copy always properties.  No special configuration files.  Just code.  One of the advantages that I’m looking for in this example is that this project will be easy to merge when multiple developers alter tables.  When hand-merging occurs, the developer will be looking at C# code and not attempting to decipher xml.

You can download the entire project here: FluentNHibernateBlogPost.zip


 

NHibernate and Linq

Summary

In my last post, I showed how to get NHibernate up and running.  This article will talk about using Linq with NHibernate.  I’m also going to add another table to the mix and show a sample query that joins these two tables together.


Using Linq

If you typed in the project from my last post or you downloaded the project (download it here), then you can continue on with this part of the task.  One of the static objects provided in the IProductRepository.cs file was the NHibernateHelper class.  I’m going to use the OpenSession() method available from this class to create a context for my test query.  In the Program.cs file (the starting method of your program), add some headers:

using System;
using System.Linq;
using NHibernate;

using NHibernate.Linq;

Now add the following code to your “Main” method:

using (ISession session = NHibernateHelper.OpenSession())
{
    var query = from p in session.Query<Product>() select p;

    foreach (var item in query)
    {
        Console.WriteLine(item.Name);
    }
}

Console.ReadKey();

Now, when you execute your program, you’ll see a list of all the product names in the console window (as well as the query that was sent).  The key to making this work is to include the NHibernate.Linq (as well as the normal System.Linq).  Then you can use Linq similar to Entity Framework.  One difference in the query you might have noticed is the “session.Query<Product>()” syntax.  “Query” is NHibernate version 3.  Versions 2 and earlier used “Linq” in the syntax.  Entity Framework only uses the table name (like “session.Product”).


Adding a Second Table

Now, let’s add a second table.  First, I’m going to create the table in MS SQL Server, called “Store”.  This will be the table that contains a list of stores where the products are located.  You can use this SQL script to create the new table:

USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Store](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Add a foreign key field to your product table:

ALTER TABLE dbo.Product ADD Store2 int NULL

Then populate the store table with two records:

INSERT INTO store
(
Name
)
VALUES
(
Shop X
)
GO
INSERT INTO store
(
Name
)
VALUES
(
East Market
)

GO

Then doctor up the product foreign key to point to these two stores:

UPDATE product SET store=1

UPDATE product SET store=2 WHERE id > 2 

OK, one more SQL step to get the data in synch with what I’m doing here.  Create a relational integrity constraint between the product foreign key and the store table:

ALTER TABLE product ADD FOREIGN KEY (store) REFERENCES Store(Id)

On to the Code

Now we need to add a “store.hbm.xml” file and a “store.cs” file, just like we did with product.  Add the store.hbm.xml file to the mappings directory.  Make sure the “Build Action” property is set to “Embedded Resource”.  Add this code to the file:

<?xml version=”1.0encoding=”utf-8” ?>
<hibernate-mapping xmlns=”urn:nhibernate-mapping-2.2assembly=”NHibernateTestBlognamespace=”NHibernateTestBlog“>

  <class name=”Storetable=”Store“>
    <id name=”Idcolumn=”Idtype=”Int32“>
      <generator class=”identity” />
    </id>
    <property name=”Namecolumn=”Name” />
    <property name=”Addresscolumn=”Address” />
    <property name=”Citycolumn=”City” />
    <property name=”Zipcolumn=”Zip” />
  </class>


</hibernate-mapping>

Now create the store.cs file inside your domain folder, and copy this into it:

namespace NHibernateTestBlog
{
    public class Store
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Address { get; set; }
        public virtual string City { get; set; }
        public virtual string Zip { get; set; }
    }

}

At this point, we can write a query to join these two tables together.  Go to “Program.cs” and change your query to look like this:

var query = from p in session.Query<Product>() 
            join s in session.Query<Store>() on p.Store equals s.Id
            where s.Id == 2

            select p;

When you run your program, you’ll see the three products that belong to store number 2.

Summary

At this point I’ve only covered an example where multiple tables are joined using Linq in NHibernate.  One minor issue to note is that I did not set the foreign key (product.store) to a not null field.  It’s best to do this to prevent someone from attempting to insert a record with a null for this field (the constraint in SQL will complain).

There is also a method to setup many-to-one list objects in NHibernate that I’m not going to dive into yet.  For an example of this technique I will refer you to this article: Playing with NHibernate – Inverse and Cascade mapping attributes.




 

EF-6 Unit testing (Part 2)

Introduction

I’ve covered some unit testing in a previous blog post.  This time I want to expand on my earlier post and show how to mock two or more tables.  I will also demonstrate a weakness I discovered in mocking a database using the mock and a method to get around it.


Two or more Tables

You might remember this code:

using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;


namespace DatabaseTestConsole
{
    [TestClass]
    public class UnitTests
    {
        [TestMethod]
        public void TestQuery()
        {
            var data = new List<account>
            {
                new account { username = “test“,pass=”testpass1” },
                new account { username = “ZZZ“,pass=”testpass2” },
                new account { username = “AAA“,pass=”testpass3” },
            }.AsQueryable();


            var mockSet = new Mock<DbSet<account>>();
            mockSet.As<IQueryable<account>>().Setup(m => m.Provider)

                   .Returns(data.Provider);
            mockSet.As<IQueryable<account>>().Setup(m => m.Expression)

                   .Returns(data.Expression);
            mockSet.As<IQueryable<account>>().Setup(m => m.ElementType)

                   .Returns(data.ElementType);
            mockSet.As<IQueryable<account>>().Setup(m => m.GetEnumerator())

                   .Returns(data.GetEnumerator());

            var mockContext = new Mock<DatabaseContext>();
            mockContext.Setup(c => c.accounts).Returns(mockSet.Object);


            UserRights rights = new UserRights(mockContext.Object);

            Assert.AreEqual(“testpass1”, rights.LookupPassword(“test“),

                  “password for account test is incorrect“);
            Assert.AreEqual(“testpass2”, rights.LookupPassword(“ZZZ“),

                  “password for account ZZZ is incorrect“);
            Assert.AreEqual(“testpass3”, rights.LookupPassword(“AAA“),

                  “password for account AAA is incorrect“);
        }
    }
}


I used it to demonstrate how to mock a table and unit test it.  Now I’m going to add a table to the mix.  First, I’m going to add an object that uses two tables (departments and accounts).  Then I’ll show how to mock the database that it uses:

public class PersonnelPerDepartment
{
    private DepartmentContext _DeptContext;

    public PersonnelPerDepartment(DepartmentContext deptContext)
    {
        _DeptContext = deptContext;
    }

    public int TotalPersonnel()
    {
        var personnelDeptQuery = (
            from d in _DeptContext.departments
            join p in _DeptContext.people on d.id equals p.department
            select p).ToList();

        return personnelDeptQuery.Count();
    }
}

I specifically setup the object to require two tables to query from.  Then I altered the unit test code as thus:

[TestMethod]
public void TestTwoTables()
{
    var deptData = new List<department>
    {
        new department {id=1, name=”Operations“},
        new department {id=2, name=”Sales“}
    }.AsQueryable();

    var deptMockSet = new Mock<DbSet<department>>();
    deptMockSet.As<IQueryable<department>>().Setup(m => m.Provider)
        .Returns(deptData.Provider);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.Expression)
        .Returns(deptData.Expression);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.ElementType)
        .Returns(deptData.ElementType);
    deptMockSet.As<IQueryable<department>>().Setup(m => m.GetEnumerator())
        .Returns(deptData.GetEnumerator());

    // department table
    var persData = new List<person>
    {
        new person {id=1, first=”Joe“,last=”Smith“,department=1},
        new person {id=2, first=”Jane“, last=”Summers“,department=1},
        new person {id=2, first=”Bob“, last=”Anders“,department=1},
    }.AsQueryable();

    var personMockSet = new Mock<DbSet<person>>();
    personMockSet.As<IQueryable<person>>().Setup(m => m.Provider)
        .Returns(persData.Provider);
    personMockSet.As<IQueryable<person>>().Setup(m => m.Expression)
        .Returns(persData.Expression);
    personMockSet.As<IQueryable<person>>().Setup(m => m.ElementType)
        .Returns(persData.ElementType);
    personMockSet.As<IQueryable<person>>().Setup(m => m.GetEnumerator())
        .Returns(persData.GetEnumerator());

    var mockContext = new Mock<DepartmentContext>();
    mockContext.Setup(c => c.departments).Returns(deptMockSet.Object);
    mockContext.Setup(c => c.people).Returns(personMockSet.Object);

    PersonnelPerDepartment persDept = new PersonnelPerDepartment(mockContext.Object);
    int total = persDept.TotalPersonnel();

    Assert.AreEqual(3, total);
}
Each table will need it’s own mockSet, which I admit, is ugly.  The mockContext has two setups, one for each table (and you can add more tables to the context as needed).  Now the test runs and returns the three personnel listd in the person table.  You can experiment with different data sets and queries to test this for yourself.  I wanted to give an example of how the code would look for two tables.
I’ve been writing unit tests for a real application that I’m working on for DealerOn.  This application has dozens of tables and, as you might have guessed, this method gets lengthy.  Of course, you can setup all this stuff in an initialization method before running your tests, but there are other problems.  One problem I discovered was that you cannot add to your tables in your methods under test.  This mock object doesn’t support adding or deleting records.  For that, we need a different technique.
Using Test Doubles
This article at microsoft (Testing with your own test doubles (EF6 onwards)) demonstrates how to use test doubles with EF-6.  The advantages to using this technique is that you can add and delete from your tables and the unit testing code is more compact (most of the code is in the fake object).  
How to do it

First, create a solution with a console application project and and a test project.  Make sure you include your console app in your references inside your unit test project and include your using statement so you can unit test objects in your console application.
Next create an EF-6 edmx file in your console application and add your tables (my example uses my demo database that I’ve been using for quite a few blog posts.  This MS SQL server sample data has a department and person table in it).
Go to the link above (testing doubles) and copy the code under the header “Creating the in-memory test doubles”, then paste it into it’s own cs file in your unit testing project.  You’ll need to change the top class to match your EF-6 database:
public class TestContext : ISampleDataContext
{
    public TestContext()
    {
        this.people = new TestDbSet<person>();
        this.departments = new TestDbSet<department>();
    }

    public DbSet<person> people { get; set; }
    public DbSet<department> departments { get; set; }
    public int SaveChangesCount { get; private set; }
    public int SaveChanges()
    {
        this.SaveChangesCount++;
        return 1;
    }
}
You’ll need to add a bunch of usings to make the errors go away.  You should end up with these:
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using EF6UnitTestingUsingDoubles; //your console project
In your console project, create an interface.  It’ll look something like this:
using System.Data.Entity;

namespace EF6UnitTestingUsingDoubles
{
    public interface ISampleDataContext
    {
        DbSet<person> people { get; set; }
        DbSet<department> departments { get; set; }
        int SaveChanges();
    }
}
You’ll need to add this interface to your EF context.cs file constructor:
public partial class sampledataEntities : DbContext, ISampleDataContext
{
    public sampledataEntities()
        : base(“name=sampledataEntities“)
    {
    }
    
}
It’s best to put the “ISampleDataContext” (and the comma) in the T4 (“tt”) script file, so auto-generated code will not wipe out your changes:
<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext, ISampleDataContext


Next create your object in the main console app:
using System.Linq;

namespace EF6UnitTestingUsingDoubles
{
    public class PersonnelPerDepartment
    {
        private ISampleDataContext _DeptContext;

        public PersonnelPerDepartment(ISampleDataContext deptContext)
        {
            _DeptContext = deptContext;
        }

        public int TotalPersonnel()
        {
            var personnelDeptQuery = (
                from d in _DeptContext.departments
                join p in _DeptContext.people on d.id equals p.department
                select p).ToList();

            return personnelDeptQuery.Count();
        }
    }
}

One thing to note: You will be passing the interface definition to your classes instead of the Entity Framework context object like normal.  When your program executes normally, it will use the interface just like the fake context does when performing your unit tests.

Last, but not least, your unit test:

[TestMethod]
public void TestMethod1()
{
    var context = new TestContext();

    var deptTable = new department()
    {
        id=1,
        name=”Sales
    };
    context.departments.Add(deptTable);
    context.SaveChanges();

    var personTable = new person()
    {
        id=1,
        first = “Joe“,
        last = “Smith“,
        department = 1
    };
    context.people.Add(personTable);
    personTable = new person() 
    { 
        id = 2, 
        first = “Jane“, 
        last = “Summers“, 
        department = 1 
    };
    context.people.Add(personTable);
    context.SaveChanges();

    PersonnelPerDepartment persDept = new PersonnelPerDepartment(context);
    int total = persDept.TotalPersonnel();

    Assert.AreEqual(2, total);
}
Notice how much cleaner the test method is.  The first line creates a test context, then one record is added to the department table.  Next two records are added to the person table and that leaves the remaining lines to call the method under test (persDept) and do an assert.  The fake context object and the interface should be setup to contain all the tables in your context.  You will then be able to add data to your tables inside your unit tests as needed.  Your methods under test can perform queries on all the tables in the context without an issue when under tests.
Downloadable Sample Project
You can download the entire sample project here: EF6 Unit Testing Doubles Project.  Be aware that this project does not include the database and you’ll need to alter the connection to your database and possibly manipulate the edmx file to make it work with your database.  If you have an MS SQL server setup on your PC, then you can use these two scripts to generate the tables used in this sample (after creating a database called “sampledata”):
USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[person](
[id] [int] IDENTITY(1,1) NOT NULL,
[first] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[department] [int] NOT NULL,
 CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
USE [sampledata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[department](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF