Skip to main content

Entity Framework 6.0 Logging and database Operations Interception

 

Entity Framework 6.0 Logging and database Operations Interception

Introduction

Entity Framework 6.0 introduced a new feature called "Logging SQL". While working with Entity Framework, it sends commands or an equivalent SQL query to the database to do a CRUD operation and this command can be intercepted by application code of Entity Framework.

How to enable the SQL logging


The Database Log property of DbContext can be set to a delegate of any method that accepts a string as the parameter. Using this method, all SQL generated by the current context will be logged.

Syntax:

            public DbContextEntities() : base("name=EntitiesConnections")

        {

            Database.Log = delegate for method which accepts string as parameter;

        }

 

Example:

For instance, if we have two database entities, Employee and Department, and we want to log, the SQL generated by the Entity Framework.


Logging SQL



SQL Entity



The DbContext.DataBase.Log property accepts a delegate for the method that has a string parameter. The Console.WriteLine function has the same type of delegate, so we can assign this function to the Database.Log property of DbContext.

         public IntegraContext() : base("csharpnaija")

        {

            Database.Log = Console.WriteLine;

 

        }

 Code:

public ActionResult Index()

        {

            using (DbContextConnection context = new DbContextConnection())

            {

                var data = context.Employees.Where(p => p.Name.StartsWith("p")).ToList();

                var data1 = (from e in context.Employees

                             join d in context.Departments on e.DepartmentId equals d.DeparmentId

                             select new

                             {

                                 Name = e.Name,

                                 Department = d.Name

                             }).ToList();

            }

            Console.ReadLine();

            return View();

        }




Writing SQL Log into text file

We can also pass the SQL query to any other function. Only the condition is a function that must have the same delegate as the DataBase.Log property accepts.

For example suppose I want to write all SQL into a text file, so I create a custom function that accepts a string and writes it into the text file.

Custom function definition

 

namespace CsharpnaijaSqlLoggingTutorial.Models

{

    public class WriteToFile

    {

        public static void LogSQL(string sql)

        {

            var filePath = @"c:\SQLLog.txt";

            File.AppendAllText(filePath, sql);

        }

    }

}

 

Now assign this custom function to the Database.Log property as a delegate.

 

namespace CsharpnaijaSqlLoggingTutorial.Models

{

    public class DbContextConnection : DbContext

    {

        public DbContextConnection() : base("DefaultConnection")

        {

            Database.Log = s => WriteToFile.LogSQL(s);

        }

        public DbSet<Employee> Employees { get; set; }

        public DbSet<Department> Departments { get; set; }

    }

}


What did it Log?


The following things are logged when the Database.Log property is set:

  • All SQL Commands


    • Equivalent SQL query for a normal LINQ query, eSQL and raw query (query that has a from clause).
    • Insert, update and delete command generated as a part of the SaveChanges method of context.
    • Relationship loading queries

  • All the Query Parameters
  • Date and Time when the command is started to execute
  • The approximate amount of time it takes to execute the command. This is the time required to send the command and get results back. It does not include time to read the result.

Summary


Using this functionality we can log the SQL commands that are generated and executed by Entity Framework. This feature can be used with only Entity Framework version 6.0 and above.

 

Labels:
Location: Abuja, Nigeria

Comments

Post a Comment

Popular posts from this blog

Collections in C#

Collections in C# In our previous article , we have learned about how we can use arrays in C#. Arrays in programming are used to group a set of related objects. So one could create an array or a set of Integers, which could be accessed via one variable name. What is Collections in C#? Collections are similar to Arrays, it provides a more flexible way of working with a group of objects. In arrays, you would have noticed that you need to define the number of elements in an array beforehand. This had to be done when the array was declared. But in a collection, you don't need to define the size of the collection beforehand. You can add elements or even remove elements from the collection at any point of time. This article will focus on how we can work with the different collections available in C#. There are three distinct collection types in C#: standard generic concurrent The standard collections are found under the System.Collections. They do not store elemen...
Post a Comment
Read more

The String.Join Method in C# Explained

The String.Join Method in C#   The string.Join concatenates the elements of a specified array or the members of a collection, using the specified separator between each element or member. Overloads of string.Join Method Description Join(Char, Object[]) Concatenates the string representations of an array of objects, using the specified separator between each member. Join(Char, String[]) Concatenates an array of strings, using the specified separator between each member. Join(String, IEnumerable<String>) Concatenates the members of a constructed IEnumerable<T> collection of type String, using the specified separator between each member. Join(String, Object[]) Concatenates the elements of an object array, using the specified separator between each element. Join(String, String[]) Concatenates all the elements of a string array, usi...
Post a Comment
Read more

System.IO Namesapce in C#

  System.IO Namesapce in C# A  file  is a collection of data stored in a disk with a specific name and a directory path. When a file is opened for reading or writing, it becomes a  stream . The stream is basically the sequence of bytes passing through the communication path. There are two main streams: the  input stream  and the  output stream . The  input stream  is used for reading data from file (read operation) and the  output stream  is used for writing into the file (write operation). From the above definition of file, the C# provides a namespace that enable us to manipulate file in C# called System.IO.   System.IO  is a  namespace  and it contains a standard IO (input/output) types such as classes , structures , enumerations , and  delegates  to perform a read/write operations on different sources like file, memory, network, etc.   System.IO Classes The table below shows differen...
Post a Comment
Read more