appsettings.json 設定連線字串
"ConnectionStrings": {
"xxx": "Server=xxx; Database=xxx; User Id=xxx; Password=xxx;"
}
startup.cs 加入連線
public void ConfigureServices (IServiceCollection services) {
services.AddDbContext<xxxContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("xxx")));
xxxController.cs 使用連線
public class xxxController : ControllerBase {
private readonly xxxContext db;
public xxxController(xxxContext db)
{
this.db = db;
}
若要單獨使用不透過controller，則須擴展xxxContext.cs
public partial class xxxContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
IConfigurationRoot configuration = new ConfigurationBuilder()
.SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
.AddJsonFile("appsettings.json")
.Build();
optionsBuilder.UseSqlServer(configuration.GetConnectionString("xxx"));
}
}
使用:
using (var db=new xxxContext()) {
...
}
※存取關聯table 要先include，否則產生集合(.ToList())後關聯物件都會是null
db.aaa.Where(a =>...).Include(a=>a.xxx) // 事後才能取得 a.xxx.ooo
※ 使用 .GroupBy 前必須先 .AsEnumerable 否則可能會報錯
※ 使用內建連線，action 回傳型態不能為 void，否則會造成 SaveChangesAsync() 出錯，因為會提前dispose(不會等待執行)
.net 7 避免 sql server table 有trigger 造成 table 無法更新 (有trigger 會改用舊架構，非必要不要使用)
擴展xxxContext.cs
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
configurationBuilder.Conventions.Add(_ => new BlankTriggerAddingConvention());
}
public class BlankTriggerAddingConvention : IModelFinalizingConvention
{
public virtual void ProcessModelFinalizing(
IConventionModelBuilder modelBuilder,
IConventionContext<IConventionModelBuilder> context)
{
foreach (var entityType in modelBuilder.Metadata.GetEntityTypes())
{
var table = StoreObjectIdentifier.Create(entityType, StoreObjectType.Table);
if (table != null
&& entityType.GetDeclaredTriggers().All(t => t.GetDatabaseName(table.Value) == null))
{
entityType.Builder.HasTrigger(table.Value.Name + "_Trigger");
}
foreach (var fragment in entityType.GetMappingFragments(StoreObjectType.Table))
{
if (entityType.GetDeclaredTriggers().All(t => t.GetDatabaseName(fragment.StoreObject) == null))
{
entityType.Builder.HasTrigger(fragment.StoreObject.Name + "_Trigger");
}
}
}
}
}
自動延遲載入
安裝套件 Microsoft.EntityFrameworkCore.Proxies
startup.cs
services.AddDbContext<xxx>(options =>
options.UseSqlServer(Configuration.GetConnectionString("圓桌基本資料")).UseLazyLoadingProxies());
擴展xxxContext.cs
optionsBuilder.UseSqlServer(connString).UseLazyLoadingProxies();
※ 於 .net 6 似乎會造成 EF 無法使用，待確認
沒有留言:
張貼留言