Using old legacy SQL, I would do "DELETE FROM t_users WHERE f_id = @u". My ASP.NET Button contains the id value on its CommandArgument property which is appended to my SQL string as a parameter.
The problem is that when using LINQ to SQL you have no way of doing this statically. I know you can do ExecuteCommand("etc") but it defeats the purpose of using LINQ; that is to do it statically.
I resorted to creating my own extension method for creating this simple query from a static construct like this:
| 1 2 |
var db = new dbDataContext(); db.t_users.Delete(x => x.f_id == 1); |
VB.NET
| 1 2 |
Dim db = new dbDataContext(); db.t_users.Delete(Function(x) x.f_id = 1); |
"t_users" is my table in the SQL server. I'm using lambda to delete a record with f_id = 1. The "Delete" function is my extension method:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Linq.Expressions; using System.Text.RegularExpressions; public static class Extensions { public static int Delete<T>(this System.Data.Linq.Table<T> Table, Expression<Func<T,Boolean>> Where) where T : class { string TableName = ((System.Data.Linq.Mapping.TableAttribute)typeof(T).GetCustomAttributes(true)[0]).Name; string Query = "DELETE FROM " + TableName; IQueryable<T> q; System.Data.Common.DbCommand c; if (Where != null) { q = Table.Where(Where).AsQueryable(); c = Table.Context.GetCommand(q); Query = Query + " WHERE " + c.CommandText.Replace("[t0].", "").Split(new string[] { "WHERE" }, StringSplitOptions.None).Last(); } else { q = Table.AsQueryable(); c = Table.Context.GetCommand(q); } c.CommandText = Query; c.Connection.Open(); var i = c.ExecuteNonQuery(); c.Connection.Close(); return i; } } |
VB.NET
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Imports Microsoft.VisualBasic Imports System.Runtime.CompilerServices Imports System.Linq.Expressions Public Module Extensions <Extension()> _ Public Function Delete(Of T As Class)(ByRef Table As Data.Linq.Table(Of T), Where As Expression(Of Func(Of T, Boolean))) As Integer Dim TableName As String = CType(GetType(T).GetCustomAttributes(True)(0), Data.Linq.Mapping.TableAttribute).Name Dim Query As String = "DELETE FROM " & TableName Dim q As IQueryable(Of T) Dim c As Data.Common.DbCommand If Where IsNot Nothing Then q = Table.Where(Where).AsQueryable c = Table.Context.GetCommand(q) Query = Query & " WHERE " & Split(c.CommandText.Replace("[t0].", ""), "WHERE").Last Else q = Table.AsQueryable c = Table.Context.GetCommand(q) End If c.CommandText = Query c.Connection.Open() Dim i = c.ExecuteNonQuery() c.Connection.Close() Return i End Function End Module |
I also wanted to delete records using the "Where" function followed by a "Delete":
| 1 2 |
var db = new dbDataContext(); db.t_users.Where(x => x.f_id == 1).Delete(); |
VB.NET
| 1 2 |
Dim db = New dbDataContext(); db.t_users.Where(Function(x) x.f_id = 1).Delete() |
So, I created an overload of the "Delete" function above:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
public static int Delete<T>(this IQueryable<T> Table) where T : class { if (Table is System.Data.Linq.Table<T>) { return Delete((System.Data.Linq.Table<T>)Table, null); } if (Table.GetType().FullName.Contains("DataQuery") == false) { throw new NotSupportedException(); } else { var e = Table.GetType().GetField("queryExpression", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance); if (e != null) { string s = e.GetValue(Table).ToString(); string r = Regex.Replace(s, "^Table[(].+?[)][.](.*?)[(].*?[)]$", "$1"); if (r != "Where") { throw new NotSupportedException("'" + r + "' is not supported."); } } var f = Table.GetType().GetField("context", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance); if (f != null) { System.Data.Linq.DataContext Context = (System.Data.Linq.DataContext)f.GetValue(Table); var c = Context.GetCommand(Table); string Criteria = c.CommandText.Replace("[t0].", "").Split(new string[] { "WHERE" }, StringSplitOptions.None).Last(); string TableName = ((System.Data.Linq.Mapping.TableAttribute)typeof(T).GetCustomAttributes(true)[0]).Name; string Query = "DELETE FROM " + TableName + " WHERE " + Criteria; c.CommandText = Query; c.Connection.Open(); var i = c.ExecuteNonQuery(); c.Connection.Close(); return i; } else { return 0; } } } |
VB.NET
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
<Extension()> _ Public Function Delete(Of T As Class)(ByRef Table As IQueryable(Of T)) As Integer If TypeOf Table Is Data.Linq.Table(Of T) Then Return Delete(CType(Table, Data.Linq.Table(Of T)), Nothing) End If If Table.GetType.FullName.Contains("DataQuery") = False Then Throw New NotSupportedException Else Dim e = Table.GetType().GetField("queryExpression", System.Reflection.BindingFlags.NonPublic Or System.Reflection.BindingFlags.Instance) If e IsNot Nothing Then Dim s As String = e.GetValue(Table).ToString() Dim r As String = Regex.Replace(s, "^Table[(].+?[)][.](.*?)[(].*?[)]$", "$1") If r <> "Where" Then Throw New NotSupportedException("'" & r & "' is not supported.") End If Dim f = Table.GetType.GetField("context", Reflection.BindingFlags.NonPublic Or Reflection.BindingFlags.Instance) If f IsNot Nothing Then Dim Context As Data.Linq.DataContext = f.GetValue(Table) Dim c = Context.GetCommand(Table) Dim Criteria As String = Split(c.CommandText.Replace("[t0].", ""), "WHERE").Last Dim TableName As String = CType(GetType(T).GetCustomAttributes(True)(0), Data.Linq.Mapping.TableAttribute).Name Dim Query As String = "DELETE FROM " & TableName & " WHERE " & Criteria c.CommandText = Query c.Connection.Open() Dim i = c.ExecuteNonQuery() c.Connection.Close() Return i Else Return 0 End If End If End Function |
No comments:
Post a Comment