none
Trying to convert working query to use PredicateBuilder RRS feed

  • Question

  • I have a complex query (shown below) that joins several tables to produce output, and has many user options for filtering:

    IEnumerable<adv_contribution_model> cash =
      from c1 in wapdc.c1s
      join c3 in wapdc.c3s on new { c1.filer_id, c1.election_year } equals new { c3.filer_id, c3.election_year }
      join rcpt in wapdc.rcpts on c3.repno equals rcpt.repno
      join report in wapdc.reports on c3.repno equals report.repno
      join fdesc in wapdc.fdescs on rcpt.code equals fdesc.type
      where report.superseded == null && rcpt.rcpt_date != null && c1.election_year.Equals(electionYear) && fdesc.type.Equals(committeeType) &&
        c1.filer_id.Equals(filer_id)
      select new adv_contribution_model
      {
        contributee = c1.name,
        contributor = rcpt.name,
        date = (DateTime)rcpt.rcpt_date,
        amount = (decimal)rcpt.amount,
        election = "" + rcpt.prim_gen,
        employer = rcpt.employer,
        occupation = rcpt.occup,
        description = "",
        repno = (int)c3.repno,
      };
    

    I want to re-write this query to use PredicateBuilder, since there are as many as 15 different user-selectable options.The problem is that some of the user options refer to fields that are in the tqbles, but not in the resultant output model. How can I create predicates for these when I have no (obvious) reference?

    I would like to do something like:

    string electionYear = "2008";
    string committeeType = "B";
    string filer_id = "GREGC 507";
    string contributee = "SMITH";
    var predicate = PredicateBuilder.True<MvcQuerySystem.Models.Data.domain_models.adv_contribution_model>();
    predicate = predicate.And(p => p.contributee.Contains(contributee));
    IEnumerable<MvcQuerySystem.Models.Data.domain_models.adv_contribution_model> cash =
    	(from c1 in c1s
    	join c3 in c3s on new { c1.filer_id, c1.election_year } equals new { c3.filer_id, c3.election_year }
    	join rcpt in rcpts on c3.repno equals rcpt.repno
    	join report in reports on c3.repno equals report.repno
    	join fdesc in fdescs on rcpt.code equals fdesc.type
    	select new MvcQuerySystem.Models.Data.domain_models.adv_contribution_model
    	{
    		contributee = c1.name,
    		contributor = rcpt.name,
    		date = (DateTime)rcpt.rcpt_date,
    		amount = (decimal)rcpt.amount,
    		election = "" + rcpt.prim_gen,
    		employer = rcpt.employer,
    		occupation = rcpt.occup,
    		description = "",
    		repno = (int)c3.repno,
    	}).OrderByDescending(o => o.amount).Where(predicate);

    The problem is, I have no way to refer to the correct record of the c1s table to compare filer_id or election_year, and the same for the rcpts table to compare its code against the type in the fdescs table.

    Tuesday, May 18, 2010 4:46 PM

Answers

  • AHAH! Never mind, I have found the answer! 

    As so often happens, the answer was in the question. The problem was that some of the items I wanted to check were not in the returned model. The solution: add them to the returned model!!! Now I can generate my predicate, even combining multiple models together, and get LIGHTNING FAST responses. The result:

    string electionYear = "2008";
    string committeeType = "B";
    string filer_id = "GREGC 507";
    string contributee = "SMITH";
    string filer_id = null;
    string filer_name = null;
    try
    {
      if (contributee != null && !contributee.Equals("ALL"))
      {
        filer_id = System.Text.Encoding.ASCII.GetString(Convert.FromBase64String(contributee.Substring(0, contributee.Length - 2)));
        filer_name = repository.getFilerName(filer_id);
        predicate = predicate.And(c => c.contributee.Equals(filer_name));
      }
    }
    catch (Exception)
    {
    }
    if (!string.IsNullOrEmpty(election) && !election.Equals("ALL"))
      predicate = predicate.And(c => c.election.Equals(election));
    if (dateFrom != null)
      predicate = predicate.And(c => c.date.CompareTo(dateFrom) >= 0);
    if (dateThru != null)
      predicate = predicate.And(c => c.date.CompareTo(dateThru) <= 0);
    if (amountFrom != null)
      predicate = predicate.And(c => c.amount.CompareTo(amountFrom) >= 0);
    if (amountThru != null)
      predicate = predicate.And(c => c.amount.CompareTo(amountThru) <= 0);
    if (!string.IsNullOrEmpty(city))
      predicate = predicate.And(c => c.city.Contains(city));
    if (!string.IsNullOrEmpty(state))
      predicate = predicate.And(c => c.state.Contains(state));
    if (!string.IsNullOrEmpty(zip))
      predicate = predicate.And(c => c.zip.Contains(zip));
    if (!string.IsNullOrEmpty(zip))
      predicate = predicate.And(c => c.zip.Contains(zip));
    if (!string.IsNullOrEmpty(contributor))
      predicate = predicate.And(c => c.contributor.Contains(contributor));
    if (!string.IsNullOrEmpty(employer))
      predicate = predicate.And(c => c.employer.Contains(employer));
    if (!string.IsNullOrEmpty(occupation))
      predicate = predicate.And(c => c.occupation.Contains(occupation));
    if (!string.IsNullOrEmpty(description))
      predicate = predicate.And(c => c.description.Contains(description));
    if (!string.IsNullOrEmpty(electionYear) && !electionYear.Equals("ALL"))
      predicate = predicate.And(c => c.electionYear.Contains(electionYear));
    if (!string.IsNullOrEmpty(committeeType) && !committeeType.Equals("IGNORE"))
      predicate = predicate.And(c => c.committeeType.Contains(committeeType));
    IEnumerable<adv_contribution_model> cash =
      (from c1 in wapdc.c1s
      join c3 in wapdc.c3s on new { c1.filer_id, c1.election_year } equals new { c3.filer_id, c3.election_year }
      join rcpt in wapdc.rcpts on c3.repno equals rcpt.repno
      join report in wapdc.reports on c3.repno equals report.repno
      where report.superseded == null && rcpt.rcpt_date != null
      select new adv_contribution_model
      {
        contributee = c1.name,
        contributor = rcpt.name,
        date = (DateTime)rcpt.rcpt_date,
        amount = (decimal)rcpt.amount,
        election = "" + rcpt.prim_gen,
        employer = rcpt.employer,
        occupation = rcpt.occup,
        description = "",
        repno = (int)c3.repno,
        filer_id = c1.filer_id,
        electionYear = c1.election_year,
        city = rcpt.city,
        state = rcpt.state,
        zip = rcpt.zip4,
        committeeType = rcpt.code,
      }).Where(predicate);
    
    Wednesday, May 19, 2010 3:35 PM