none
Dynamically insert Where clause RRS feed

  • Question

  • I have a Linq to SQL query as follows that works the way I want:
    var query =
     from ct in dataDC.CountryTargets 
     where ct.CountryName == countrySelected
     group ct by ct.TargetName;
    
    
    However the countrySelected variable will not always be available so I want to make the where filter clause optional.
     
    I thought this would work:
    var query =
     from ct in dataDC.CountryTargets 
     group ct by ct.TargetName;
    
     if (countrySelected != null)
     query = query.Where(x => x.CountryName == countrySelected);
    
    
    but CountryName is not recognised and I get the following error at "x.CountryName" :
    'System.Linq.IGrouping<string,ME.Modules.Country.Data.CountryTarget>' does not contain a definition for 'CountryName' and no extension method 'CountryName' accepting a first argument of type 'System.Linq.IGrouping<string,ME.Modules.Country.Data.CountryTarget>' could be found (are you missing a using directive or an assembly reference?)
    Can anyone tell me what I'm doing wrong?
    Friday, May 27, 2011 12:42 AM

Answers

  • Hi Gary,

    In your second version you are trying to use the where method on the grouped result.

    I think the easiest solution to do what you want is to change the first query slightly:

     

     

    var query =
     from ct in dataDC.CountryTargets 
     where string.IsNullOrEmpty(countrySelected) || ct.CountryName == countrySelected
     group ct by ct.TargetName;
    

     

    The second option is to create your final query in 3 steps

    var query = 
     from ct in dataDC.CountryTargets 
     select ct;
    
     if (countrySelected != null)
      query = query.Where(x => x.CountryName == countrySelected);
    
    var finalquery = 
     from g in query 
     group g by g.TargetName;
    

     

     

     


    Regards, Peter
    • Proposed as answer by Crick3t Friday, May 27, 2011 11:57 AM
    • Marked as answer by Jackie-SunModerator Thursday, June 2, 2011 11:03 AM
    Friday, May 27, 2011 11:15 AM
  • but i recommend  crick3t 's writting. I think his writting is better.

     

    you can also write like this:

     

    var query = 
     from ct in dataDC.CountryTargets 
     select ct;
    
     if (countrySelected != null)
     query = query.Where(x => x.CountryName == countrySelected);
    
    query =query.GroupBy(x=>x.TargetName);
     

     

     


    Best Regards,
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, May 27, 2011 11:33 AM

All replies

  • Hi Gary,

    In your second version you are trying to use the where method on the grouped result.

    I think the easiest solution to do what you want is to change the first query slightly:

     

     

    var query =
     from ct in dataDC.CountryTargets 
     where string.IsNullOrEmpty(countrySelected) || ct.CountryName == countrySelected
     group ct by ct.TargetName;
    

     

    The second option is to create your final query in 3 steps

    var query = 
     from ct in dataDC.CountryTargets 
     select ct;
    
     if (countrySelected != null)
      query = query.Where(x => x.CountryName == countrySelected);
    
    var finalquery = 
     from g in query 
     group g by g.TargetName;
    

     

     

     


    Regards, Peter
    • Proposed as answer by Crick3t Friday, May 27, 2011 11:57 AM
    • Marked as answer by Jackie-SunModerator Thursday, June 2, 2011 11:03 AM
    Friday, May 27, 2011 11:15 AM
  • var query =
     from ct in dataDC.CountryTargets 
     group ct by ct.TargetName;
    
     if (countrySelected != null)
     query = query.Where(x => x.FirstOrDefault().CountryName == countrySelected);
    

    Best Regards,
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, May 27, 2011 11:27 AM
  • but i recommend  crick3t 's writting. I think his writting is better.

     

    you can also write like this:

     

    var query = 
     from ct in dataDC.CountryTargets 
     select ct;
    
     if (countrySelected != null)
     query = query.Where(x => x.CountryName == countrySelected);
    
    query =query.GroupBy(x=>x.TargetName);
     

     

     


    Best Regards,
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, May 27, 2011 11:33 AM
  • Hi Tim,

    In this case if I group by target name and I have different CountryTargets with different Countrynames what the result will be?

    var query =
     from ct in dataDC.CountryTargets 
     group ct by ct.TargetName;
    
     if (countrySelected != null)
     query = query.Where(x => x.FirstOrDefault().CountryName == countrySelected);

    I mean if I am right you select the first CountryTarget from each grouped result and filter it out if it is not the selected country. But this is not the same to select the Country targets where the countryname is the selected one and group them.


    Regards, Peter
    Friday, May 27, 2011 11:50 AM