DevForce Community Forum : Doing "generic" queries with no ESQL

Doing "generic" queries with no ESQL : Yes, thanks! I just figured...

Thu, 12 Jan 2012 12:50:32 -700

Author: katit
Subject: 3123
Posted: 12-Jan-2012 at 12:50pm

Yes, thanks! I just figured it 5 minutes ago. The more I deal with DevForce the more I like it. Let's see how it goes in Production :)

Doing "generic" queries with no ESQL : Is this what you're looking...

Thu, 12 Jan 2012 12:38:09 -700

Author: DenisK
Subject: 3123
Posted: 12-Jan-2012 at 12:38pm

Is this what you're looking for?

customerQuery.AddIncludePaths(Customer.PathFor(c => c.Orders));

customerQuery.AddIncludePaths(Customer.PathFor(c => c.Shippers));

customerQuery.AddIncludePaths("Orders");

customerQuery.AddIncludePaths("Shippers");

Doing "generic" queries with no ESQL : Didn't want to open...

Thu, 12 Jan 2012 08:52:03 -700

Author: katit
Subject: 3123
Posted: 12-Jan-2012 at 8:52am

Didn't want to open another topic.

1. Is it possible to dynamically add "Include" statements?

Something like (pseudocode)

var customerQuery;

if (needOrders) customerQuery.Include("AA");

if (needHistory) customerQuery.Include("BB");

customerQuery.execute..

Edited by katit - 12-Jan-2012 at 10:28am

Doing "generic" queries with no ESQL : I'm doing it like this...

Tue, 10 Jan 2012 14:11:24 -700

Author: katit
Subject: 3123
Posted: 10-Jan-2012 at 2:11pm

I'm doing it like this just because I wanted to build abstraction layer between DevForce and my controls.

Suggestion with selector also worked great.

Thanks!

Doing "generic" queries with no ESQL : Ah I see. In that case, my suggestion...

Tue, 10 Jan 2012 14:05:56 -700

Author: DenisK
Subject: 3123
Posted: 10-Jan-2012 at 2:05pm

Ah I see. In that case, my suggestion would be to refactor the switch statement by putting another property on the FilterRow type that essentially maps FilterChoice to FilterOperator.

For example,

public class FilterRow {
  
  public FilterChoice FilterAs { get; set; }
  
  public FilterOperator {
    get {
       return GetFilterOperatorByFilterChoice(this.FilterAs);
    }
  }
}

//This can be a static or utility method.
private FilterOperator GetFilterOperatorByFilterChoice(FilterChoice filterAs) {
  switch (filterAs) {
     case FilterChoice.EqualsTo:
       return FilterOperator.IsEqualTo;
     
     case FilterChoice.NotEqualsTo:
       return FilterOperator.IsNotEqualTo;

     etc.........
  }
}

Then when you build your predicate, you can simply do,

foreach (var filterBoxRow in filterRows.Where(x => !x.FilterAs.Equals(FilterChoice.All)))
{
    predicates.Add(PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, filterBoxRow.FilterOperator, filterBoxRow.FilterFrom));
}

This is the only suggestion that I can think of. Hope it can get you started.

Doing "generic" queries with no ESQL : Originally posted by DenisKHi...

Mon, 09 Jan 2012 18:31:29 -700

Author: katit
Subject: 3123
Posted: 09-Jan-2012 at 6:31pm

Originally posted by DenisK

Hi katit;

If I understand correctly, it looks like the ForEach loop can be reduced to the following:

Actually I do need loop(I thought one item in switch statement illustrated what I'm doing) also number of items rarely going to be more than 2-3 and 10-15 max (I doubt it). I'm not building IN-clauses

My code looks like:

var baseQuery = EntityQuery.Create(typeof(T), this.entityManager);

            var predicates = new List();

            foreach (var filterBoxRow in filterRows.Where(x => !x.FilterAs.Equals(FilterChoice.All)))
            {
                switch (filterBoxRow.FilterAs)
                {
                    case FilterChoice.EqualsTo:
                        predicates.Add(PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, FilterOperator.IsEqualTo, filterBoxRow.FilterFrom));
                        break;

                    case FilterChoice.NotEqualsTo:
                        predicates.Add(PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, FilterOperator.IsNotEqualTo, filterBoxRow.FilterFrom));
                        break;

                    case FilterChoice.BeginsWith:
                        predicates.Add(PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, FilterOperator.StartsWith, filterBoxRow.FilterFrom));
                        break;

                    case FilterChoice.EndsWith:
                        predicates.Add(PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, FilterOperator.EndsWith, filterBoxRow.FilterFrom));
                        break;

                    case FilterChoice.GreaterThan:
                    case FilterChoice.After:
                        predicates.Add(PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, FilterOperator.IsGreaterThan, filterBoxRow.FilterFrom));
                        break;

                    case FilterChoice.LessThan:
                    case FilterChoice.Before:
                        predicates.Add(PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, FilterOperator.IsLessThan, filterBoxRow.FilterFrom));
                        break;

                    case FilterChoice.Between:
                        predicates.Add(PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, FilterOperator.IsGreaterThanOrEqualTo, filterBoxRow.FilterFrom));
                        predicates.Add(PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, FilterOperator.IsLessThanOrEqualTo, filterBoxRow.FilterTo));
                        break;
                }
            }

            ITypedEntityQuery query = baseQuery;
            if (predicates.Count > 0)
            {
                var compositePredicateDescription = PredicateBuilder.And(predicates.ToArray());
                query = baseQuery.Where(compositePredicateDescription);
            }

query.ExecuteAsync(

Edited by katit - 09-Jan-2012 at 6:36pm

Doing "generic" queries with no ESQL : Hi katit; SELECT CustomerKey...

Mon, 09 Jan 2012 18:21:39 -700

Author: DenisK
Subject: 3123
Posted: 09-Jan-2012 at 6:21pm

Hi katit;

SELECT CustomerKey FROM TableCustomer WHERE CustomerId = @myParameter

You can write the above query dynamically using a ProjectionSelector and a PredicateDescription. For example,

      var ids = _em1.Customers.Where(c => c.Id == custKey).Select(c => c.Id).ToList();
      Assert.IsTrue(ids.Count == 1);
      Assert.IsTrue(ids.First() == custKey);

      _em1.Clear();
      var selector = new ProjectionSelector(Customer.EntityPropertyNames.Id);
      var wherePredicate = PredicateBuilder.Make(Customer.EntityPropertyNames.Id, FilterOperator.IsEqualTo, custKey);
      var rootQuery = EntityQuery.Create(typeof(Customer), _em1);      
      var finalQuery = rootQuery.Where(wherePredicate).Select(selector);

      var ids2 = finalQuery.Execute().Cast().ToList();
      Assert.IsTrue(ids2.Count == 1);
      Assert.IsTrue(ids2.First() == custKey);

Re: your second question,

If I understand correctly, it looks like the ForEach loop can be reduced to the following:

var filterBoxRow = filterRows.Where(x => x.FilterAs.Equals(FilterChoice.EqualsTo)
predicates.Add(PredicateBuilder.Make(typeof(T), filterBoxRow.FieldName, FilterOperator.IsEqualTo, filterBoxRow.FilterFrom));

since you only want to build the predicates when filterBoxRow.FilterAs is FilterChoice.EqualsTo.

Looking at the whole query though, since you're building a list of predicates for equality comparison, you may get a StackOverflowException if your list of predicates gets too big.

I wonder if instead you can build a list of values and use FilterOperator.InList. For example,

_em1.Customers.Where(c => ids.Contains(c.Id));

//The dynamic query equivalent of the above
var inListPredicate = PredicateBuilder.Make(typeof(Customer), "Id", FilterOperator.InList, ids);      
rootQuery.Where(inListPredicate);

Hope this helps.

Doing "generic" queries with no ESQL : Another question...I implemented...

Mon, 09 Jan 2012 15:49:03 -700

Author: katit
Subject: 3123
Posted: 09-Jan-2012 at 3:49pm

Another question... I implemented solution with PredicateBuilder. Here is how it looks:

var baseQuery = EntityQuery.Create(typeof(T), this.entityManager);

query.ExecuteAsync(

I have filterRows array with list of my own descriptors for predicates. So, now I need to build predicates.

I wonder if my code looks reasanoble since I had to:

1. Add new List where I store PredicateDescriptions

2. cast baseQuery to ITypedEntityQuery

3. if predicates there - use PredicateBuilder and "join" collected predicates.

I'm just not sure if I can write it more compact and easy to look at..

Doing "generic" queries with no ESQL : Originally posted by DenisK I...

Mon, 09 Jan 2012 12:31:24 -700

Author: katit
Subject: 3123
Posted: 09-Jan-2012 at 12:31pm

Originally posted by DenisK

I see. Will this work for you?

var entityType = typeof(Entity);
var baseQuery = EntityQuery.CreateQuery(entityType , anEntityManager);
PredicateDescription p1 = PredicateBuilder.Make(entityType, "IdColumnName", FilterOperator.IsEqualTo, id);
var query = baseQuery.Where(p1);
var result = query.Execute();

Thanks! That gives me an idea how I do this. What about scalar query like this?

SELECT CustomerKey FROM TableCustomer WHERE CustomerId = @myParameter

Is it possible to run such query with generics and return only one value? In this case I want not just run scalar query but also specify "CustomerKey" as field I need to select.

From documentation I see that Scalar operations support .First and various aggregates. In my case I'm bringing whole object but it might be more efficient to just query one column. Can I achieve this with LINQ and DevForce?

Doing "generic" queries with no ESQL : You might want to also check out...

Wed, 07 Dec 2011 19:40:00 -700

Author: chuckc
Subject: 3123
Posted: 07-Dec-2011 at 7:40pm

You might want to also check out Dynamic LINQ.

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Doing "generic" queries with no ESQL : I see. Will this work for you? var...

Tue, 06 Dec 2011 19:10:13 -700

Author: DenisK
Subject: 3123
Posted: 06-Dec-2011 at 7:10pm

I see. Will this work for you?

var entityType = typeof(Entity);
var baseQuery = EntityQuery.CreateQuery(entityType , anEntityManager);
PredicateDescription p1 = PredicateBuilder.Make(entityType, "IdColumnName", FilterOperator.IsEqualTo, id);
var query = baseQuery.Where(p1);
var result = query.Execute();

Edited by DenisK - 06-Dec-2011 at 7:10pm

Doing "generic" queries with no ESQL : I looked at it, I'm just...

Tue, 06 Dec 2011 14:58:15 -700

Author: katit
Subject: 3123
Posted: 06-Dec-2011 at 2:58pm

I looked at it, I'm just not sure how to produce code equal to my E-SQL code

Doing "generic" queries with no ESQL : Hi katit;Sounds like our dynamic...

Tue, 06 Dec 2011 14:33:14 -700

Author: DenisK
Subject: 3123
Posted: 06-Dec-2011 at 2:33pm

Hi katit;

Sounds like our dynamic LINQ support is your answer. Have you tried it?

http://drc.ideablade.com/xwiki/bin/view/Documentation/dynamic-queries

Doing "generic" queries with no ESQL : I wonder if something like...

Mon, 05 Dec 2011 07:18:56 -700

Author: katit
Subject: 3123
Posted: 05-Dec-2011 at 7:18am

I wonder if something like this even possible. I'm writing bunch of repository classes and see this repeating pattern where generic class should solve my problem. Consider this example:

public void GetKeyAsync(string id, Action onSuccess, Action onError)
        {
            // TODO: SQL INjection!!
            // TODO: Scalar lookup
            // TODO: Use hard types, no eSQL
            var esqlString = "SELECT VALUE e FROM " + typeof(T).Name + "s AS e";
            esqlString += " WHERE e." + this.IdColumnName + "='" + id + "'";

In this example I know type(T) of my entity. And I know column I need to filter by. How do I write LINQ query in this case?

If I have to stay with ESQL, is there any way to do Scalar query on ESQL query?

Edited by katit - 05-Dec-2011 at 7:50am