A couple things to try, too much for a comment so:
Try deleting a single "old" item:
using (AppHost_DataHubEntities db = new AppHost_DataHubEntities())
{
DateTime tooOld = (DateTime.Now - HISTORY_TIME_SPAN);
var item = db.FromDataHub.Where(x => x.DateTime < tooOld).First();
db.FromDataHub.Remove(item);
db.SaveChanges();
}
Does it delete the expected row? Does it appear to change anything in the related Point?
Next, try a Range:
using (AppHost_DataHubEntities db = new AppHost_DataHubEntities())
{
DateTime tooOld = (DateTime.Now - HISTORY_TIME_SPAN);
var items = db.FromDataHub.Where(x => x.DateTime < tooOld).Take(3).ToList();
db.FromDataHub.RemoveRange(items);
db.SaveChanges();
}
Same checks for any errors or unexpected modifications? What "Point" records do each of these touch? The same one? different ones?
Next, check the data if there are any where all From records might be deleted, all items for a point that are older than the threshold, get that Point ID:
using (AppHost_DataHubEntities db = new AppHost_DataHubEntities())
{
DateTime tooOld = (DateTime.Now - HISTORY_TIME_SPAN);
var items = db.FromDataHub.Where(x => x.DateTime < tooOld && x.PointId == pointId)..ToList();
db.FromDataHub.RemoveRange(items);
db.SaveChanges();
}
Does this work?
Basically when facing issues like this you need to work out the lowest common denominator to see if there is a mapping issue or such that results in the error in all cases, or a particular data combination that trips it up in cases where that data might be identified for deletion.
If you cannot delete a single row, then something in the mapping is blocking it.
If you can delete a single row, but a range is causing an issue, either across a single Point or different points then further drilling will be needed to determine why.
If all three worked, you could try adding the .ToList() inside your DeleteRange call.
Normally bulk deletes for maintenance isn't something I'd do with EF, rather setting up a scheduled job to run after hours against the database directly. If I do want to do bulk deletes in EF I will typically break it up into blocks of work. for example:
List<FromDataHub> itemsToClean = new List<FromDataHub>();
using(var context = new AppHost_DataHubEntities())
{
itemsToClean = context.FromDataHub
.Where(x => x.DateTime < tooOld)
.Select(x => new FromDataHub { PointId = x.PointId, DateTime = x.DateTime })
.ToList();
}
const int batchSize = 500;
int batchCount = 1;
var batch = itemsToClean.Take(batchSize);
while(batch.Any())
{
using(var context = new AppHost_DataHubEntities())
{
foreach(var item in batch)
{
context.FromDataHub.Attach(item);
context.Entity(item).EntityState = EntityState.Deleted;
}
context.SaveChanges();
}
batch = itemsToClean.Skip(++batchCount).Take(batchSize);
}
That code is basically off the top of the head and it's late, but should give the idea. For potentially large entities I would consider a bounded context specific to this purpose where the defined "FromDataHub" entity registered for that Context consisted of just the ID columns. (in which case you could simplify this to read & delete in batches. What this example does instead is executes a query to just return the relevant ID colums (assumed to be PointId and the DateTime) and populating detached entities from it. Then in batches of 500 it opens a new DbContext, attaches that entity, sets its modified state to deleted, and then saves the batch. This is to guard against tracking a large # of entities within a DbContext instance in case this code needs to delete a lot of rows. Again, my first preference would be to use a scheduled maintenance job on the database server after hours, but it might give you some ideas to track down the issue.
Update: You will need to update your example to provide a complete and minimal reproducible example. I've set up a schema based on what you provided and was not able to reproduce the issue with EF:
Entities:
[Table("Points")]
public class Point
{
[Key]
public int PointId { get; set; }
public string PointName { get; set; }
public bool Collect { get; set; }
public virtual ICollection<FromDataHub> FromDataHubs { get; set; } = new List<FromDataHub>();
public virtual ICollection<ToDataHub> ToDataHubs { get; set; } = new List<ToDataHub>();
}
[Table("FromDataHub")]
public class FromDataHub
{
[Key, Column(Order = 0), ForeignKey("Point")]
public int PointId { get; set; }
[Key, Column(Order = 1)]
public DateTime DateTime { get; set; }
[Key, Column(Order = 2)]
public int Value { get; set; }
public virtual Point Point { get; set; }
}
[Table("ToDataHub")]
public class ToDataHub
{
[Key, Column(Order = 0), ForeignKey("Point")]
public int PointId { get; set; }
[Key, Column(Order = 1)]
public DateTime DateTime { get; set; }
public virtual Point Point { get; set; }
}
with mapping:
modelBuilder.Entity<Point>()
.HasMany(x => x.FromDataHubs)
.WithRequired(x => x.Point);
modelBuilder.Entity<Point>()
.HasMany(x => x.ToDataHubs)
.WithRequired(x => x.Point);
... which was completely optional, it worked without the explicit mapping.
The test:
using (var context = new TestDbContext())
{
DateTime date = DateTime.Today.AddMonths(-6);
var itemsToDelete = context.FromDataHubs.Where(x => x.DateTime < date);
context.FromDataHubs.RemoveRange(itemsToDelete);
context.SaveChanges();
}
After seeding the tables with a couple Points and a handful of both From and To DataHub records ranging less than and greater than 6 months old, running this code successfully deleted the old records without any exception.
If your classes differ significantly from the above then please post the entity definitions and any mapping configuration you are currently using.
Update #2:
Ok, WOW, that is a mystery. What I have been able to uncover is that the issue seems to be particular to your Database for some reason. I modified my schema to match yours and modified your database to eliminate possible wierdness. I also copied your test data across to my database in case it was a data-related issue. The result was that my test against your DB continues to fail with the same exception, while your app against my DB succeeds.
Your schema:
USE [StackExample]
GO
/****** Object: Table [dbo].[FromDataHub] Script Date: 25/11/2020 8:50:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FromDataHub](
[PointId] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[Value] [float] NOT NULL,
CONSTRAINT [PK_FromDataHub] PRIMARY KEY CLUSTERED
(
[PointId] ASC,
[DateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Point] Script Date: 25/11/2020 8:50:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Point](
[PointId] [int] IDENTITY(1,1) NOT NULL,
[PointName] [varchar](255) NOT NULL,
[Collect] [bit] NOT NULL,
CONSTRAINT [PK_Point] PRIMARY KEY CLUSTERED
(
[PointId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ToDataHub] Script Date: 25/11/2020 8:50:04 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ToDataHub](
[PointId] [int] NOT NULL,
[Value] [float] NOT NULL,
CONSTRAINT [PK_ToDataHub] PRIMARY KEY CLUSTERED
(
[PointId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FromDataHub] WITH CHECK ADD CONSTRAINT [FK_FromDataHub_Point] FOREIGN KEY([PointId])
REFERENCES [dbo].[Point] ([PointId])
GO
ALTER TABLE [dbo].[FromDataHub] CHECK CONSTRAINT [FK_FromDataHub_Point]
GO
ALTER TABLE [dbo].[ToDataHub] WITH CHECK ADD CONSTRAINT [FK_ToDataHub_Point] FOREIGN KEY([PointId])
REFERENCES [dbo].[Point] ([PointId])
GO
ALTER TABLE [dbo].[ToDataHub] CHECK CONSTRAINT [FK_ToDataHub_Point]
GO
My Schema
USE [Spikes]
GO
/****** Object: Table [dbo].[FromDataHub] Script Date: 25/11/2020 8:50:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FromDataHub](
[PointId] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[Value] [float] NOT NULL,
CONSTRAINT [PK_FromDataHub_1] PRIMARY KEY CLUSTERED
(
[PointId] ASC,
[DateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Point] Script Date: 25/11/2020 8:50:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Point](
[PointId] [int] NOT NULL,
[PointName] [nvarchar](20) NOT NULL,
[Collect] [bit] NOT NULL,
CONSTRAINT [PK_Points] PRIMARY KEY CLUSTERED
(
[PointId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ToDataHub] Script Date: 25/11/2020 8:50:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ToDataHub](
[PointId] [int] NOT NULL,
[Value] [float] NOT NULL,
CONSTRAINT [PK_ToDataHub_1] PRIMARY KEY CLUSTERED
(
[PointId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Point] ADD CONSTRAINT [DF_Points_Collect] DEFAULT ((1)) FOR [Collect]
GO
ALTER TABLE [dbo].[FromDataHub] WITH CHECK ADD CONSTRAINT [FK_FromDataHub_Points] FOREIGN KEY([PointId])
REFERENCES [dbo].[Point] ([PointId])
GO
ALTER TABLE [dbo].[FromDataHub] CHECK CONSTRAINT [FK_FromDataHub_Points]
GO
ALTER TABLE [dbo].[ToDataHub] WITH CHECK ADD CONSTRAINT
[FK_ToDataHub_Point] FOREIGN KEY([PointId])
REFERENCES [dbo].[Point] ([PointId])
GO
ALTER TABLE [dbo].[ToDataHub] CHECK CONSTRAINT [FK_ToDataHub_Point]
GO
Data in both was identical. The only schema difference I didn't change was the data type/size for the PointName. I also had a default constraint on the Collect value. The changes from your original Schema was basically to change the Point ID PK and FKs from "ID" and "Point" to "PointId" in both cases. Originally my schema didn't have the FKs declared.
One thing I will note is that I was not able to get your EDMX to work out of the box. I noticed that the entities it declared were lacking any Key or FK declarations for the entities. I had to add these manually and disable the CodeFirst assertion:
public AppHost_DataHubEntities : base("name=AppHost_DataHubEntities")
{
Database.SetInitializer<AppHost_DataHubEntities>(null);
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//throw new UnintentionalCodeFirstException();
modelBuilder.Entity<Point>()
.ToTable("Point")
.HasKey(x => x.PointId)
.HasMany(x => x.FromDataHub)
.WithRequired(x => x.Point)
.HasForeignKey(x => x.PointId);
//modelBuilder.Entity<Point>()
// .HasKey(x => x.ID)
// .HasOptional(x => x.ToDataHub)
// .WithRequired(x => x.Point1);
modelBuilder.Entity<FromDataHub>()
.ToTable("FromDataHub")
.HasKey(x => new { x.PointId, x.DateTime });
modelBuilder.Entity<ToDataHub>()
.ToTable("ToDataHub")
.HasKey(x => new { x.PointId });
}
Test Code:
using (var context = new SoDbContext())
{
DateTime tooOld = DateTime.Now - new TimeSpan(0, 15, 0);
var items = context.FromDataHubs.Where(x => x.DateTime < tooOld).ToList();
//db.FromDataHub.RemoveRange(items);
context.FromDataHubs.Remove(items.First());
context.SaveChanges();
}
I also set the initializer on the DbContext to null and deleted the Migrations, but the error persists on your test database. So if I point your app's config at your DB I get the error. If I point it at my "Spikes" DB then it runs without issue. Same goes for my previous unit tests.
About the only thing I can suggest would be to avoid the EDMX & Code First generation and create the desired schema manually, mapping EF entities over to it. There seems to be something subtle in the generated schema that strangely isn't apparent in the generated SQL for the tables, or at the DB level?
