Hi,

I'm using LLBLGen Pro 2.6 with Oracle 10 and I need to create a particular SQL query with a cast operator on a particular field of my where clause.

Is there a way to do that with perhaps linq or native llblgen features? Is it possible to write my own query in lllbgen (like ado .net sqlcommand) ?

Thanks in advance,

Best regards,

Please post the query you need to execute. And to which object data structure you want to fetch the data?

Walaa wrote:

Please post the query you need to execute. And to which object data structure you want to fetch the data?

SELECT code, userName FROM Users WHERE code = cast( : param as VARCHAR2(50));

I want to fetch this in an existing entity (UserEntity).

Thanks,

Regards,

Please check Calling a database function, especially the "Function calls in expressions" section.

Once you have the correct predicate expression, you can use it to fetch entities or dynamicList....etc.

Walaa wrote:

Please check Calling a database function, especially the "Function calls in expressions" section.

Once you have the correct predicate expression, you can use it to fetch entities or dynamicList....etc.

Can I couple this method with linq?

Do you have an example built on the top of my sql statement ?

Thanks in advance,

regards,

The LINQ2LLBL mehotd is called Function Mappings. There are a lot of default mappings you can use right away (ToString, for instance that will execute CONVERT or CAST).

I use the following linq statement:

from u in metadata.UsersEntity
where u.Code == param.ToString()
select u.Code, u.userName

And the generated sql is :

SELECT code, userName FROM Users WHERE code = : param

So I can't convert the param with a cast likse this :

SELECT code, userName FROM Users WHERE code = cast( : param as VARCHAR2(50));

Do you have any idea?

Thanks in advance,

Regards,

Please try creating your own custom FunctionMapping, check the code samples in the link posted by Daelmo.

Walaa wrote:

Please try creating your own custom FunctionMapping, check the code samples in the link posted by Daelmo.

I will try this one but nothing change :

public class FunctionMap
{
    public static string Cast(string param)
    {
        return param;
    }
}

public class FunctionMapping: FunctionMappingStore
{
    public FunctionMapping()
    {
        this.Add(new FunctionMapping(typeof(FunctionMap), "Cast", 1, 
                    "cast({0} as VARCHAR(10)", "", "testschema"));
    }
}

Linq statement:

new LinqMetaDataAdapter (adapter,new FunctionMapping());

from u in metadata.UsersEntity
where u.Code == FunctionMap.Cast(param)
select u.Code, u.userNamewhere 

The sql output never change, what's wrong in my code ?

Thanks in advance,

Try this:

public class FunctionMap { public static string Cast(string param) { return ""; } }

public class FunctionMapping: FunctionMappingStore { public FunctionMapping()** : base()** { this.Add(new FunctionMapping(typeof(FunctionMap), "Cast", 1, "cast({0} as VARCHAR(10)", "", "testschema")); } }

Walaa wrote:

Try this:

public class FunctionMap { public static string Cast(string param) { return ""; } }

public class FunctionMapping: FunctionMappingStore { public FunctionMapping()** : base()** { this.Add(new FunctionMapping(typeof(FunctionMap), "Cast", 1, "cast({0} as VARCHAR(10)", "", "testschema")); } }

The sql generated is still the same one without my cast. Is it normal ? And also my paramter in the sql query is empty.

If I put the code on the select statement no problem but in the where clause no sql generated contains my cast.

Thanks,

Regards,

Mmm. The problem is that you are applying ToString to an in-memory variable. What if you apply the function (ToString) to the column (instead of the param)? Or, What is the problem with compare the column with the string?

Anyway, I'lll review your mapping to see if I can reproduce your problem

I have a problem with Paging, when ever page number higher than 1, the function throw an exception "[b]Object reference not set to an instance of an object.", i really wonder becoz there is no null object at all. i am using llbl gen pro 2.6 and sql server 2005 + .Net 3.5 here is my code

totalrecords = 0;
            ResultsetFields field = new ResultsetFields(8);
            int i = 0;
            field.DefineField(ListingFields.HouseNo, i++);
            field.DefineField(ListingFields.Bed, i++);
            field.DefineField(ListingFields.Bath, i++);
            field.DefineField(ListingFields.Price, i++);
            field.DefineField(ListingFields.StreetName, i++);
            field.DefineField(ListingFields.ListingId, i++);
            field.DefineField(ListingFields.City, i++);

            PredicateExpression prsub = new PredicateExpression();
            prsub.Add(ListingMediaFields.ListingId == ListingFields.ListingId);
            prsub.Add(ListingMediaFields.MediaOrder== 1);       

            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.PredicateExpression.Add(SetPredicate(bed));
            SortExpression se = new SortExpression();
            se.Add(ListingFields.Price | SortOperator.Descending);

            IEntityField2 fileName = new EntityField2("FileName", new ScalarQueryExpression(ListingMediaFields.FileName, prsub), AggregateFunction.None);
            field.DefineField(fileName, i++);

            DataAccessAdapter da = new DataAccessAdapter();

            DataTable dt = new DataTable();

                da.FetchTypedView(field, dt, bucket, 10, se, true, null, pageNo, 10); (this line gives error)
            
                totalrecords = da.GetDbCount(new EntityCollection<ListingEntity>(), bucket, null);
                return dt;

i have also used fetch entity collection but, it also give the same error.... pllz help

daelmo wrote:

Mmm. The problem is that you are applying ToString to an in-memory variable. What if you apply the function (ToString) to the column (instead of the param)? Or, What is the problem with compare the column with the string?

Anyway, I'lll review your mapping to see if I can reproduce your problem

The result is the same when I apply the tostring on the column instead of the variable. I also try without the tostring and I ve got the same problem.

Any idea?

Regards,

Please use the Convert.ToString(theField) instead of the theField.ToString().

@wagar: please open a new thread so we can follow your issue better

daelmo wrote:

Please use the Convert.ToString(theField) instead of the theField.ToString().

This is the same result. I also try this one :

from u in metadata.UsersEntity
where u.Code == FunctionMap.Cast(param)
select u.Code, u.userName

And I don't see the cast on the where clause.

Regards,

Mmm. It works nice to me. I will try to reproduce that on my Oracle EX installation and post the results here. Are you using the latest LLBLGen Runtime Libraries?

daelmo wrote:

Mmm. It works nice to me. I will try to reproduce that on my Oracle EX installation and post the results here. Are you using the latest LLBLGen Runtime Libraries?

Yes I have recently update my dll.

Could you please post the complete code used ?

Thanks in advance,

Regards

The 'Cast' function mapping definition, do you pass it to the metaData constructor? please post some more code (e.g. around the query).

Otis wrote:

The 'Cast' function mapping definition, do you pass it to the metaData constructor? please post some more code (e.g. around the query).

Please read the post on 11-Mar-2009 11:25:08 ALl the code are there and this code is in a unit test function so there is nothing around it.

I don't understand why LLBLgen sometimes generate a cast on some sql statement and sometimes not. Do you have a special rule for that ?

What's strange is the functionmapping store work only on the select clause.

Regards,

from u in metadata.UsersEntity where u.Code == FunctionMap.Cast(param) select u.Code, u.userName

In the above code, where does "param" come from? Is it a string value? Did you check the produced SQL query? Would you please post it here?

Thanks.

Walaa wrote:

from u in metadata.UsersEntity where u.Code == FunctionMap.Cast(param) select u.Code, u.userName

In the above code, where does "param" come from? Is it a string value? Did you check the produced SQL query? Would you please post it here?

Thanks.

Hi,

The sql generated was posted on the 11-Mar-2009 09:51:29. but here is it again:

Generated Sql query: 
Query: SELECT "LPLA_1"."CODE" AS "Code", "LPLA_1"."USERNAME" AS "UserName " FROM "TEST"."Users" "LPLA_1" WHERE ( ( ( ( "LPLA_1"."CODE" = :param1))))
Parameter: :param1 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "12345".

param is a string value that contains 12345

You said in a previous post that the cast work on your side could you please post the complete code that your are using to do this operation ?

I don't understand why LLBLgen sometimes generate a cast on some sql statement and sometimes not. Do you have a special rule for that ?

What's strange is the functionmapping store work only on the select clause.

Thanks a lot,

Regards,

WHen I pasted your code directly in the editor:

public class FunctionMap
{
    public static string Cast(string param)
    {
        return param;
    }
}

public class FunctionMapping: FunctionMappingStore
{
    public FunctionMapping()
    {
        this.Add(new FunctionMapping(typeof(FunctionMap), "Cast", 1,
                    "cast({0} as VARCHAR(10)", "", "testschema"));
    }
}

It threw an error. You see the issue? The 'FunctionMapping' instantiation you pass to this.Add() is ... the FunctionMapping ctor call you do the this.Add in.

i.o.w. your code doesn't compile. So I wonder how it did compile on your side.

Furthermore, your cast(... ) function misses a closing ')'.

Now to the issue. Functionmapping is for values available inside a query. You pass a constant. This isn't passed to the function in the DB, but to the method in memory. So if you place a breakpoint in FunctionMap.Cast, you'll see it gets called. This is due to the fact that linq allows mixing of db code and in-memory code, however it doesn't say which part is which. So the linq provider tries to find these parts by itself, as there's no other way. It does that by checking if a given expression inside the expression tree has any relations with other parts. As your query simply does FunctionMap.Cast(param) where 'param' is an in-memory variable, it sees that as an in-memory call and simply executes that call before processing the query and replaces FunctionMap.Cast(param) with the result of that method.

Your string also doesn't require casting to a string, it's already a string, (as Cast simply accepts a string).

Also remember that you specified a schema called 'testschema' though your query contains the schema 'TEST'. In general, the schema you specifiy in the functionmapping is the one specified in front of the function.

You want to use a system function, CAST(), so you shouldn't specify any schema in the mapping.

Otis wrote:

WHen I pasted your code directly in the editor:

public class FunctionMap
{
    public static string Cast(string param)
    {
        return param;
    }
}

public class FunctionMapping: FunctionMappingStore
{
    public FunctionMapping()
    {
        this.Add(new FunctionMapping(typeof(FunctionMap), "Cast", 1,
                    "cast({0} as VARCHAR(10)", "", "testschema"));
    }
}

It threw an error. You see the issue? The 'FunctionMapping' instantiation you pass to this.Add() is ... the FunctionMapping ctor call you do the this.Add in.

i.o.w. your code doesn't compile. So I wonder how it did compile on your side.

Furthermore, your cast(... ) function misses a closing ')'.

Now to the issue. Functionmapping is for values available inside a query. You pass a constant. This isn't passed to the function in the DB, but to the method in memory. So if you place a breakpoint in FunctionMap.Cast, you'll see it gets called. This is due to the fact that linq allows mixing of db code and in-memory code, however it doesn't say which part is which. So the linq provider tries to find these parts by itself, as there's no other way. It does that by checking if a given expression inside the expression tree has any relations with other parts. As your query simply does FunctionMap.Cast(param) where 'param' is an in-memory variable, it sees that as an in-memory call and simply executes that call before processing the query and replaces FunctionMap.Cast(param) with the result of that method.

Your string also doesn't require casting to a string, it's already a string, (as Cast simply accepts a string).

Also remember that you specified a schema called 'testschema' though your query contains the schema 'TEST'. In general, the schema you specifiy in the functionmapping is the one specified in front of the function.

You want to use a system function, CAST(), so you shouldn't specify any schema in the mapping.

Hi,

thanks for your help. So it is not possible to make an explicit cast on a where clause ?

Regards,