Entity Framework

[netcat-na-diary]

Entity Framework

https://msdn.microsoft.com/en-us/data/jj193542.aspx

Server Name .\SQLEXPRESS

Создать конструктор

namespace OdeToFood.Models
{
    public class OdeToFoodDb : DbContext
    {
        public OdeToFoodDb() :base("server=")
        {

        }
        
        public DbSet<Restaurant> Restaurants { get; set; }
        public DbSet<RestaurantReview> Reviews { get; set; }
    }
}

Sample Connection String:

server=production;initial catalog=fooddb;integrated security=true


Строка соединения с БД прописана в файле Web.config

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-OdeToFood-20151105182223;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-OdeToFood-20151105182223.mdf" providerName="System.Data.SqlClient" />
  </connectionStrings>

И прописать в конструкторе

namespace OdeToFood.Models
{
    public class OdeToFoodDb : DbContext
    {
        public OdeToFoodDb()
            : base("name=DefaultConnection")
        {

        }
        
        public DbSet<Restaurant> Restaurants { get; set; }
        public DbSet<RestaurantReview> Reviews { get; set; }
    }
}

Database Migrations

Используем Package Manager Console

Команды
Get-Date
Enable-Migrations -ContextTypeName OdeToFoodDb

Файл Configuration.cs

namespace OdeToFood.Migrations
{
    using OdeToFood.Models;
    using System;
    using System.Collections.Generic;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;

    internal sealed class Configuration : DbMigrationsConfiguration<OdeToFood.Models.OdeToFoodDb>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }

        protected override void Seed(OdeToFood.Models.OdeToFoodDb context)
        {
            context.Restaurants.AddOrUpdate(r => r.Name,
                new Restaurant { Name = "Sabatino's", City = "Baltimore", Country = "USA" },
                new Restaurant { Name = "Great Lake", City = "Chicago", Country = "USA" },
                new Restaurant
                {
                    Name = "Smaka",
                    City = "Gothenburg",
                    Country = "Sweden",
                    Reviews =
                       new List<RestaurantReview> {
                            new RestaurantReview { Rating = 9, Body = "Great food!"}
                        }
                });
        }
    }
}

Команда
Update-Database -Verbose

LINQ

var query = _db.Restaurants
                           .Where(r => r.Country == "USA")
                           .OrderBy(r => r.Name)
                           .Skip(10)
                           .Take(10);

Запрос

public ActionResult Index()
        {
            var model =
                from r in _db.Restaurants
                orderby r.Name ascending
                select r;

            
            return View(model);
        }

public ActionResult Index()
        {
            //var model =
            //    from r in _db.Restaurants
            //    orderby r.Reviews.Average(review => review.Rating) descending
            //    select new RestaurantListViewModel
            //    {
            //        Id = r.Id,
            //        Name = r.Name,
            //        City = r.City,
            //        Country = r.Country,
            //        CountOfReviews = r.Reviews.Count()
            //    };


            var model =
                _db.Restaurants
                   .OrderByDescending(r => r.Reviews.Average(review => review.Rating))
                   .Take(10)
                   .Select(r => new RestaurantListViewModel
                            {
                                Id = r.Id,
                                Name = r.Name,
                                City = r.City,
                                Country = r.Country,
                                CountOfReviews = r.Reviews.Count()
                            }
                            );
            
            return View(model);
        }

Апдейт базы данных
Update-Database -Verbose -Force