I have middle tier functions that I've created that take in a query object and any of say 5 fields could have a value that needs to be used in a sql query to the db.

In version 2.5, I would use something such as this.

EntityCollection<Customers> customers = new EntityCollection<Customers>();
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
    
           if(query.CustomerIds.Count > 0)
           {
                bucket.PredicateExpression.Add(CustomerFields.CustomerId ==  query.CustomerIds.ToArray()); 
            }

            if(query.OrderIds.Count > 0)
            {
                bucket.PredicateExpression.Add(OrderFields.OrderId == query.OrderIds.ToArray();
             }

             bucket.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);



            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedView(customers.GetFieldsInfo(), customers, bucket, false);
            }

That example is short and sweet and going off Northwind, but the end points have a lot more conditions.

Could somebody please post how you would write equivalent code using Linq and preferrably with Lambda expressions.

Thanks.

Please check this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13954

Walaa wrote:

Please check this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13954

I have and it doesn't seem to cover the exact scenario I listed code for. The thread is quite long and goes back and forth. Plus where are they using list.ToArray anywhere in the predictes?

Is what I'm asking not possible. If so is it too much trouble to take what I have and display a nice short concise example utilizing Linq and Lamdba expression in your product?

Would be nice to do here before this thread gets long and convoluted like the other so it can act as a legitimate reference for others. Also, I can get back to evaluating this project versus getting diverted to other things and trying to figure out something that should be in documentation.

Thanks

Linq as a language construct isn't really suitable for flexible predicate construction. There are a couple of Linq to sql predicate builders out there, but all of them are in-memory postprocessing filters. Not what you want.

The main problem is that you can append .Where(predicateFunc) calls to a Linq query, and it will likely work, but if the predicateFunc lambda isn't known at compile time (e.g. the field to filter on isn't known), it won't be possible. That's not something we can change, as it's a downside of Linq.

In those situations, we recommend to use our native query api, as it's designed for flexible predicate building without limits. As our Linq provider is build on top of the native query api, you don't have to be worried it will go away, our native query api is here to stay, for Linq but above all to serve as a safety net for developers to write the query they need if Linq (the language construct) doesn't offer the flexibility they need.

Is what I'm asking not possible. If so is it too much trouble to take what I have and display a nice short concise example utilizing Linq and Lamdba expression in your product?

We ship over 500 Linq queries in C# code as examples with LLBLGen Pro, have you had a look at them? They're in the 'sourcecode' folder. Linq is a language construct so the queries you'll write with Linq to Sql (except of course query constructs which are unique for LLBLGen Pro, like prefetch paths in linq) for example will look very much the same as with Linq to LLBLGen Pro:

var q = from c in metaData.Customer where c.Country=="Germany" orderby c.City ascending select c;

vs.

var q = from c in ctx.Customer where c.Country=="Germany" orderby c.City ascending select c;

the difference is the source of the query, i.e. the IQueryable<T>, with LLBLGen Pro it's the LinqMetaData class, with Linq to Sql it's the context class. Please have a look at our query examples in sourcecode.

If I may, your code:

EntityCollection<Customers> customers = new EntityCollection<Customers>();
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
    
         if(query.CustomerIds.Count > 0)
         {
                bucket.PredicateExpression.Add(CustomerFields.CustomerId ==  query.CustomerIds.ToArray());
            }

            if(query.OrderIds.Count > 0)
            {
                bucket.PredicateExpression.Add(OrderFields.OrderId == query.OrderIds.ToArray();
             }

             bucket.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);



            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedView(customers.GetFieldsInfo(), customers, bucket, false);
            }

can be written as linq as: (please forgive me if the code doesn't compile, I guessed a few things) Also, typedviews aren't supported in linq, you've to project to an anonymous type if you want that (or to a custom type)

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);
    var q = from c in metaData.Customer
            select c;
    
    if(query.CustomerIds.Count>0)
    {
        q = q.Where(c=>query.CustomerIds.ToArray().Contains(c.CustomerId));
    }
    if(query.OrderIds.Count>0)
    {
        q = q.Where(c=>c.Orders.Contains(from o in metaData.Orders where query.OrderIds.Contains(o.OrderId) select o));
    }

    // execute query q here. This code is omitted, you've to foreach it or call ToList for example.
}

Be sure you use the latest runtime build.

I've been able to pass expressions in quite successfully to the business layer and then return the appropriate entities:

EntityCollection<T> GetEntitytCollection<T>(Expression<Func<T, bool>> expr) where T : IEntity2, EntityBase2

(This is off the top of my head). Ive then passed in things like:

var data = service.GetEntityCollection<PersonEntity>(p => p.Created <= DateTime.Now);

So far it seems to work...

Hello Frans,

Thank you for the response.

using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                List<string> CustomerIds = new List<string>();
                List<int> OrderIds = new List<int>();

                LinqMetaData metaData = new LinqMetaData(adapter);

                var q = from c in metaData.Customers
                        select c;

                if (CustomerIds.Count > 0)
                {
                    q = q.Where(c => CustomerIds.ToArray().Contains(c.CustomerId));
                }

                if (OrderIds.Count > 0)
                {
                    q = q.Where(c => c.Orders.Contains(from o in metaData.Orders where OrderIds.ToArray().Contains(o.OrderId) select o)); <-- This generates a compiler error
Error   2   Argument '1': cannot convert from 'System.Linq.IQueryable<NW.DAL.EntityClasses.OrdersEntity>' to 'NW.DAL.EntityClasses.OrdersEntity'    C:\TFS\TFS-02\Projex\Trunk\ADONW\LLBLNW\Program.cs  35  82  LLBLNW

                }
            }

I build quite of few of my bll endpoints as generic and utilize dynamic. Right now I've been evaluating Linq to EF, Linq to Sql and now LLBL which I used at a previous job.

Linq to SQL and the lack of "eager loading" multi levels deep is a show stopper for me. Linq to EF doesn't appear to have a good way to build dynamic queries without reflection and even then not sure when you may need to set a predicate against a table 7 levels deep in a relation.

I guess for now, I'll evaluat LLBL as a mix of both. Use Linq on simple queries I don't need to build dynamic. However, if you do figure out a way to get this to work, I'd be interested seeing a working example posted as the first product that allows us to standardize on something like linq and gives us the other tangibles such as building dynamic queries, eager loading will be what we ultimate end up with.

Thanks

frosty wrote:

Hello Frans,

Thank you for the response.

using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                List<string> CustomerIds = new List<string>();
                List<int> OrderIds = new List<int>();

                LinqMetaData metaData = new LinqMetaData(adapter);

                var q = from c in metaData.Customers
                        select c;

                if (CustomerIds.Count > 0)
                {
                    q = q.Where(c => CustomerIds.ToArray().Contains(c.CustomerId));
                }

                if (OrderIds.Count > 0)
                {
                    q = q.Where(c => c.Orders.Contains(from o in metaData.Orders where OrderIds.ToArray().Contains(o.OrderId) select o)); <-- This generates a compiler error
Error   2   Argument '1': cannot convert from 'System.Linq.IQueryable<NW.DAL.EntityClasses.OrdersEntity>' to 'NW.DAL.EntityClasses.OrdersEntity'    C:\TFS\TFS-02\Projex\Trunk\ADONW\LLBLNW\Program.cs  35  82  LLBLNW

                }
            }

I build quite of few of my bll endpoints as generic and utilize dynamic. Right now I've been evaluating Linq to EF, Linq to Sql and now LLBL which I used at a previous job.

Linq to SQL and the lack of "eager loading" multi levels deep is a show stopper for me. Linq to EF doesn't appear to have a good way to build dynamic queries without reflection and even then not sure when you may need to set a predicate against a table 7 levels deep in a relation.

I guess for now, I'll evaluat LLBL as a mix of both. Use Linq on simple queries I don't need to build dynamic. However, if you do figure out a way to get this to work, I'd be interested seeing a working example posted as the first product that allows us to standardize on something like linq and gives us the other tangibles such as building dynamic queries, eager loading will be what we ultimate end up with.

Thanks

Dynamic queries with linq in general is a bit of a problem due to linq's design, you can go as far as adding Where clauses to an existing queryable but that's about it. So there's not a lot we can do to that, it's rooted in Linq's design unfortunately.

Your error: q = q.Where(c => c.Orders.Contains(from o in metaData.Orders where OrderIds.ToArray().Contains(o.OrderId) select o)); is caused by the fact that the query you pass to Contains is a set, not a single entity . You should rewrite this to: q = q.Where(c => c.Orders.Contains((from o in metaData.Orders where OrderIds.ToArray().Contains(o.OrderId) select o)).First());

I think that will make it compile.