tags:

views:

115

answers:

4

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;
+3  A: 

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

mattruma
Strictly speaking, you aren't using an actual dynamically-defined expression here, but this is nonetheless the right solution.
Adam Robinson
@Adam Robinson Good point ... I am really building the query by "appending" ... as opposed to do anything "dynamic". Thanks for the clarification!
mattruma
A: 

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.

salgiza
+2  A: 

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.

Nick Craver
thanks for the help, and thanks for the link to the library as well, appreciated
DarkWinter
A: 

like this:

namespace overflow4
{
    class Program
    {
        static void Main(string[] args)
        {
            var orig = new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };

            var newlist = FilterNumbers(orig, true, false, false);

            foreach (int x in newlist)
                Console.WriteLine(x);
            Console.ReadLine();
        }

        private static IEnumerable<int> FilterNumbers(
            List<int> origlist, 
            bool wantMultiplesOf2, 
            bool wantMultiplesOf3, 
            bool wantMultiplesOf5)
        {
            IEnumerable<int> sofar = origlist;

            if (wantMultiplesOf2)
                sofar = sofar.Where(n => n % 2 == 0);

            if (wantMultiplesOf3)
                sofar = sofar.Where(n => n % 3 == 0);

            if (wantMultiplesOf5)
                sofar = sofar.Where(n => n % 5 == 0);

            return sofar;
        }
    }
}
Mike Jacobs