How to force EF to create a SQL file with concat string added?

Question

In my ASP.NET MVC 4 / EF 5 web application database, I have a table with a Comments column in which I want to add new comments. In traditional T-SQL, I would write:

UPDATE MyTable SET Comments = 'Hi' + CHAR(13)+CHAR(10) + Comments WHERE ID = 2 

I found with the linq method for EF (uow + the repository template I'm sorry about), I must first request the current value of the comments and add new comments to it:

 StringBuilder displayComment = new StringBuilder(); var item = uow.MyRepos.Where(i => i.ID == 2); item.Comments = displayComment.Append("Hi") .Append(Environment.NewLine) .Append(item.Comments) .ToString(); uow.Save(); 

Is there any way to write this update without having to query the database? I would like to somehow force EF to generate t-sql above. Comments can be long, and I don't care what it contains. Performance is a problem.

Thanks in advance.

Answer 1

The standard update sets the field, and I assume that you will need to maintain this functionality, otherwise you will not be able to edit previous comments. Thus, this means that you have a special update case. I would add a method to the repository for MyTable , which runs the sql you want:

 public int PrependComment(int id, String comment) { String sql = "UPDATE MyTable SET Comments = {0} + CHAR(13)+CHAR(10) + Comments WHERE ID = {1}"; return dbContext.Database.ExecuteSqlCommand(sql, comment, id); } 

Answer 2

I think the only thing you can play with is to use the DbCommandInterceptor . You can intercept every command executed without a request (update, delete, insert) and change the text of the command until it is actually executed. The code here is only for understanding how this can be done. You can reorganize it for more convenient use:

 public class PrependTextInterceptor : DbCommandInterceptor { const string table = "MyTable"; const string column = "Comments"; public override void NonQueryExecuting(DbCommand command, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext<int> interceptionContext) { command.CommandText = Regex.Replace(command.CommandText, string.Format(@"(?i:(?<=^UPDATE \[.+\]\.\[{0}\]\r\nSET .* \[{1}\] = ).+?(?=(,|\r\n)))", table, column), "$& + " + column); base.NonQueryExecuting(command, interceptionContext); } } //Usage DbInterception.Add(new PrependTextInterceptor()); //after this every time you set the Comments property //it will be understood as prepending item.Comments = displayComment.Append("Hi") .Append(Environment.NewLine).ToString(); 

One more note. If you load your item normally, its Comments property must also be loaded. Thus, you lose the benefit of not downloading it sooner than adding it. I think you need to use Attach , in which you just need to know the ID in order to be able to update it without loading it (for example, via the context.Set<MyTable>().Single(e => e.ID == 2) , Comments ), and then it must be confidently loaded ).