Print Page | Close Window

Doing "generic" queries with no ESQL

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=3123
Printed Date: 13-May-2026 at 3:27pm


Topic: Doing "generic" queries with no ESQL
Posted By: katit
Subject: Doing "generic" queries with no ESQL
Date 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<int?> onSuccess, Action<Exception> 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?


Replies:
Posted By: DenisK
Date 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 - http://drc.ideablade.com/xwiki/bin/view/Documentation/dynamic-queries

Posted By: katit
Date 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

Posted By: DenisK
Date 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();

Posted By: chuckc
Date 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 - http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Posted By: katit
Date 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?
 
 

Posted By: katit
Date 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);
            var predicates = new List<PredicateDescription>();
              
            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;
                }
            }
            ITypedEntityQuery query = baseQuery;
            if (predicates.Count > 0)
            {
                var compositePredicateDescription = PredicateBuilder.And(predicates.ToArray());
                query = baseQuery.Where(compositePredicateDescription);
            }
            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..

Posted By: DenisK
Date 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<long>().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.

Posted By: katit
Date 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<PredicateDescription>();
              
            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(

Posted By: DenisK
Date 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.

Posted By: katit
Date 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!

Posted By: katit
Date 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..
 

Posted By: DenisK
Date 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));

or 

customerQuery.AddIncludePaths("Orders");
customerQuery.AddIncludePaths("Shippers");

Posted By: katit
Date 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 :)


Print Page | Close Window