關於Migration表名&导航属性

[ed555009]

目前有三個Entities：

User（不分表）

[Index(nameof(NationalId), IsUnique = true)]
public class User : BaseTrackUpdateEntity, IHasIdEntity<long>
{
	[Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
	public long Id { get; set; }

	[Required, MinLength(8), MaxLength(10)]
	public string NationalId { get; set; }

	...

	public virtual UserInvoiceSetting InvoiceSetting { get; set; }
}

InvoiceSetting（不分表）

[Index(nameof(UpdatedAt))]
public class UserInvoiceSetting
{
	[Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
	public long UserId { get; set; }

	[Required]
	public CarrierType? CarrierType { get; set; } = ECPay.Invoice.Enums.CarrierType.ECPayMember;

	...

	public virtual User User { get; set; }
}

Transaction（按年分表）

public class Transaction : BaseHasAutoIdEntity<long>
{
	[Required, MinLength(1), MaxLength(15), RegularExpression(@"^\d{1,15}$")]
	public string MatchId { get; set; }

	[Required]
	public long UserId { get; set; }

	...
}

Transaction virtual table route

public class TransactionVTR : AbstractSimpleShardingYearKeyDateTimeVirtualTableRoute<Transaction>
{
	public override bool AutoCreateTableByTime() => true;

	public override void Configure(EntityMetadataTableBuilder<MatchedTransaction> builder) =>
		builder
			.ShardingProperty(x => x.CreatedAt)
			.TableSeparator("_");

	public override DateTime GetBeginTime() => new(2022, 11, 1);
}

Program.cs（WebApi）

var builder = WebApplication.CreateBuilder(args);
var connectionString = builder.Configuration.GetConnectionString("MyDatabase");

// Add services to the container.
builder.Services.AddShardingDbContext<MyContext>()
	.UseRouteConfig(options =>
	{
		options.AddShardingTableRoute<TransactionVTR>();
	})
	.UseConfig((serviceProvider, options) =>
	{
		string dataSourceName = Unified.UnifiedId.NewId();

		options.UseShardingQuery((connection, builder) =>
		{
			builder.UseNpgsql(connection);
		});
		options.UseShardingTransaction((connection, builder) =>
		{
			builder.UseNpgsql(connection);
		});
		options.AddDefaultDataSource(dataSourceName, connectionString);
	})
	.AddShardingCore();

Migration script：

CREATE TABLE "Transactions" (
    "Id" bigint GENERATED BY DEFAULT AS IDENTITY,
    "MatchId" character varying(15) NOT NULL,
    "UserId" bigint NOT NULL,
    ....
    CONSTRAINT "PK_MatchedTransactions" PRIMARY KEY ("Id")
);

CREATE TABLE "UserInvoiceSettings" (
    "UserId" bigint NOT NULL,
    "CarrierType" integer NOT NULL,
    CONSTRAINT "PK_UserInvoiceSettings" PRIMARY KEY ("UserId"),
    CONSTRAINT "FK_UserInvoiceSettings_Users_UserId" FOREIGN KEY ("UserId") REFERENCES "Users" ("Id") ON DELETE CASCADE
);

1. Migration script創建的表名為Transactions而不是Transactions_2022，這樣是正確的嗎？
2. 官方文檔說明不支持导航属性，所以我在User & Transaction 沒有設定對應關係，但從script中看出這樣Transaction表就不支持外鍵約束，請問是否有其他的方式可以做到FK constraint呢？或是其實可以在entity中定義navigation property，只是不要調用Include就可以呢？

謝謝

[xuejmnet]

@ed555009 实在不好意思今天才看到,

1.肯定是不对的不应该生成Transactions
2.如果你需要迁移应该添加

sharding-core/samples/Sample.MySql/Startup.cs

Line 107 in 1f810f9

o.UseShardingMigrationConfigure(b =>

[xuejmnet]

https://xuejm.gitee.io/sharding-core-doc/adv/code-first/#sqlgenerator

[xuejmnet]

@ed555009 你要的外键目前没有好办法除了自己执行slq脚本添加

[ed555009]

@ed555009 你要的外键目前没有好办法除了自己执行slq脚本添加

您是指我在migration script中自行添加一行constrain foreign key是嗎？

[xuejmnet]

@ed555009 你要的外键目前没有好办法除了自己执行slq脚本添加

您是指我在migration script中自行添加一行constrain foreign key是嗎？

是的

[xuejmnet]

@ed555009 脚本生成出错是因为你没有添加我说的UseShardingMigrationConfigure替换掉默认的sqlgenerator

[ed555009]

@ed555009 脚本生成出错是因为你没有添加我说的UseShardingMigrationConfigure替换掉默认的sqlgenerator

這邊加上UseShardingMigrationConfigure後，目前生成的migration script中已有看到表的年份後綴Transactions_2022

CREATE TABLE "Transactions_2022" (
    "Id" bigint GENERATED BY DEFAULT AS IDENTITY,
    "MatchId" character varying(15) NOT NULL,
    "UserId" bigint NOT NULL,
    ...
    CONSTRAINT "PK_MatchedTransactions_2022" PRIMARY KEY ("Id")
);

@ed555009 你要的外键目前没有好办法除了自己执行slq脚本添加

您是指我在migration script中自行添加一行constrain foreign key是嗎？

是的

想請問，如果是自行在migration script中添加constrain foreign key，那麼未來ShardingCore自動建表Transactions_2023的時候會自動加上FK（table structure clone）嗎？

另外想請教，官方文檔 / 快速上手AspNetCore / 第五步配置启动项中關於「启动检查缺少的表并且创建」

app.ApplicationServices.UseAutoTryCompensateTable();

這一段在.NET6 WebApi專案中具體我該怎麼寫呢？一直無法找到ApplicationServices
Edit: 使用app.Services

不好意思，第一次要實作分片，概念上不是很清楚，問題比較多

[xuejmnet]

@ed555009 大数据不建议增加外建约束

[xuejmnet]

@ed555009

        /// <summary>
        /// 自动尝试补偿表
        /// </summary>
        /// <param name="serviceProvider"></param>
        /// <param name="parallelCount"></param>
        public static void UseAutoTryCompensateTable(this IServiceProvider serviceProvider, int? parallelCount = null)
        {
            var shardingRuntimeContext = serviceProvider.GetRequiredService<IShardingRuntimeContext>();
            shardingRuntimeContext.UseAutoTryCompensateTable(parallelCount);
        }

他是IServiceProvider的扩展

[ed555009]

他是IServiceProvider的扩展

是的，Net6將ApplicationServices併入Services了

[ed555009]

@ed555009 大数据不建议增加外建约束

瞭解，那我再想想怎麼驗證Transactions中的UserId，原本是想透過FK來驗證的