Build dynamic LINQ?

Question

I'm using LINQ to query data. Consider a case where the user only wants to report on say 1 of the 3 fields? (see below)

Can anyone tell me how to build the query dynamically?

Thanks

DD

var query =
    from cl in db.tblClaims
    join cs in db.tblCases
        on cl.ref_no equals cs.ref_no
    where cl.claim_status == "Appeal"
        && cl.appeal_date >= Convert.ToDateTime(txtReferedFromDate.Text)
        && cl.appeal_date <= Convert.ToDateTime(txtReferedToDate.Text)
        && cs.referred_from_lho == dlLHO.Text
        && cs.adviser == dlAdviser.Text
    select new
    {
        Ref = cs.ref_no,
        ClaimType = cl.claim_type,
        ClaimStatus = cl.claim_status,
        AppealDate = cl.appeal_date
    };

gvReport.DataSource = query;

Answer 1

You could do something like this:

var query = from cl in db.tblClaims
                        join cs in db.tblCases
                            on cl.ref_no equals cs.ref_no
                        where cl.claim_status == "Appeal"
                        select new
                        {
                            Ref = cs.ref_no,
                            ClaimType = cl.claim_type,
                            ClaimStatus = cl.claim_status,
                            AppealDate = cl.appeal_date,
                            cs.referred_from_lho,
                            cs.adviser
                        };

if(!string.IsNullOrEmpty(txtReferedFromDate.Text) 
   && !string.IsNullOrEmpty(txtReferedToDate.Text))
  query = query.Where(cl => 
                   cl.appeal_date >= Convert.ToDateTime(txtReferedFromDate.Text) 
                && cl.appeal_date <= Convert.ToDateTime(txtReferedToDate.Text));

if(!string.IsNullOrEmpty(dlLHO.Text))
  query = query.Where(cl => cl.referred_from_lho == dlLHO.Text);

if(!string.IsNullOrEmpty(dlAdviser.Text))
  query = query.Where(cl => cl.adviser == dlAdviser.Text);

gvReport.DataSource = 
      query.Select(o => new { o.Ref, o.ClaimType, o.ClaimStatus, o.AppealDate });

This would only use those fields as filters if they had values to filter on, otherwise they wouldn't be used in the query.

Answer 2

We do it by passing in a Criteria object and then build the query by appending the desired criteria using expression trees.

IQueryable<Data.Story> query = ctx.DataContext.Stories;

if (criteria.StoryId != null) // StoryId
    query = query.Where(row => row.StoryId == criteria.StoryId);

if (criteria.CustomerId != null) // CustomerId
    query = query.Where(row => row.Project.CustomerId == criteria.CustomerId);

if (criteria.SortBy != null) // SortBy
    query = query.OrderBy(criteria.SortBy + " " + criteria.SortOrder.Value.ToStringForSql());

if (criteria.MaximumRecords != null) // MaximumRecords
    query = query.Take(criteria.MaximumRecords.Value);

var data = query.Select(row => StoryInfo.FetchStoryInfo(row));

You can also take a look at the Dynamic Linq library at http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Answer 3

Microsoft has created an example library (Linq Dynamic Library) that allows you to construct dynamic querys in Linq, you can see how to use it and download it from this link: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

That being said, you can also declare an IQueriable at the begining of your code, and add where clauses depending on parameters. I was going to write an example, but mattruma beat me to it :)

I prefer the second option, but there are situations where using the Dynamic Library is a better solution.