New Posts New Posts RSS Feed: Dynamically building Predicate description
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Dynamically building Predicate description
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
BillG View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 05-Dec-2007
Location: Monroe, MI
Posts: 233 		Post Options Post Options   Quote BillG Quote  Post ReplyReply Direct Link To This Post Topic: Dynamically building Predicate description
    Posted: 30-Jul-2010 at 3:38pm

I have a search form that has 5 fields on it; ssn, cardno,employeeno, firstname, lastname. The user could fill in anyone of the fields and click find to find the member.

 
I am collecting the filled-in fields with a Query Object and passing it to my FetchMembers() method in my viewmodel.
 
Here is the code so far.
 

PredicateDescription p1 = null;

PredicateDescription p2 = null;

PredicateDescription p3 = null;

PredicateDescription p4 = null;

PredicateDescription p5 = null;

if(q.SSN != string.Empty)

p1 = PredicateBuilder.Make(typeof(Member), "SocSecNo", FilterOperator.IsEqualTo, q.SSN);

if(q.LastName != string.Empty)

p2 = PredicateBuilder.Make(typeof(Member), "LastName", FilterOperator.StartsWith, q.LastName);

if(q.FirstName != string.Empty)

p3 = PredicateBuilder.Make(typeof(Member), "FirstName", FilterOperator.StartsWith, q.FirstName);

if(q.EmployeeNo != string.Empty)

p4 = PredicateBuilder.Make(typeof(Member), "EmployeeNo", FilterOperator.IsEqualTo, q.EmployeeNo);

if(q.CardNo != 0)

p5 = PredicateBuilder.Make(typeof(Member), "CardNo", FilterOperator.IsEqualTo, q.CardNo);

var query = PredicateBuilder.FilterQuery(entityManager.Members, p1.And(p2).And(p3).And(p4).And(p5));

but not all the PredicateDescription objects might have values. How do I dynamically combine the p1, p2, p3 only if they are not null.
 
Bill
 
Back to Top
GregD View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 374 		Post Options Post Options   Quote GregD Quote  Post ReplyReply Direct Link To This Post Posted: 02-Aug-2010 at 11:52am
There is a static overload on PredicateBuilder 

public static Expression<Func<T, Boolean>> And<T>(params Expression<Func<T, Boolean>>[] expressions) {


               that allows an arbitrary array of Expression<…> to be anded together. So code would look something like… 

List<Expression<Func<Member, bool>> expressions = new List<Func<Member, bool>>();

if(q.SSN != string.Empty)

expressions.Add(PredicateBuilder.Make(typeof(Member), "SocSecNo", FilterOperator.IsEqualTo, q.SSN));

if(q.LastName != string.Empty)

expressions.Add(PredicateBuilder.Make(typeof(Member), "LastName", FilterOperator.StartsWith, q.LastName));

if(q.FirstName != string.Empty)

expressions.Add(PredicateBuilder.Make(typeof(Member), "FirstName", FilterOperator.StartsWith, q.FirstName));

var newExpr = PredicateBuilder.And(expressions.ToArray());

var query = PredicateBuilder.FilterQuery(entityManager.Members, newExpr);
Back to Top
BillG View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 05-Dec-2007
Location: Monroe, MI
Posts: 233 		Post Options Post Options   Quote BillG Quote  Post ReplyReply Direct Link To This Post Posted: 03-Aug-2010 at 1:18pm
Here is the new revised method but I am getting a syntax error after the comma in the word Member

public void FetchMembers(MemberQuery q)

{

List<Expression<Func<Member, bool>> expressions = new List<Func<Member, bool>>(); //Error on this line.

if(q.SSN != string.Empty)

expressions.Add(PredicateBuilder.Make(typeof(Member), "SocSecNo", FilterOperator.IsEqualTo, q.SSN));

if(q.CardNo != 0)

expressions.Add(PredicateBuilder.Make(typeof(Member), "CardNo", FilterOperator.IsEqualTo, q.CardNo));

if(q.EmployeeNo != string.Empty)

expressions.Add(PredicateBuilder.Make(typeof(Member), "EmployeeNo", FilterOperator.IsEqualTo, q.EmployeeNo));

if(q.LastName != string.Empty)

expressions.Add(PredicateBuilder.Make(typeof(Member), "LastName", FilterOperator.IsEqualTo, q.LastName));

if(q.FirstName != string.Empty)

expressions.Add(PredicateBuilder.Make(typeof(Member), "FirstName", FilterOperator.IsEqualTo, q.FirstName));

var newExpr = PredicateBuilder.And(expressions.ToArray());

var query = PredicateBuilder.FilterQuery(entityManager.Members, newExpr);

var results = query.Execute<Member>();

results.ForEach(Members.Add);

}
Back to Top
BillG View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 05-Dec-2007
Location: Monroe, MI
Posts: 233 		Post Options Post Options   Quote BillG Quote  Post ReplyReply Direct Link To This Post Posted: 03-Aug-2010 at 1:23pm
Ok I found the problem it was missing a > in the first line but where is the Expression class located?
 
Bill
 
Back to Top
BillG View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 05-Dec-2007
Location: Monroe, MI
Posts: 233 		Post Options Post Options   Quote BillG Quote  Post ReplyReply Direct Link To This Post Posted: 03-Aug-2010 at 1:58pm
Make a little bit more progress, Expression is in System.Linq.Expressions. here is the updated code so far
 

public void FetchMembers(MemberQuery q)

{

List<Expression<Func<Member, bool>>> expressions = new List<Expression<Func<Member, bool>>>();

if(q.SSN != string.Empty)

expressions.Add(PredicateBuilder.Make(typeof(Member), "SocSecNo", FilterOperator.IsEqualTo, q.SSN));

if(q.CardNo != 0)

expressions.Add(PredicateBuilder.Make(typeof(Member), "CardNo", FilterOperator.IsEqualTo, q.CardNo));

if(q.EmployeeNo != string.Empty)

expressions.Add(PredicateBuilder.Make(typeof(Member), "EmployeeNo", FilterOperator.IsEqualTo, q.EmployeeNo));

if(q.LastName != string.Empty)

expressions.Add(PredicateBuilder.Make(typeof(Member), "LastName", FilterOperator.IsEqualTo, q.LastName));

if(q.FirstName != string.Empty)

expressions.Add(PredicateBuilder.Make(typeof(Member), "FirstName", FilterOperator.IsEqualTo, q.FirstName));

var newExpr = PredicateBuilder.And(expressions.ToArray());

var query = PredicateBuilder.FilterQuery(entityManager.Members, newExpr);

var results = query.Execute<Member>();

results.ForEach(Members.Add);

}

I am getting two made errors they prefer to each add line.

Error 1 The best overloaded method match for 'System.Collections.Generic.List<System.Linq.Expressions.Expression<System.Func<DomainModel.Member,bool>>>.Add(System.Linq.Expressions.Expression<System.Func<DomainModel.Member,bool>>)' has some invalid arguments D:\Software Development\VS2010\TicketWare2010\TicketWare2010\Models\MemberViewModel.cs 67 18 TicketWare2010
Error 2 Argument 1: cannot convert from 'IdeaBlade.Linq.PredicateDescription' to 'System.Linq.Expressions.Expression<System.Func<DomainModel.Member,bool>>' D:\Software Development\VS2010\TicketWare2010\TicketWare2010\Models\MemberViewModel.cs 67 34 TicketWare2010
Back to Top
BillG View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 05-Dec-2007
Location: Monroe, MI
Posts: 233 		Post Options Post Options   Quote BillG Quote  Post ReplyReply Direct Link To This Post Posted: 04-Aug-2010 at 3:09pm
Any ideas?
Back to Top
GregD View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 374 		Post Options Post Options   Quote GregD Quote  Post ReplyReply Direct Link To This Post Posted: 24-Aug-2010 at 6:00pm
Bill:

Thanks for backchannelling  the AdventureworksDB-backed sample. That made it a lot easier to understand and address your question. 

Here's a version of your FetchCustomers() method that compiles:

    public void FetchCustomers(CustomerQueryObject q) {
      List<PredicateDescription> predicateDescriptions = new List<PredicateDescription>();

      PredicateDescription aPredicateDescription;
      if (q.CompanyName != string.Empty) {
        aPredicateDescription = PredicateBuilder.Make(typeof(Customer), "CompanyName", FilterOperator.IsEqualTo, q.CompanyName);
        predicateDescriptions.Add(aPredicateDescription);
      }

      if (q.LastName != string.Empty)
        predicateDescriptions.Add(PredicateBuilder.Make(typeof(Customer), "LastName", FilterOperator.IsEqualTo, q.LastName));

      if (q.FirstName != string.Empty)
        predicateDescriptions.Add(PredicateBuilder.Make(typeof(Customer), "FirstName", FilterOperator.IsEqualTo, q.FirstName));

      CompositePredicateDescription aCompositePredicateDescription = PredicateBuilder.And(predicateDescriptions.ToArray());
      IQueryable query = PredicateBuilder.FilterQuery(mgr.Customers, aCompositePredicateDescription);

      IEnumerable results = mgr.ExecuteQuery((IEntityQuery)query);

      foreach (Customer aCustomer in results) {
        Customers.Add(aCustomer);
      }

    }
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down