Hi,

Here is my problem:

I have a column that is a string/varchar. This column always contains a number saved in text/string/varchar.

I would like to write the following where clause in LINQ TO LLBL GEN ... I know I can do this in MS SQL:

Where cast([ScheduledTime] as bigint) < 1000000000

Here is my code so far:

            // using an EntityRelation object
            var q2 = qf.StagingMessage
                .From(QueryTarget.LeftJoin(StagingMessageEntity.Relations.SubmitMessageEntityUsingStagingMessageId)).
                Where(

                (StagingMessageFields.ScheduledTime <  HELP ME HERE  &StagingMessageFields.DateTime < DateTime.Now & StagingMessageFields.DateTime > DateTime.Now.AddDays(-100) & (SubmitMessageFields.Id == System.DBNull.Value))
                
                );

            // fetch them using a DataAccessAdapter instance
            using (IDataAccessAdapter adapter = dataFactory.NewDataAccessAdapter())
            {

                adapter.FetchQuery(q2, customers);
            }

In need the StagingMessageFields.ScheduledTime to be converted in the where clause to INT64

what does 10000000000000 mean? ticks? You have to define a custom function mapping of DateTime.Ticks. http://www.llblgen.com/documentation/4.0/LLBLGen%20Pro%20RTF/hh_goto.htm#Using%20the%20generated%20code/Linq/gencode_linq_functionmappings.htm#Preface

Yes, the link you provided explains it well, but I am still stuck :

I am attaching a screenshot of SQL SERVER

The "cast" function I want to use in my where clause is already defined in SQL Server as a native function

I don't know how to map something that does not clearly shows me the parameters it needs

Thus when I write "cast( x as bigint)" does this translate to

public class NorthwindFunctions
{
    public static INT64 CastMe(object x, Type forNowIamAbigInt)
    {
        // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        return 0.0M;
    }
}


/// Class which defines the custom mapping between NorthwindFunctions.CalculateOrderTotal and
/// fn_CalculateOrderTotal, the database function.
public class NorthwindFunctionMappings : FunctionMappingStore
{
    public NorthwindFunctionMappings() : base()
    {
    // define the mapping. SQLServer 2000 needs the schema to be present for the function call, 
    // so we specify that as well.
    this.Add(new FunctionMapping(typeof(NorthwindFunctions), "CastMe", 2, 
                        "Cast({0}, {1})", "Northwind", "dbo"));
    }
}

Please help me understand how to do this function mapping.

Please see the attachment in regards to this as well

Specifying StagingMessageFields.ScheduledTime in the query will simply result in the field, not the cast. So you have to direct the linq provider to add the cast. So the function mapping should be something like CAST({0} as bigint).

I see you're using queryspec, I overlooked this, I assumed you were using Linq and I sent you to the wrong spot. Sorry for that. (you posted this in the linq forum, hence my confusion)

In QuerySpec you can either define a DbFunctionCall predicate directly, or define a functionmappingexpression.

A DbFunctionCall expression is a little awkward looking, as it falls back onto the old low-level API. It still is OK to know how it works though.

    
var q2 = qf.StagingMessage
    .From(QueryTarget.LeftJoin(StagingMessageEntity.Relations.SubmitMessageEntityUsingStagingMessageId)).
    Where(
    (
        (qf.Field("ScheduledTime").SetExpression(new DbFunctionCall("CAST({0} as bigint)", StagingMessageFields.ScheduledTime)) < 1000000000)
            .And(StagingMessageFields.DateTime < DateTime.Now).And(StagingMessageFields.DateTime > DateTime.Now.AddDays(-100)).And(SubmitMessageFields.Id == System.DBNull.Value)
    );

Another, more cleaner alternative, is using a function mapping. You first define the function mapping:

public class MyFunctionMappings : FunctionMappingStore
{
    public MyFunctionMappings() : base()
    {
        this.Add(new FunctionMapping(typeof(MyFunctions), "CastToBigInt", 1, "Cast({0} as bigint)"));
    }
}

And then you define the method. This is done a little different in queryspec than it is done in linq.

public class MyFunctions
{
    public static FunctionMappingExpression CastToBigInt(IEntityFieldCore fieldToCast)
    {
        return new FunctionMappingExpression(typeof(MyFunctionMappings), "CastToBigInt", 1, fieldToCast);
    }
}

You then use it like:

var q2 = qf.StagingMessage
    .From(QueryTarget.LeftJoin(StagingMessageEntity.Relations.SubmitMessageEntityUsingStagingMessageId)).
    Where(
    (
        (MyFunctions.CastToBigInt(StagingMessageFields.ScheduledTime) < 1000000000)
            .And(StagingMessageFields.DateTime < DateTime.Now).And(StagingMessageFields.DateTime > DateTime.Now.AddDays(-100)).And(SubmitMessageFields.Id == System.DBNull.Value)
    
    );
q2.CustomFunctionMappingStore = new MyFunctionMappings();

Not tested, but it should give you an idea how to get it working.