New Posts New Posts RSS Feed: Doing "generic" queries with no ESQL
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Doing "generic" queries with no ESQL
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
katit View Drop Down
Senior Member
Senior Member


Joined: 09-Sep-2011
Posts: 146 		Post Options Post Options   Quote katit Quote  Post ReplyReply Direct Link To This Post Topic: Doing "generic" queries with no ESQL
    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?


Edited by katit - 05-Dec-2011 at 7:50am
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post Posted: 06-Dec-2011 at 2:33pm
Hi katit;

Sounds like our dynamic LINQ support is your answer. Have you tried it?
Back to Top
katit View Drop Down
Senior Member
Senior Member


Joined: 09-Sep-2011
Posts: 146 		Post Options Post Options   Quote katit Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
chuckc View Drop Down
Groupie
Groupie


Joined: 27-Feb-2010
Posts: 54 		Post Options Post Options   Quote chuckc Quote  Post ReplyReply Direct Link To This Post Posted: 07-Dec-2011 at 7:40pm
You might want to also check out Dynamic LINQ.
Back to Top
katit View Drop Down
Senior Member
Senior Member


Joined: 09-Sep-2011
Posts: 146 		Post Options Post Options   Quote katit Quote  Post ReplyReply Direct Link To This Post 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?
 
 
Back to Top
katit View Drop Down
Senior Member
Senior Member


Joined: 09-Sep-2011
Posts: 146 		Post Options Post Options   Quote katit Quote  Post ReplyReply Direct Link To This Post 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..
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
katit View Drop Down
Senior Member
Senior Member


Joined: 09-Sep-2011
Posts: 146 		Post Options Post Options   Quote katit Quote  Post ReplyReply Direct Link To This Post 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(


Edited by katit - 09-Jan-2012 at 6:36pm
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
katit View Drop Down
Senior Member
Senior Member


Joined: 09-Sep-2011
Posts: 146 		Post Options Post Options   Quote katit Quote  Post ReplyReply Direct Link To This Post 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!
Back to Top
katit View Drop Down
Senior Member
Senior Member


Joined: 09-Sep-2011
Posts: 146 		Post Options Post Options   Quote katit Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
DenisK View Drop Down
IdeaBlade
IdeaBlade


Joined: 25-Aug-2010
Posts: 715 		Post Options Post Options   Quote DenisK Quote  Post ReplyReply Direct Link To This Post 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");
Back to Top
katit View Drop Down
Senior Member
Senior Member


Joined: 09-Sep-2011
Posts: 146 		Post Options Post Options   Quote katit Quote  Post ReplyReply Direct Link To This Post 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 :)
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down