Entity Framework Code First执行SQL语句、视图及存储过程

1、Entity Framework Code First查询视图

　　Entity Framework Code First目前还没有特别针对View操作的方法，但对于可更新的视图，可以采用与Table一样的方式进行插入、修改、删除及查询。在实际的项目过程中，视图多只用于进行查询。

　　Entity Framework Code First查询视图示例：

　　使用到的表及视图结构如下：

　　文件类VCity.cs：

using System;using System.Collections.Generic;namespace Portal.Models{    public class VCity    {        public int CityID { get; set; }        public Nullable<int> ProvinceID { get; set; }        public string ProvinceNo { get; set; }        public string ProvinceName { get; set; }        public string CityNo { get; set; }        public string CityName { get; set; }    }}

　　映射文件类VCityMap.cs：

using System.ComponentModel.DataAnnotations.Schema;using System.Data.Entity.ModelConfiguration;namespace Portal.Models.Mapping{    public class VCityMap : EntityTypeConfiguration<VCity>    {        public VCityMap()        {            // Primary Key            this.HasKey(t => t.CityID);            // Properties            this.Property(t => t.CityID)                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);            this.Property(t => t.ProvinceNo)                .HasMaxLength(10);            this.Property(t => t.ProvinceName)                .HasMaxLength(50);            this.Property(t => t.CityNo)                .HasMaxLength(10);            this.Property(t => t.CityName)                .HasMaxLength(50);            // Table & Column Mappings            this.ToTable("VCity");            this.Property(t => t.CityID).HasColumnName("CityID");            this.Property(t => t.ProvinceID).HasColumnName("ProvinceID");            this.Property(t => t.ProvinceNo).HasColumnName("ProvinceNo");            this.Property(t => t.ProvinceName).HasColumnName("ProvinceName");            this.Property(t => t.CityNo).HasColumnName("CityNo");            this.Property(t => t.CityName).HasColumnName("CityName");        }    }}

　　文件类PortalContext.cs：

using System.Data.Entity;using System.Data.Entity.Infrastructure;using Portal.Models.Mapping;namespace Portal.Models{    public class PortalContext : DbContext    {        static PortalContext()        {            Database.SetInitializer<PortalContext>(null);        }        public PortalContext()            : base("Name=PortalContext")        {        }        public DbSet<City> Cities { get; set; }        public DbSet<Province> Provinces { get; set; }        public DbSet<VCity> VCities { get; set; }        protected override void OnModelCreating(DbModelBuilder modelBuilder)        {            modelBuilder.Configurations.Add(new CityMap());            modelBuilder.Configurations.Add(new ProvinceMap());            modelBuilder.Configurations.Add(new VCityMap());        }    }}

　　文件类Program.cs，用于查询视图：

using System;using System.Collections.Generic;using System.Linq;using System.Text;using Portal.Models;namespace Portal{    class Program    {        static void Main(string[] args)        {            using (var ctx = new PortalContext())            {                foreach (var vCity in ctx.VCities)                {                    Console.WriteLine("{0}-{1}-{2}-{3}-{4}-{5}", vCity.CityID, vCity.ProvinceID, vCity.ProvinceNo, vCity.ProvinceName, vCity.CityNo, vCity.CityName);                }            }            Console.ReadKey();        }    }}

2、Entity Framework Code First执行SQL语句

　　在使用Entity Framework Code First时，当需要直接执行SQL时，可以使用SqlQuery方法。SqlQuery方法采用属性名即列名的方法进行映射查询，要求返回的查询结果均有完全对应的类属性。

　　1>、已定义的表映射类查询

using (var ctx = new PortalContext()){    var provincelist = ctx.Provinces.SqlQuery("SELECT TOP 10 * FROM Province");    foreach (var province in provincelist)    {        Console.WriteLine("{0}-{1}-{2}", province.ProvinceID, province.ProvinceNo, province.ProvinceName);    }}

　　2>、未有定义表的临时SQL语句查询

　　示例：需要查询Province表中的ProvinceNo，ProvinceName，首先定义一个临时类TempProvince.cs：

using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace Portal.Models{    public class TempProvince    {        public string ProvinceNo { get; set; }        public string ProvinceName { get; set; }    }}

　　执行SQL语句查询：

using (var ctx = new PortalContext()){    var provincelist = ctx.Database.SqlQuery<TempProvince>("SELECT TOP 10 ProvinceNo,ProvinceName FROM Province");    foreach (var province in provincelist)    {        Console.WriteLine("{0}-{1}", province.ProvinceNo, province.ProvinceName);    }}

3、Entity Framework Code First执行存储过程

　　Entity Framework Code First执行存储过程同样是使用SqlQuery方法。

　　创建存储过程：

CREATE PROCEDURE GetCityByProvinceID(    @ProvinceID INT)AS    SELECT * FROM City    WHERE ProvinceID = @ProvinceID

　　执行存储过程：

using (var ctx = new PortalContext()){    var cityList = ctx.Cities.SqlQuery("dbo.GetCityByProvinceID @p0", 3);    foreach (var city in cityList)    {        Console.WriteLine("{0}-{1}-{2}-{3}", city.CityID, city.ProvinceID, city.CityNo, city.CityName);    }}

　　存储过程多个输入参数：

var country = "Australia";var keyWords = "Beach, Sun";var destinations = context.Database.SqlQuery<DestinationSummary>("dbo.GetDestinationSummary @p0, @p1", country, keyWords);