Entity Framework: How to log SQL statements

In this blog, we will learn how to log the commands and SQL quries sent to database by EntityFramework. In Entity Framework, DBContext provides the DbContext.Database.Log property to log the SQL generated by DbContext.

Logging the generated SQL statement by entity framework always help to you to understand that how the entity framework are parsing the EF query and generating raw SQL statement and send to SQL engine to fetch records and  it helps you to tune EF query and improve system performance.

Here is an IQueryable EF query, which fetch the customer entity based on customer ID. We can use ToString() method to get the generated SQL statement.

IQueryable<Customer> query = _dbConext.Customers.Where(x => x.CustomerID == ID);

string sql = query.ToString();

Console.WriteLine(sql)

Output:

SELECT [Extent1].[CustomerID] AS [CustomerID], [Extent1].[CompanyName] AS [CompanyName]

FROM [dbo].[Customers] AS [Extent1]

WHERE [Extent1].[CustomerID] = @p0

In Entity Framework 6.0, there is a DbContext.Database.Log property, which used to setup delegate to log the commands and generated SQL queries sent to database by DBContext.

public Action<string> Log { get; set; }

Here is an example for logging EF activities to the console.

_dbConext.Database.Log = Console.Write;

IQueryable<Customer> query = _dbConext.Customers.Where(x => x.CustomerID == ID);

Other entity framework related posts:

·        Entity Framework : Bulk Update

·        Basic Steps to improve EF query performance

·        Entity Framework 4.0: How to log SQL statements

·        Entity Framework : Create, Update and Delete

·        Entity Framework - Asynchronous Programming (async/await)

·        Entity Framework : Inner /Left Outer Joins

·        Entity Framework : Eager Loading of Multiple Levels of Entities by Using Include

·        Entity Framework : Bulk Update

·        EF 4.0 : Managing Concurrency

SQL - Delete Duplicate Rows

This blog will demonstrates how to delete duplicate rows by using SQL Row_Number() function in sql server.

Here is a Student table which have few duplicate records (in yellow color)

ID	Code	Address	Created Date
1	CN01	Memphis, TN	7/1/2017
2	CN02	Nashville, TN	7/9/2017
3	CN03	Cordova, TN	7/10/2017
4	CN01	Memphis, TN	7/11/2017

First we need to decide, which row we are going to keep and which one we are going to delete.

Here is an example to delete all duplicate records except the oldest record.

Step 1: Get duplicate student records – here is a sub query which group by code and select only those code which are having more than 1 records (duplicate records)

Select Code, count(Code) [Ct] from vr.Student group by Code

having count(Code) > 1

Step 2: Generate Row Number by using SQL ROW_NUMBER() function for each duplicate records

With CTE AS

(

Select y.* , ROW_NUMBER() OVER(PARTITION BY y.Code ORDER BY y.Code, y.ID) AS RowRank from 

(

Select Code, count(Code) [Ct] from dbo.Student group by Code

having count(Code) > 1

) y

)

Select * from CTE

Output: 

ID	Code	Address	Created Date	RowRank
1	CN01	Memphis, TN	7/1/2017	1
4	CN01	Memphis, TN	7/11/2017	2

In above output, the oldest duplicate row has low row rank and now we can delete all duplicate records except the oldest record (row rank = 1)

Delete from dbo.Student where ID IN (Select ID from CTE where CTE.RowRank <> 1)

Here is a complete SQL Query

With CTE AS

(

  Select y.* , ROW_NUMBER() OVER(PARTITION BY y.Code ORDER BY y.Code, y.ID) AS RowRank  from 

(

Select Code, count(Code) [Ct] from dbo.Student group by Code

having count(Code) > 1

) 

)

Delete from dbo.Student where ID IN (Select ID from CTE where CTE.RowRank <> 1)

Entity Framework : Create, Update and Delete Record

Entity Framework is ORM .net framework, which is used to update, create or delete record from table.

As Per MSDN “Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects”

EntityFramework works as a layer between database and business layer (application code) and entity framework maps each database table to entity class ( C# CLR class) and with help of this entity class, developer can perform SQL DML operation like create, modify and delete on mapped database table.

Create Record: 

Below code shows how to add new record of department entity

   Department department = newDepartment()

            {

                Name = "Admin"

            };

   DBContext DbContext = new DBContext ();

   DbContext.Departments.Add(department);

   DbContext.SaveChanges();

As you can see, we are performing below steps to create a new record of department with department name ‘Admin’ 

1. Create new object of Department and set department Name to “Admin”.
2. Create new DBContext Object.
3. Add new department object to Department entity set.
4. Call  saveChanges() method of DBContext Object to add new department record and this method  parse the entity graph and generate SQL script and execute on connected database.

Update Record:

Below code shows how to modified the existing record of department entity

   DBContext DbContext = new DBContext ();

   Department department = DbContext.Departments.FirstOrDefault(d => d.Name == "Admin");

   department.Code = "A";

   DbContext.SaveChanges();

As you can see, below steps are being used to update the existing record of department entity

1. Create new DBContext Object.
2. Fetch the existing record of department and load into local memory.
3. Update desired fields or properties of department record.
4. Call saveChanges() method of DBContext Object to update department record and this method parse the entity graph and generate SQL script 

Delete Record:

Below code shows how to delete the existing record of department entity

   DBContext DbContext = new DBContext ();

   Department department = DbContext.Departments.FirstOrDefault(d => d.Name == "Admin");

   DbContext.Departments.Remove(department);

   DbContext.SaveChanges();

As you can see, we are performing below steps to delete the existing record of department entity

1. Create new DBContext Object.
2. Load the existing record of department into memory.
3. Remove the record from Department entity Set.
4. Call SaveChanges() method of DBContext Object to delete the attached department record and this method parse the entity graph and generate SQL script and execute on the connected database.

Thanks for visiting !!


Other Entity framework related Links: 

Entity Framework : Bulk Update

Basic Steps to improve EF query performance

Entity Framework 4.0: How to log SQL statements

Entity Framework : Create, Update and Delete

Entity Framework - Asynchronous Programming (async/await)

Entity Framework : Inner /Left Outer Joins

Entity Framework : Eager Loading of Multiple Levels of Entities by Using Include 

Entity Framework : Bulk Update

EF 4.0 : Managing Concurrency

System.InvalidOperationException: This request has been blocked because sensitive information could be disclosed to third party web sites when this is used in a GET request. To allow GET requests, set JsonRequestBehavior to AllowGet

I encountered error “This request has been blocked because sensitive information could be disclosed to third party web sites when this is used in a GET request. To allow GET requests, set JsonRequestBehavior to AllowGet.” when I tried to return JSON response from MVC controller.

Here is a MVC Controller method, which returns the JSON response.

 public JsonResult GetUserClaims(string userID)

        {

            var response =_userManager.GetClaims(userID);

            return Json(response);

        }

Here is a reason for this exception:

By default, the ASP.NET MVC framework does not allow you to respond to an HTTP GET request with a JSON payload.

If you need to send JSON in response to a GET, you'll need to explicitly allow the behavior by using JsonRequestBehavior.AllowGet as the second parameter to the Json method. However, there is a chance a malicious user can gain access to the JSON payload through a process known as JSON Hijacking. You do not want to return sensitive information using JSON in a GET request.

Here the way to set JsonRequestBehavior to AllowGet for JSON response

   public JsonResult GetUserClaims(string userID)

        {

            var response =_userManager.GetClaims(userID);

            return Json(response,JsonRequestBehavior.AllowGet);

        }

WCF MTOM MessageEncoding

In blog, we will learn about the Message Transmission Optimization Mechanism (MTOM) message encoding with a WSHttpBinding in WCF service and will demonstrates steps how to use the MTOM message encoding with C# example.

MTOM is an efficient technology for transmitting large binary data with SOAP in WCF messages. By default, the WSHttpBinding sends and received messages as normal text XML but transferring large chunk of data as normal XML text form, it is not better choice, if we choose the binary encoding, it only support to WCF-to- WCF communication and but exclude interoperability option for service.

MTOM encoder create a balance efficiency and interoperability and The MTOM encoding transmits most XML in textual form, but optimizes large blocks of binary data by transmitting them as-is, without conversion to text.

Here is pros and cons of message encoding:

·         Text Encoding :

o   Highly supports the interoperability

·         Binary Encoding:

o   High speed but only WCF –to- WCF communication

·         MTOM Encoding :

o   Message Transmission Optimization Protocol

o   Support both XML and binary

Here are steps how to use the MTOM message encoding in WCF Service

1.   Create Message Contract:

The method, which sends MTOM content, can only use [MessageContract] attribute decorated class or "primitive" types for receiving or sending data.

[MessageContract]

    public class OrderReport

    {

     [MessageHeader]

     public int OrderID { get; set; }

     [MessageBodyMember]

     public Byte OrderReportPDF { get; set; }

}

In [MessageContract] Class, the byte array properties must be decorated with a [MessageBodyMember] attribute and all the other properties of class must be decorated with [MessageHeader] attribute.

2.   Service Contract:

Here is a ServiceContract, which has a method GetOrderReportData to get Order Report Data based on input Order ID

namespace OrderWCF

{

    [ServiceContract]

    public interface IOrder

    {            

         [OperationContract]

             OrderReport GetOrderReportData(int OrderID);

     

    }

}

3.   WCF Binding Configuration:

Both client and server bindingConfiguation must have the messageEncoding attribute to Mtom

 &lt;bindings>

  <wsHttpBinding>

    <binding name="WsHttpMtomBinding" messageEncoding="Mtom" />

  </wsHttpBinding>

</bindings>

<services>

  <service behaviorConfiguration="Behavior" name="OrderWCF.OrderService">

    <endpoint binding="wsHttpBinding" bindingConfiguration="WsHttpMtomBinding" contract="OrderWCF.IOrder">

  </service> 

</services>

ASP.NET MVC - View not refreshing after postback

Recently I faced a strange problem with MVC @HTMLHelper class and after post, the view is not getting updated with new data.

I have one hidden field (property name = ‘ID’ as below) on HTML Form to track the new entity or existing entity, so on basis of ID value, I need to make a call to create new entity or update the existing entity.

@using (Html.BeginForm("", "", FormMethod.Post, new { @class = "form-inline" }))

 {

    @Html.HiddenFor(m => m.ID)

 }

But when I tried to update the recently created entity and I found that the value of ID property is 0 in model, it should hold newly created entity ID and it seems like the view model is not getting refreshed after post

I found the two possible solution to solve this view model restressed issue.

1.   Clear ModelState Value in controller:

You can clear the model state before returning model to view and ModelState.Clear() method is usually used to clear errors but it is also used to force the MVC engine to rebuild the model to be passed to your View.

MODELSTATE.CLEAR ();

2.   Second option Instead of clear of all ModelState dictionary Values and validation error message, you can only remove desired ID property /Field and MVC Engine will rebuild value only for removed property

ModelState.Remove(“ID”);

Other ASP.NET MVC related topics:

·         ASP.NET MVC how to disable Caching

·         Ajax Get method not hitting the ASP.NET MVC Controllers

·         Asp.net MVC view not refreshing after Postbank 

·         OutPutCache Action Filter in ASP.NET MVC

Thanks for visiting!!