Server side where predicate

[Alessio Bulleri]

Hello,

I read Your recent posts saying ....Telerik OpenAccess ORM does not support executing queries with nested Where clauses on the server yet

So I'm asking You when will be possible to have this feature implemented.

Thanks,

Alessio

[Zoran]

Hi Alessio Bulleri,

There is not a concrete date for which we can promise you the implementation of sub queries, but Q1 is a likely goal for this feature. The unsupported Linq feature at the moment are sub-select queries and you mentioned about nested where clauses. Can you give us a specific query that you have problems executing so we can tell you more accurately about the status of that part of the Linq implementation or maybe suggest you a possible workaround? 

Regards,
Zoran
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Alessio Bulleri]

Thank You for the response.

What I'm trying to do is a method on a base class like this

        public IQueryable<T> GetFiltered(Expression<Func<T, bool>> whereExpression, Expression<Func<T, bool>> orderbyExpression)
        {
            IQueryable<T> qr = null;
            try
            {
                if (whereExpression != null)
                {
                    qr = _scope.Extent<T>().Where<T>(whereExpression.Compile()).AsQueryable<T>();

//this piece of code works, but is slowly, first it make a SELECT * FROM db then, after loaded all recs it filter them.

//If I try to omit .Compile() I receive the following error   Invocation of 'f => (f.Fieldname = 257547)' on the database server side currently not implemented; consider using the uncompiled expression so that symbolic processing is possible.

                }
                else
                    qr = _scope.Extent<T>().AsQueryable<T>();

                if (orderbyExpression != null)
                   ....
            }
            catch (OpenAccessException oaex)
            {
                throw oaex;
            }
            finally
            {
                if (object.ReferenceEquals(qr, null))
                    qr = new List<T>().AsQueryable<T>();
            }

            return qr;
        }

so, I tried to substitute the code above with this

            qr = from row in _scope.Extent<T>()
                    where whereExpression.Compile().Invoke(row)
                    select row;

but I receive the runtime error saying that Server-side queries aren't supported.

Can You help me more?

Alessio Bulleri

[Zoran]

Hello Alessio Bulleri,

Using the Compile call in LINQ frameworks that translate to SQL is not possible as the Compile call is translating the expression tree into MSIL code and it is therefore not readable for further processing.

You should be able to use the code without the call of Compile. I would like to ask you to share us the query where you got the second exception without the call of Compile. The reason for such exception is that maybe you made the call passing some parameters that can not be translated to SQL or a possible bug in our framework so we would be very grateful if you could continue your collaboration in that direction.

All the best,
Zoran
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Alessio Bulleri]

Hello .... excuse-me for my silence!

Following is the code used from VB.Net project :

        Dim predicate = PredicateBuilder.False(Of CustomDAO.Customers)()
        predicate = predicate.Or(Function(f As CustomDAO.Customers) f.ID = 257547)

        ...list = _customersFactory.GetFiltered(predicate, Nothing)
.....

Please note the usage of Albahari Predicate Builder

I hope You'll find a solution otherwise I'll change all my generic layer!

Thanks,

Alessio Bulleri

[Zoran]

Hi Alessio Bulleri,

Unfortunately, at the moment OpenAccess does not process the invocation expressions generated by the PredicateBuilder class. We put this on our to-do list and we will make sure that it works in the near future. Q1 2010 release in the beginning of March is the soonest this functionality could be delivered.

Regards,
Zoran
the Telerik team

Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Follow the status of features or bugs in PITS and vote for them to affect their priority.

[Alessio Bulleri]

Hi....I downloaded OpenAccess 2010 Q1 as You said in Your last message, but I'm having the same problem. Can You help me, telling me when server side queries will be supported or how handle expression tree predicate in LINQ queries?

thanks ..... and good job!

Alessio Bulleri

[Zoran]

Hello Alessio Bulleri,

 Our Linq support is going under major improvements at the moment and we hope to deliver the new support in about a month, two months the latest. Please excuse us for the delay of this functionality as there were really a lot of tasks with greater priority for the Q1 release so we did not make it for the predicates. 

All the best,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Mitch Wagers]

Still doesn't work for me, with Q3 2010.

[Zoran]

Hello Mitch,

 Building dynamic expressions with OpenAccess is possible but the workflow is slightly different than using the Albahari PredicateBuilder for example. I suggest you to have a look at the following blog post to see how you can build expressions and pass them as argument to the Where clause of a Linq query executed by OpenAccess.

Best wishes,
Zoran
the Telerik team

[Jeff]

I'm using the 2011.1.411.2 version of OpenAccess using the Domain Model context method.  When I try to create my filter expression as explained here, http://blogs.telerik.com/openaccessteam/posts/10-04-16/dynamic_filter_expressions_in_an_openaccess_linq_query.aspx and I receive the error below.  Is it possible when not using scope.extent?


Error 15 'System.Linq.IQueryable<Verify.Model30.InfractionEvent>' does not contain a definition for 'Where' and the best extension method overload 'System.Linq.Dynamic.DynamicQueryable.Where<T>(System.Linq.IQueryable<T>, string, params object[])' has some invalid arguments D:\Projects\Verify2010\Verify\Verify.Model.BLL\Repository.cs 19 40 Verify.Model.BLL

[Zoran]

Hi Jeff,

 I just tested the code on my side and had no problems achieving what was explained in the blog post. I guess the problem on your side could be of another nature - maybe your implementation is not 100% like the one in the blog. Please try copy/paste-ing the following code in a console application which contains domain model where the Northwind database has been mapped. It worked perfectly well on my side I must admit.

    static void Main(string[] args)
    {
        Expression<Func<Customer, bool>> filter1 = c => c.City.StartsWith("S");
        Expression<Func<Customer, bool>> filter2 = c => c.City.StartsWith("M");
        Expression<Func<Customer, bool>> filter3 = c => c.ContactTitle == "Owner";

        Expression<Func<Customer, bool>>[] filterExpressions = new Expression<Func<Customer, bool>>[] { filter1, filter2, filter3 };
        Func<Expression, Expression, BinaryExpression>[] operators = new Func<Expression, Expression, BinaryExpression>[] { Expression.OrElse, Expression.AndAlso };
        Expression<Func<Customer, bool>> filter = CombinePredicates<Customer>(filterExpressions, operators);

        EntitiesModel objectScope = new EntitiesModel();
        IList<Customer> query = objectScope.Customers.Where(filter).ToList();
    }

    public static Expression<Func<T, bool>> CombinePredicates<T>(IList<Expression<Func<T, bool>>> predicateExpressions,
IList<Func<Expression, Expression, BinaryExpression>> logicalFunctions)
    {
        Expression<Func<T, bool>> filter = null;

        if (predicateExpressions.Count > 0)
        {
            Expression<Func<T, bool>> firstPredicate = predicateExpressions[0];
            Expression body = firstPredicate.Body;
            for (int i = 1; i < predicateExpressions.Count; i++)
            {
                body = logicalFunctions[i - 1](body, predicateExpressions[i].Body);
            }
            filter = Expression.Lambda<Func<T, bool>>(body, firstPredicate.Parameters);
        }

        return filter;
    }

Best wishes,
Zoran
the Telerik team

[Jeff]

I guess part of my confusion is how I can do groupings.  If I was to use the SofiaCarRental20 database as an example and want to emulate the SQL statement below.  How would I use and statements on everything except the make portion of the where clause

SELECT c.*
FROM Cars c
inner join Categories cat on cat.CategoryID = c.CategoryID
inner join RentalRates rr on rr.CategoryID = cat.CategoryID
where
    cat.CategoryName = 'suv'
    and (c.Make = 'Audi' or c.Make = 'Nissan')
    and  rr.Daily >1

[Zoran]

Hi Jeff,

 I guess that your idea is to build your filters dynamically and then use one query to execute them. One of the approaches you can take is the one from the blog. It is working but it is quite hard to implement, especially when you want to build more complex queries as the one you proposed with inner filters. Playing around with expression will eventually allow you to do this but I am not sure that it is worth the investment. You can automate  this code, by using dynamic Linq. By automate I mean that similar code will eventually be executed, but you do not need to worry about it.

Please have a look into this blog post and see if that is the correct approach for your queries. I hope this will ease your task.

Kind regards,
Zoran
the Telerik team

[Andrew]

this link to the blog post does not work, do u have a new link?

[Jan Blessenohl]

Hi Andrew,

The correct link is:
http://blogs.telerik.com/openaccessteam/posts/09-12-28/dynamic-query-with-telerik-openaccess.aspx


Sorry for the inconvenience,
Jan Blessenohl
Telerik