Custom .net functions in Linq to Sql

Question

Custom function in Linq to Sql problem - I've googled for about an hour now trying to find answers to this one. However, the puzzle I've set myself is a little trickier than usual. At the bottom of the post is a generic CRUD operations class I'm using in a legacy project. We can use it either purely with static methods OR by having a proper instance property on the partial classes in question. The project uses a DBML file so these partials are for each of the tables in this model. I wished to implement a generic Find(id, byref Db as dbcontext) so that classes do not need their own implementation.

However, I'm running into Linq-to-Sql's rather frustrating obtuseness in relation to translating .net functions into SQL queries.

I've tried a few approaches - building expression trees and so on but nothing passes the unit test and I end up with the same error message:

Message: System.NotSupportedException : Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

This is where I'm at now:

   Friend Function Find(Id As Integer, ByRef db As Travelworld_DataContext) As T
    Dim table = DirectCast(db.GetTable(M_Type), Linq.Table(Of T))
    If Id < 1 Then Return Nothing
    Dim x As Expression(Of Func(Of T, Boolean)) = Function(c) CInt(PrimaryKeyProperty.GetValue(c)) = Id
    Return table.Where(Function(c As T) x.Compile.Invoke(c)).FirstOrDefault
End Function

If I merely try to put the Expression itself in here like so:

Return table.Where(Function(c As T) x(c)).FirstOrDefault

I get x cannot be indexed as it has no default value-my thinking here was that it wanted an Expression(of Func(of T, boolean)) but give it one and it whines.

I've tried a few different ways to building this but nothing has worked yet. Rather than just abandon it completely, I'd like to find out if anyone else has solved this and, if using other libraries ones I can still get. I have seen Tomas Petricek's article but the Dlinq project does not appear anymore.

The code below is the full class minus a couple of extra methods we use. It will work for other folks in a similar situation if anyone else has need of this.

Because the schema is in a legacy project, a large one, we can't alter primary key names and other items so we have to pick them up in a fairly manual fashion.

Thank you for any advice!

Public Class DataClass(Of T As Class)
#Region "Shared"
Private Shared DoNotUpdate As List(Of String) = New List(Of String) From {"CreatedOn", "CreatedBy", "Created_On", "Created_By", "Create_On", "Create_by", "CreateBy", "CreateOn"}

Private Shared Function ValidProperties(p As Type, NonWriteable() As String) As List(Of PropertyInfo)
    DoNotUpdate.AddRange(NonWriteable)
    Return p.GetProperties.Where(Function(x) Not x.PropertyType.Name.Contains("EntityRef") _
                                                    AndAlso Not DoNotUpdate.Any(Function(s) s.ToUpper.Contains(x.Name.ToUpper)) _
                                                     AndAlso x.CanWrite AndAlso x.PropertyType.Namespace = "System").ToList
End Function

''' <summary>
''' Send me db, UpdateItem, OriginalItem and array of field you want me to skip and I'll compare and update the rest.
''' Example: Return DataClass(Of Part).Update(db, updated, Original, {"Part_id"} )
''' </summary>
''' <param name="db"></param>
''' <param name="UpdateItem"></param>
''' <param name="originalItem"></param>
''' <param name="NonWriteable"></param> 
''' <returns></returns>
Public Shared Function Update(ByRef db As DBContext,
                              UpdateItem As T,
                              originalItem As T,
                              NonWriteable() As String,
                              Optional _validProperties As List(Of PropertyInfo) = Nothing) As T
    If originalItem Is Nothing OrElse UpdateItem Is Nothing Then Return Nothing
    If _validProperties Is Nothing Then _validProperties = ValidProperties(UpdateItem.GetType, NonWriteable)
    For Each p In _validProperties
        If p.PropertyType.Name.Contains("Nullable") Then
            If Not Nullable.Equals(p.GetValue(UpdateItem), p.GetValue(originalItem)) Then p.SetValue(originalItem, p.GetValue(UpdateItem))
        Else
            If p.GetValue(UpdateItem) IsNot p.GetValue(originalItem) Then p.SetValue(originalItem, p.GetValue(UpdateItem))
        End If
    Next 
    If db.GetChangeSet.Updates.Count > 0 Then SubmitChanges(db)
    Return originalItem
End Function

''' <summary>
''' Example  Return If(Item IsNot Nothing, DataClass(Of Part).Save(Item.part_id, Item, Function() Update(Item)), Nothing)
''' </summary>
''' <param name="PrimaryKey"></param>
''' <param name="UpdateItem"></param>
''' <param name="updateAction"></param>
''' <returns></returns>
Public Shared Function Save(PrimaryKey As Integer,
                            UpdateItem As T,
                            updateAction As Func(Of T),
                            Optional RefreshAction As Action = Nothing) As T
    If UpdateItem Is Nothing Then Return Nothing
    If PrimaryKey = 0 Then 'Without an interface or inheritance we can't know which field is primary key without extensive reflection.
        Using db As New DBContext
            db.DeferredLoadingEnabled = False
            Dim table = db.GetTable(UpdateItem.GetType) 'What table are we inserting to?
            table.InsertOnSubmit(UpdateItem)
            SubmitChanges(db)
        End Using
    Else
        UpdateItem = updateAction() 'Need a proper update action sent through 
    End If
    If RefreshAction IsNot Nothing Then RefreshAction()
    Return UpdateItem
End Function
#End Region

#Region "Instance Version"
Private ReadOnly M_Type As Type
Private ReadOnly PrimayKeyName As String
Private ReadOnly PrimaryKeyProperty As PropertyInfo

Private _ValidProperties As List(Of PropertyInfo)
Private ReadOnly Property M_ValidProperties As List(Of PropertyInfo)
    Get
        Return _ValidProperties
    End Get
End Property
Public Sub New(_type As Type, pkey As String)
    M_Type = _type
    PrimayKeyName = If(pkey <> "", pkey, "id")
    PrimaryKeyProperty = _type.GetProperties.Where(Function(x) x.Name = pkey).FirstOrDefault
    _ValidProperties = ValidProperties(_type, {pkey})
End Sub

Public Function Save(Item As T) As T
    Return If(Item IsNot Nothing, Save(CInt(PrimaryKeyProperty.GetValue(Item)), Item, Function() Update(Item)), Nothing)
End Function

Private Function Update(Updated As T) As T
    Using db As New DBContext
        Dim Original = Find(CInt(PrimaryKeyProperty.GetValue(Updated)), db)
        Return Update(db, Updated, Original, {PrimayKeyName}, M_ValidProperties)
    End Using
End Function

 Public Function Clone(Original As T) As T
    Dim c = Clone(Original, {PrimayKeyName})
    Save(c)
    Return c
End Function

End Class

Update: Thanks to Netmage I've got a complete generic data layer class that works in a commercial setting.

Minus anything confidential I've laid out a complete version here for anyone else who has a similar scenario.

If you are in Winforms, using a DBML (linq to sql) file and have a lot of db context work stuck on the back of forms in a medium to large sized legacy project, then this will help. I use t4 templating and a separate linq pad tool that use reflection to generate consuming classes. I'll add this to a larger write up if anyone is interested.

https://gist.github.com/SoulFireMage/cc725e4ff0e8b5af5ce6c38eb1fe2578

I may convert it to C# this weekend :)

Why would you need such a function. Isn't it just as easy just to write db.users.FirstOrDefault(function(x) x.Id = id) than new DataClass(of Users).Find(id, db) — Magnus
– Magnus, Commented Aug 17, 2017 at 11:24
If you look at the context of the problem, my primarykeys aren't all named ID (sadly). — Richard Griffiths
– Richard Griffiths, Commented Aug 17, 2017 at 12:13
As it's a generic class, the idea is that consuming classes merely have a save and a private find for use by the private update method. — Richard Griffiths
– Richard Griffiths, Commented Aug 17, 2017 at 12:14
Your Expression doesn't go deep enough - you are creating a lambda that uses GetValue when you need to create a lambda using PrimaryKeyName with a Expression.PropertyOrField. — NetMage
– NetMage, Commented Aug 17, 2017 at 23:32

NetMage · Accepted Answer · 2017-08-17 23:47:24Z

2

To create your generic Find, you need to build up the entire lambda as an Expression tree, not just the end result:

Friend Function Find(Id As Integer, ByRef db As Travelworld_DataContext) As T
    Dim table = DirectCast(db.GetTable(M_Type), Linq.Table(Of T))
    If Id < 1 Then Return Nothing
    Dim parm = Expression.Parameter(GetType(T))
    Dim keyRef = Expression.PropertyOrField(parm, PrimaryKeyProperty.Name)
    Dim keyMatch = Expression.Constant(Id)
    Dim Body = Expression.MakeBinary(ExpressionType.Equal, keyRef, keyMatch)
    Dim x As Expression(Of Func(Of Accounts, Boolean)) = Expression.Lambda(Body, parm)

    Return table.Where(x).FirstOrDefault
End Function

BTW, it appears in my database that the columns are Fields, not Propertys.

edited Aug 17, 2017 at 23:47

answered Aug 17, 2017 at 23:42

NetMage

27.1k3 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Richard Griffiths Over a year ago

I'll try this when I get into work. I added complexity by having the class be generic :)

Richard Griffiths Over a year ago

Oh and thank you for answering! I'll be back later with results :)

Richard Griffiths Over a year ago

This works perfectly. All tests go green on this and the performance is fine.

Richard Griffiths Over a year ago

Thank you very very much @NetMage!

Collectives™ on Stack Overflow

Custom .net functions in Linq to Sql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related