Has anybody written IExpression implementation for IsNull sql function, so that one can use it in Select like: Select IsNull(field1, field2) as field

The following thread might be helpful to you: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

If you're using v2.0, you can use the DbFunctionCall expression for this

There is a FunctionExpression written in C# in the thread http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

I converted it to VB.NET. Can you take a look as see if it looks right.

    <Serializable()> _
    Public Class FunctionExpression : Inherits Expression

        Private _functionToApply As String

        Public Sub New()
            _functionToApply = String.Empty
        End Sub

        ' Selfservicing constructor.All other constructors are hidden, we only work on a single field. 

        Public Sub New(ByVal field As IEntityField, ByVal functionToApply As String)
            MyBase.New(field)
            _functionToApply = functionToApply
        End Sub

        ' Adapter constructor. All other constructors are hidden, we only work on a single field.
        Public Sub New(ByVal field As IEntityField2, ByVal functionToApply As String)
            MyBase.New(field)
            _functionToApply = functionToApply
        End Sub

        Public Overloads Function ToQueryText(ByRef uniqueMarker As Integer, ByVal inHavingClause As Boolean) As String
            If (_functionToApply.Length > 0) Then
                Return String.Format("{0}({1})", _functionToApply, MyBase.ToQueryText(uniqueMarker, inHavingClause))
            Else
                Return MyBase.ToQueryText(uniqueMarker, inHavingClause)
            End If
        End Function

        Public Property FunctionToApply() As String
            Get
                Return _functionToApply
            End Get
            Set(ByVal Value As String)
                _functionToApply = Value
            End Set
        End Property

    End Class

Looks OK

Forgive my ignorance, as I am still learning how to use the tool, so I am not quite clear now how to proceede from here to extend it to except two arugments. I guess there is an overload for constructor that allows left and right operands to be specified?

That's only necessary if you're using adapter and use 2 fields as parameters. (so the adapter can add the persistence info for both parameters). Otherwise just add the parameters in the constructor and emit them into the SQL in ToQueryText.

Ok, so I created this IsNullFunction. Now how do I use it in dymamic list to get this SQL

SELECT 
        isnull (U2.Name, U1.Name) as Name,

When I am defining fields every overload expects field index, and ExpressionToApply is per field. I am lost.

 <Serializable()> _
    Public Class IsNullFunction : Inherits Expression

        Public Sub New(ByVal field1 As IEntityField2, ByVal field2 As IEntityField2)
            MyBase.New(field1, ExOp.None, field2)
        End Sub

        Public Overloads Function ToQueryText(ByRef uniqueMarker As Integer, ByVal inHavingClause As Boolean) As String

            Dim builder1 As New StringBuilder(128)

            OperandToText(builder1, Me.LeftOperand, uniqueMarker, True, inHavingClause)

            builder1.Append(",")

            Me.OperandToText(builder1, Me.RightOperand, uniqueMarker, False, inHavingClause)

            Return String.Format("IsNull({0})", builder1.ToString)

        End Function

        Private Sub OperandToText(ByRef queryText As StringBuilder, ByVal operand As IExpressionElement, ByRef uniqueMarker As Integer, ByVal isLeftOperand As Boolean, ByVal inHavingClause As Boolean)
            Dim element1 As IExpressionFieldElement = Nothing
            Select Case operand.Type
                'Case ExpressionElementType.Value
                '   Dim parameter1 As IDataParameter = Me._creator.CreateParameter(("LO" & operand.GetHashCode.ToString), ParameterDirection.Input, operand.Contents)
                '   Me._parameters.Add(parameter1)
                '   uniqueMarker = (uniqueMarker + 1)
                '   parameter1.Value = operand.Contents
                '   parameter1.ParameterName = (parameter1.ParameterName & uniqueMarker.ToString)
                '   queryText.Append(parameter1.ParameterName)
                '   Return
            Case ExpressionElementType.Field
                    element1 = DirectCast(operand, ExpressionFieldElement)
                    Dim core1 As IEntityFieldCore = DirectCast(element1.Contents, IEntityFieldCore)
                    queryText.AppendFormat("{0}", Me.DatabaseSpecificCreator.CreateFieldName(core1, element1.PersistenceInfo, core1.Name, core1.ObjectAlias, uniqueMarker, inHavingClause))
                    If (Not core1.ExpressionToApply Is Nothing) Then
                        Me.Parameters.AddRange(core1.ExpressionToApply.Parameters)
                    End If
                    'Case ExpressionElementType.Expression
                    '   Dim expression1 As IExpression = DirectCast(operand.Contents, IExpression)
                    '   expression1.DatabaseSpecificCreator = Me._creator
                    '   queryText.AppendFormat(Nothing, "({0})", New Object() {expression1.ToQueryText(uniqueMarker, inHavingClause)})
                    '   Me._parameters.AddRange(expression1.Parameters)
                    '   Return
            End Select
        End Sub
    End Class

Try the following:

dynamicListFields[0].SetExpression(new IsNullFunction(yourEntityFields.Field1, yourEntityFields.Field2));

Hi,

I found a lot of inspiration in this thread. Here's my ISNULL implementation, based on a field and a value. It can (and prollie should) be extended for your purpose. It's written in C# (.Net2), for SQL Server 2000 and using 1.0.2005.1 version of LLBLGen (the latest before 2 that is).

using System;
using System.Collections;
using System.Text;
using System.Data;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace Dipica.BM.LLBLGenCustom
{
    [Serializable]
    public class IsNullExpression : Expression
    {
        #region class member declarations
        private string m_DefaultValue;
        private Type m_type;
        #endregion

        public IsNullExpression(IEntityField field, string defaultValue, Type type)
            : base(field, ExOp.None, defaultValue)
        {
            m_DefaultValue = defaultValue;
            m_type = type;
        }

        public IsNullExpression()
            : base()
        {
            m_DefaultValue = String.Empty;
            m_type = null;
        }

        public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
        {
            StringBuilder str = new StringBuilder();

            OperandToText(str, this.LeftOperand, ref uniqueMarker, true, inHavingClause);
            str.Append(",");
            OperandToText(str, this.RightOperand, ref uniqueMarker, false, inHavingClause);

            return String.Format("ISNULL({0})", str.ToString());
        }


        private void OperandToText(StringBuilder str, IExpressionElement operand, ref int uniqueMarker, bool isLeft, bool inHavingClause)
        {
            IExpressionFieldElement element = new ExpressionFieldElement();
            switch (operand.Type)
            {
                case ExpressionElementType.Field:
                    element = (ExpressionFieldElement)operand;
                    IEntityFieldCore core = (IEntityFieldCore)element.Contents;
                    str.AppendFormat("{0}", DatabaseSpecificCreator.CreateFieldName(core, element.PersistenceInfo, core.Name, core.ObjectAlias, ref uniqueMarker, inHavingClause));
                    if (core.ExpressionToApply != null)
                    {
                        this.Parameters.AddRange(core.ExpressionToApply.Parameters);
                    }
                    break;
                case ExpressionElementType.Value:
                    if (m_type == typeof(DateTime))
                        str.AppendFormat("{0}", "CONVERT(DATETIME,'" + operand.Contents.ToString() + "')");
                    else
                        throw new NotImplementedException("Only datetime is implemented as IsNull param.");
                    break;
            }
        }


        #region properties
        public string DefaultValue
        {
            get { return m_DefaultValue; }
            set { m_DefaultValue = value; }
        }
        #endregion
    }
}

It's used as follows (pretty complex example...):

// filter to take the latest contract based upon the datedeparture
            filter.AddWithAnd(new FieldCompareSetPredicate(
                ContractFields.MonthDateDeparture.SetExpression(new LLBLGenCustom.IsNullExpression(ContractFields.MonthDateDeparture, "01-01-3000", typeof(DateTime))),
                ContractFields.MonthDateDeparture.SetObjectAlias("c").SetExpression(new LLBLGenCustom.IsNullExpression(ContractFields.MonthDateDeparture.SetObjectAlias("c"), "01-01-3000", typeof(DateTime))),
                SetOperator.Equal,
                (ContractFields.EmploymentOid.SetObjectAlias("c") == EmploymentFields.Oid),
                null,
                String.Empty,
                1,
                new SortExpression(ContractFields.MonthDateDeparture.SetObjectAlias("c") | SortOperator.Descending)));

The filter above took me a lot of time to write out, but Walaa asked the right questions and gave the correct tips. But you'll see the flexibility and usage (I hope).

I hope it'll have a bit of use.

Koen

Thanks for sharing the code, Koen!