dnasca

/*
  The second step in creating the data model class will be modeling the Reply. 
  Each reply will have it's own Id, a body, created date, and a property that will point Entity to creating a foriegn key (TopicId)
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

dnasca

/*
  Entity framework expects a Context class. This class is used to communicate between the code and the dB. Since we built our model
  classes by convention, we won't need to do much in the way of configuration of our context.
  
  This is the pipeline to all queries, inserts, updates, deletes for any data operation is going to be passed through.
  
  *The next class we'll build will be the NoteRepository class to wrap the context in.
*/

using System;

dnasca

/*
  
  We'll use an interface because there are going to be places in the future when we want to be able to
  write unit tests. For example, to test against things like controllers. The controller will need a repository to do their data access.
  This allows us to split the methods that need to be implemented into a testing and live environment without having to re-write code.

  The interface will provide individual methods that have the data operations we will need.
  
  We will use the return type of IQueryable to return Topic objects. This is to allow users to page, sort, group, and filter. 
  If we did not want/need to do this, we could use IEnumerable instead. This is important because any operations that are requested

dnasca

/*
  This is where all of the steps are tied together.
  
  After we complete this class, we will simply supply the Repository (which wraps the Context) directly to the 
  Controllers constructor method as a parameter.
  
  --For example. After we complete the class below, the Controller might look something like this:
  
    public class HomeController : Controller
    {

dnasca

##Using the baseball statistics dB found at http://www.seanlahman.com/baseball-archive/statistics/ 
SELECT 
    b.playerID,
    CONCAT(m.nameFirst, ' ', m.nameLast) AS NAME,
    SUM(b.AB) AS AB,
    SUM(b.HR) AS HR,
    SUM(b.R) AS R,
    SUM(b.RBI) AS RBI,
    SUM(b.SB) AS SB,
    ROUND(b.bb / (b.AB + b.BB + b.HBP + COALESCE(b.SF, 0)) * 100,

dnasca

SELECT 
    b.playerID,
    CONCAT(m.nameFirst, ' ', m.nameLast) AS NAME,
    AB,
    H,
    2B,
    3B,
    HR,
    R,
    RBI,

dnasca

##Using the baseball statistics dB found at http://www.seanlahman.com/baseball-archive/statistics/ 
SELECT 
    b.playerID,
    CONCAT(m.nameFirst, ' ', m.nameLast) AS NAME,
    SUM(b.AB) AS AB,
    ROUND(b.bb / (b.AB + b.BB + b.HBP + COALESCE(b.SF, 0)) * 100, 2) AS BBPERC,
    ROUND(b.so / (b.AB + b.BB + b.HBP + COALESCE(b.SF, 0)) * 100, 2) AS SOPERC,
    ROUND(SUM(b.H) / SUM(b.AB), 3) AS AVG
FROM
    Batting b,

dnasca

#using the Lahman Baseball Database 
SELECT 
    CONCAT(YEAR(m.debut), ' - ', YEAR(m.finalGame)) AS YEARSPLAYED,
    CONCAT(m.nameFirst, ' ', m.nameLast) AS NAME,
    SUM(b.AB) AS AB,
    ROUND(SUM(b.so) / SUM(b.AB + b.BB + b.HBP + COALESCE(b.SF, 0)) * 100, 2) AS SOPERC

FROM
    Batting b,
    Master m

dnasca

#using the Lahman Baseball Database 
SELECT 
    m.birthYear AS BIRTH,
    CONCAT(YEAR(m.debut), ' - ', YEAR(m.finalGame)) AS YEARSPLAYED,
    CONCAT(m.nameFirst, ' ', m.nameLast) AS NAME,
    SUM(b.AB) AS AB,
    ROUND(SUM(b.so) / SUM(b.AB + b.BB + b.HBP + COALESCE(b.SF, 0)) * 100, 2) AS SOPERC,
    SUM(b.h) AS H,
    SUM(b.h - (2b + 3b + hr)) AS 1B

dnasca

#using the Lahman Baseball Database 
SELECT 
    m.birthYear AS BIRTH,
    CONCAT(m.nameFirst, ' ', m.nameLast) AS NAME,
    SUM(b.AB) AS AB,
    SUM((b.H - b.HR)/(b.AB - b.SO - b.HR + COALESCE(b.SF, 0))) AS BABIP #Batting Average on Balls In Play
	
FROM
    Batting b,
    Master m