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

时间:2021-09-09 02:36:27

1、Entity Framework Code First查询视图

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

  Entity Framework Code First查询视图示例:

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

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

  文件类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(); this.Property(t => t.ProvinceName)
.HasMaxLength(); this.Property(t => t.CityNo)
.HasMaxLength(); this.Property(t => t.CityName)
.HasMaxLength(); // 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", );
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);