New Posts New Posts RSS Feed: Filtering query with EntityKeyList
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Filtering query with EntityKeyList
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
kpinniger View Drop Down
Newbie
Newbie
Avatar

Joined: 10-Mar-2010
Location: Atlanta, GA USA
Posts: 7 		Post Options Post Options   Quote kpinniger Quote  Post ReplyReply Direct Link To This Post Topic: Filtering query with EntityKeyList
    Posted: 29-Dec-2011 at 1:00pm
Our legacy database has multi part keys.  So our Customer tables primary key is CompanyCode, Division, CustomerNumber.
 
We would like to have each users query results filtered to use their "Active Customers"  so basically something like this...
 
EntityKey one = new EntityKey(typeof(Customer), new [] {"1", "US", "A100"});
EntityKey two = new EntityKey(typeof(Customer), new [] {"1", "US", "C100"});
EntityKey three = new EntityKey(typeof(Customer), new[] { "1", "WW", "I-A100" });
EntityKey four = new EntityKey(typeof(Customer), new[] { "1", "WW", "I-D100" });
EntityKeyList myCustomerList = new EntityKeyList(typeof(Customer), new[] { one, two, three, four });
 
So then when I am querying against another table like ArTransactions that has CompanyCode, Division, and CustomerNumber in the table I would like to limit the results of select all ArTransactions to this list of Customers above.
 
Ideally I would like to do something like this in my repository
 
            return from art in myEntityManager.ArTransactions
                   where myCustomerList.Contains(new { CompanyCode = art.CompanyCode, Division = art.Division, CustomerNumber = art.CustomerNumber })
                             select art;
 
or even something like this
 
            var entityQuery = myCustomerList.ToQuery();
            var myActiveCustomers = myEntityManager.ExecuteQuery(entityQuery).Cast<Customer>();
            return from art in myEntityManager.ArTransactions
                   join actCust in myActiveCustomers
                           on new { art.CompanyCode, art.Division, art.CustomerNumber } equals
                              new { actCust.CompanyCode, actCust.Division, actCust.CustomerNumber }
                   select art;
 
Anyone have any ideas on how to do this?
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 29-Dec-2011 at 5:12pm
That's a tough one.  Since the "contains" is essentially an OR of the many clauses, you could use the PredicateBuilder to build up the predicate.  It might be a bit ugly, potentially slow, and if the list is large also a potential StackOverflowException waiting to happen, but it would look something like this:
 
var predicate = PredicateBuilder.False<ArTransaction>();
foreach (var key in myCustomerList) {
   var code = (int)key.Values[0];
   var division = (string)key.Values[1];
   var custno = (string)key.Values[2];
  
   predicate = predicate.Or(art => art.CompanyCode == code && art.Division == division && art.CustomerNumber == custno);
 }
var query = myEntityManager.ArTransactions.Where(predicate);
 
If possible, doing this filtering within a server-side EntityServerQueryInterceptor would be the best location.
 
However, I'd also possibly re-think this.  Is it possible to query for the Active Customers and use Includes to retrieve their transactions?  Or lazily load the transactions for a given Customer when needed? 
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down