(NET) NET (2013 год)

Five amazing Linq-to-SQL extension (original in Ukrainian)

.NET Extension is a special .NET technology that looks like opposite of Class Inheritance. You can add a some function to static (or shared in VB.NET terminology) in base class. This is opportunity of language compilator, that transform high languages like VB or C# to MSIL, and programmers whose horizons are restricted С#, usually don't understand how to realize this function in VB.NET. Fist of all, VB have many advantage comparing simple languages like C# (for exmple see it in my page //www.vb-net.com/VB-Csharp-Difference/index.htm), one of this advantages is definition Module, that similar to mix as Namespace and static in C#. And in VB.NET Extension function is defined in Module code with special attribute System.Runtime.CompilerServices.Extension.

Extension function has difference to any another function in .NET, formally in definition (on C# or VB.NET) it has for one parameter greater than in run-time calling. This first parameter defines a base class, which static section is expanded with current extension function (in code below "T"). And definition of this class write in Generic-syntax (before first function parameters) - "(Of T as contraints)", where contrains defines interfaces, that can be necessary present in base class "T".

In my opinion Linq-to-SQL is best ORM in .NET framework stack. Commonly Microsoft create more and more method for manipulate data in memory buffer on high languages (evolution of MS method I I did listed in page //www.vb-net.com/DataAccessReview/index.htm and last technology of data manipulate (Entity Framework)is intended for fast change data base engine during program life cycle, and contains strange functional like mapper table name in memory to table name in DB engine and unfortunately not support main functional of any db engine like DB View (fully ignored that DB views is external presentation layer of simple normalized table of data). This disadvantage of Entity Framework and strange unclaimed functional of Entity Framework (like mapping many separate table column in memory to one table column in DB) increases the value of LINQ-TO-SQL as simple RAPID and very useful ORM among the general list of data access method for .NET framework (//www.vb-net.com/AspNetTechnologyStack/index.htm).

One of advantages of Linq-to-SQL is a simple expanding it for the extension function. LINQ-TO-SQL contains some alghorithms, that usually LINQ-TO-SQL manipulate of "Data.Linq.Table(Of T)", where defenition of "T" created automatically by Visual Studio by drag-and-drop from SQL Object Explorer. Below I show five amazing function on VB.NET, that expand Linq-to-SQL. But first of this function NewIfNull is very general and not directly related only to Linq-to-SQL. In this function "T" is defined as general class and this function expand any object in current packet of compilation.


   1:  Module LinqToSqlExtension0
   2:      ''' <summary>
   3:      ''' Create new object if it nothing (attention! parameters polimorphism not supported)
   4:      ''' </summary>
   5:      ''' <typeparam name="T"></typeparam>
   6:      ''' <param name="value"></param>
   7:      ''' <param name="ParametersForNew">If object instance created without parameters, ParametersForNew may be omitted or may be nothing  </param>
   8:      ''' <returns>return reference to object instance</returns>
   9:      ''' <remarks>(attention! parameters polimorphism not supported)</remarks>
  10:      <System.Runtime.CompilerServices.Extension()> _
  11:      Public Function NewIfNull(Of T As Class)(ByRef value As T, ByVal ParamArray ParametersForNew() As Object) As T
  12:          If value Is Nothing Then
  13:              Dim Types(0) As Type
  14:              Types(0) = GetType(T)
  15:              Dim ObjConstructors() As Reflection.ConstructorInfo = GetType(T).GetConstructors
  16:              If ParametersForNew Is Nothing Then
  17:                  'шукаємо CTOR без параметрів
  18:                  For Each One In ObjConstructors
  19:                      If One.GetParameters.Count = 0 Then
  20:                          value = ObjConstructors(0).Invoke(ParametersForNew)
  21:                          Return value
  22:                      End If
  23:                  Next
  24:                  Throw New Exception("ParametersForNew is nothing, but constructor wihtout parameters is absent")
  25:              Else
  26:                  Dim ParametersCount As Integer = ParametersForNew.Count
  27:                  For i As Integer = 0 To ObjConstructors.Count - 1
  28:                      If ObjConstructors(i).GetParameters.Count = ParametersCount Then
  29:                          'є конструктор, який має стільки ж параметрів, скілки передали у ParametersForNew
  30:                          value = ObjConstructors(i).Invoke(ParametersForNew) 'Polimorphism not supported !!!
  31:                          Return value
  32:                      End If
  33:                  Next
  34:                  Throw New Exception("ParametersForNewhas " & ParametersCount & " parameters, but constructor with " & ParametersCount & " parameters with the same type is absent")
  35:              End If
  36:          Else
  37:              Return value
  38:          End If
  39:      End Function
  40:  End Module



This function my apply to any objects in you program, including create DataContext of Linq-to-SQL and base page in ASP.NET. But in Linq-To-SQL there are one feature. If you use old DataContext, you have old cached data in DB, otherwise you need a new Linq-To-SQL datacontext. And below you may see a variant of this function specially fitted to LINQ-TO-SQL (with boolean parameter ClearCache).


   1:  Module LinqToSqlExtension4
   2:      ''' <summary>
   3:      ''' Return Linq-to-SQL context
   4:      ''' </summary>
   5:      ''' <typeparam name="T"></typeparam>
   6:      ''' <param name="value"></param>
   7:      ''' <param name="ClearCache">True, if need clear cache</param>
   8:      ''' <returns>Linq-to-SQL context</returns>
   9:      <System.Runtime.CompilerServices.Extension()> _
  10:      Public Function GetContext(Of T As System.Data.Linq.DataContext)(ByRef value As T, ByVal ClearCache As Boolean) As T
  11:          If value IsNot Nothing And Not ClearCache Then
  12:              Return value
  13:          Else
  14:              'create new
  15:              Dim ObjConstructors() As Reflection.ConstructorInfo = GetType(T).GetConstructors
  16:              'шукаємо CTOR без параметрів
  17:              For Each One In ObjConstructors
  18:                  If One.GetParameters.Count = 0 Then
  19:                      value = ObjConstructors(0).Invoke(Nothing)
  20:                      Return value
  21:                  End If
  22:              Next
  23:          End If
  24:      End Function
  25:  End Module



Most common operation with data in DB is update old data row or insert new row, if this data row is absent. Below you may see this operation in TSQL:


   1:  Create procedure UpdateURL 
   2:  @URL as varchar (50)
   3:  as
   4:  IF NOT EXISTS (select 1 from  [Gruveo].[dbo].[ProxyTab] where URL=@URL)
   5:     BEGIN
   6:        Insert  [Gruveo].[dbo].[ProxyTab]
   7:        Values  (GETDATE(),@URL)
   8:     END
   9:  ELSE
  10:        Update  [Gruveo].[dbo].[ProxyTab]
  11:        set     CrDate=GETDATE() 
  12:        where   URL=@URL

For example in this article I show this simple table of proxy-server. If ploxy-server is listed in this table, CrDate is update, otherwise new row with description of proxy-server is addind to this table.




This Insert/Update pattern repeat many times from project to project and I finally I create extension for Linq-To-SQL that realize this pattern.

Alternate to this extension is a simple VB.NET code, like this:


   1:              Dim X As IEnumerable(Of ProxyTab) = (From Y In db1.ProxyTabs Select Y Where Y.URL = Full_ProxyURL).ToList
   2:              If X.Count = 0 Then
   3:                  db1.ProxyTabs.InsertOnSubmit(New ProxyTab With {.CrDate = Now, .URL = Full_ProxyURL})
   4:              Else
   5:                  For Each One As ProxyTab In X
   6:                      One.CrDate = Now
   7:                  Next
   8:              End If

This simple VB.CODE is easy to support and modifications, but if you have many-many fragment code, similar to this, you program is obstruct with this code template (with different table names and field names) and finally using a Linq-To-SQL extension has more benefits.

And below I show extension of Linq-To-SQL, that realized Insert operation of Insert/Update pattern of programming. This is a first simple function InsertIfNotExists:


   1:  Module LinqToSqlExtension1
   2:      ''' <summary>
   3:      ''' First prm - new record in table ;
   4:      ''' Second prm - checkng expression, that apply to table ; 
   5:      ''' Return True if data inserted
   6:      ''' </summary>
   7:      ''' <typeparam name="T"></typeparam>
   8:      ''' <param name="Table"></param>
   9:      ''' <param name="SelectPredicate">First prm - checking expression, appling to table, for example:  Function(e) e.URL = Full_ProxyURL</param>
  10:      ''' <param name="NewEntity">Second prm - new record in table, for example:  New ProxyTab With {.CrDate = Now, .URL = Full_ProxyURL}</param>
  11:      ''' <returns>Return True if data inserted</returns>
  12:      ''' <remarks>If Not db1.ProxyTabs.InsertIfNotExists(Function(e) e.URL = Full_ProxyURL, New ProxyTab With {.CrDate = Now, .URL = Full_ProxyURL}) Then  db1.ProxyTabs.UpdateForCondition(Function(e) e.URL = Full_ProxyURL, Sub(e) e.CrDate = Now)</remarks>
  13:      <System.Runtime.CompilerServices.Extension()> _
  14:      Public Function InsertIfNotExists(Of T As Class)(ByVal Table As Data.Linq.Table(Of T),
  15:                                                             ByVal SelectPredicate As Expressions.Expression(Of Func(Of T, Boolean)),
  16:                                                             ByVal NewEntity As T) As Boolean
  17:          If Not Table.Any(SelectPredicate) Then
  18:              Table.InsertOnSubmit(NewEntity)
  19:              Table.Context.SubmitChanges()
  20:              Return True
  21:          Else
  22:              Return False
  23:          End If
  24:      End Function
  25:   
  26:  End Module



From now Insert-update template (that you seen twice above - in TSQL and simple VB.NET) will look as that:


   1:              If Not db1.ProxyTabs.InsertIfNotExists(Function(e) e.URL = Full_ProxyURL,
   2:                  New ProxyTab With {.CrDate = Now, .URL = Full_ProxyURL}) Then _
   3:                  db1.ProxyTabs.UpdateForCondition(Function(e) e.URL = Full_ProxyURL, Sub(e) e.CrDate = Now)

Commonly, it's a big questions, what code is more understandable - simple VB, TSQL or this execution of Linq-To-SQL extension function with Lambda Expression. But this calling to extension may be write in one string of VB.NET with different table and fields of table.

Pay attention that VB-compiler separating from this one string of VB-code one labda function, especially Lambda function "Sub(e) e.CrDate = Now" transform to hidden function _Lambda$__XXXX.




The secondary additionally function of insert-update pattern is UpdateForCondition:




   1:  Module LinqToSqlExtension2
   2:      ''' <summary>
   3:      ''' First prm - checking expression, appling to table, for example:  Function(e) e.URL = Full_ProxyURL;
   4:      ''' Second prm - Action, for example: Sub(e) e.CrDate = Now;
   5:      ''' Return True if data updated
   6:      ''' </summary>
   7:      ''' <typeparam name="T"></typeparam>
   8:      ''' <param name="table"></param>
   9:      ''' <param name="SelectPredicate">First prm - checking expression, appling to table, for example:  Function(e) e.URL = Full_ProxyURL</param>
  10:      ''' <param name="UpdateAction">Second prm - Action, for example: Sub(e) e.CrDate = Now</param>
  11:      ''' <returns>Return True if data updated</returns>
  12:      ''' <remarks>If Not db1.ProxyTabs.InsertIfNotExists(Function(e) e.URL = Full_ProxyURL, New ProxyTab With {.CrDate = Now, .URL = Full_ProxyURL}) Then  db1.ProxyTabs.UpdateForCondition(Function(e) e.URL = Full_ProxyURL, Sub(e) e.CrDate = Now)</remarks>
  13:      <System.Runtime.CompilerServices.Extension()> _
  14:      Public Function UpdateForCondition(Of T As Class)(ByVal table As Data.Linq.Table(Of T),
  15:                                                              ByVal SelectPredicate As Expressions.Expression(Of Func(Of T, Boolean)),
  16:                                                              ByVal UpdateAction As Action(Of T)) As Boolean
  17:   
  18:          'Dim X As IEnumerable(Of ProxyTab) = db1.ProxyTabs.Where(Function(e) e.URL = Full_ProxyURL).ToList
  19:          'For Each One As ProxyTab In SelectedRows
  20:          '    One.CrDate = Now
  21:          'Next
  22:   
  23:          Dim SelectedRows As System.Collections.Generic.IEnumerable(Of T) = table.Where(SelectPredicate)
  24:          If SelectedRows.Count > 0 Then
  25:              For Each One As T In SelectedRows
  26:                  UpdateAction.Invoke(One)
  27:              Next
  28:              table.Context.SubmitChanges()
  29:              Return True
  30:          Else
  31:              Return False
  32:          End If
  33:      End Function
  34:   
  35:  End Module

And third function of insert-update pattern is InsertOrUpdateTable, that combine functional of both above function and consist fully realized of insert-update pattern.




   1:  Module LinqToSqlExtension3
   2:      ''' <summary>
   3:      ''' First prm - checking expression, appling to table, for example:  Function(e) e.URL = Full_ProxyURL;
   4:      ''' Second prm - new record in table, for example:  New ProxyTab With {.CrDate = Now, .URL = Full_ProxyURL};
   5:      ''' Third prm - Action, for example: Sub(e) e.CrDate = Now;
   6:      ''' Return True if inserted or false if update
   7:      ''' </summary>
   8:      ''' <typeparam name="T"></typeparam>
   9:      ''' <param name="Table"></param>
  10:      ''' <param name="SelectPredicate">First prm - checking expression, appling to table, for example:  Function(e) e.URL = Full_ProxyURL</param>
  11:      ''' <param name="NewEntity">Second prm - new record in table, for example:  New ProxyTab With {.CrDate = Now, .URL = Full_ProxyURL}</param>
  12:      ''' <param name="UpdateAction">Third prm - Action, for example: Sub(e) e.CrDate = Now</param>
  13:      ''' <returns>Return True if inserted or false if update</returns>
  14:      ''' <remarks>db1.ProxyTabs.InsertOrUpdateTable(Function(e) e.URL = Full_ProxyURL, New ProxyTab With {.CrDate = Now, .URL = Full_ProxyURL}, Sub(e) e.CrDate = Now)</remarks>
  15:      <System.Runtime.CompilerServices.Extension()> _
  16:      Public Function InsertOrUpdateTable(Of T As Class)(ByVal Table As Data.Linq.Table(Of T),
  17:                                                               ByVal SelectPredicate As Expressions.Expression(Of Func(Of T, Boolean)),
  18:                                                               ByVal NewEntity As T,
  19:                                                               ByVal UpdateAction As Action(Of T)) As Boolean
  20:          Dim SelectedRows As System.Collections.Generic.IEnumerable(Of T) = Table.Where(SelectPredicate)
  21:          If SelectedRows.Count > 0 Then
  22:              For Each One As T In SelectedRows
  23:                  UpdateAction.Invoke(One)
  24:              Next
  25:              Table.Context.SubmitChanges()
  26:              Return False
  27:          Else
  28:              Table.InsertOnSubmit(NewEntity)
  29:              Table.Context.SubmitChanges()
  30:              Return True
  31:          End If
  32:      End Function
  33:   
  34:  End Module

Execution of this function is show as this:


   1:              db1.ProxyTabs.InsertOrUpdateTable(Function(e) e.URL = Full_ProxyURL,
   2:                                                New ProxyTab With {.CrDate = Now, .URL = Full_ProxyURL},
   3:                                                Sub(e) e.CrDate = Now)

and this execution doing the same, as execution TSQL-procedure UpdateURL.






Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23
Link to this page: //www.vb-net.com/Linq-To-SQL-Extension/index.html
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>