How to exclude matching records in EF Core Database

muttBunch 100 Reputation points
2024-06-20T04:43:10.63+00:00

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();
    
}
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
721 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,604 questions
0 comments No comments
{count} votes

Accepted answer
  1. Hongrui Yu-MSFT 945 Reputation points Microsoft Vendor
    2024-06-20T08:19:46.24+00:00

    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.


0 additional answers

Sort by: Most helpful