48 lines
1.3 KiB
C#
48 lines
1.3 KiB
C#
namespace MileageTraker.Web.Migrations
|
|
{
|
|
using System.Data.Entity.Migrations;
|
|
|
|
public partial class AddVehiclePreviousLog : DbMigration
|
|
{
|
|
public override void Up()
|
|
{
|
|
AddColumn("Log", "VehiclePreviousLogId", c => c.Int());
|
|
AddForeignKey("Log", "VehiclePreviousLogId", "Log", "LogId");
|
|
CreateIndex("Log", "VehiclePreviousLogId");
|
|
var populateSql =
|
|
@"WITH summary AS (
|
|
SELECT
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY l.LogId
|
|
ORDER BY pl.EndOdometer DESC, pl.Created DESC) AS rk,
|
|
l.LogId, pl.LogId as PreviousLogId
|
|
FROM
|
|
[Log] as l,
|
|
[Log] as pl
|
|
WHERE
|
|
pl.VehicleId = l.VehicleId
|
|
AND ((pl.[Date] <= l.[Date]
|
|
AND pl.EndOdometer < l.EndOdometer)
|
|
OR (
|
|
pl.EndOdometer = l.EndOdometer
|
|
AND pl.Created < l.Created
|
|
))
|
|
)
|
|
UPDATE [Log]
|
|
SET [Log].VehiclePreviousLogId = summary.PreviousLogId
|
|
FROM summary
|
|
WHERE summary.rk = 1
|
|
AND [Log].LogId = summary.LogId
|
|
";
|
|
Sql(populateSql);
|
|
}
|
|
|
|
public override void Down()
|
|
{
|
|
DropIndex("Log", new[] { "VehiclePreviousLogId" });
|
|
DropForeignKey("Log", "VehiclePreviousLogId", "Log");
|
|
DropColumn("Log", "VehiclePreviousLogId");
|
|
}
|
|
}
|
|
}
|