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

6

How is the generated query created in Linq 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 the columns of the object (Product_Table), change the Name field, and send the Context save, if I were doing the update query with the sql syntax would look like this;

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

My doubts are;

  • Does Context have some way to generate the same syntax?
  • Is there any way to see the query that is being sent to the bank in this case?
  • The question below is the most critical and the one I'm most interested to know.

  • How is it done by Context so that it knows that only the column Name changed?
  • asked by Marconcilio Souza 28.10.2016 в 20:24

    2 answers

    2

    As the context ( DbContext ) works when I call an entity by First() , FirstOrDefault() , Find(value) , ie I bring a record to 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 (DbContext):

    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 code above, basic auditing is used, which shows which SQL is being generated, in the example case two are Select and Update , being an update only of what was modified, as shown in debug logo below:

      

    Then,concludethattheupdateismadeonlyofthepropertythathasbeenchanged.

      

    1-DoesContexthavesomewaytogeneratethesamesyntax?

    Yesitgeneratesthesamesyntax,makingupdatesonlyofpropertiesthathavebeenchanged.

      

    2-Isthereanywaytoseethequerybeingsenttothebankinthiscase?

    Yes,itdoes,db.Database.LogwillbringtheSQLgeneratedbythecontext.

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

    CanalsobeusedintheDebugTracewindow:

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

      

    3-HowisContextmadeawarethatonlytheNamecolumnhaschanged?

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

    Thisshowsdb.Entry(cr).Property(x=>x.Lines).IsModifiedifthepropertyhaschanged,theContextisbasedonthetrueresponse(true),andthecodeonlychangeswhatismodifiedastrue.

    Thereisnothingimplementedtocheckwhichfieldsaremodifiedatonce,butcanbeeasilydonebyanextensionmethod,totellwhichfieldshavechangedandwhichhavenot.

    publicstaticclassMethodsUpdate{publicstaticDictionary<string,bool>GetUpdatePropertyNames<T>(thisDbEntityEntry<T>entry)whereT:class,new(){Dictionary<string,bool>entryUpdate=newDictionary<string,bool>();foreach(stringnameinentry.CurrentValues.PropertyNames){entryUpdate.Add(name,entry.Property(name).IsModified);}returnentryUpdate;}}Dictionary<string,bool>propertyChanges=db.Entry(cr).GetUpdatePropertyNames();
      

    Inthisspecificcasethechangeinthebankhasnotyetbeenapplied,onlyinitscontext.

    References:

    28.10.2016 / 21:09
    5
  • Does Context have some way to generate the same syntax?
  • I do not know, but I do not think ... it makes sense to use entity when you want to do things with ADO.NET .

  • Is there a 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);
    
  • How is it done by Context so that it knows that only the column Name changed?
  • Through 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;
            }
        }
    }
    
        
    28.10.2016 в 20:45