Shulou(Shulou.com)06/02 Report--

This article mainly explains the "LINQ to SQL simple single table batch deletion how to achieve", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "LINQ to SQL simple single table batch deletion how to achieve" it!

How to get SQL? there is a db.GetCommand (IQueryable) method in LINQ, and the CommandText of DBCommand is SQL, so how do we get IQueryable? my feeling is the where method. So there is the following statement, IQueryable Q = source.Where (query). AsQueryable (); DbCommand cmd = db.GetCommand (Q)

Now that we have waited until the query SQL, how to convert it to Delete, I used regular expression matching. You get the following complete code:

Public static class TableExtension {/ single table operation batch delete / public static int Delete (this System.Data.Linq.Table source Expression query) where T: class {if (source = = null) throw new ArgumentException ("source") If (query = = null) throw new ArgumentException ("query"); / / query = t = > true; / / empty DELETE FROM [dbo]. [test] all deleted Personally, I think it is inhumane to delete all of it, so I still throw an exception System.Data.Linq.DataContext db = source.Context; IQueryable Q = source.Where (query). AsQueryable (); DbCommand cmd = db.GetCommand (Q); string sql = cmd.CommandText String regex = @ "from\ s*\ [\ s*dbo\ s*\]\ s*\\ [\ s*\ w+\ s*\]\ s* (? (as\ s* (? (\ [\ s*\ w+\ s*\]) (\. | (\ r) | (\ n)) *"; MatchCollection matches = Regex.Matches (sql, regex, RegexOptions.Multiline | RegexOptions.IgnoreCase) Debug.Assert (matches! = null, "regex match: null"); Debug.Assert (matches.Count = = 1, "regex match length:" + matches.Count); if (matches! = null & & matches.Count > 0) {Match match = matches [0] Sql = ("DELETE" + match.Value.Replace (match.Groups ["tableparam"] .Value, ") + sql.Substring (match.Index + match.Length)) .replace (match.Groups [" tablepname "] .Value +". "); List dbparams = new List (); foreach (SqlParameter item in cmd.Parameters) {SqlParameter p = new SqlParameter (item.ParameterName, item.SqlDbType, item.Size) P.Value = item.Value; dbparams.Add (item.Value);} Q = null; cmd = null; matches = null; Debug.WriteLine ("delete sql:" + sql); return db.ExecuteCommand (sql, dbparams.ToArray ()) } return 0;}

Now we can delete in batches, such as:

DataContext.test.Delete (t = > t.id > 0 & & t.name.Contains ("test") | | new string [] {"12", "34", "45"} .Conclusion (t.name) & & t.name.Length > 1)

Isn't it simple: take a look at the SQL output from Debug:

Looking at one:

DataContext.Orders.Delete (t = >! t.EmployeeID1.HasValue)

The drawbacks in this extension:

1: only for single table operations, linq connections are not supported, such as DataContext.test.Delete (t = > t.Orders.OrderID > 0). I think this can be done through a more complex regular expression. Another way of thinking is more concise, which is to query the results through the table variables or temporary tables supported by sql server and delete them according to the primary key. You may say that you can write as a stored procedure, I personally do not like it, I generally try to TSQL in the program, portability is good, unless performance considerations use stored procedures.

2: transaction processing: this is just a previous test Demo, so there is no consideration. I think we can set up a class to manage sql statements or query conditional expressions for internal transaction operations when calling the save method (I generally don't like to show transactions externally, because I think transactions are very resource-intensive, the database is easy to crash for too long waiting time, or the locking of tables, etc., I don't know if this idea is correct, please give me a suggestion). Now can operate in external transactions is a bit like ADO.NET, ha ha trouble.

Advantages:

1: complex expressions can be supported, such as "item.Introduction.Length < 10" mentioned by Lao Zhao, as well as in, like and so on mentioned in later comments.

2: simple to implement, simple to operate, this is the most important, the code is easy to understand.

3: execution does not need to extract entities from the database to execute, which is the practice of Linq. It's more in line with our habits. I saw a comment on the old Zhao blog * * that it also uses EF to achieve batch deletion. Its way is to take out entities in delete, which is not unique to EF. Linq can also be implemented, just a few lines of code.

Also want to write more advantages, just think of these for the time being, if you have any Bug or suggestions, please be sure to point out. In addition, I don't know if this has any impact on memory, and I haven't had time to test it yet. I personally think that in fact, computer things are simple problems in our lives, just in the personal mind, just like the PredicateExtensions class of the foreigner Linq combination query mentioned earlier. Well, now hurry to take a look at the implementation of Lao Zhao's predecessor. I haven't had time to see a line of code. I don't know how to support transactions and multi-tables.

Thank you for your reading, the above is the "LINQ to SQL simple single table batch delete how to achieve" content, after the study of this article, I believe you on the LINQ to SQL simple single table batch delete how to achieve this problem has a deeper understanding, the specific use of the need for you to practice and verify. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.