Logging SQL Statements of Entity Framework 5 for Database-First Aproach

Logging SQL statements of Entity Framework 5 for database-first aproach

After modifying your code, you need to enable tracing in your web.config like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.diagnostics>
    <sources>
      <source name="EntityFramework.NorthwindEntities" switchValue="All" />
    </sources>
  </system.diagnostics>
</configuration>

The name of your TraceSource should be your context name prefixed with 'EntityFramework'.
Make sure that you disable tracing when you deploy your application to production by setting the switchValue to Off.

Logging all Entity Framework queries in the debug window in DB-First Approach

You can install global logger by adding the following class to the project containing your DbContext derived class:

class MyDbConfiguration : System.Data.Entity.DbConfiguration
{
    public MyDbConfiguration()
    {
        AddInterceptor(new System.Data.Entity.Infrastructure.Interception.DatabaseLogFormatter(
            s => System.Diagnostics.Debug.WriteLine(s)));
    }
}

The class represents the so called Code-based configuration and in this particular case is used to automatically register DatabaseLogFormatter with the specified Action<string> for all DbContext derived types and instances in the project that contains it.

Logging every data change with Entity Framework

How about handling Context.SavingChanges?

How do I view the SQL generated by the Entity Framework?

You can do the following:

IQueryable query = from x in appEntities
             where x.id == 32
             select x;

var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();

or in EF6:

var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query)
            .ToTraceString();

or in EF6.3+:

var sql = ((dynamic)flooringStoresProducts).Sql;

That will give you the SQL that was generated.

Suppress SQL Queries logging in Entity Framework core

Don't know if this is still an active question, but this is my solution, override the minimum level for "Microsoft.EntityFrameworkCore.Database.Command"

 Log.Logger = new LoggerConfiguration()
            .MinimumLevel.ControlledBy(loggingLevelSwitch)
            .MinimumLevel.Override("Microsoft.EntityFrameworkCore.Database.Command", Serilog.Events.LogEventLevel.Warning)
            .Enrich.WithProperty("app", environment.ApplicationName)
            .Enrich.FromLogContext()
            .WriteTo.RollingFile($"./Logs/{environment.ApplicationName}")
            .CreateLogger();

you can also have this on the appconfig.json

"Serilog": {
    "Using": [ "Serilog.Sinks.Console" ],
    "MinimumLevel": {
      "Default": "Verbose",
      "Override": {
        "Microsoft": "Warning",
        "Microsoft.EntityFrameworkCore.Database.Command": "Warning"
      }
    },
    "WriteTo": [
      {
        "Name": "Console",
        "Args": {
          "outputTemplate": "[{Timestamp:u}] [{Level:u3}] {SourceContext} {Message:lj}{NewLine}{Exception}"
        }
      },
    ],
    "Enrich": [ "FromLogContext", "WithExceptionDetails" ]
  }

Entity Framework How to see SQL statements for SaveChanges method

In general you can hook up the built-in tracer or any logger by simple

context.Database.Log = msg => Trace.WriteLine(msg);

in the DbContext constructor.
See more in MSDN. Some other approaches from MS are here (all based on DataContext.Log property).

Talking about the Clutch solution mentioned by Nate, it doesn't work with EF v6 (see this bug-report).

REFERENCES

1. Logging and Intercepting Database Operations (EF6 Onwards)
2. Logging and Intercepting Database Operations

---

How to get data from Junction Table using DB First Approach in MVC

The junction table was not included by Entity Framework because it did not have a primary key. you fix this problem by adding a primary key to the junction table then update your Edmx file.

After updating model, you can retrieve data from your junction table with a simple linq query.

The difference between the two cases:

1) if your junction table doesn't contain a primary key, EF will generate two class with many to many relation

2) if your junction table contain a primary key, EF will generate 3 Class with: Junction table in one to many relation with user and one to many with user_role

Update

Try with this please :

public override string[] GetRolesForUser(string username)
    {
        //get all user data from user table for id based on user email
        int _user_id = Convert.ToInt32(db.users.Where(u => u.user_email == username).Select(i => i.user_id).FirstOrDefault());

        // Get role from user_has_role against user id
        var _role = db.user_has_role.Where(r => r.user_id == _user_id).Select(r => r.user_role.user_role_name);

        // store selected
        string[] roleName = _role.ToArray();

        if (roleName != null)
        {
            return roleName;
        }
        else
        {
            roleName = null;
            return roleName;
        }
    }

---

Related Topics