Pages

2015-04-04

SQLite for Entity Framework 6.x安裝實例

為一個ASP.NET Website/MVC安裝SQLite for EF6的NuGet安裝程序如下: (撰寫時使用EF 6.1.3 + SQLite 1.0.96版本,2015/04/01)

使用NuGet方式依序安裝:
PM> Install-Package EntityFramework
PM> Install-Package System.Data.SQLite
SQLite的資料庫檔放在/App_Data/Database/Northwind.db
Web.config下的連接字串為:

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=|DataDirectory|Database\Northwind.db" providerName="System.Data.SQLite"/>
</connectionStrings>

新增一個ADO.NET Entity Data Model, 因為沒有內建適當的SQLite EF Provider自動產生POCO物件(MS獨厚SQL Server?),因此得手動新增POCO類別。
DbContext:
public virtual DbSet<Products> Products { get; set; }

public class Products
{
    [Key]
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public int SupplierID { get; set; }
    public int CategoryID { get; set; }
}
查詢SQL資料方式:
using (Database db = new Database())
{
    var query = from p in db.Products
                where p.ProductID == 1
                select p;

    foreach (var p in query)
    {
        string PID = p.ProductID;
    }
}


執行時會出現Provider Not Found錯誤:


Provider Not Found 


這是因為Nuget安裝只加入EF6的宣告,並沒在系統GAC裏安裝SQLite Provider,當內部運作呼叫到DbProviderFactories.GetFactory("System.Data.SQLite")就會產生上述的錯誤。因此必須在默認的Web.config下方增加對System.Data.SQLite的宣告(增加下面紅色的字串)。

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
        <parameters>
            <parameter value="mssqllocaldb" />
        </parameters>
    </defaultConnectionFactory>
    <providers>
        <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
        <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />

        <!--Solve SQLite error of "Unable to find the requested .Net Framework Data Provider."-->
        <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
</entityFramework>
<system.data>
    <DbProviderFactories>
        <remove invariant="System.Data.SQLite.EF6" />
        <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />

        <!--Solve SQLite error of "Unable to find the requested .Net Framework Data Provider."-->
        <remove invariant="System.Data.SQLite"/>
        <add name="SQLite Data Provider" invariant="System.Data.SQLite"
            description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

    </DbProviderFactories>
</system.data>

以上就是讓SQLite使用EF6.x讀取資料的完整範例了。

No comments: