Entity Framework & LINQ (Language Integrated Query)

1. Entity Framework& LINQ (Language Integrated Query) MIS 324 Professor Sandvig

2. Outline • Entity Framework • Model generation • Scaffolding • Controllers & views • LINQ • Language integrated query

3. What is Entity Framework • Short version: • Code-less CRUD web interface • Microsoft’s ORM (object relation mapper) • Wizards create model, controller, views from database

4. What is Entity Framework • Dapper lightweight ORM • Fast • Uses standard SQL queries • EF is ORM + code writing wizards

5. EF Model • Examines database • Generates model of database tables • Example: • Database with three tables • VS: Add  New Item  ADO.NET Entity Data Model

6. EF Controller & Views • VS: Add > New Scaffolded Item • Generates: • Controller with CRUD methods • Views for CRUD

7. EF Controller & Views • Run Scaffolding wizard for each table • CRUD Interface

8. Entity Framework • VS wizards: • Create models from database • Create database from model • Createmodel and database from db.model • Create MVC controller and views from models

9. Entity Framework • Very convenient • Scaffolding wizard generates: • Controller • Views • Time: minutes instead of hours • Can add validation, customize view, etc.

10. LINQ • Language Integrated Query • Alternative to SQL • Select, sort, filter, etc. • Used by Entity Framework • Eliminates disconnect between database & code

11. LINQ - Intellisense • We have data models • Same as database • LINQ examines models • Provides Intellisense for writing queries

12. LINQ SQL Comparison • Example: • Sql: string sql = "select Id, Fname, Lname, street, city, state from students where id = @Id"; Execute query and map query results to model • LINQ • Student student = db.Students.Find(id);

13. LINQ - Intellisense See Student LINQ Controller handout

14. LINQ • LINQ can query many collection types:

15. Drawbacks • Microsoft propriety technology • Not useful outside MS universe • LINQ query syntax different than SQL • Better in some ways • Synchronization between database and entity objects • Change database • Rebuild entity objects

16. HW 1 • Tutorial HW 1: Getting Started with Entity Framework 6 Database First using MVC 5 • Steps: • Create database with three tables: • Students, courses, enrollments • Create EF model with wizard • Create controllers and view with wizard

17. Summary • Entity Framework • Very handy for CRUD operations • VS wizards create: • Models • Controllers • Views