Entity Framework的“Code First”模式也同样可以基于现有数据库进行开发。今天就让我们一起看一下使用Entity Framework Power Tools如何基于现有数据库生成数据类和数据库上下等。
基于现有数据库生成POCO数据类和数据库上下文需要借助Visual Studio一个扩展插件--Entity Framework Power Tools(一个Code First反向工程工具)。只要在Visual Studio扩展里面输入“Entity Framework Power”搜索即可找到最新的扩展,点击下载即可(如下图)。
安装完之后只要在项目上右键选择Entity Framework->Reverse Engineer Code First(项目中首先需要安装Entity Framework 包,否则会有错误),然后在弹出的窗口中输入相关的数据库连接信息即可(我们这里使用“AdventureWorks”数据库)。
不过:在点击OK时候,或许会弹出如图
对于“异常来自 HRESULT:0x80070057 (E_INVALIDARG)”,有方法说“删除 C:/WINDOWS/Microsoft.NET/Framework/v4.0.30319/Temporary ASP.NET files这个文件夹”,只是删除掉该文件夹之后,再次尝试生成代码时仍然会报出上面的错误。
这里提供的解决方法:
在“连接属性”中,点击“高级”,修改“Persist Security Info” 为“True”,具体设置如下图所示:
注意:如果使用“AdventureWorks”数据库,可能发生如下错误: One or more errors occurred while loading schema information. error 6004: The table 'AdventureWorks.Production.Document' is referenced by a relationship, but cannot be found. 这是由于EF目前还不支持SQL Server 2008中新增的“Hierarchyid”数据类型,目前只有先不用该类型的数据了,暂时将它改为其他类型。 |
然后我们稍等片刻,可以看到Entity Framework Power Tools已经根据所选数据库自动为你生成了数据类、数据库上下文操作类、对应的映射配置类并添加了数据库连接配置。
我们先看一下配置文件,可以看到在配置文件中Entity Framework Power Tools已经自动对数据库连接串进行了配置,添加了名为“AdventureWorksContext”的数据库连接串:
<?xml version="1.0" encoding="utf-8"?> <configuration> <configSections> <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 --> <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" /> </configSections> <connectionStrings> <add name="AdventureWorksContext" connectionString="Data Source=.\SQL2008;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=123;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" /> </connectionStrings> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" /> </startup> <entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework"> <parameters> <parameter value="v11.0" /> </parameters> </defaultConnectionFactory> </entityFramework> </configuration>
与此同时,对生成的数据库上下文操作类“AdventureWorksContext”进行了配置,在构造函数中使用了上面配置的“AdventureWorksContext”数据库连接:
using System.Data.Entity; using System.Data.Entity.Infrastructure; using EFPowerTools.Models.Mapping; namespace EFPowerTools.Models { public partial class AdventureWorksContext : DbContext { static AdventureWorksContext() { Database.SetInitializer<AdventureWorksContext>(null); } public AdventureWorksContext() : base("Name=AdventureWorksContext") { } public DbSet<AWBuildVersion> AWBuildVersions { get; set; } public DbSet<DatabaseLog> DatabaseLogs { get; set; } public DbSet<ErrorLog> ErrorLogs { get; set; } public DbSet<OrderDetail> OrderDetails { get; set; } public DbSet<Order> Orders { get; set; } public DbSet<Department> Departments { get; set; } public DbSet<Employee> Employees { get; set; } public DbSet<EmployeeDepartmentHistory> EmployeeDepartmentHistories { get; set; } public DbSet<EmployeePayHistory> EmployeePayHistories { get; set; } public DbSet<JobCandidate> JobCandidates { get; set; } public DbSet<Shift> Shifts { get; set; } public DbSet<Address> Addresses { get; set; } public DbSet<AddressType> AddressTypes { get; set; } public DbSet<BusinessEntity> BusinessEntities { get; set; } public DbSet<BusinessEntityAddress> BusinessEntityAddresses { get; set; } public DbSet<BusinessEntityContact> BusinessEntityContacts { get; set; } public DbSet<ContactType> ContactTypes { get; set; } public DbSet<CountryRegion> CountryRegions { get; set; } public DbSet<EmailAddress> EmailAddresses { get; set; } public DbSet<Password> Passwords { get; set; } public DbSet<Person> People { get; set; } public DbSet<PersonPhone> PersonPhones { get; set; } public DbSet<PhoneNumberType> PhoneNumberTypes { get; set; } public DbSet<StateProvince> StateProvinces { get; set; } public DbSet<BillOfMaterial> BillOfMaterials { get; set; } public DbSet<Culture> Cultures { get; set; } public DbSet<Illustration> Illustrations { get; set; } public DbSet<Location> Locations { get; set; } public DbSet<Product> Products { get; set; } public DbSet<ProductCategory> ProductCategories { get; set; } public DbSet<ProductCostHistory> ProductCostHistories { get; set; } public DbSet<ProductDescription> ProductDescriptions { get; set; } public DbSet<ProductInventory> ProductInventories { get; set; } public DbSet<ProductListPriceHistory> ProductListPriceHistories { get; set; } public DbSet<ProductModel> ProductModels { get; set; } public DbSet<ProductModelIllustration> ProductModelIllustrations { get; set; } public DbSet<ProductModelProductDescriptionCulture> ProductModelProductDescriptionCultures { get; set; } public DbSet<ProductPhoto> ProductPhotoes { get; set; } public DbSet<ProductProductPhoto> ProductProductPhotoes { get; set; } public DbSet<ProductReview> ProductReviews { get; set; } public DbSet<ProductSubcategory> ProductSubcategories { get; set; } public DbSet<ScrapReason> ScrapReasons { get; set; } public DbSet<TransactionHistory> TransactionHistories { get; set; } public DbSet<TransactionHistoryArchive> TransactionHistoryArchives { get; set; } public DbSet<UnitMeasure> UnitMeasures { get; set; } public DbSet<WorkOrder> WorkOrders { get; set; } public DbSet<WorkOrderRouting> WorkOrderRoutings { get; set; } public DbSet<ProductVendor> ProductVendors { get; set; } public DbSet<PurchaseOrderDetail> PurchaseOrderDetails { get; set; } public DbSet<PurchaseOrderHeader> PurchaseOrderHeaders { get; set; } public DbSet<ShipMethod> ShipMethods { get; set; } public DbSet<Vendor> Vendors { get; set; } public DbSet<CountryRegionCurrency> CountryRegionCurrencies { get; set; } public DbSet<CreditCard> CreditCards { get; set; } public DbSet<Currency> Currencies { get; set; } public DbSet<CurrencyRate> CurrencyRates { get; set; } public DbSet<Customer> Customers { get; set; } public DbSet<PersonCreditCard> PersonCreditCards { get; set; } public DbSet<SalesOrderDetail> SalesOrderDetails { get; set; } public DbSet<SalesOrderHeader> SalesOrderHeaders { get; set; } public DbSet<SalesOrderHeaderSalesReason> SalesOrderHeaderSalesReasons { get; set; } public DbSet<SalesPerson> SalesPersons { get; set; } public DbSet<SalesPersonQuotaHistory> SalesPersonQuotaHistories { get; set; } public DbSet<SalesReason> SalesReasons { get; set; } public DbSet<SalesTaxRate> SalesTaxRates { get; set; } public DbSet<SalesTerritory> SalesTerritories { get; set; } public DbSet<SalesTerritoryHistory> SalesTerritoryHistories { get; set; } public DbSet<ShoppingCartItem> ShoppingCartItems { get; set; } public DbSet<SpecialOffer> SpecialOffers { get; set; } public DbSet<SpecialOfferProduct> SpecialOfferProducts { get; set; } public DbSet<Store> Stores { get; set; } public DbSet<vEmployee> vEmployees { get; set; } public DbSet<vEmployeeDepartment> vEmployeeDepartments { get; set; } public DbSet<vEmployeeDepartmentHistory> vEmployeeDepartmentHistories { get; set; } public DbSet<vJobCandidate> vJobCandidates { get; set; } public DbSet<vJobCandidateEducation> vJobCandidateEducations { get; set; } public DbSet<vJobCandidateEmployment> vJobCandidateEmployments { get; set; } public DbSet<vAdditionalContactInfo> vAdditionalContactInfoes { get; set; } public DbSet<vStateProvinceCountryRegion> vStateProvinceCountryRegions { get; set; } public DbSet<vProductAndDescription> vProductAndDescriptions { get; set; } public DbSet<vProductModelCatalogDescription> vProductModelCatalogDescriptions { get; set; } public DbSet<vProductModelInstruction> vProductModelInstructions { get; set; } public DbSet<vVendorWithAddress> vVendorWithAddresses { get; set; } public DbSet<vVendorWithContact> vVendorWithContacts { get; set; } public DbSet<vIndividualCustomer> vIndividualCustomers { get; set; } public DbSet<vPersonDemographic> vPersonDemographics { get; set; } public DbSet<vSalesPerson> vSalesPersons { get; set; } public DbSet<vSalesPersonSalesByFiscalYear> vSalesPersonSalesByFiscalYears { get; set; } public DbSet<vStoreWithAddress> vStoreWithAddresses { get; set; } public DbSet<vStoreWithContact> vStoreWithContacts { get; set; } public DbSet<vStoreWithDemographic> vStoreWithDemographics { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Configurations.Add(new AWBuildVersionMap()); modelBuilder.Configurations.Add(new DatabaseLogMap()); modelBuilder.Configurations.Add(new ErrorLogMap()); modelBuilder.Configurations.Add(new OrderDetailMap()); modelBuilder.Configurations.Add(new OrderMap()); modelBuilder.Configurations.Add(new DepartmentMap()); modelBuilder.Configurations.Add(new EmployeeMap()); modelBuilder.Configurations.Add(new EmployeeDepartmentHistoryMap()); modelBuilder.Configurations.Add(new EmployeePayHistoryMap()); modelBuilder.Configurations.Add(new JobCandidateMap()); modelBuilder.Configurations.Add(new ShiftMap()); modelBuilder.Configurations.Add(new AddressMap()); modelBuilder.Configurations.Add(new AddressTypeMap()); modelBuilder.Configurations.Add(new BusinessEntityMap()); modelBuilder.Configurations.Add(new BusinessEntityAddressMap()); modelBuilder.Configurations.Add(new BusinessEntityContactMap()); modelBuilder.Configurations.Add(new ContactTypeMap()); modelBuilder.Configurations.Add(new CountryRegionMap()); modelBuilder.Configurations.Add(new EmailAddressMap()); modelBuilder.Configurations.Add(new PasswordMap()); modelBuilder.Configurations.Add(new PersonMap()); modelBuilder.Configurations.Add(new PersonPhoneMap()); modelBuilder.Configurations.Add(new PhoneNumberTypeMap()); modelBuilder.Configurations.Add(new StateProvinceMap()); modelBuilder.Configurations.Add(new BillOfMaterialMap()); modelBuilder.Configurations.Add(new CultureMap()); modelBuilder.Configurations.Add(new IllustrationMap()); modelBuilder.Configurations.Add(new LocationMap()); modelBuilder.Configurations.Add(new ProductMap()); modelBuilder.Configurations.Add(new ProductCategoryMap()); modelBuilder.Configurations.Add(new ProductCostHistoryMap()); modelBuilder.Configurations.Add(new ProductDescriptionMap()); modelBuilder.Configurations.Add(new ProductInventoryMap()); modelBuilder.Configurations.Add(new ProductListPriceHistoryMap()); modelBuilder.Configurations.Add(new ProductModelMap()); modelBuilder.Configurations.Add(new ProductModelIllustrationMap()); modelBuilder.Configurations.Add(new ProductModelProductDescriptionCultureMap()); modelBuilder.Configurations.Add(new ProductPhotoMap()); modelBuilder.Configurations.Add(new ProductProductPhotoMap()); modelBuilder.Configurations.Add(new ProductReviewMap()); modelBuilder.Configurations.Add(new ProductSubcategoryMap()); modelBuilder.Configurations.Add(new ScrapReasonMap()); modelBuilder.Configurations.Add(new TransactionHistoryMap()); modelBuilder.Configurations.Add(new TransactionHistoryArchiveMap()); modelBuilder.Configurations.Add(new UnitMeasureMap()); modelBuilder.Configurations.Add(new WorkOrderMap()); modelBuilder.Configurations.Add(new WorkOrderRoutingMap()); modelBuilder.Configurations.Add(new ProductVendorMap()); modelBuilder.Configurations.Add(new PurchaseOrderDetailMap()); modelBuilder.Configurations.Add(new PurchaseOrderHeaderMap()); modelBuilder.Configurations.Add(new ShipMethodMap()); modelBuilder.Configurations.Add(new VendorMap()); modelBuilder.Configurations.Add(new CountryRegionCurrencyMap()); modelBuilder.Configurations.Add(new CreditCardMap()); modelBuilder.Configurations.Add(new CurrencyMap()); modelBuilder.Configurations.Add(new CurrencyRateMap()); modelBuilder.Configurations.Add(new CustomerMap()); modelBuilder.Configurations.Add(new PersonCreditCardMap()); modelBuilder.Configurations.Add(new SalesOrderDetailMap()); modelBuilder.Configurations.Add(new SalesOrderHeaderMap()); modelBuilder.Configurations.Add(new SalesOrderHeaderSalesReasonMap()); modelBuilder.Configurations.Add(new SalesPersonMap()); modelBuilder.Configurations.Add(new SalesPersonQuotaHistoryMap()); modelBuilder.Configurations.Add(new SalesReasonMap()); modelBuilder.Configurations.Add(new SalesTaxRateMap()); modelBuilder.Configurations.Add(new SalesTerritoryMap()); modelBuilder.Configurations.Add(new SalesTerritoryHistoryMap()); modelBuilder.Configurations.Add(new ShoppingCartItemMap()); modelBuilder.Configurations.Add(new SpecialOfferMap()); modelBuilder.Configurations.Add(new SpecialOfferProductMap()); modelBuilder.Configurations.Add(new StoreMap()); modelBuilder.Configurations.Add(new vEmployeeMap()); modelBuilder.Configurations.Add(new vEmployeeDepartmentMap()); modelBuilder.Configurations.Add(new vEmployeeDepartmentHistoryMap()); modelBuilder.Configurations.Add(new vJobCandidateMap()); modelBuilder.Configurations.Add(new vJobCandidateEducationMap()); modelBuilder.Configurations.Add(new vJobCandidateEmploymentMap()); modelBuilder.Configurations.Add(new vAdditionalContactInfoMap()); modelBuilder.Configurations.Add(new vStateProvinceCountryRegionMap()); modelBuilder.Configurations.Add(new vProductAndDescriptionMap()); modelBuilder.Configurations.Add(new vProductModelCatalogDescriptionMap()); modelBuilder.Configurations.Add(new vProductModelInstructionMap()); modelBuilder.Configurations.Add(new vVendorWithAddressMap()); modelBuilder.Configurations.Add(new vVendorWithContactMap()); modelBuilder.Configurations.Add(new vIndividualCustomerMap()); modelBuilder.Configurations.Add(new vPersonDemographicMap()); modelBuilder.Configurations.Add(new vSalesPersonMap()); modelBuilder.Configurations.Add(new vSalesPersonSalesByFiscalYearMap()); modelBuilder.Configurations.Add(new vStoreWithAddressMap()); modelBuilder.Configurations.Add(new vStoreWithContactMap()); modelBuilder.Configurations.Add(new vStoreWithDemographicMap()); } } }
在项目中我们还可以看到Entity Framework Power Tools自动创建了一个Models文件夹,这里除了“AdventureWorksContext”类还有所有的数据类。在Models文件夹下还有一个“Mapping”文件夹,这里放了数据类与数据库的映射配置类,可以看出Entity Framework Power Tools通过fluent API的方式进行映射细节配置(目前Entity Framework Power Tools还不支持Data Annotations方式),关于配置类的具体细节我们这里暂不进行详细介绍。
下面我们进行数据查询:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using EFPowerTools.Models; namespace EFPowerTools { class Program { static void Main(string[] args) { using (var db = new AdventureWorksContext()) { var persons = db.People.Where(p => p.LastName == "Stevens").OrderBy(p=>p.FirstName); foreach(var p in persons) { Console.WriteLine("FirstName:{0},LastName:{1}", p.FirstName, p.LastName); } } } } }
查询结果: