Home Convert entity framework C# code to raw SQL for performance
Reply: 0

Convert entity framework C# code to raw SQL for performance

user5452
1#
user5452 Published in June 19, 2018, 6:21 pm

I have a requirement where I need to check logins and logout pairs in the eventsLogs table. A pair consists one login and one logout. In the eventLogs table have many events out which I am interested in eventType 11 and 12 which means when user logged in(event 11) and the logged out (event 12). There could be duplicate events for that user from a computer. For example, login,login ,logout, so what I am doing in C# is taking the first login and then find its pair. The problem is I do not know to do this in SQL, unfortunately I am loading all the logins and logouts into memory!. I want a way so that I could call a store procedure and get the same result which I am currently binding to a class in C#. I will highly appreciate your help. If I didn't explain my question well, please ask for explanation.

 // C#
   var departmentEventsDic = new Dictionary<string, List<Sessions>>();   

   //get all departments and run the loop
 foreach (var department in departments)
                    {
                        // create a list to push a pairs
                        var sessionPairsList = new List<Sessions>();
                      // for each computer in the department
                        foreach (var computer in department.Computers)
                        {

                            var  tempLogs= eventLogs.Where(x => x.ComputerId == computer.ComputerId) .OrderBy(x => x.EventDateTime).ToList();
                            var tempUnlock = DateTime.MinValue;

                            //for each log for the computer 
                            foreach (var log in tempLogs)
                            {
                                // if the event is login store it in temp varible 
                                if (log.EventType == 11)
                                {
                                    tempUnlock = log.EventDateTime;

                                }
                                // if its logout and its time is greater than tempUnlock , found a pair create class and add to list 
                                if (log.EventType == 12 && (tempUnlock != DateTime.MinValue) && log.EventDateTime > tempUnlock)
                                {
                                    var sessionPair = new Sessions
                                    {
                                        DepartmentId = department.DepartmentId,
                                        DepartmentName = department.DepartmentName,
                                        ComputerId = computer.ComputerId,
                                        ComputerName = computer.Name,
                                        LoginTime = tempUnlock,
                                        LogOutTime = log.EventDateTime,
                                        UserId = department.Users.FirstOrDefault(x => x.UserId == log.UserId)?.UserId,
                                        UserName = department.Users.FirstOrDefault(x => x.UserId == log.UserId)?.Name,
                                        Difference = (log.EventDateTime - temUnlock)
                                    };
                                    sessionPairsList.Add(sessionPair);
                                }
                            }
                        }
                        // add to dictionary with department name as key and list of sessionspairs as values

                        departmentEventsDic.Add(department.DepartmentName, sessionPairsList);
                    }



 public class Results
    {
        public Guid DepartmentId { get; set; }
        public string DepartmentName { get; set; }
        public Guid ComputerId { get; set; }
        public string ComputerName { get; set; }
        public DateTime LoginTime { get; set; }
        public DateTime LogOutTime { get; set; }
        public Guid? UserId { get; set; }
        public string UserName { get; set; }
        public TimeSpan Difference { get; set; }
    }       
    //Tables
    CREATE TABLE [dbo].[Departments](
    [DepartmentId] [uniqueidentifier] NOT NULL PrimaryKey,
    [DepartmentName] [nvarchar](max) NULL
    );
    CREATE TABLE [dbo].[Computers](
    [ComputerId] [uniqueidentifier] NOT NULL PrimaryKey,    
    [DepartmentId] [uniqueidentifier] NOT NULL, 
    [Name] [nvarchar](max) NULL );

    ALTER TABLE [dbo].[Computers]  WITH CHECK ADD  CONSTRAINT [FK_Computers_Departments_DepartmentId] FOREIGN KEY([DepartmentId])
    REFERENCES [dbo].[Departments] ([DepartmentId])
    ON DELETE CASCADE



     CREATE TABLE [dbo].[EventLogs](
    [EventLogId] [uniqueidentifier] NOT NULL PrimaryKey,
    [ComputerId] [uniqueidentifier] NOT NULL,
    [EventDateTime] [datetime2](7) NOT NULL,
    [EventType] [int] NOT NULL,
    [UserId] [uniqueidentifier] NULL);
    ALTER TABLE [dbo].[EventLogs]  WITH CHECK ADD  CONSTRAINT [FK_EventLogs_Computers_ComputerId] FOREIGN KEY([ComputerId])
    REFERENCES [dbo].[Computers] ([ComputerId])
    ON DELETE CASCADE



   CREATE TABLE [dbo].[Users](
    [UserId] [uniqueidentifier] NOT NULL,
    [DepartmentId] [uniqueidentifier] NOT NULL, 
    [Name] [nvarchar](max) NULL);
    ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_Departments_DepartmentId] FOREIGN KEY([DepartmentId])
    REFERENCES [dbo].[Departments] ([DepartmentId])
    ON DELETE CASCADE
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.321511 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO