How to exclude matching records in EF Core Database

Question

I've been working on a project from a gentleman that is no longer on my team.

We want to have the below code running as a Windows Service, running every 5 minutes (easy for me to do the Service).

However, if we run the below code in our API, manually clicking every few minutes or so, it just continuously duplicates data that is already in the database.

What we want, is, to get the logs for logons for the past 24 hours from the current date/time, store it in the database, ignoring records that already exist, if it's in the same 24 hour period, but also to delete records that are greater than the 24 hour period from current date/time, to delete them from the database. I'm probably making this more complicated than it needs to be.

I hope this makes sense.

public async Task PullEventsLogons(CancellationToken token)
{
    string? query = "*[System[TimeCreated[timediff(@SystemTime) <= 86400000]]]";
   
    EventLogQuery eventsQuery = new EventLogQuery("Security",
                        PathType.LogName, query);
    using SecureString secureString = new NetworkCredential("domainServiceAccountLogon", 
		"myDumbPassword").SecurePassword;

    using EventLogSession session = new EventLogSession("mydomain01.mydomain.local", "mydomain.local", 
		"domainServiceAccountLogon", secureString, SessionAuthentication.Default);

    eventsQuery.Session = session;

    using EventLogReader logReader = new EventLogReader(eventsQuery);

    for (EventRecord eventInstance = logReader.ReadEvent();
        eventInstance != null;
        eventInstance = logReader.ReadEvent())
    {
        try
        {
            if (eventInstance.Id == 4624)
            {
                var logStoreLogons = new LogStoreLogons()
                {
                    DomainId = 1,
                    Domain = "mydomain.local",
                    Keywords = eventInstance.KeywordsDisplayNames.FirstOrDefault(),
                    EventId = eventInstance.Id,
                    TimeStamp = (DateTime)eventInstance.TimeCreated,
                    Description = eventInstance.FormatDescription(),
                    TaskCategory = eventInstance.TaskDisplayName,
                    DomainControllerHostname = eventInstance.MachineName
                };
                await _applicationDbContext.AddAsync(logStoreLogons, token);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }
    }

    await _applicationDbContext.SaveChangesAsync(token);
    GC.Collect();
    
}

Answer 1

Hi,@muttBunch. Welcome to Microsoft Q&A. 

You can use the following examples as reference

1.Inserting Data

Assume that insertList is the data you want to insert

MyDbContext myDbContext = new MyDbContext();

//Find data within 24 hours from the database

var databaseList = myDbContext.MyLogs.Where(p => p.LogTime < DateTime.Now && p.LogTime > DateTime.Now.AddDays(-1)).ToList();

//Insert new data and remove duplicates

var resultList = databaseList.Union(insertList).DistinctBy(p => p.Id).ToList();

//Writing to the database

myDbContext.RemoveRange(databaseList);

myDbContext.SaveChanges();

myDbContext.AddRange(resultList);

myDbContext.SaveChanges();

 

public partial class MyLog

{

    public int Id { get; set; }

 

    public string Name { get; set; } = null!;

 

    public DateTime LogTime { get; set; }

}

 

2.Delete data older than 24 hours

var deleteList = myDbContext.MyLogs.Where(p => p.LogTime < DateTime.Now.AddDays(-1)).ToList();

myDbContext.RemoveRange(deleteList);

myDbContext.SaveChanges();

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.