We are using: LLBL Gen Pro version 4.2 SD.LLBLGen.Pro.ORMSupportClasses.dll version 4.2.15.1216 SqlServer versions 2008 - 2019

We are using SQL Server views remapped in LLBL to PocoWithLinqQuery objects. For backward compatibility with previous code, we need to adapt methods that dynamically add certain conditions to the query.

In the past, without Linq, using IPredicateExpressions there was no problem composing the filter dynamically. Introducing Linq, on the other hand, it's not clear to me how to achieve this result.

Thank you in advance

        public void AddFilterConsuntiviOdpDati(ref IQueryable<OdpConsuntiviOdpDatiPOCORow> query,
                                                    List<Tuple<int,int>> listMasterDetailFilter)
        {
            try
            {
                IPredicateExpression principalPredicate = new PredicateExpression();

                foreach (Tuple<int, int> masterDetailFilter in listMasterDetailFilter)
                {
                    IPredicateExpression secondaryPredicate = new PredicateExpression();

                    if (masterDetailFilter.Item2 == -1)
                    {
                        secondaryPredicate.Add(orm.HelperClasses.OdpConsuntiviOdpDatiPOCOFields.IdStabilimento == masterDetailFilter.Item1);
                    }
                    else
                    {
                        secondaryPredicate.Add(orm.HelperClasses.OdpConsuntiviOdpDatiPOCOFields.IdStabilimento == masterDetailFilter.Item1);
                        secondaryPredicate.Add(orm.HelperClasses.OdpConsuntiviOdpDatiPOCOFields.IdReparto == masterDetailFilter.Item2);
                    }

                    principalPredicate.AddWithOr(secondaryPredicate);
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

Just create another linq query to process the original one as its datasource and append filters to it.

Thanks for the reply.

How do you dynamically add where in or conditions on LLBL with LINQ? On LINQ they suggest to use Union or similar methods, but these methods give error on LLBL.

I specify that the input query can already have a series of AND conditions. It is therefore necessary to dynamically add a set of conditions in AND to those that already exist, but which are in OR with each other within the set.

something like this? (not live tested, but you get the idea)

public List<CustomerEntity> GetCustomers(string country, string city)
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        IQueryable<CustomerEntity> q = from c in metaData.Customer 
                                       where c.Country==country
                                       select c;
        if(!string.IsNullOrEmpty(city))
        {
            q = q.Where(c=>c.City==city);
        }
        return q.ToList();
    }
}

Union queries are combined sets. you want to filter an existing set on another predicate. You can do that by e.g. wrapping the query you had with another query which filters the initial set. Union queries in linq are supported in v5.

Hi Otis, thanks for the feedback.

My problem is, taking your example, I would need to add a series of conditions in OR that come from a list of combinations.

I am attaching an update of your code also containing an example on the SQL side of what we would need to achieve.

Many thanks in advance.

        public List<CustomerEntity> GetCustomers(string country, List<Tuple<int, int>> listMasterDetailFilter)
        {
            using (var adapter = new DataAccessAdapter())
            {
                var metaData = new LinqMetaData(adapter);
                IQueryable<CustomerEntity> q = from c in metaData.Customer
                                               where c.Country == country
                                               select c;

                /* How can the following conditions be added in AND with respect to the previous condition "c.Country == country"? */
                foreach (Tuple<int, int> masterDetailFilter in listMasterDetailFilter)
                {
                    if (masterDetailFilter.Item2 == -1)
                    {
                        /* How can this condition be added in OR with respect to the previous ones? */
                        q = q.Where(c => c.IdStabilimento == masterDetailFilter.Item1);
                    }
                    else
                    {
                        /* How can this condition be added in OR with respect to the previous ones? */
                        q = q.Where(c => c.IdStabilimento == masterDetailFilter.Item1
                                         && c.IdReparto == masterDetailFilter.Item2);
                    }
                }

                /*  SELECT *
                 *  FROM Customer
                 *  WHERE Country = "England"
                 *        AND
                 *        (
                 *          (IdStabilimento = 1)
                 *          OR
                 *          (
                 *              IdStabilimento = 2
                 *              AND IDReparto = 1
                 *          )
                 *          OR
                 *          (
                 *              IdStabilimento = 2
                 *              AND IDReparto = 3
                 *          )
                 *          OR
                 *          (
                 *              ... listMasterDetailFilter
                 *          )
                 *        )
                 *  
                 */

                return q.ToList();
            }
        }

Good morning,

we solved the problem.

The solution is to use the System.Linq.Expressions.Expressions, concatenating them with the System.Linq.Expressions.Expression.AndAlso() and System.Linq.Expressions.Expression.Expression.OrElse() methods.

Thanks so much for the support