How is the query generated in Linq mounted when we do UPDATE?

Question

How the query generated in Linq is mounted when we do UPDATE?

using ColWebDBContext.DBase.Banco;
using System;
using System.Web.UI;
using System.Linq;

namespace DBContext
{
    public partial class _Default : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            using (var ctx = new dbContext())
            {
                var Produto = ctx.Tabela_Produtos.FirstOrDefault(p => p.Produto == 1);
                Produto.Nome = "Bombril";
                ctx.SaveChanges();
            }
        }
    }
}

In the example above, I search all object columns (Table_products), change the field Name and send the Context save, if I were doing the update query with the sql syntax that would be like this;

update Tabela_Produtos
set Nome = 'Bombril'
where Produto = 1;

My doubts are;

1. The Context has some form to generate the same syntax?
2. Is there any way to see the query being sent to bank in that case?

The question below is the most critical and the one I’m most interested in knowing.

3. As it is done by Context so that he knows that only the column Name has changed?

Answer 1

1. The Context has some form to generate the same syntax?

I don’t know, but I don’t think... the use of entity when you want to do "things" of ADO.NET.

2. Is there any way to see the query being sent to the bank in this case?

Yes. You can create your own logger or create a simple extension:

public static class QueryHelper
{
    public static string TraceSql<T>(this IQueryable<T> t)
    {
        var sql = "";
        ObjectQuery<T> oqt = t as ObjectQuery<T>;

        if (oqt != null)
            sql = oqt.ToTraceString();

        return sql;
    }
}

Or:

ctx.Database.Log = x => System.Diagnostics.Debug.WriteLine(x);

3. As it is done by Context so that he knows that only the column Name has changed?

Through the Getmodifiedmembers or through a small implementation:

using (var ctx = new dbContext())
{
    var Produto = (from p in ctx.Produto
                            where p.Produto == 1
                            select p).FirstOrDefault();
    Produto.Nome = "Bombril";

    QueryHelper<Product>.Log(db, Produto);//seu logger

    ctx.SaveChanges();
}


public static class QueryHelper
{
    public static void Log<T>(DataContext dataContext, T entidade) where T : Class
    {
        foreach(var propriedadeModificada in dataContext.GetTable<T>().GetModifiedMembers(entidade))
        {
            var valorAtual = propriedadeModificada.CurrentValue;
            var valorOriginal = propriedadeModificada.OriginalValue;
        }
    }
}

Answer 2

As the context (DbContext) works when I call an entity by First(), FirstOrDefault(), Find(value), that is, I bring a record for change:

Model:

[Table("Credit")]
public class Credit
{
    public Credit()
    {
    }
    public Credit(string description)
    {
        Description = description;
    }
    public Credit(int id, string description)
    {
        Id = id;
        Description = description;
    }

    [Key()]  
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]        
    public int Id { get; set; }

    [Required()]
    [MaxLength(50)]
    public string Description { get; set; }

    public bool Status { get; set; }
    public int Lines { get; set; }
}

Context ():

public class Database  : DbContext
{
    public Database()
        :base(@"Server=.\SqlExpress;Database=dbtest;User Id=sa;Password=senha;")
    {
       Database.Initialize(false);
    }

    public DbSet<Credit> Credit { get; set; }    
}

Running operations:

class Program
{
    static void Main(string[] args)
    {
        using (Database db = new Database())
        {
            db.Database.Log = ca => WriteLog(ca);

            int value = 1;
            Credit cr = db.Credit.Find(value);
            cr.Lines = cr.Lines + 1;
            db.SaveChanges();

        }



        System.Console.WriteLine("Pression <Enter> ...");
    }

    public static void WriteLog(string c)
    {
        System.Console.WriteLine(c);
    }
}

In the above code is used the basic audit which shows which SQL are being generated, in the case example are two a Select and a Update, being an update only of what has been modified, as demonstrated in debug just below:

Then conclude that the update only the property that has been changed.

1 - Context has some form to generate the same syntax?

Yes it generates the same syntax, updating only the properties that have been changed.

2 - Is there any way to see the query that is being sent to the bank in this case?

Yes exite, db.Database.Log will bring the SQL generated by context.

using (Database db = new Database())
{
     db.Database.Log = ca => WriteLog(ca);

Can also be used in the Debug Trace:

db.Database.Log = ca => System.Diagnostics.Debug.WriteLine(ca);

---

3 - How is done by Context to let it know that only the column Name has changed?

Credit cr = db.Credit.Find(value);
cr.Lines = cr.Lines + 1;
db.Entry(cr).Property(x => x.Lines).IsModified

This shows db.Entry(cr).Property(x => x.Lines).IsModified if the property has undergone any alteration, the Context is based on the truth answer (true) and the code is responsible for changing only what is modified as true.

There is nothing implemented to check which fields are modified at once, but it can be done easily by an extension method to tell which fields have changed and which have not suffered:

public static class MethodsUpdate
{
    public static Dictionary<string, bool> GetUpdatePropertyNames<T>(this DbEntityEntry<T> entry)
        where T: class, new()
    {
        Dictionary<string, bool> entryUpdate =
            new Dictionary<string, bool>();
        foreach (string name in entry.CurrentValues.PropertyNames)
        {
            entryUpdate.Add(name, entry.Property(name).IsModified);
        }
        return entryUpdate;
    }
}

Dictionary<string, bool> propertyChanges = db.Entry(cr).GetUpdatePropertyNames();

In this specific case the change in the bank has not yet been applied, only in its context.

References:

• Entity Framework 6 - Logging SQL commands
• Logging and Intercepting Database Operations With Entity Framework 6.0
• Update only modified Fields in Entity Framework
• How can I log the generated SQL from Dbcontext.Savechanges() in my Program?
• Microsoft Developer Network - Dbentityentry Class
• Entity Framework Tutorial - Dbentityentry Class
• Get Table Name From Dbentityentry (Code-First)
• Get Primary Key of Dbentityentry when Adding
• Mocking or faking Dbentityentry or Creating a new Dbentityentry